Data Warehousing: Snowflake, BigQuery & Modern ELT

Q: Data warehouse vs data lake vs lakehouse?

Warehouse: structured, SQL-first, modeled tables. Lake: raw files (Parquet/JSON) on object storage — cheap and flexible, historically weak on management (hence "data swamp"). Lakehouse: table formats (Iceberg/Delta) add warehouse semantics — ACID, schema evolution, time travel — on lake storage. The industry is converging on lakehouse storage with warehouse-style engines on top.

Q: How does the warehouse relate to machine learning?

It's usually the feature source: models train on tables the analytics pipeline already cleans and tests. Warehouses increasingly run light ML in-SQL (BigQuery ML, Snowflake Cortex), while serious training exports to Python/Spark — see Feature Engineering and MLOps.

Data Warehousing

A data warehouse is a database built for analysis instead of transactions: it consolidates data from your application databases, SaaS tools, and event streams into one place where analysts can scan billions of rows in seconds. Where PostgreSQL answers "fetch order #1042" in a millisecond, a warehouse answers "revenue by region by month for three years" without breaking a sweat — and without touching production.

The cloud warehouses — Snowflake, BigQuery, Redshift, Databricks SQL — turned what was seven-figure enterprise hardware into pay-per-query infrastructure any startup adopts in an afternoon, and spawned the "modern data stack" (loaders + warehouse + dbt + BI) that most data teams now run.

TL;DR

OLTP vs OLAP: application databases optimize many small reads/writes of single rows; warehouses optimize few huge queries scanning millions of rows. Different engines because different physics.
The physics is columnar storage: values of each column stored together → scan only the 3 columns your query touches, compress them 10×, and vectorize the math.
The modern pipeline is ELT: extract and load raw data first (Fivetran/Airbyte), transform inside the warehouse with SQL (dbt) — not transform-then-load.
Compute is separated from storage in cloud warehouses — scale and pay for them independently; queries are billed by data scanned or compute time.
Model business data as facts and dimensions (star schema) — still the vocabulary, even when wide denormalized tables serve dashboards.
Cost discipline is the operational skill: partition/cluster your tables, avoid SELECT *, and watch scheduled queries — warehouse bills grow silently.

OLTP vs OLAP

Why columnar wins for analytics: SELECT region, SUM(revenue) FROM orders GROUP BY region needs 2 of the table's 40 columns. A row store reads all 40 from disk; a column store reads exactly 2 — already a 20× I/O win before compression (similar values stored adjacently compress dramatically) and vectorized execution stack on top.

The corollary: don't run analytics on your production OLTP database. The queries are brutal on it, the schema fights you, and one analyst's join can take down checkout. The warehouse exists so that never happens.

The Modern Architecture

ELT, not ETL

Old world: transform data on the way in (ETL), because warehouse compute was precious. Cloud world: load raw, transform later — storage is cheap, warehouse SQL is powerful, and keeping the raw data means you can re-derive anything when logic changes. Managed loaders (Fivetran, Airbyte) handle the hundreds of SaaS connectors; Kafka/CDC streams handle realtime feeds; dbt owns the in-warehouse transformation layer; an orchestrator (Airflow, Dagster) schedules the whole graph.

Dimensional Modeling

