Warehouses on the Cloud

• Describe how MPP + columnar storage serves SQL at warehouse scale
• Compare virtual-warehouse (Snowflake), cluster (Redshift) and serverless (BigQuery) compute
• Tune warehouse sizing and auto-scaling for cost and concurrency

• Concurrency and scale: BI + ML + ad-hoc queries share one governed SQL endpoint without a big fixed server.
• Elastic cost: scale to hundreds of nodes for a report, scale to zero after.
• Pause/resume, cloning, time-travel and cost allocation are product features, not scripts.
• One-version-of-truth: the warehouse is where model outputs, raw analytics and governed views converge.

On moving day you want 10 trucks; most weeks you want none. Warehouses meter compute the same way — rent 512 nodes for the monthly rebuild, pay for a single node at 3am.

• Ingest → storage: columnar tables in the vendor format (or open formats on object storage).
• MPP execution: SQL compiler splits work into per-partition tasks on independently scheduled workers.
• Elastic compute: virtual warehouse size (XS..6XL) controls worker count; auto-suspend drops it when idle.
• Concurrency: separate warehouses per workload (ETL, BI, ad-hoc) isolate consumption and cost.
• Time travel / cloning: query or clone any historical version without re-ETL.
• Governance: row/column policies, masking, and cost-per-warehouse visibility as first-class.

• A retail company isolates a nightly ETL warehouse (100-node) from a BI warehouse (4-node) so a bad query can't starve dashboards.
• An analytics team uses Snowflake time-travel to debug a fat-fingered DELETE and restore data to the pre-update snapshot in minutes.
• A startup runs BigQuery queries priced by slot-hour, letting ML teams and finance share one governed dataset with budget caps.

• Right-size warehouses by workload — small steady, large for heavy builds — and let auto-suspend do the rest.
• Refine schemas: cluster/sort keys (Redshift), clustering in BigQuery/Snowflake where supported.
• Use separate warehouses per workload to control concurrency and chargeback.
• Turn on query cost attribution and monitor the top spenders weekly.

• One giant warehouse for everything, so one analyst's cartesian join delays finance's month-end.
• Forgetting sort/cluster keys, so scans read entire tables for slice predicates.
• Paying for an 8XL warehouse that a 2XL with better SQL handles.
• No query optimizer review — ten near-identical reports re-scan the same table ten times.

• Slow but cheap query → user-scaled compute is the fix; slow AND expensive → fix the query or clustering.
• Concurrency errors: profile which warehouse is saturated and move the offending workload off it.
• Cost spike after 'optimization': compare execution profiles (partition pruning vs full scans).
• Data 'changed' after reopen — verify you're querying the active warehouse and not a stale clone.