ETL Process Visually Explained | Data Engineering Architecture & Portfolio Design

In modern data engineering, the Extract, Transform, Load (ETL) pipeline serves as the fundamental engine of the data warehouse. In real-world enterprise environments, data engineers spend roughly 90% of their time designing, building, testing, and optimizing ETL components. This chapter breaks down the theoretical mechanics, architectural variations, load strategies, historical tracking (Slowly Changing Dimensions), and practical design choices required to build robust data pipelines.

1. Core Mental Model: What is ETL?

At its simplest, an ETL pipeline bridges raw operational source systems and optimized analytical targets (such as a Data Warehouse or Data Mart).

Source System (Raw Data)
   |
   | 1. EXTRACT (Pull untouched data)
   v
Staging / Intermediate Layer
   |
   | 2. TRANSFORM (Cleanse, reshape, normalize, aggregate)
   v
Prepared Analytical Model
   |
   | 3. LOAD (Insert into target destination)
   v
Target Data Warehouse (Reporting & BI)

The Three Phases

1. Extract

• Objective: Identify and pull the necessary data subset from the source system.
• Golden Rule: Do not alter or manipulate the data during extraction. The extracted data must be a precise 1:1 replica of the source data.
• Scope: Extracts can be full baseline snapshots or delta/incremental pulls based on tracking mechanisms.

2. Transform

• Objective: Reshape, clean, and enrich raw data into a structured model tailored for analytics and business intelligence.
• Workload: This is the most computationally intensive phase. It encompasses business logic execution, data cleansing, normalization, deduplication, schema conversion, and metric aggregations.

3. Load

• Objective: Write the transformed data into the final target database or data warehouse tables.
• Mechanisms: Data is persisted using batch or streaming methods through operations like append, update, merge, or overwrite.

2. Multi-Layer Data Architectures & Deconstructed Flows

In production environments, data architectures rarely use a simple, single-step pipeline from point A to point B. Instead, modern data platforms feature multi-layered architectures (e.g., Bronze $\rightarrow$ Silver $\rightarrow$ Gold, or Staging $\rightarrow$ ODS $\rightarrow$ DW $\rightarrow$ Data Mart).

Depending on the boundary between architectural layers, engineers apply specific subsets of the ETL workflow rather than running a full ETL cycle at every boundary.

Source System
   |
   | Extract Only (Raw copy into Staging)
   v
Layer 1: Raw Staging Layer
   |
   | Full ETL (Extract from Layer 1, Heavy Transformation, Load to Layer 2)
   v
Layer 2: Cleansed / Integrated Layer (ODS)
   |
   | Transform & Load (In-engine Transformation, Load to Layer 3)
   v
Layer 3: Enterprise Data Warehouse (Dimensional Model)
   |
   | Load then Transform (ELT Strategy / Data Replication)
   v
Layer 4: Specialized Data Marts / BI Aggregations

Process Variations Across Architectural Boundaries

3. Data Extraction Deep-Dive: Methods, Types, and Techniques

The extraction process requires choosing the right access pattern, extraction frequency, and technical interface based on source system capabilities and latency requirements.

Extraction Access Methods

1. Pull Extraction: The ETL process actively connects to the source system, queries or requests data, and pulls it into the staging environment.
2. Push Extraction: The source system initiates data transfer by pushing event messages, files, or payload objects directly into the data lake or target system.

Full vs. Incremental Extraction

• Full Extraction: Every record from the source tables is extracted during every execution pipeline run.
  • Pros: Simple to implement; no change tracking required.
  • Cons: High network and memory overhead; scales poorly with large datasets.
• Incremental Extraction: Only records created, modified, or deleted since the last execution run are extracted.
  • Pros: Low resource overhead; fast execution.
  • Cons: Requires robust metadata tracking (e.g., updated_at timestamps or high-water marks).

Technical Extraction Techniques

4. Data Transformation Deep-Dive: Operations & SQL Blueprint

Data transformation converts raw data into high-quality analytical assets. Transformations handle data quality issues, enforce business rules, and restructure data models.

Transformation Categories

• Data Cleansing: Standardizing values, removing duplicate records, trimming whitespace, imputing missing/null values, fixing invalid fields, and casting string representations to strict data types.
• Data Integration: Joining tables across disparate source systems using unified keys.
• Normalization & Code Mapping: Mapping cryptic source domain codes (e.g., status_cd = 1) into human-readable business terms (status = 'Active').
• Derived Columns & Business Logic: Applying domain-specific business calculations (e.g., net_revenue = gross_revenue - discount).
• Data Aggregation: Summarizing transactional atomic data to required analytical granularities (e.g., daily sales summaries by store).
• Data Enrichment: Augmenting internal data with external datasets (e.g., appending geographical metadata via IP address lookup).

