SQL in Warehouses
One mental model, several dialects to mind
Snowflake, BigQuery and Redshift speak near-standard SQL but differ in partitions, clustering, scaling, and function dialects. Portability-aware SQL survives migration.
What you will learn
- Map common warehouse dialect differences
- Understand partitioning/clustering and its SQL consequences
- Write portable SQL that avoids vendor traps
What is this?
Warehouses implement standard SQL with vendor-specific storage, scaling and function sets. Snowflake uses micro-partitions + virtual warehouses; BigQuery is serverless with slot-based pricing and bigint dates; Redshift is Postgres-flavored with sort keys and distribution styles. Most query patterns transfer; the traps are in DDL, partitioning and function names.
Why do you need it?
- A stack migration is a SQL-porting project; knowing dialects keeps it cheap.
- Partitioning/clustering differences turn the same query from a full-scan into a pruned one.
- Cross-vendor code that works on SQLite ALSO works in warehouses — the common denominator is the safe subset.
Mental model
Three restaurants, the same spice rack
Warehouse SQL dialects
Each restaurant uses paprika differently, but the ingredient list overlaps. Know the spice rack (ANSI SQL), learn each restaurant's specials, and you can cook anywhere.
How it works
- Partitioning: Snowflake micro-partitions + clustering keys; BigQuery integer/timestamp partitions + clustering; Redshift sort keys/distribution.
- Scaling: Snowflake virtual warehouses (scale pause/resume); BigQuery slots in reservations; Redshift clusters and spectrum.
- Functions: TO_CHAR vs FORMAT_DATE vs to_char; CURRENT_DATE everywhere; REGEXP_LIKE vs REGEXP_EXTRACT differences.
- DDL: TIME travel (Snowflake), bigquery table decorators/`_PARTITIONTIME`, Redshift sortkeys in CREATE TABLE.
- Portability lite: keep to ANSI, centralize dialect-specific SQL in macros (dbt).
Storage-tuned querying per warehouse.
In production
- A dbt project centralizes dialect variation in macros so models run on BigQuery and Snowflake.
- A migration tool maps TO_CHAR calls from Postgres to BigQuery FORMAT_DATE during a warehouse lift.
- A cost review found a BigQuery query filtering on an unpartitioned top-level field — adding a partition cut cost 40%.
Best practices
- Write ANSI-first SQL; isolate vendor functions in one layer.
- Partition on the hottest filter (usually date) and cluster on co-filters.
- Test queries on all target warehouses in CI when you support multiple.
- Use dbt-style macros for date arithmetic that differs across dialects.
Common mistakes
- Relying on Postgres-specific functions that silently break in BigQuery.
- Querying without a partition filter, scanning the whole warehouse and paying for it.
- Porting a query then 'fixing' per-dialect by hand into a maintenance nightmare.
Common issues
Query works on Postgres, fails on BigQueryFunction or type dialect difference (e.g. ::cast vs CAST, to_char)
Standardize on ANSI forms or route through a portability macro layer.
Snowflake query scans 5TB every timeFilter column isn't a clustering key / no partition pruning
Add a clustering key on the hottest predicate or restructure the warehouse query.
Debugging tips
- When a query is slow in a warehouse, check partition/table scan size in the console first.
- Look up the vendor's function name via the documentation search, not from memory.
- Keep a small dialect crosswalk table in the repo for the functions your team uses.
Interview checkpoint
Quick quiz
Which warehouse is serverless with slot-based pricing and no virtual-warehouse management?
Portable SQL is best written as a common subset of ANSI plus macros for vendor functions.