ETL & Data Warehousing Concepts
What it is & where it is used
ETL = Extract data from sources, Transform it (clean, validate, conform, aggregate), Load it into a target — usually a data warehouse used for reporting and analytics.
ELT = Extract, Load raw data first into a powerful target (cloud warehouse/lakehouse), then Transform inside it with SQL (dbt, Spark, Fabric). Modern cloud platforms favour ELT.
| ETL | ELT | |
|---|---|---|
| Transform where | Separate engine (SSIS, Informatica) | Inside the target (Synapse, Fabric, Snowflake, Databricks) |
| Raw data kept | Often not | Yes (data lake "bronze" layer) |
| Scale | Limited by ETL server | Elastic cloud compute |
| Typical tools | SSIS, Informatica PowerCenter, Talend, DataStage | Azure Data Factory/Fabric pipelines + dbt/Spark, Databricks, Snowflake |
OLTP vs OLAP: OLTP systems (orders, banking) handle many small transactions, normalised; OLAP systems (warehouses) handle large analytical queries, denormalised star schemas, historical data.
Versions & history
| Era | Milestone |
|---|---|
| 1970s–80s | Batch file processing, COBOL jobs |
| 1990s | Bill Inmon ("father of data warehousing", top-down, 3NF enterprise DW, 1992) and Ralph Kimball (bottom-up, dimensional modelling, The Data Warehouse Toolkit 1996) |
| 1990s–2000s | Commercial ETL tools: Informatica PowerCenter, IBM DataStage, Ab Initio; Microsoft DTS (SQL Server 7/2000) |
| 2005 | SSIS replaces DTS in SQL Server 2005 |
| 2010s | Hadoop, data lakes, Spark (2014); Azure Data Factory (2015, v2 2018) |
| 2016–2020 | Cloud warehouses (Snowflake, BigQuery, Synapse), ELT + dbt (2016), Lambda/Kappa streaming architectures |
| 2020s | Lakehouse (Delta Lake, Databricks), medallion architecture (bronze/silver/gold), Microsoft Fabric (GA Nov 2023), data mesh, real-time streaming (Kafka, Event Hubs) |
Lifecycle
ETL process lifecycle:
- Requirements — business questions, KPIs, sources, refresh frequency (SLA), history needs.
- Source analysis / profiling — volumes, keys, data quality, how changes can be detected.
- Design — target model (star schema), staging area, mappings (source-to-target document), load strategy (full/incremental), SCD types, error handling.
- Build — packages/pipelines, transformations, lookups, logging.
- Test — row counts, reconciliation with source, data quality checks, performance, rerun/idempotency.
- Deploy — dev → test → prod (SSIS catalog, ADF CI/CD), with environment-specific parameters.
- Schedule & orchestrate — SQL Agent, ADF triggers, Airflow; dependencies between jobs.
- Monitor & support — alerts on failure, SLA tracking, audit tables, reprocessing failed batches.
- Maintain — schema changes in sources, performance tuning, archiving.
Data flow per run: Source → Extract (full or delta) → Staging (raw copy, truncated each run) → Transform (cleanse, standardise, deduplicate, lookup surrogate keys, apply business rules, SCD logic) → Load dimensions first → Load facts → Audit/log → Reconcile.
Tools used to develop
| Category | Tools |
|---|---|
| Microsoft on-prem | SSIS, SQL Server Agent, T-SQL stored procedures |
| Microsoft cloud | Azure Data Factory, Microsoft Fabric (Data Factory pipelines, Dataflows Gen2, Lakehouse, Warehouse), Azure Synapse Analytics, Azure Databricks |
| Enterprise ETL | Informatica (IDMC / PowerCenter), IBM DataStage, Talend, Oracle Data Integrator, Ab Initio |
| Open source | Apache Airflow (orchestration), Apache Spark, Apache NiFi, Apache Hop, Airbyte, dbt Core |
| Cloud warehouses | Snowflake, BigQuery, Amazon Redshift, Fabric Warehouse, Databricks SQL |
| Streaming | Kafka, Azure Event Hubs, Azure Stream Analytics, Spark Structured Streaming |
| Data quality | Great Expectations, dbt tests, Soda |
| Reporting on top | Power BI, SSRS, Tableau |