Warehouses & Lakehouses

Snowflake

Strengths:

• Best-in-class SQL ergonomics — QUALIFY, LATERAL FLATTEN, MATCH_RECOGNIZE.
• Separation of storage and compute (multiple "warehouses" sized independently).
• Time travel — query data as of any point in the retention window. Zero-copy clones.
• Strong JSON / VARIANT handling.
• Dynamic Tables for declarative incremental materialization.
• Snowpark for Python/Scala compute on the warehouse.

Pricing: per-second of warehouse compute (Standard / Enterprise / Business Critical tiers) + storage. Compute dominates cost — pause unused warehouses.

Partitioning: automatic (micro-partitions). User-controlled via cluster_by for high-cardinality skewed access patterns.

Watch out for: cost runaway from over-provisioned warehouses. Use auto-suspend and resource monitors.

BigQuery

Strengths:

• Serverless — no warehouse sizing. You query, Google scales compute.
• Strong UNNEST + nested/repeated fields — great for event data.
• Native integration with GCP services (Pub/Sub, Dataflow, Vertex AI).
• BigQuery ML for in-warehouse ML.
• Materialized views with automatic incremental refresh.
• BigQuery Omni — query across clouds.

Pricing: two models. On-demand: $5-7/TB of data scanned. Editions: flat-rate slots/hour. On-demand bills you for column scans — strong partition/cluster filters matter a lot for cost.

Partitioning: by date or integer range (1 column). Clustering: up to 4 columns within a partition.

Watch out for: on-demand cost from unfiltered SELECT *. Use INFORMATION_SCHEMA to monitor heavy queries.

Redshift

Strengths:

• Original cloud warehouse — mature, AWS-native.
• Redshift Spectrum — query S3 data without loading.
• RA3 nodes separate storage from compute.
• Strong with AWS-stack integration.

Pricing: per-node-hour for provisioned, or serverless. Cheaper than Snowflake at sustained high utilization; more operational overhead.

Partitioning: distribution keys determine how rows shard across nodes. Sort keys determine row order within a node. Both materially affect performance — get them wrong and queries crawl.

Watch out for: VACUUM and ANALYZE — manual maintenance unlike Snowflake/BigQuery. Newer Redshift Serverless reduces this.

Databricks

Strengths:

• Native lakehouse — Delta Lake as the table format, query with Spark SQL or Photon engine.
• Strong for ML workflows — MLflow, Unity Catalog, model serving in one place.
• Notebooks-first dev experience.
• Multi-language (Python / SQL / Scala / R).
• Open data — Delta tables are usable from outside Databricks.

Pricing: DBUs (Databricks Units) × cluster size × time. Compute-heavy.

Partitioning: partition columns + Z-order (correlated clustering across columns).

Watch out for: can be more complex operationally than pure SQL warehouses. Best-fit for teams doing both analytics and ML.

ClickHouse

OLAP database optimized for fast analytics on huge tables. Common for:

• Customer-facing dashboards (sub-second response on billions of rows).
• Observability platforms (logs, metrics).
• Inference logs / event analytics at AI infra companies.

Strengths: extreme scan speed, real-time ingest, materialized views for incremental refresh. Weaknesses: quirky SQL dialect (until recently), historically weaker joins (improving).

DuckDB

OLAP on your laptop. Single-process, columnar, full modern SQL (CTEs, window functions, QUALIFY, list/struct types). Fast.

Common uses: local development against warehouse-style data, embedded analytics, "smaller-than-Snowflake" workloads. dbt-duckdb is real.

Mention as ecosystem awareness, not as a primary warehouse choice.

Postgres as warehouse

Works at small/medium scale. Add citus for sharding or use a managed offering. Useful when:

• You're early-stage and don't want a separate warehouse yet.
• Data volume is GBs, not TBs.
• You want operational and analytical workloads in one place (with care).

Limits: row-oriented storage is slow for warehouse-style queries past medium scale. Migrate to a real warehouse before pain hits.

Iceberg / Delta / Hudi — table formats

Open table formats over object storage. The "lakehouse" foundation.

What they all give you:

• ACID transactions on object storage.
• Time travel (query past versions).
• Schema evolution.
• Partition evolution (Iceberg specifically).
• Multi-engine readability — write once, query from many tools.

Be ready to talk about Iceberg specifically. It's becoming the default open format, and major warehouses now read/write it.

Partitioning & clustering — the universal pattern

Every modern warehouse uses some form of partitioning + clustering for performance. The pattern:

