ETL Scenarios & Pipeline Design
✅ Pro Tip
Focus: SCD implementations, CDC, Medallion, DLT, end-to-end pipeline design
Approach: Every topic starts with simple explanation + analogy → technical depth → code with comments → Interview Tip → What NOT to Say
MEMORY MAP: ETL DESIGN → MEDALS
🧠 M → Medallion Architecture (Bronze → Silver → Gold)
MMedallion Architecture (Bronze → Silver → Gold)
EExtraction (Auto Loader, COPY INTO, readStream)
DDeduplication (ROW_NUMBER, dropDuplicates, MERGE)
AAuto Loader (cloudFiles, schema evolution, exactly-once)
LLate-arriving Data (watermarks, reprocessing, backfill)
SSCD Types (0, 1, 2, 3 — know Type 2 cold!)
QUICK VISUAL: MEDALLION = KITCHEN WORKFLOW
🧠 Memory Map
BRONZE (Raw Kitchen Delivery) → Raw ingredients as-is, no cleaning
↓ Schema validation, dedup
SILVER (Prep Station) → Washed, chopped, measured — clean & validated
↓ Business logic, aggregation
GOLD (Plated Dish) → Ready to serve to customers (BI dashboards)
SECTION 1: SCD (SLOWLY CHANGING DIMENSION) IMPLEMENTATIONS
Q1: Explain all SCD Types. When would you use each?
Simple Explanation: Dimensions in your data warehouse (like customer name, address, pricing tier) change over time. SCD is a set of strategies for dealing with those changes. The big question: do you want to keep history or not?
Analogies:
- SCD Type 0 = A printed birth certificate. It never changes. Your date of birth is your date of birth.
- SCD Type 1 = Overwriting with whiteout. You erase the old value and write the new one. The old value is gone forever.
- SCD Type 2 = Adding a new page to the history book. The old page stays, a new page is added with the updated info. You can always flip back.
- SCD Type 3 = A sticky note on the current page. You keep one previous value alongside the current one, but that is all the history you get.
Technical depth:
| SCD Type | Strategy | History? | Use Case |
|---|