Data Transformation Code Example

The following SQL statement illustrates common operational transformations applied to raw input data within a staging environment:

-- SQL Implementation of Operational Transformations
SELECT 
    -- 1. Data Cleansing & Type Casting
    CAST(TRIM(raw_user_id) AS INT) AS user_id,
    
    -- 2. String Standardization & Whitespace Trimming
    LOWER(TRIM(email_address)) AS clean_email,
    
    -- 3. Handling Missing/NULL Values with Default Imputation
    COALESCE(phone_number, 'N/A') AS contact_phone,
    
    -- 4. Normalization & Code Mapping via Business Logic
    CASE 
        WHEN status_code = 'A' THEN 'Active'
        WHEN status_code = 'I' THEN 'Inactive'
        WHEN status_code = 'P' THEN 'Pending'
        ELSE 'Unknown'
    END AS account_status,
    
    -- 5. Derived Calculated Columns
    order_amount - discount_amount AS net_sales_amount,
    
    -- 6. Date Standardization
    CAST(raw_created_timestamp AS DATE) AS transaction_date
FROM staging_raw_orders
WHERE raw_user_id IS NOT NULL -- Deduplication/Filtering step
  AND is_test_record = 0;

5. Data Loading Strategies & Processing Paradigms

Loading determines how transformed data is persisted in the target storage layer.

Processing Modes: Batch vs. Stream

• Batch Processing: Data is processed and loaded in discrete chunks on a predefined schedule (e.g., nightly at 02:00 AM).
  • Characteristics: Highly performant for large volumes; predictable resource usage; higher latency.
• Stream Processing: Data is transformed and loaded continuously as individual events or micro-batches in near real-time.
  • Characteristics: Extremely low latency; higher operational complexity and cost.

Target Database Load Patterns

1. Full Load Options

• Truncate and Insert: The target table is entirely emptied (TRUNCATE TABLE) and repopulated from scratch with the incoming dataset.
• Drop, Create, and Insert: The target schema object is dropped entirely (DROP TABLE), recreated, and loaded.
• Full Upsert: The system scans all records, updating existing records and inserting missing ones.

2. Incremental Load Options

• Append-Only (Insert Only): New records are appended to the end of the table without updating or modifying existing records (ideal for log tables).
• Upsert (Update & Insert): Existing records matched on a unique key are updated with new state data; unmatched records are inserted.
• Merge (SQL Merge): Executes atomic INSERT, UPDATE, and DELETE operations based on match conditions between source and target tables.

SQL Implementation Patterns for Load Strategies

Pattern A: Truncate & Insert (Full Load)

-- Clear all existing records from target table
TRUNCATE TABLE dw.fact_daily_sales;

-- Load fresh dataset
INSERT INTO dw.fact_daily_sales (date_key, store_id, total_revenue)
SELECT 
    date_key, 
    store_id, 
    SUM(net_sales_amount)
FROM staging_orders
GROUP BY date_key, store_id;

Pattern B: SQL MERGE Statement (Incremental Upsert)

-- Atomically update matching rows and insert new rows
MERGE INTO dw.dim_customer AS target
USING staging_customers AS source
    ON target.customer_id = source.customer_id
