ETL Process: Extract, Transform, Load

Every data warehouse journey begins with a fundamental challenge: How do we reliably extract data from operational systems without disrupting their primary purpose? This question sits at the heart of the Extract phase in ETL (Extract, Transform, Load) processing—the critical first step in building any analytical data infrastructure.

The Extract phase isn't merely about 'copying data.' It's a sophisticated engineering discipline that must navigate heterogeneous source systems, handle constantly changing data, respect operational constraints, and maintain data lineage—all while guaranteeing that the exact state of business data at any point in time can be accurately captured and replayed.

Production databases serve transactions. They're optimized for writes, updates, and point queries. Extracting analytical workloads from these systems—potentially billions of rows across hundreds of tables—requires strategies that minimize impact while maximizing completeness and accuracy. Get extraction wrong, and every downstream transformation and analysis inherits the error.

Before designing an extraction strategy, you must deeply understand the source systems from which data will be pulled. Each source type presents unique access patterns, data formats, and operational constraints that fundamentally shape how extraction must proceed.

The heterogeneity problem:

In enterprise environments, data rarely lives in a single, unified system. A typical organization might have:

• Relational databases (Oracle, PostgreSQL, SQL Server) for transactional applications
• Legacy mainframe systems with COBOL copybooks and flat files
• SaaS applications (Salesforce, Workday, HubSpot) accessible only via REST APIs
• Log files and event streams from web applications
• Spreadsheets and Access databases maintained by business units
• NoSQL databases (MongoDB, Cassandra) for specific workloads

Each of these systems has different authentication mechanisms, query interfaces, rate limits, data models, and change tracking capabilities. The Extract phase must normalize this chaos into a coherent, reliable data flow.

Operational database considerations:

When extracting from production OLTP systems, you're essentially a secondary workload competing for shared resources. Critical considerations include:

1. Read replicas: Many organizations provision read replicas specifically for reporting and ETL. Extracting from replicas protects primary database performance but introduces replication lag considerations.

2. Connection pooling: Long-running extraction queries consume connection slots. Misconfigured extractions can exhaust connection pools, blocking application traffic.

3. Query optimization: Extraction queries should use appropriate indexes and avoid table scans during peak hours. Monitoring execution plans is essential.

4. Transaction isolation: The isolation level affects both data consistency and locking behavior. READ COMMITTED typically balances accuracy with minimal blocking.

The most fundamental architectural decision in extraction design is choosing between full extraction (pulling all data every time) and incremental extraction (pulling only changed data since the last extraction). This decision impacts performance, storage requirements, data freshness, and recovery capabilities.

When to use each approach:

Hybrid strategies:

Production systems often employ hybrid approaches:

• Full weekly + incremental daily: Weekly full extraction ensures baseline integrity; daily incremental captures changes. Provides both safety and efficiency.
• Full on-demand + incremental continuous: Incremental normally; full extraction triggered by data quality issues or schema changes.
• Partitioned full: For partitioned tables, extract only changed partitions fully. Common for time-partitioned fact tables.

For incremental extraction to work, you need a reliable mechanism to identify which rows have changed. This is the change detection problem, and different approaches offer varying trade-offs between accuracy, performance, and invasiveness.

Timestamp-based extraction in detail:

The most common approach relies on timestamp columns. The extraction query pattern looks like:

-- Standard timestamp-based incremental extraction
SELECT *
FROM orders
WHERE last_modified_timestamp >= :last_extraction_timestamp
  AND last_modified_timestamp < :current_extraction_timestamp;

Critical considerations:

1. Timestamp precision: Millisecond precision matters. If your timestamp column only stores seconds and multiple updates occur within a second, you might miss changes.

2. Transaction boundaries: A transaction modifying row at 10:00:00.500 might not commit until 10:00:01.200. If extraction runs at 10:00:00.800, it might see the timestamp but query read-committed snapshot misses uncommitted data.

3. Clock skew: In distributed systems, server clocks can drift. A row might receive a timestamp in the 'past' relative to your extraction watermark.

4. High-water mark management: The 'last_extraction_timestamp' must be persisted reliably. Losing this value means you must fall back to full extraction.

Change Data Capture (CDC) represents the gold standard for incremental extraction. Rather than querying tables to infer what changed, CDC reads the database's transaction log to observe the exact sequence of insert, update, and delete operations as they occur.

This approach offers profound advantages:

• Complete accuracy: Captures every change, including deletes and rapid in-place updates
• Minimal source impact: Log reading is typically separate from query processing
• Near-real-time: Changes can be available within seconds of commit
• Operation type preservation: Know whether a row was inserted, updated, or deleted
• Before/after images: Capture previous values on updates for audit trails

