ETL & Data Warehousing Concepts
In short: ETL is the process of moving data from source systems into a target (usually a data warehouse).
- Extract: read data from databases, files, APIs — full or incremental.
- Transform: cleanse, validate, standardise, deduplicate, look up surrogate keys, apply business rules, aggregate.
- Load: write into dimension and fact tables (inserts, updates, SCD handling), then log and reconcile.
- Goal: consistent, integrated, historical data for reporting.
In short: ETL transforms data in a separate engine before loading (SSIS, Informatica), good when the target is limited or data must be cleaned before landing.
- ELT loads raw data into a scalable target first and transforms it there with SQL/Spark (Synapse, Fabric, Snowflake, Databricks + dbt), keeping raw history and using elastic compute.
- Modern cloud architectures mostly use ELT with a medallion design.
In short: A data warehouse is a subject-oriented, integrated, time-variant, non-volatile store of data for analysis (Inmon's definition).
- OLTP databases serve day-to-day transactions: many small reads/writes, normalised, current data.
- Warehouses serve analytical queries over large volumes: denormalised star schemas, history, bulk loads, read-optimised (columnstore).
- Running analytics on OLTP harms the business application.
In short: Fact tables store measurable business events (quantity, amount) at a defined grain plus foreign keys to dimensions.
- Dimension tables store descriptive attributes (customer name, city; product category; date parts) used to filter and group facts.
- Example: FactSales(DateKey, ProductKey, CustomerKey, Qty, Amount) with DimDate, DimProduct, DimCustomer.
In short: In a star schema, each dimension is a single denormalised table directly joined to the fact — fewer joins, faster and simpler for BI tools.
- In a snowflake schema, dimensions are normalised into multiple related tables (Product → Subcategory → Category) — less redundancy but more joins and complexity.
- Kimball recommends star schemas; Power BI performs best with them.
In short: A temporary storage layer where extracted raw data lands before transformation.
- It minimises the time and load on source systems, allows transformations and validations with SQL, supports reruns without re-extracting, helps debugging and reconciliation, and decouples source availability from the warehouse load schedule.
- Usually truncated and reloaded each run (or kept as bronze history in lakehouses).
In short: They control how dimension attribute changes are stored.
- Type 1 overwrites the old value (no history — corrections).
- Type 2 inserts a new row with a new surrogate key and validity columns (ValidFrom, ValidTo, IsCurrent), preserving full history so old facts keep pointing to the old version.
- Type 3 adds a column for the previous value (only one level of history).
- Type 0 never changes; Type 6 combines 1, 2 and 3.
In short: A system-generated integer key (IDENTITY/sequence) used as the dimension's primary key instead of the source's natural key.
- Reasons: integrates multiple sources with overlapping IDs, enables SCD Type 2 (several rows per business key), protects from source key changes/reuse, smaller and faster joins, supports special members like "Unknown" (-1).
In short: Detect changes since the last successful run using a watermark (ModifiedDate/rowversion stored in a control table), CDC or Change Tracking, or hash comparison.
- Extract only those rows into staging, then upsert into the target (MERGE or UPDATE + INSERT;
- SCD logic for dimensions), handle deletes (soft-delete flags, CDC delete records, or periodic full compare), and update the watermark only after the load succeeds so failures don't skip data.
In short: Validate early;
- redirect bad rows to error tables with the reason instead of failing the whole batch (unless the threshold is exceeded);
- use an "Unknown" dimension member for missing lookups (late-arriving dimensions) and fix later;
- log every run in an audit table (counts, durations, status);
- send alerts;
- make loads idempotent and restartable;
- reconcile counts and totals with the source;
- keep raw data for reprocessing.
In short: The grain is what one row represents (one order line, one customer per day, one account per month-end).
- It must be declared before choosing dimensions and measures.
- Mixing grains causes double counting.
- The most atomic grain gives the most flexibility; aggregates can be built on top.
In short: Additive measures can be summed across all dimensions (sales amount, quantity).
- Semi-additive can be summed across some dimensions but not time (account balance, inventory level — use last/average over time).
- Non-additive can't be summed at all (ratios, percentages, unit price) — store numerator and denominator and calculate in the report.
In short: A SQL Server feature (also in Azure SQL and other databases) that reads the transaction log asynchronously and records inserts, updates and deletes with column values into change tables (cdc.<schema>_<table>_CT), queried via cdc.fn_cdc_get_all_changes_... using LSN ranges.
- ETL reads only changes — including deletes — with low impact on the source.
- Change Tracking is lighter: it records which rows changed, not the values.
In short: Inmon builds a normalised enterprise DW first (top-down) — consistent, but long time-to-value.
- Kimball builds dimensional data marts per business process using conformed dimensions (bottom-up) — faster delivery and BI-friendly.
- Many organisations use a hybrid: a raw/normalised (or Data Vault) integration layer with Kimball star schemas for presentation — similar to the medallion bronze/silver/gold pattern.
In short: A fact arrives referencing a dimension member that doesn't exist yet (e.g. sale for a new customer not yet loaded from CRM).
- Options: assign the "Unknown" member (-1) and update the fact later;
- or insert an inferred member (placeholder row with just the business key) and update its attributes when the real dimension data arrives (SSIS SCD wizard supports inferred members).
In short: Find which step slowed (audit log durations per task).
- Check data volume growth (switch full loads to incremental), source query plans and missing indexes, blocking on target tables during loads, row-by-row operations (OLE DB Command, cursors, SCD wizard on big dimensions → replace with set-based MERGE), blocking SSIS transformations (sort/aggregate), lookups in full-cache mode with huge reference tables, index maintenance on targets (disable/rebuild), log growth, server resources (memory, tempdb), and parallelism between independent loads.
In short: Reconcile step by step: compare counts and sums source → staging → warehouse for the same period and filters.
- Typical causes: duplicate extraction (overlapping incremental windows, reruns without idempotency), join fan-out (fact joined to a dimension with duplicate business keys, SCD2 lookup not restricted to the valid row), different business rules (cancelled orders, returns, tax/currency, time zones/cut-off), late-arriving corrections not applied.
- Fix the logic, add automated reconciliation checks and uniqueness tests.
In short: Parameterised pipeline looped over a store config table (ADF ForEach or SSIS Foreach with a project parameter for the connection).
- Incremental extract per store using a watermark/CDC into a staging table tagged with StoreId and BatchId.
- Transform centrally: conform codes, dedupe, look up surrogate keys (DimStore, DimProduct, DimDate, DimCustomer with SCD2).
- Load dimensions then FactSales (partitioned by date).
- Audit table per store per run, error rows redirected, alerts for stores that failed, rerun only failed stores.
- Schedule after stores close; monitor SLA.
In short: Unit-test transformations (sample inputs → expected outputs), validate mappings against the source-to-target document, test row counts and sums (reconciliation), null/duplicate/RI checks, SCD behaviour (insert, change, no-change cases), incremental logic (no duplicates on rerun, late changes captured, deletes handled), error handling (bad rows go to error tables), performance with production-like volumes, and restart/idempotency.
- Automate with tSQLt, dbt tests or Great Expectations in CI.