BigQuery vs Snowflake SQL: One Query, Two Dialects
Both are cloud warehouses with SQL — and the dialects diverge exactly where migration hurts: types, dates, arrays, QUALIFY, LIMIT vs FETCH, and the cost model that should shape your query style.
Quick Answer
- Cost models differ fundamentally: BigQuery bills by bytes scanned (so column pruning and partition filters are money), Snowflake bills by warehouse compute time (so caching and warehouse sizing matter more than scan width).
- Neither is standard SQL: BigQuery uses
GENERATE_DATE_ARRAY, backtick-quoted identifiers, and->>only in JSON functions; Snowflake uses double-quoted identifiers with case folding,DATEADD, and hasQUALIFY. - Both have QUALIFY and window functions — that shared subset means most modern analytical SQL ports with only naming changes.
Syntax Differences That Bite
| Task | BigQuery | Snowflake |
|---|---|---|
| Identifier quoting | `col` | "col" (folds to UPPER) |
| Current timestamp | CURRENT_TIMESTAMP() | CURRENT_TIMESTAMP (no parens) |
| Date add | DATE_ADD(d, INTERVAL 7 DAY) | DATEADD(day, 7, d) |
| Date diff (days) | DATE_DIFF(d2, d1, DAY) | DATEDIFF(day, d1, d2) |
| Date spine | UNNEST(GENERATE_DATE_ARRAY(a, b)) | TABLE(GENERATOR(ROWCOUNT => n)) or a VALUES list with DATEADD |
| String concat | CONCAT(a, b) or a || b | a || b or CONCAT(a, b) |
| Array access | arr[OFFSET(0)], arr[SAFE_OFFSET(0)] | arr[0] (0-based) |
| Array unnest | UNNEST(arr) AS x (correlated allowed) | LATERAL FLATTEN(input => arr) |
| Pivot | PIVOT(SUM(v) FOR k IN (…)) | PIVOT(SUM(v) FOR k IN (…)) (same shape) |
| Limit | LIMIT n | LIMIT n (FETCH FIRST n ROWS also legal) |
| Qualify filter after window | QUALIFY ROW_NUMBER() OVER (…) = 1 | QUALIFY ROW_NUMBER() OVER (…) = 1 (same) |
| Regex extract | REGEXP_EXTRACT(s, r'…') | REGEXP_SUBSTR(s, '…') |
| Approx distinct | APPROX_COUNT_DISTINCT(x) | APPROX_COUNT_DISTINCT(x) (same) |
| Safe divide | SAFE_DIVIDE(a, b) | CASE WHEN b = 0 THEN NULL ELSE a / b END |
The Cost Model Changes How You Write SQL
BigQuery: every query is billed on bytes read. That makes SELECT * on a 2 TB table an expensive habit — prune to the five columns you need. Partitioning and clustering are not just performance features, they are the cost control system: a WHERE event_date = CURRENT_DATE() predicate against a day-partitioned table reads one day, everything else reads all history. Snowflake: you pay for warehouse seconds, so a query that scans a lot but runs fast on a bigger warehouse may cost more than a slower well-tuned query on a small one. The practical difference in SQL style: in BigQuery, aggressively filter and prune; in Snowflake, focus on eliminating repeated scans (cached results, avoid re-running exploration queries) and right-sizing the warehouse.
Type System Personalities
BigQuery is strongly typed with no implicit string-to-number coercion — '42' = 42 is an error, not a match. Snowflake coerces more freely, which is forgiving until it silently compares a VARCHAR with a NUMBER. Both support STRUCT and ARRAY as first-class types; BigQuery's ARRAY<STRUCT> pattern (one row per parent, nested children) is the canonical shape, and Snowflake handles the same data through VARIANT + FLATTEN. When migrating models, the STRUCT vs VARIANT decision usually reshapes more SQL than any function name change.
What Ports Cleanly
The shared core is large: window functions, QUALIFY, CTEs, GROUP BY ROLLUP, PIVOT syntax shape, APPROX_COUNT_DISTINCT, JSON access functions (though with different names). If your SQL sticks to that subset, migration is mostly a find-and-replace of DATEADD/DATE_ADD and aggregation function names. The date function mapping continues in our SQL date functions cheat sheet.
Try It Yourself
Paste a BigQuery query into our SQL generator and ask for the Snowflake version — it rewrites DATEADD/DATE_DIFF naming, quoting style, and the FLATTEN pattern for unnests.