Cloud warehouses add features that PostgreSQL lacks, and the database here shares several of them:
- QUALIFY filters on a window function directly, with no CTE:
... QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1. Snowflake, BigQuery, Databricks and DuckDB support it.
- Safe casts return NULL instead of failing:
TRY_CAST in Snowflake, SQL Server and here; SAFE_CAST in BigQuery.
- Arrays: a list in one cell.
UNNEST turns it into rows. BigQuery writes FROM t, UNNEST(t.tags) AS tag; Snowflake uses LATERAL FLATTEN; here, unnest(list) in the SELECT. string_split(s, ',') (BigQuery: SPLIT, Snowflake: SPLIT) makes a list from text.
Some functions take their arguments in a different order: BigQuery writes DATE_TRUNC(order_date, MONTH) and DATE_DIFF(end, start, DAY), the reverse of PostgreSQL. Snowflake follows PostgreSQL for DATE_TRUNC('month', d) and has DATEADD(day, 7, d) like SQL Server.