ELT and the Warehouse
Transform where the data lives
In ELT, loading is simple and transformation happens in warehouse compute. BI benefits: raw is retained, definitions live in SQL, backfills are cheap, and the report layer trusts the model.
What you will learn
- Explain the ELT data flow into and through the warehouse
- Describe how dbt/SQL models become the BI contract
- Use backfill and refactor without re-extracting
What is this?
ELT (Extract, Load, Transform) loads raw data into the warehouse verbatim, then a transformation step — usually SQL and tools like dbt — models it in place using warehouse compute. BI consumes the modeled, tested outputs. Because raw stays, every modeled table is reproducible by re-running SQL, which is the foundation of trustworthy reports.
Why do you need it?
- Raw retention makes any definition change re-embeddable and auditable.
- SQL transformations run on scalable warehouse compute instead of a dedicated ETL box.
- Modeling-as-code brings review, tests and versioning to the BI contract.
- Backfills and reprocessing become a command, not a multi-day extraction job.
Mental model
Stocking the pantry raw, cooking on site
ELT
ELT brings ingredients straight off the truck (raw load), then cooks in the restaurant kitchen (warehouse) where the ovens can handle scale. You never re-order ingredients to cook a refund extra plate — the pantry's raw is enough.
How it works
- Ingestion lands source snapshots/events into the bronze layer unchanged.
- Staging models lightly normalize; core models clean, join and define entities.
- Mart/serving models build the facts, dimensions and semantic views BI reads.
- Tests (uniqueness, non-null, freshness, known values) gate each model's promotion.
- Orchestration schedules runs and retries; failures stop the gold pipeline, not raw loading.
ELT through three model layers into BI.
In production
- Fivetran/Airbyte load SaaS exports to bronze; dbt builds silver and gold; Power BI reads gold views; a nightly pipeline refresh guarantees 6am dashboards.
- A definition change to 'revenue' is a one-line dbt edit, reviewed in a PR, tested against fixture values, and backfilled by rerunning the month.
- A failed gold build never touches bronze: raw arrived, modeling waits, report consumers get an alert — not wrong data.
Best practices
- Keep the bronze layer append-only and immutable.
- Make each dbt/model stage tested and idempotent (full-refresh safe).
- Version models in git with review and CI running the tests.
- Backfill in stages: gold from silver, silver from bronze, verifying counts each step.
Common mistakes
- Folding raw into model code so backfills need source re-extraction.
- Modeling directly from source API calls in the tool instead of through the warehouse.
- No tests on gold models, so a bad deploy ships wrong numbers silently.
- Scheduling gold to run while silver is still mid-write — reading partial data.
Common issues
Dashboard sits at yesterday's gold stateModel run scheduled after its upstream finished but failed silently
Fail the pipeline loudly on any model failure; alert on freshness, not just errors.
Backfill returns different numbersNon-idempotent model (append) or source re-sent rows
Full-refresh model semantics; add idempotency tests to CI.
Debugging tips
- Reproduce any model by dropping it and rerunning from the layer below.
- Diff bronze-collected counts vs model output to locate where a row vanished.
- Review the PR that changed a model when a headline number moves — the diff is the suspect.
Interview checkpoint
Quick quiz
Why does ELT keep raw data in the warehouse?
In ELT, model tests run (or should run) before gold reaches dashboard consumers.