The classic vocabulary for organizing marts (Kimball's star schema):

Fact tables: events/measurements — order placed, page viewed — with foreign keys to dimensions and numeric measures.
Dimension tables: the entities you slice by — customer, product, date — wide and descriptive.
Slowly changing dimensions (SCD): when a customer moves regions, do you overwrite (type 1) or keep history with validity ranges (type 2)? Getting this wrong silently rewrites the past in reports.

Modern practice is pragmatic: star schemas at the core, plus wide pre-joined "one big table" marts where dashboards need speed over purity. Either way the point stands — model deliberately; a warehouse of raw table copies with no semantic layer is a data swamp with a bigger bill.

Choosing a Platform

The lakehouse convergence is the main architectural trend: open table formats (Apache Iceberg, Delta Lake) store warehouse-grade tables as Parquet on S3/GCS with ACID semantics, and every engine above increasingly reads/writes them. The bet: storage in open formats you own, engines as interchangeable compute — ending per-vendor data gravity.

Honest sizing note: below a few hundred GB of analytical data, DuckDB or even a Postgres read replica may be all the "warehouse" you need. The modern stack scales down poorly on cost and complexity if adopted ceremonially.

Cost and Performance Discipline

Warehouse pricing rewards exactly the practices that also make queries fast:

Partition and cluster big tables by the columns you filter on (usually date). A dashboard hitting yesterday's partition scans gigabytes, not the 10 TB table.
**Never SELECT * on wide tables — columnar billing/scanning is per column touched.
Materialize expensive logic (dbt tables/incremental models) instead of recomputing it in every dashboard refresh.
Audit scheduled queries and BI refreshes — the classic silent bill is a 15-minute refresh of a dashboard nobody opens, scanning a full table each time.
Separate workloads: loaders, transformations, and BI on separate warehouses/slots so an analyst's rogue query doesn't starve the pipeline (and so you can see who spends what).
Watch spill and skew on huge joins — same distributed-query intuitions as query optimization, different engine.

Common Mistakes

Dashboards on the Production Database

Works at 1 GB, then analytics queries start timing out checkout. The moment analysts and the app share an OLTP database, you're one GROUP BY away from an incident — replicate to a warehouse early.

Loading Everything, Modeling Nothing

Fivetran syncing 200 raw tables that analysts query directly produces contradictory metrics and tribal-knowledge SQL. The warehouse's value is the modeled layer — invest in staging/marts (dbt) from the first month.

Ignoring Slowly Changing Dimensions

Overwriting customer.region rewrites three years of regional revenue history retroactively. Decide SCD strategy per dimension before the first "why did last quarter's numbers change?" escalation.

Treating the Warehouse as an Application Backend

Warehouses have seconds-scale latency and per-query costs — the wrong engine behind a user-facing API. For operational analytics endpoints, use ClickHouse-class engines or serve precomputed marts from Redis/Postgres.

No Cost Ownership

Per-query billing plus self-serve access equals exponential bills with no owner. Tag workloads, set per-warehouse budgets/alerts, and review the top-10 most expensive queries monthly — see Cloud Costs.

FAQ

Data warehouse vs data lake vs lakehouse?

Warehouse: structured, SQL-first, modeled tables. Lake: raw files (Parquet/JSON) on object storage — cheap and flexible, historically weak on management (hence "data swamp"). Lakehouse: table formats (Iceberg/Delta) add warehouse semantics — ACID, schema evolution, time travel — on lake storage. The industry is converging on lakehouse storage with warehouse-style engines on top.

When does a company need a warehouse?

Signals: analytics queries hurting the production DB; data spread across app DB + Stripe + CRM + ad platforms that someone joins in spreadsheets; metric definitions varying by who computes them. Typically this bites somewhere between 10 and 100 employees — and the fix (loader + warehouse + dbt + BI) is a quarter's project, not a year's.

Snowflake or BigQuery?

Both excellent; decide by ecosystem and pricing shape. BigQuery if you're on GCP or want zero infrastructure and per-scan billing; Snowflake for multi-cloud, granular compute control, and its data-sharing features. Redshift mainly when deep AWS integration dominates. Migrating between them is painful enough that the modeled layer (dbt) being portable matters.

How does the warehouse relate to machine learning?

It's usually the feature source: models train on tables the analytics pipeline already cleans and tests. Warehouses increasingly run light ML in-SQL (BigQuery ML, Snowflake Cortex), while serious training exports to Python/Spark — see Feature Engineering and MLOps.

What about realtime analytics?

Classic warehouses are batch-first (minutes of freshness via streams/CDC is now routine; Snowflake and BigQuery both ingest streaming). For sub-second, user-facing analytics on live events, purpose-built engines (ClickHouse, Druid, Pinot) fed by Kafka are the right tool — many stacks run both tiers.