Duckdb SQL

DuckDB SQL - Key Differences from PostgreSQL/MySQL

Gotchas

• Integer division truncates: 5/2 = 2. Fix: CAST(numerator AS DOUBLE) / denominator
• DATE_TRUNC returns TIMESTAMP: Cast result if DATE needed: CAST(DATE_TRUNC('month', col) AS DATE)
• INTERVAL syntax: INTERVAL '1' DAY (quoted), NOT INTERVAL 1 DAY
• No DATEADD/DATEDIFF: In standalone queries, use col + INTERVAL '1' DAY and DATE_DIFF('day', start, end) - DuckDB has no DATEADD/DATEDIFF function.
• SUM(NULL) = NULL: Not 0. Use COALESCE(SUM(col), 0) if 0 is needed.
• inf/NaN checks: use isinf(col) and isnan(col), NOT IS_INF/IS_NAN (those are BigQuery function names).
• ROUND precision: If the YML specifies a decimal type like decimal(6,2), cast the FINAL output to match: CAST(ROUND(AVG(col), 2) AS DECIMAL(6,2)). Do NOT cast the input - cast the result after rounding.
• Avoid CURRENT_DATE for date spines - when generating or capping a calendar over historical data, use (SELECT MAX(date_col) FROM source) to anchor to the data's actual date range

Date Parsing

• Non-ISO strings: STRPTIME(col, '%d/%m/%Y')::DATE
• TRY_STRPTIME returns NULL on failure (safe)
• Never CAST(date_str AS DATE) on non-ISO strings

QUALIFY Clause

Filter window function results without a subquery:

SELECT *, ROW_NUMBER() OVER (PARTITION BY group ORDER BY col DESC) AS rn
FROM table
QUALIFY rn <= 10

Date Spines

SELECT UNNEST(GENERATE_SERIES(min_date::DATE, max_date::DATE, INTERVAL '1' DAY)) AS date_day

Always use the primary fact table's max date as endpoint - call get_date_boundaries first.

Type Casting

CAST(x AS INTEGER), CAST(x AS DOUBLE), CAST(x AS VARCHAR), CAST(x AS DATE) TRY_CAST returns NULL on failure.