WHEN MATCHED AND (target.email <> source.email OR target.phone <> source.phone) THEN
    UPDATE SET 
        target.email = source.email,
        target.phone = source.phone,
        target.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (customer_id, customer_name, email, phone, created_at, updated_at)
    VALUES (source.customer_id, source.customer_name, source.email, source.phone, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

6. Historical Data Management: Slowly Changing Dimensions (SCD)

In data warehousing, dimension tables store context for business transactions (e.g., customer addresses, product prices). Over time, these attributes change. Slowly Changing Dimensions (SCD) define how these updates are handled in target tables.

Comparison of Primary SCD Types

Detailed Mechanics of SCD Type 2 Implementation

SCD Type 2 uses surrogate keys, version metadata, or timestamp ranges to retain full record histories.

Recommended Schema Structure for SCD Type 2 Table

CREATE TABLE dw.dim_customer_scd2 (
    customer_surrogate_key INT IDENTITY(1,1) PRIMARY KEY, -- Unique key for every row version
    customer_id INT NOT NULL,                             -- Natural business key from source
    customer_name VARCHAR(100),
    city VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,                        -- Active flag (True = current version)
    effective_start_date DATE NOT NULL,                   -- Version valid from date
    effective_end_date DATE NULL                          -- Version valid until date (NULL = current)
);

Analytical Querying of SCD Type 2 Records

• To query current customer state:
  SQL

  Copy

  SELECT * FROM dw.dim_customer_scd2 WHERE is_active = TRUE;
  

• To query historical customer state as of a specific date (Point-in-Time analysis):
  SQL

  Copy

  SELECT * FROM dw.dim_customer_scd2 
  WHERE '2023-06-15' BETWEEN effective_start_date AND COALESCE(effective_end_date, '9999-12-31');
  

7. Project Implementation Blueprint: Technical Decisions

Here is an architectural decision matrix for building a portfolio project data pipeline:

Architectural Decisions

Justification of Project Decisions

1. Pull Extraction via Files: Standardizes dataset ingestion using local file drops (CSVs/JSONs), simplifying environmental setup while mirroring enterprise batch interfaces.
2. Full Extraction & Full Batch Load: Processing full baseline snapshots allows simple environment resetting and predictable idempotent batch runs.
3. Truncate & Insert Load Strategy: Completely clearing destination tables before loading prevents partial execution bugs and duplicate rows during batch retries.
4. SCD Type 1 Dimension Management: Simplifies schema design by keeping single master records for each dimensional entity while focusing engineering effort on robust transformation logic.
5. Comprehensive Transformation Suite: Applies all major data engineering transformation patterns—cleansing, missing value imputation, type casting, schema normalization, and dimensional aggregation—to simulate production workloads.

8. Real-World Pitfalls & Debugging Workflows

Building production-grade ETL pipelines requires handling failure modes, performance bottlenecks, and structural data issues.

Common Production Edge Cases

Note: Real-world data engineering pipelines often fail due to structural schema changes, network timeouts, or unhandled data quality issues. Building defensive data processing logic into your ETL framework is essential.

1. Schema Drift (Unannounced Source Changes):

   • Issue: Source systems add, drop, or rename columns without notification, breaking downstream SQL queries or object mappings.
   • Mitigation: Implement explicit schema validation checks at the extraction staging boundary before executing transformation pipelines.

2. Late-Arriving Data (Out-of-Order Records):

   • Issue: Transaction records arrive with timestamp delays after batch processing windows have already closed.
   • Mitigation: Use dynamic incremental watermarking based on record creation/update timestamps rather than pipeline execution run times.

3. Memory Exhaustion (OOM Failures):

   • Issue: Loading massive datasets entirely into application memory causes ETL processes to crash.
   • Mitigation: Stream data processing in chunks or push heavy join/aggregation processing down into modern cloud data warehouses using SQL engines.

4. Non-Idempotent Pipeline Execution:

   • Issue: Re-running a failed pipeline run leads to duplicated rows or corrupted cumulative metrics in target tables.
   • Mitigation: Ensure all pipeline stages are idempotent—executing the same pipeline run multiple times with the same input data should produce the exact same target state (e.g., using TRUNCATE & INSERT or MERGE statements).

9. Summary and Key Takeaways

• ETL Workload Dominance: The ETL process constitutes roughly 90% of the overall engineering effort in data warehouse projects.
• Strict Extraction Rule: The Extract phase must pull operational source data without altering its underlying business values, producing an exact 1:1 representation in staging storage.
• Transformation Versatility: Transformations handle a wide array of data processing tasks, including data cleansing, de-duplication, format normalization, join integration, business logic derivation, and metric aggregation.
• Flexible Architecture: Real-world enterprise architectures vary between traditional ETL (Transform before Load) and modern ELT (Load raw data first, then Transform using database engines).
• Extraction Access Patterns: Extraction operates using Pull or Push patterns, implemented through options like full baseline pulls, incremental delta pulls, CDC, file drops, APIs, or event streaming pipelines.
• Deterministic Load Strategies: Data loading uses Batch or Stream execution modes. Full loads rely on patterns like Truncate & Insert or Drop & Create, whereas incremental loads use Upsert or MERGE operations.
• Dimension History (SCD): Slowly Changing Dimensions manage structural changes over time: SCD Type 1 overwrites data (no historical tracking), whereas SCD Type 2 inserts new version rows with tracking flags and valid date ranges.
• Portfolio Project Baseline: The portfolio project pipeline built in this series uses Batch Pull Extraction of baseline files, applies a Comprehensive Transformation Suite, and executes Full Batch Truncate-and-Insert Loads using SCD Type 1 dimension rules.