Cloud data warehouses

The first lesson compared warehouses with app databases. Cloud warehouses run that idea as a service; Snowflake, Google BigQuery and Amazon Redshift are well-known examples. They differ in the details and names, but a handful of ideas show up in most of them. The examples below are labelled with the style they follow; check your warehouse's docs for its exact syntax and limits.

Storage and compute are separate. The data sits in the cloud provider's object storage in a compressed, column-by-column format, and compute clusters read it when queries run. That means you can grow storage without paying for more compute, run several clusters against the same tables without copying them, and pay for compute only while it's working.

In Snowflake a compute cluster is called a virtual warehouse, and you pick its size. A bigger size gives one heavy query more power (scaling up); more clusters let more queries run at the same time (scaling out). Give jobs their own compute so the nightly load doesn't slow the 9 a.m. dashboards, and let idle compute suspend itself so you're not paying for it to wait. BigQuery is serverless instead: you don't size anything, and you pay either for the data each query scans or for reserved capacity.

Loading starts in object storage. Files land in a bucket (S3, Google Cloud Storage, Azure Blob Storage), and a stage (or external location) tells the warehouse where they are, how to authenticate and what format they're in. A bulk load command (COPY INTO in Snowflake, COPY in Redshift, a load job or LOAD DATA in BigQuery) then reads many files in parallel, far faster than inserting rows one at a time.

-- Snowflake-style; other warehouses spell this differently
CREATE STAGE raw_events_stage
  URL = 's3://example-bucket/events/'
  STORAGE_INTEGRATION = example_s3_access
  FILE_FORMAT = (TYPE = 'JSON');

CREATE TABLE raw.events (payload VARIANT);

COPY INTO raw.events
FROM @raw_events_stage
PATTERN = '.*2024-10-.*[.]json';

Rerunning a load is where duplicates come from. Some warehouses remember which files a table has already loaded and skip them for a while; others load whatever you point them at. Either way, make the job safe to rerun yourself, as in the idempotent jobs lesson.

Raw events often arrive as JSON whose fields change over time. Rather than design every column up front, load each event into a single semi-structured column (VARIANT in Snowflake, JSON in BigQuery, SUPER in Redshift) and pull fields out with a path when you query. Missing fields come back as NULL, not an error. The sample database here is SQLite, whose json_extract(payload, '$.device.os') does the same job as Snowflake's payload:device.os or BigQuery's JSON_VALUE(payload, '$.device.os'):

SELECT id,
       json_extract(payload, '$.type') AS type,
       json_extract(payload, '$.device.os') AS os
FROM events
ORDER BY id;

Once a field is used everywhere, promote it to a typed column in a staging model, so every query doesn't re-parse JSON and the type is checked once.

Time travel lets you query a table as it was at an earlier moment, and bring back a dropped table, within a retention window that depends on the warehouse and your settings. A zero-copy clone makes a new table, schema or database that shares the original's storage, so it's instant and you pay only for what changes afterwards. Together they make risky work safer: clone a table before a backfill, give a test environment real-sized data, or compare today's numbers with yesterday's.

-- Snowflake-style
SELECT COUNT(*) FROM orders AT (OFFSET => -60*60);   -- the table as it was an hour ago
CREATE TABLE orders_before_fix CLONE orders;          -- a zero-copy clone
UNDROP TABLE orders;                                  -- bring back a dropped table

-- BigQuery-style
SELECT COUNT(*) FROM shop.orders
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

Access is managed with roles. Privileges (use this compute, read this schema, write that table) are granted to roles, and roles to people and service accounts. A common split: a loader role that can write only the raw layer, a transformer role that reads raw and writes the marts, and analyst roles that read the marts. Pipelines run as service accounts, never as someone's personal login. For sensitive columns, masking and row-level policies show each role only what it should see.

-- Snowflake-style: privileges go to roles, roles go to people and service accounts
CREATE ROLE analyst;
GRANT USAGE ON WAREHOUSE reporting_wh TO ROLE analyst;
GRANT USAGE ON DATABASE shop TO ROLE analyst;
GRANT USAGE ON SCHEMA shop.marts TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA shop.marts TO ROLE analyst;
GRANT ROLE analyst TO USER ana;