🟡 Intermediate
ETL vs ELT
| ETL | ELT |
|---|---|
| Transform before load, OLTP, normalized, keep raw, slower, less storage | Transform at destination, large data, simple transforms, faster, needs more warehouse storage, highly scalable, cloud tools: Snowflake, Databricks, BigQuery |
When: On-prem, complex preload validation, small-medium → ETL. Large, simple, real-time analytics, cloud → ELT.
🟢 Easy
Medallion Architecture
Bronze: raw (unstructured/semi/structured) → Silver: cleaned/enriched (join, filter) → Gold: business answers (aggregations like daily revenue, total sales by region).
🔴 Advanced
Incremental Loads — How We Handled It (from file)
- Enabled job bookmarks (timestamp/id column) — processes only new data
- Landing bucket with yyyy_mm_dd directory structure
- System to identify new/updated files (naming convention
mydata_01012025.json, prefix/suffix, Lambda on S3 PUT) - Timestamp config table: store max timestamp, fetch where lastupdated > max
- CDC via AWS DMS (full load + CDC) → S3 → crawler → Redshift with MERGE (upsert)
Python
lastprocessedtime = df.agg(max(col('lastupdated'))).collect()[0][0]
df = spark.read.jdbc(sourceurl, 'table', properties=properties)
df = df.filter(col('lastupdated') > lastprocessedtime)
🔴 Advanced
CDC & DMS
CDC identifies inserts/updates/deletes, avoids full scan, near real-time, ideal for incremental loads.
AWS DMS: Source endpoint (MySQL) → Target endpoint (S3) → enable CDC mode (full load + CDC or CDC only) → configure format (JSON/CSV/Parquet) → S3 landing → Glue crawler → Redshift. DMS captures changes via timestamp columns.