• Partition by a column with low-cardinality "natural" buckets — usually date.
• Cluster / sort / Z-order by higher-cardinality columns frequently used in filters or joins.
• Always filter on the partition key in your WHERE clause — that's the biggest cost lever.

Cost models — know which is which

• Compute-time (Snowflake, Databricks) — pay for warehouse-seconds. Lever: pause unused warehouses; right-size.
• Data-scanned (BigQuery on-demand) — pay per TB queried. Lever: partition filters, SELECT only needed columns.
• Slots / flat-rate (BigQuery editions, Redshift) — pay for reserved capacity. Lever: workload management to balance concurrent queries.
• Storage — typically cheap, but watch for "infinite retention" + zero-copy clones piling up.

For interview answers: "My approach to cost: monitor query history, identify the top expensive queries, fix those first. Often a handful of queries account for most of the bill. Then it's partition filters, query rewrites, and right-sizing compute."

How to pick (interview-grade answer)

✓ Check yourself

Answer from memory before opening each one.

1. The same query, a date filter on a partitioned table selecting two columns, runs on BigQuery on demand and on Snowflake. How is each billed, and which lever matters most on each?

   Show answer

   BigQuery on demand bills bytes scanned, so partition filters and selecting only needed columns cut cost. Snowflake bills warehouse-seconds, so right-sizing and auto-suspend matter. A partition filter sets the BigQuery bill directly; on Snowflake it only helps by shortening the run. See cost models.

2. A Snowflake bill doubles in a month while query volume is flat. Where do you look first?

   Show answer

   At warehouse configuration and usage: oversized warehouses, missing auto-suspend, and idle warehouses left running. Then rank the most expensive queries in the query history, since a handful usually drive the bill. Resource monitors cap the damage. See Snowflake and cost models.

3. A large join on Redshift crawls even though the cluster is idle. Which physical design choices do you check?

   Show answer

   The distribution key, which decides how rows are spread across nodes, and the sort key, which decides row order within a node. A poor distribution forces data movement for the join. Also confirm VACUUM and ANALYZE have run. See Redshift.

4. A product needs customer-facing dashboards that answer in under a second over billions of inference logs. Which system class fits, and what do you give up?

   Show answer

   An OLAP database such as ClickHouse, for scan speed and real-time ingest. You give up some SQL breadth and join performance, and you run another system. See ClickHouse.

5. What does an Iceberg table give you that a folder of Parquet files in object storage does not?

   Show answer

   ACID transactions, time travel, schema and partition evolution, and a metadata layer that several engines can read, so one copy serves many tools. A bare folder has none of that. See table formats.

6. A five-person team that knows only Postgres has 200 GB of data and asks which warehouse to buy. What do you recommend, and what would change your answer?

   Show answer

   Stay on Postgres for now: the volume is small, the team knows it, and switching has real cost. Migrate before queries slow down, or sooner if workloads need separation, ML sits beside analytics (Databricks), or the company is already on one cloud. See how to pick and Postgres.

Takeaway

• The billing model sets the optimisation lever: warehouse-seconds on Snowflake and Databricks, bytes scanned on BigQuery on demand, reserved capacity on slots and Redshift.
• Every warehouse partitions on a low-cardinality column, usually date, and clusters on filter columns; filtering on the partition key is the largest cost lever.
• Snowflake wins on SQL ergonomics and operations, BigQuery on serverless simplicity, Databricks where ML shares the platform, and Redshift where AWS integration dominates.
• ClickHouse serves sub-second customer-facing analytics, DuckDB is a single-node laptop engine, and Postgres works at gigabyte scale.
• Open table formats, Iceberg above all, add ACID, time travel and schema evolution to object storage and let several engines read one copy.
• Pick the warehouse the team already knows unless there is a concrete reason, because switching cost is real.

With the platform chosen, the next chapter lists the SQL patterns you will be asked to write on it. → SQL Pattern Menu

Further reading

• Micro-partitions & Data Clustering — Snowflake documentation, accessed 2026. How automatic partitioning and clustering work.
• BigQuery pricing — Google Cloud, accessed 2026. On-demand and capacity models, with current rates.
• Introduction to partitioned tables — Google Cloud documentation, accessed 2026. Partition types and how pruning reduces bytes scanned.
• Distribution styles — Amazon Redshift documentation, accessed 2026. How distribution and sort keys affect joins.
• What is Delta Lake in Databricks? — Databricks documentation, accessed 2026. The table format behind the lakehouse.
• Evolution — Apache Iceberg documentation, accessed 2026. Schema and partition evolution without rewriting data.