ETL / ELT
Intermediate 13 min read ETL / ELT

ELT Architectures

One shape for today, a migration path for tomorrow

ELT architectures range from all-in-warehouse to hybrid lake + warehouse. The shape you choose — where raw lands, who transforms, where marts live — decides governance, cost, and recovery.

ELTarchitectureraw landinghybridlake + warehousetransforms
Prerequisites: ETL / ELT Pipeline Design DAG Design
Before we start

What you will learn

  • Sketch all-in-warehouse vs hybrid-lake ELT
  • Explain where governance lives in each shape
  • Choose a shape from SLOs, cost, and ecosystem
Introduction

What is this?

ELT architectures answer: where does raw land, what engine transforms, and where do marts live? All-in-warehouse keeps everything in the warehouse (simple, ACID, but storage-expensive). Hybrid puts raw in a lake and models in a lakehouse/warehouse tier (cheap retention, open formats, best for scale). The deciding variables: retention, cost envelope, tooling ecosystem, and team skill.

Motivation

Why do you need it?

  • Choosing the shape is choosing your governance and recovery story for the next two years.
  • All-in-warehouse is right for many teams; hybrid earns its complexity only at scale.
  • A well-chosen shape survives growth without a wholesale replatform.
Think about it

Mental model

Imagine

One building for everything vs a warehouse next to the store

Think of

All-in-warehouse vs hybrid

A single building (all-in) is simple to walk around; when inventory (raw) grows, a separate cheap warehouse + a storefront (modeled marts) keeps the store clean and the storage bill low.

Mechanics

How it works

  • All-in-warehouse: source -> staging tables -> modeled transformations ((maybe) dbt) -> marts, all inside one warehouse engine.
  • Hybrid lake+warehouse: source -> raw lake files -> table-format zone -> read model from the lake while the warehouse queries it.
  • Both keep transformations in SQL and versioned; the difference is where raw bytes and compute live.
  • Governance mirrors shape: all-in = warehouse eg grants and views; hybrid = catalog + storage ACL + view masking.
  • Migration path: start all-in-warehouse, add a raw-lake tier when storage/dollar or open-format/fidelity needs arrive.

Hybrid: lake for raw, lakehouse/warehouse for modeled.

Sources
Raw lake
Table-format zone
Modeled marts
BI / ML
Hands-on

Examples

Two shapes, same question

Two shapes, same question
# All-in-warehouse
raw.orders      -- staged copy
fct_order_lines -- modeled SQL
marts.revenue   -- published
 
# Hybrid
lake/raw/orders/*.parquet      -- raw fidelity, cheap retention
iceberg.orders                 -- table-format zone for SQL
marts.revenue (view over it)   -- same dashboard surface

Dashboards read 'marts.revenue' either way; the shape only changes where bytes and compute live.

Real world

In production

  • A 40-analyst shop runs all-in-warehouse: simple, few parts, one team.
  • A scale-out platform lands raw to S3 first and models from the lake so retention is years at one cost.
  • A regulated finance org keeps raw in a lake with full audit (immutable) while the warehouse models the views.
Guidelines

Best practices

  • Start simple (all-in-warehouse) and migrate layers only when a measured driver (retention cost, open formats) bites.
  • Keep a single contract for raw: same naming/partition convention whether it lives in warehouse or lake.
  • Prefer 'one transformation layer' (SQL/dbt) shared across shapes so moving tiers doesn't re-platform your code.
Watch out

Common mistakes

  • Buying a hybrid stack 'for the future' while the team is still learning warehouse basics.
  • Duplicating raw into lake AND warehouse, paying twice and drifting.
  • Changing shape without migrating raw/replays, leaving history split across old and new zones.
When things break

Debugging tips

  • A 'missing history' bug after a shape migration is usually an incomplete raw backfill into the new zone.
  • When BI reads hybrids, check the catalog pointer per table — two layers can point at different versions.
  • Cost review: compare raw retention across warehouse vs lake tiers to justify the shape.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

All-in-warehouse ELT keeps raw retention inside the warehouse, which can become the dominant cost driver.

2

The main driver for a hybrid lake+warehouse ELT is usually…

Go deeper

Official documentation

BigQuery ELT patterns
docs
Snowflake ELT best practices
docs