ETL & Data Warehousing Concepts
ETL vs ELT
ETL: transform before load (SSIS). ELT: load raw, transform in the warehouse (ADF/Fabric + SQL/dbt/Spark).
Load types
Full (truncate + reload) · Incremental (watermark, rowversion, CDC, Change Tracking, hash) · Upsert (MERGE) · Deletes (soft delete, CDC, full compare).
Dimensional model
Fact (measures at a grain) + Dimensions (descriptive) · Star (denormalised) vs Snowflake (normalised dims) · Surrogate keys · Conformed, role-playing, junk, degenerate dimensions.
Fact types
Transaction · Periodic snapshot · Accumulating snapshot · Factless. Measures: additive, semi-additive, non-additive.
SCD
0 keep · 1 overwrite · 2 new row + ValidFrom/ValidTo/IsCurrent · 3 previous-value column · 4 history table · 6 hybrid.
Reliability
Staging · audit table · error tables · Unknown member (-1) · idempotent reruns · watermark updated after success · reconciliation counts/sums · alerts.
Architectures
Inmon (top-down 3NF) · Kimball (bottom-up stars) · Data Vault (hubs/links/satellites) · Data lake · Lakehouse · Medallion bronze → silver → gold.
Tools
SSIS · ADF · Fabric · Synapse · Databricks · Informatica · Talend · Airflow · dbt · Spark · Kafka.