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