data-eng-warehouse-patterns

Data Warehouse Patterns

Core Principles

1. ELT over ETL — Load raw data into the warehouse first, then transform using SQL. Modern warehouses have the compute power to handle transformations at scale, eliminating fragile middleware.
2. Layered architecture — Organize data into staging (raw), transformation (cleaned/conformed), and presentation (business-ready) layers. Each layer has clear ownership and SLAs.
3. Immutable raw data — Never modify source data after landing. Treat the raw layer as an append-only audit log. All transformations produce new tables or views downstream.
4. Schema-on-read flexibility — Semi-structured data (JSON, Avro, Parquet) can be loaded without predefined schemas, then parsed and typed during transformation.
5. Cost-aware design — Warehouse compute is elastic but not free. Partition, cluster, and materialize strategically to minimize scan volume and compute spend.

ELT vs ETL Architecture

Modern cloud warehouses favor ELT because transformations run inside the warehouse engine, leveraging massive parallel processing. External ETL tools add latency, maintenance burden, and failure points.

-- ELT pattern: land raw JSON, then transform in-warehouse
-- Step 1: Load raw data into staging
COPY INTO raw.stripe_events
FROM @s3_stage/stripe/
FILE_FORMAT = (TYPE = JSON);

-- Step 2: Transform with SQL into clean layer
CREATE OR REPLACE TABLE cleaned.payments AS
SELECT
    raw:id::STRING                          AS event_id,
    raw:data:object:amount::NUMBER / 100    AS amount_dollars,
    raw:data:object:currency::STRING        AS currency,
    raw:created::TIMESTAMP_NTZ              AS event_timestamp,
    CURRENT_TIMESTAMP()                     AS _loaded_at
FROM raw.stripe_events
WHERE raw:type::STRING = 'charge.succeeded';

See elt-pipeline-patterns for: medallion architecture, incremental loading strategies, staging layer design.

Snowflake Patterns