Recap — Designing ELT for the Modern Warehouse

# Designing ELT for the Modern Warehouse ELT (Extract, Load, Transform) replaced ETL (Extract, Transform, Load) as the modern default. The order of operations matters more than the names suggest. ## The old ETL world ``` [Source] ↓ extract [Staging server with ETL tool: Informatica, Talend] ↓ transform (in the ETL tool) [Data warehouse] ``` The ETL tool was the heavy lifter: - Pulled data from source. - Ran transformations in a separate compute environment. - Loaded transformed data into the warehouse. Why this made sense: - Warehouses were expensive; you didn't want to load raw data. - Transformations in the warehouse were slow (row-oriented MPP DBs were slow at SQL beyond simple queries). - ETL tools provided visual workflows and lineage. ## The new ELT world ``` [Source] ↓ extract & load (mostly raw) [Data warehouse — Snowflake/BigQuery/Redshift] ↓ transform (in SQL via dbt) [Same warehouse — marts layer] ``` The warehouse does the heavy lifting: - Raw data lands in source schemas. - dbt transforms in SQL, materializing intermediate and mart layers. - Same warehouse for all. ## Why ELT won ### Reason 1: Cloud warehouses got cheap Snowflake / BigQuery / Redshift compute scales horizontally. Running transformations in SQL is cost-effective at modern prices. The old "loading raw data is wasteful" argument no longer holds. ### Reason 2: dbt happened dbt made SQL transformations version-controllable, testable, and modular. Suddenly transformations could be code (with all the engineering benefits). ETL tools couldn't compete with the engineering workflow. ### Reason 3: Raw data is valuable Storing raw data lets you re-transform when: - Business logic changes. - Bugs are discovered. - New analyses require different aggregations. With ETL, raw data was discarded after transformation. Re-extracting from source was slow and often impossible (source systems don't retain history). ### Reason 4: Lineage and time-travel Modern warehouses provide time-travel (query as of timestamp). Combined with raw data preservation, you can reconstruct ANY historical state. ### Reason 5: Decoupled team workflows Analysts can work on transformations independently of engineers who manage ingestion. The "modern data team" structure (data engineers + analytics engineers + analysts) emerged from this separation. ## What ELT looks like in practice ``` 1. Fivetran/Airbyte syncs source data to raw schemas in Snowflake. - Source tables → raw.salesforce.accounts, raw.stripe.charges, etc. - Schedule: every 15 minutes or hourly. 2. dbt models transform raw → staging → marts. - models/staging/stg_salesforce__accounts.sql - models/marts/dim_customers.sql - Run: scheduled via Airflow, Prefect, or dbt Cloud. 3. BI tools query the marts. - Looker / Tableau pointed at marts.dim_customers, marts.fct_orders. ``` Three tools, clear separation. Each replaceable independently. ## The minimal modern data stack For a startup or small team: | Layer | Tool | Cost (small scale) | |---|---|---| | Ingestion | Fivetran / Airbyte / Meltano | $200-1000/month | | Storage | Snowflake / BigQuery | $200-500/month | | Transformation | dbt | $0 (dbt-core) or $100/month (dbt Cloud) | | Orchestration | dbt Cloud schedules, GitHub Actions, or Prefect Cloud | $0-200/month | | BI | Metabase / Hex | $0-200/month | Total: $400-2000/month. Affordable for most teams. A decade ago this required enterprise budgets. ## When to deviate from pure ELT ### Heavy transformation before load If source data is enormous and you only need a small slice, transforming during extraction saves bandwidth + storage. Example: pulling 10GB of logs per day but only need a 100MB summary. Filter at extraction time. ### Regulated data Personally Identifiable Information (PII) might need redaction/encryption BEFORE landing in the warehouse for compliance. Transform-then-load. ### Streaming Stream processing transforms data in-flight. Different model from batch ELT. For most batch analytics: pure ELT is the way. ## Common ELT mistakes 1. **Treating ELT as just "load everything then transform".** You still need to think about what data to load and how often. 2. **No staging layer.** Loading raw data and going directly to marts. Skip the cleaning step. 3. **dbt models that re-implement extraction logic.** dbt is for transformations. Extraction belongs in Fivetran/Airbyte. 4. **Hand-rolling extraction when a managed tool would do.** Maintaining custom Salesforce connectors is a tax. 5. **Ignoring ingestion costs.** Fivetran can get expensive at scale; budget early. ## What's NOT changing - **Source systems still matter.** OLTP DBs, SaaS apps, files. ELT doesn't change where data lives. - **Schema design still matters.** Raw → staging → marts requires thought. - **Data quality still matters.** Garbage in is still garbage out, just in a more accessible format. ## Takeaway ELT = load raw, transform in SQL inside the warehouse. Cheaper, more flexible,