08.1 ETL vs ELT in Databricks
Architect goal: understand not only how to move data, but where transformation should happen, who owns each layer, how data quality and governance are enforced, and how the design behaves at enterprise scale.
ETL and ELT are not competing products. They are data-processing strategies.
In Databricks, the lakehouse usually becomes the scalable transformation and storage platform, which makes ELT a natural default for many workloads. ETL remains valuable when transformation must happen before landing data, when source systems require protection, when data minimization is mandatory, or when an external processing engine is part of the architecture.
1. The Core Idea
ETL
Extract → Transform → Load
Data is transformed before it is loaded into the target analytical store.
Typical flow:
Source
↓
Extract
↓
Transform
↓
Load
↓
Target
Example:
SAP S/4HANA
↓
CDC / extraction
↓
Transformation engine
↓
Curated warehouse
ELT
Extract → Load → Transform
Raw or lightly processed data is loaded first. Transformation happens inside the analytical platform.
Source
↓
Extract
↓
Landing / Bronze
↓
Transform
↓
Silver
↓
Gold
This maps naturally to a Databricks lakehouse.
2. Why Databricks Changes the ETL vs ELT Decision
A traditional warehouse often made transformation capacity a scarce resource.
A lakehouse separates:
- storage
- compute
- processing
- governance
- consumption
That changes the architecture.
A common Databricks pattern is:
┌───────────────────────┐
│ Source Systems │
│ SAP / CRM / APIs │
└──────────┬────────────┘
│
▼
┌───────────────────────┐
│ Bronze │
│ Raw / replayable │
└──────────┬────────────┘
│
quality + standardize
│
▼
┌───────────────────────┐
│ Silver │
│ Conformed / trusted │
└──────────┬────────────┘
│
business logic
│
▼
┌───────────────────────┐
│ Gold │
│ Business products │
└──────────┬────────────┘
│
┌────────────┴────────────┐
▼ ▼
BI / SQL / SAC ML / AI / APIs
The important architectural distinction is:
Landing raw data does not mean loading bad data into production consumption layers.
Bronze is a controlled persistence boundary. Silver and Gold remain governed consumption-ready layers.
3. ETL vs ELT: Side-by-Side
| Dimension | ETL | ELT |
|---|---|---|
| Transformation location | Before target | Inside target platform |
| Raw data retention | Often limited | Usually strong |
| Replayability | Depends on raw retention | Strong |
| Compute model | Transformation engine | Lakehouse compute |
| Scaling | Engine dependent | Spark/SQL/serverless/Photon dependent |
| Schema flexibility | Often stricter | More flexible |
| Data science access | May require copies | Raw + curated data available |
| Governance | Depends on architecture | Can be centralized with Unity Catalog |
| Best fit | Pre-processing / minimization | Lakehouse analytics |
| Typical Databricks pattern | Selective | Default for many workloads |
Do not turn this table into a simplistic rule that “ELT is always better.”
Architecture depends on:
- source limitations
- regulatory requirements
- data sensitivity
- latency
- transformation complexity
- cost
- ownership
- target consumers
- network topology
4. ETL in a Databricks Architecture
Databricks can still participate in ETL.
For example:
SAP
│
│ extract
▼
Databricks compute
│
│ transform
▼
Delta target
This is useful when the raw source data should not be persisted in the lakehouse.
Example
Suppose a source contains:
- customer name
- phone
- national identifier
- financial transactions
A regulatory requirement may prohibit storing the national identifier in the lake.
An ETL-oriented pipeline could:
- extract source data
- remove restricted fields
- tokenize or hash required identifiers
- validate records
- write only approved attributes to Bronze/Silver
The transformation therefore becomes a data minimization control, not merely a technical step.
5. ELT in a Databricks Architecture
A common lakehouse pattern is:
EXTRACT
│
▼
┌──────────────┐
│ Bronze │
│ Raw / CDC │
└──────┬───────┘
│
▼
┌──────────────┐
│ Silver │
│ Quality + │
│ Conformance │
└──────┬───────┘
│
▼
┌──────────────┐
│ Gold │
│ Business │
│ Products │
└──────────────┘
The key benefit is separation of ingestion and transformation.
If a Silver rule changes, you can often recompute it from governed Bronze data instead of extracting the source again.
That is one of the strongest architectural arguments for ELT.
6. Bronze Is Not a Dumping Ground
A common beginner mistake is:
“Bronze means we can put anything there.”
Production Bronze should still have engineering standards.
At minimum define:
- ingestion timestamp
- source system
- source object
- batch/run identifier
- record identifier
- ingestion status
- schema strategy
- checkpoint strategy where applicable
- retention policy
- access policy
For CDC pipelines also consider:
- operation type
- source commit timestamp
- sequence/version
- delete indicator
- source transaction identifier
Example:
sales_order
├── business columns
├── _ingest_ts
├── _source_system
├── _source_object
├── _run_id
├── _operation
└── _source_sequence
These metadata columns become extremely valuable during troubleshooting and replay.
7. Silver Is the Contract Layer
Silver should normally answer:
“Can another engineering team trust this dataset as a reusable enterprise input?”
Typical Silver responsibilities:
- schema normalization
- type casting
- deduplication
- CDC application
- reference-data enrichment
- standard code mapping
- null handling
- data quality rules
- business key standardization
- late-arriving data handling
- record survivorship
For SAP data, Silver is often where technical source structures become reusable enterprise structures.
Example:
S/4HANA
↓
raw extraction
↓
Bronze
↓
field/type normalization
↓
code harmonization
↓
CDC handling
↓
Silver
8. Gold Is a Data Product Layer
Gold should not simply be “Silver with fewer columns.”
Gold represents business-ready products.
Examples:
- finance revenue mart
- procurement spend mart
- customer 360
- flight operations KPI mart
- supply-chain inventory mart
A Gold data product should define:
- business owner
- technical owner
- grain
- dimensions
- measures
- refresh SLA
- quality SLA
- security classification
- access groups
- lineage
- consumption patterns
This is where ETL/ELT becomes an enterprise data architecture problem.
9. Transformation Ownership
A scalable organization should explicitly decide who owns transformation logic.
Pattern A — Central engineering
Sources → Data Engineering → Gold → BI
Good for:
- strict standards
- centralized teams
- regulated environments
Pattern B — Domain-owned data products
Platform Team
│
┌────────────┼────────────┐
▼ ▼ ▼
Finance Sales Procurement
product product product
Good for:
- data mesh
- domain autonomy
- large enterprises
Pattern C — Hybrid
Central platform team owns:
- governance
- security
- infrastructure
- standards
- observability
Domain teams own:
- transformation
- business semantics
- data products
- quality rules
This is often the most practical enterprise model.
10. ETL vs ELT Decision Framework
Use ELT when:
- raw data can legally be retained
- replayability is valuable
- transformations are compute-intensive
- multiple consumers need the same source
- data science needs raw history
- lakehouse compute is available
- governance can be applied at the platform layer
Use ETL when:
- sensitive data must be removed before persistence
- source extraction needs aggressive minimization
- target format must be produced before landing
- source data cannot legally or operationally be retained
- an existing enterprise transformation engine must remain in the architecture
Use hybrid ETL + ELT when:
Source
│
├── pre-processing / masking
│
▼
Bronze
│
├── lakehouse transformation
▼
Silver
│
▼
Gold
This is often the best enterprise answer.
11. Incremental Processing
Do not build an enterprise pipeline around repeated full loads unless the data volume is genuinely small.
Prefer:
Initial Load
+
Incremental Changes
+
Periodic Reconciliation
Possible incremental mechanisms include:
- source CDC
- source change timestamps
- sequence numbers
- file arrival
- Auto Loader
- Delta change tracking patterns
- source-specific connectors
A robust design separates:
initialization from steady-state processing.
12. Idempotency
An enterprise pipeline should tolerate retries.
Suppose a pipeline processes:
Batch 1042
and the compute job fails after writing part of the output.
A retry should not create duplicate business records.
Design for:
same input + same processing contract
↓
deterministic result
Common mechanisms:
- MERGE
- deterministic business keys
- batch identifiers
- checkpointing
- transactionally committed Delta writes
- deduplication rules
Idempotency is one of the most important differences between a demo pipeline and a production pipeline.
13. Data Quality Belongs in the Pipeline
A production pipeline should define quality controls at the appropriate layer.
Bronze
Check ingestion integrity:
- file readability
- schema compatibility
- source metadata
- duplicate ingestion
- malformed records
Silver
Check data correctness:
- required fields
- referential integrity
- valid domains
- duplicate business keys
- timestamp validity
- CDC consistency
Gold
Check business semantics:
- KPI reconciliation
- aggregate consistency
- financial balancing
- expected freshness
- metric definitions
Think of quality as a flow:
Ingestion Quality
↓
Structural Quality
↓
Business Quality
↓
Consumption Quality
14. Data Contracts
A mature pipeline should have an explicit contract between producer and consumer.
A contract can define:
Dataset
├── Owner
├── Grain
├── Schema
├── Keys
├── Refresh SLA
├── Quality SLA
├── Security classification
├── Retention
├── Change policy
└── Contact / escalation
Without a contract, downstream consumers become dependent on undocumented implementation details.
15. Failure Isolation
Avoid architectures where one source failure stops every downstream product.
Prefer:
SAP Finance ────────┐
SAP Procurement ────┼──→ Bronze
CRM ────────────────┘
│
independent
transformations
│
┌───────────┼───────────┐
▼ ▼ ▼
Finance Spend Customer
product product 360
A failure in CRM should not automatically prevent Finance processing.
This is an important architectural property of domain-oriented pipelines.
16. Performance Architecture
The transformation layer should be designed with Spark and Delta behavior in mind.
Important considerations:
Avoid unnecessary full scans
Use incremental processing whenever possible.
Avoid excessive small files
Small files increase metadata and scheduling overhead.
Reduce unnecessary shuffles
Watch:
- large joins
- aggregations
- distinct
- repartition
- window operations
Select only required columns
Projection pruning reduces I/O and memory pressure.
Use appropriate join strategies
For small dimensions, a broadcast strategy may be appropriate.
For large joins, evaluate:
- partitioning
- statistics
- data layout
- skew
- AQE
- Photon
Performance engineering is covered in depth later in 14. Performance Engineering.
17. Cost Architecture
The cheapest pipeline is not necessarily the one with the fewest compute minutes.
Consider the total cost:
Total Cost
=
Compute
+
Storage
+
Data Transfer
+
Source Load
+
Operational Support
+
Failure / Replay Cost
For example, repeatedly extracting 5 TB from SAP because Bronze was not retained may cost more than storing a governed Bronze layer.
Therefore:
Architecture should optimize total platform economics, not only cluster runtime.
18. Governance Architecture
ETL/ELT does not replace governance.
In Databricks, the governance layer should cover:
- identity
- catalog
- schema
- tables
- volumes
- storage
- lineage
- classification
- audit
- row-level security
- column masking
- data sharing
A useful mental model is:
Unity Catalog
│
┌───────────┼───────────┐
▼ ▼ ▼
Access Lineage Audit
│ │ │
└───────────┼───────────┘
▼
Governed pipelines
The pipeline is therefore not an isolated Spark application. It is part of a governed data platform.
19. SAP → Databricks Example
For your SAP/BW background, consider:
S/4HANA
│
├── CDC / extraction
▼
Landing
│
▼
Bronze
│
├── preserve source semantics
├── technical metadata
└── replay capability
│
▼
Silver
│
├── harmonize company codes
├── normalize currencies
├── apply CDC
├── validate master data
└── standardize business keys
│
▼
Gold
│
├── Finance
├── Procurement
└── Controlling
│
├──────────────┬───────────────┐
▼ ▼ ▼
Databricks SQL SAC / BI ML / AI
This is conceptually similar to the evolution from source-aligned BW layers toward reusable enterprise semantic/data-product layers, but the implementation model is different.
20. Anti-Patterns
Anti-pattern 1: Transform everything before landing
Problem:
- no raw replay
- difficult reprocessing
- tightly coupled extraction and transformation
Anti-pattern 2: Keep everything forever
Problem:
- uncontrolled storage
- compliance risk
- unnecessary cost
Retention must be intentional.
Anti-pattern 3: Bronze becomes a production consumption layer
Problem:
- unstable schema
- poor business semantics
- downstream coupling
Anti-pattern 4: Every team creates its own copy
Problem:
- duplicated storage
- inconsistent metrics
- governance fragmentation
Anti-pattern 5: Full refresh every day
Problem:
- unnecessary compute
- unnecessary source load
- longer recovery windows
Anti-pattern 6: Transformation logic without ownership
Problem:
- unclear accountability
- undocumented business rules
- difficult change management
21. Hands-On Lab 1 — Build a Mini ELT Pipeline
Create three Delta tables:
silver.orders
gold.daily_sales
Bronze
Load raw order records.
Include:
- order_id
- customer_id
- order_date
- amount
- status
- ingestion timestamp
Silver
Implement:
- type casting
- null validation
- duplicate removal
- status normalization
Gold
Create:
- sales date
- order count
- total sales
- average order value
Then answer:
- What is the grain of each table?
- Which table is replayable?
- Where is business logic applied?
- Which layer should BI consume?
- What happens if the Silver transformation changes?
22. Hands-On Lab 2 — Make the Pipeline Idempotent
Run the same input twice.
Your result should not contain duplicate business records.
Test:
- initial load
- repeat same load
- introduce updates
- introduce deletes
- rerun the batch
Document the expected state after every run.
23. Hands-On Lab 3 — Add Data Quality
Create quality rules for:
- missing order ID
- invalid amount
- future order date
- invalid status
- duplicate order ID
Classify records as:
VALID
INVALID
QUARANTINED
Do not silently discard invalid records.
24. Hands-On Lab 4 — Design an SAP Pipeline
Design:
S/4HANA → Bronze → Silver → Gold
for procurement.
Your Gold layer must answer:
- spend by supplier
- spend by purchasing organization
- spend by material group
- monthly spend
- open purchase orders
Document:
- source
- extraction method
- grain
- keys
- CDC strategy
- data quality
- security
- retention
- SLA
- ownership
25. Architect Exercise
You receive this requirement:
“The company wants to ingest 20 TB/day from SAP, CRM, IoT and external APIs. Data must be available for BI within 30 minutes. Sensitive identifiers cannot be retained in raw form. Data scientists need historical raw events. Finance requires seven-year retention for curated financial data.”
Design the architecture.
Your answer should explicitly decide:
- ETL, ELT or hybrid?
- What is transformed before Bronze?
- What is retained?
- What belongs in Silver?
- What becomes Gold?
- Which data is managed vs external?
- How is sensitive data governed?
- How is incremental processing implemented?
- How are failures isolated?
- How is cost controlled?
A strong architect answer will usually be hybrid rather than dogmatically choosing ETL or ELT.
26. Production Checklist
Before calling an ETL/ELT pipeline production-ready, verify:
- Source ownership defined
- Target ownership defined
- Data contract documented
- Grain documented
- Business keys defined
- Incremental strategy defined
- Idempotency tested
- Replay strategy tested
- Data quality rules implemented
- Invalid records handled
- Schema evolution strategy defined
- Security classification defined
- Unity Catalog permissions defined
- Lineage available
- Audit requirements defined
- Retention defined
- SLA defined
- Monitoring defined
- Alerting defined
- Cost model reviewed
- Recovery procedure documented
- Backfill strategy tested
27. Interview Questions
Beginner
Q: What is ETL?
Extract, transform and load. Transformation happens before loading into the target.
Q: What is ELT?
Extract, load and transform. Raw or lightly processed data is loaded first and transformation happens inside the target platform.
Intermediate
Q: Why is ELT common in Databricks?
Because scalable lakehouse storage and compute allow organizations to retain governed source data and perform transformations using Spark or SQL after landing.
Q: Is Bronze always raw?
It should preserve source fidelity as much as practical, but controlled pre-processing may be required for security, privacy, format compatibility or source protection.
Advanced
Q: When would you choose ETL over ELT?
When transformation is required before persistence, especially for data minimization, regulatory restrictions, source protection, or mandatory external transformation stages.
Architect
Q: Would you use ETL or ELT for an enterprise SAP lakehouse?
Do not answer with a single technology slogan. Evaluate data sensitivity, source constraints, replayability, latency, governance, transformation ownership, cost and downstream consumers. A hybrid model is frequently appropriate.
28. The Architect Mental Model
Remember this:
DATA PLATFORM
│
┌──────────┴──────────┐
│ │
INGESTION GOVERNANCE
│ │
▼ ▼
BRONZE ─────────── Unity Catalog
│ │
▼ │
SILVER │
│ │
▼ │
GOLD │
│ │
┌────┼────┐ │
▼ ▼ ▼ │
BI ML APIs │
│ │
└──────────┬──────────┘
▼
OPERATIONS
quality / lineage / SLA
monitoring / cost / DR
The real enterprise question is not:
“ETL or ELT?”
It is:
“Where should each transformation happen so that the resulting data platform is secure, replayable, governed, scalable, cost-effective and useful to multiple consumers?”
That is the question an enterprise Databricks architect should be able to answer.