Unit
- Pipeline lifecycle phases: The ETL process moves through four core phases: data extraction from source systems, data transformation to meet target schema requirements, data loading into the destination, and error handling for records that fail processing. Each phase introduces specific failure modes that operations teams must anticipate.
- Batch vs. streaming distinction: Traditional ETL operates in batch mode with scheduled runs, while modern streaming pipelines process data in near real-time using technologies like Apache Kafka. Batch ETL suits high-volume historical loads; streaming fits low-latency use cases such as fraud detection.
- State management in pipelines: Pipelines must track which data has been processed to avoid duplication or loss. Common strategies include using watermark columns, checkpoint tables, or offset tracking in message queues. Stateless pipelines risk reprocessing or missing records on failure.
- Scheduling and orchestration: ETL jobs rarely run in isolation. Orchestrators like Apache Airflow manage dependencies between tasks, retry logic, and notification triggers. A well-designed DAG (directed acyclic graph) ensures jobs execute in the correct order.
- Data quality gates: Each pipeline stage should validate row counts, schema conformity, and referential integrity. Quality gates prevent defective data from propagating to downstream consumers and trigger alerts when thresholds are exceeded.
- Metadata management importance: Metadata repositories store information about source schemas, transformation logic, and job run histories. This metadata enables impact analysis when sources change and provides lineage for auditing.
- Error handling patterns: Fail-fast approaches stop the pipeline on critical errors, while fault-tolerant patterns isolate bad records and continue processing. Dead-letter queues and error tables capture rejected data for later investigation.
- Idempotency principle: A pipeline is idempotent if rerunning it produces the same result as the first successful run. Idempotency simplifies recovery by allowing safe replays without side effects. Techniques include using upsert logic and deterministic transformations.
System Architecture
Pattern: Layered ETL Architecture
Components:
- Extraction Layer: Connectors to source systems (databases via JDBC, REST APIs, file systems). Handles incremental extraction using high-water marks.
- Staging Area: Temporary storage for raw extracted data, often in cloud object storage (S3, Azure Blob) or a staging database. Preserves raw format for reprocessing.
- Transformation Engine: Compute layer (Spark, dbt, SQL) that applies business logic, data cleansing, and aggregation. Outputs conformed data for loading.
- Loading Layer: Writes transformed data into target warehouse or data lake using bulk insert, upsert, or partition swapping.
- Orchestration Layer: Manages job scheduling, dependency resolution, retries, and monitoring. Examples: Airflow, Prefect, Dagster.
Code Example
Python
import pandas as pd
# Simulate an idempotent ETL step using upsert logic
def upsert_data(conn, df, target_table, key_columns):
# Delete existing rows matching keys
for _, row in df.iterrows():
where_clause = ' AND '.join([f'{col} = %s' for col in key_columns])
delete_sql = f'DELETE FROM {target_table} WHERE {where_clause}'
params = [row[col] for col in key_columns]
conn.execute(delete_sql, params)
# Insert all rows
df.to_sql(target_table, conn, if_exists='append', index=False)
print('Upsert completed successfully')This function implements a simple upsert pattern: it deletes existing rows based on key columns, then appends the new data. Re-running this function on the same input yields identical final state.
Quick Revision
- The ETL lifecycle includes extraction, transformation, loading, and error handling phases.
- Batch and streaming pipelines serve different latency requirements.
- State management ensures exactly-once processing semantics.
- Orchestration tools manage job dependencies and error recovery.
- Idempotency enables safe pipeline replays without duplication.
- Architecture layers isolate concerns and simplify maintenance.