CDC architecture with Debezium:

Debezium is the most widely adopted open-source CDC platform, typically deployed as Kafka Connect connectors. The architecture follows this flow:

1. Capture: Debezium connector reads database transaction logs
2. Serialize: Changes are converted to structured events (JSON or Avro)
3. Stream: Events are published to Apache Kafka topics (one per table)
4. Consume: ETL processes consume from Kafka, applying changes to the warehouse

┌─────────────┐     ┌───────────┐     ┌─────────┐     ┌────────────┐
│ Source DB   │────▶│ Debezium  │────▶│  Kafka  │────▶│   ETL      │
│ (MySQL)     │     │ Connector │     │ Topics  │     │ Consumer   │
└─────────────┘     └───────────┘     └─────────┘     └────────────┘
       │                                                     │
       │                                                     ▼
       │                                            ┌────────────┐
       └─────── Binlog reads ─────────────────────▶│    Data    │
                                                    │ Warehouse  │
                                                    └────────────┘

CDC event structure:

A typical CDC event contains rich metadata beyond just the new row values:

Real-world extraction systems employ recognizable architectural patterns that balance performance, reliability, and operational complexity. Understanding these patterns helps you design appropriate solutions for specific requirements.

Parallelization strategies:

Extracting large tables requires parallelization to meet time windows. Common approaches include:

Range partitioning: Divide extraction by key ranges. Multiple extractors each handle a key range:

-- Extractor 1: IDs 1-1000000
-- Extractor 2: IDs 1000001-2000000
-- Extractor 3: IDs 2000001-3000000
SELECT * FROM orders 
WHERE order_id BETWEEN :range_start AND :range_end;

Time partitioning: Divide extraction by time ranges. Particularly effective for append-only fact tables:

-- Extract one day at a time across parallel workers
SELECT * FROM transactions
WHERE transaction_date = :target_date;

Hash partitioning: Extract rows based on hash of a column. Ensures even distribution:

-- 4 parallel extractors, each handling 25% of data
SELECT * FROM customers
WHERE MOD(HASH(customer_id), 4) = :worker_id;

Critical parallel extraction considerations:

• Ensure key ranges don't overlap (would duplicate data)
• Ensure ranges cover all data (would lose data)
• Monitor for skewed partitions (uneven work distribution)
• Coordinate checkpointing across parallel workers

Production extraction systems must be designed for failure. Networks fail, databases become unavailable, credentials expire, and queries time out. Robust extraction architecture assumes failures will occur and provides mechanisms to detect, recover, and prevent data loss.

Common failure modes:

Recovery strategies:

Checkpoint-based recovery: Persist extraction progress at regular intervals. On failure, resume from the last checkpoint rather than restarting from the beginning.

Extraction progress: [=====|=========>          ]
                           ↑
                      Checkpoint saved
                           
                      On failure, resume here

Idempotent extraction: Design extraction to be safely repeatable. Re-extracting the same data range should produce identical results, allowing failed batches to be retried without duplication or data loss.

Dead letter handling: When specific rows fail extraction (encoding issues, constraint violations), route them to a 'dead letter' queue for investigation while continuing with other rows.

Circuit breaker pattern: After repeated failures, stop attempting extraction and alert operators rather than overwhelming failing source systems with retry storms.

The Extract phase is your first line of defense for data quality. Problems detected early are far cheaper to resolve than problems that propagate through transformation layers and into analytical reports.

Extraction-time validations:

Schema evolution handling:

Source schemas change over time—new columns are added, columns are renamed, data types change. Extraction systems must detect and handle these changes:

1. Schema detection: Query source metadata (information_schema, pg_catalog) to detect current schema
2. Schema comparison: Compare against previously extracted schema
3. Change classification: Categorize changes as backward-compatible (add column) or breaking (drop column, type change)
4. Automated handling: Backward-compatible changes can often be handled automatically
5. Alerting: Breaking changes require human review before proceeding

Data lineage tracking:

Every extracted row should carry metadata enabling full traceability:

• Source system identifier
• Source table/entity name
• Extraction timestamp
• Extraction job ID
• Source row version/timestamp

This metadata enables debugging, auditing, and impact analysis when issues are discovered downstream.

The Extract phase sets the foundation for everything that follows in the ETL pipeline. Extraction done well provides reliable, timely, high-quality data to downstream processes. Extraction done poorly creates data quality issues, operational incidents, and eroded trust in analytical outputs.

What's next:

With data successfully extracted from source systems, we turn to the Transform phase—where raw operational data is cleaned, integrated, conformed to business rules, and structured for analytical consumption. The next page explores transformation techniques from simple data cleansing to complex business logic application.