ETL for AI

Data Pipeline Architecture

ETL (Extract, Transform, Load) pipelines are the backbone of data systems. Architecture decisions define your ability to scale: batch (nightly runs, high latency but simple) vs. streaming (continuous, sub-second latency but complex). Orchestration tools (Airflow, Prefect, Dagster) manage dependencies, retries, and monitoring. A robust pipeline: (1) Logs every step with timestamps, (2) Validates data at each stage (row counts, schema, statistics), (3) Implements backpressure (slows down if downstream is congested), (4) Has rollback capability (revert bad transforms). Testing ETL code is non-trivial: mock data sources, test edge cases (empty files, schema changes), and verify aggregations.

def etl_pipeline(source_path: str, dest_path: str) -> dict:
    """Minimal ETL: extract, validate, transform, load."""
    import pandas as pd
    # Extract
    df = pd.read_parquet(source_path)
    # Validate
    assert df.isnull().sum().sum() < len(df) * 0.05, "Too many nulls"
    # Transform
    df['created_date'] = pd.to_datetime(df['created_date'])
    df = df[df['created_date'] > '2025-01-01']
    df['year'] = df['created_date'].dt.year
    # Load
    df.to_parquet(dest_path, compression='snappy')
    return {'rows': len(df), 'cols': len(df.columns)}

Orchestration & Monitoring

Monitoring ETL is critical because silent failures are worst — bad data flows downstream without warning. Monitor: (1) Pipeline latency (how long did each step take?), (2) Data volume (are we processing expected row counts?), (3) Quality metrics (nulls, outliers, schema compliance), (4) Resource usage (memory, CPU, network). Set alerts for anomalies: if tonight's run is 3x slower than usual, investigate. Implement idempotency — running the same transformation twice produces the same result. This enables safe retries and recovery. Data observability platforms (Monte Carlo, Great Expectations) provide automated drift detection across your pipelines.

Data pipeline reliability is as important as model reliability. If your pipeline fails silently (processes bad data without raising alarms), models trained on that data become unreliable. Implement comprehensive error handling: (1) Schema validation (check every incoming file/record), (2) Row-level assertions (data should satisfy business rules), (3) Downstream reconciliation (counts, checksums, statistics should match expectations). Tools like dbt provide built-in testing; custom pipelines need manual instrumentation. A common pattern: at each stage, log row counts, nullity statistics, and sample values. Compare to historical baselines; large deviations trigger investigation. In production, "silent failures" (data flows through but is bad) are worse than "loud failures" (pipeline crashes, triggering alerts). Design for safety: fail explicitly when something's wrong.

Backpressure and flow control prevent cascading failures. If downstream processing is slow (the ML training job is saturated), the upstream ETL should slow down, not queue infinite data. Implement producer-consumer patterns: upstream processes data and publishes to a queue (Kafka, RabbitMQ); downstream consumes at its own pace. If the queue fills up, upstream blocks. This prevents memory overflow and gives you time to diagnose bottlenecks. For batch pipelines, implement checkpointing: if a job fails halfway through processing 1 million rows, resume from the checkpoint rather than restarting. This saves compute and enables recovery. Idempotency is key: if you rerun a transform on the same input, the output should be identical. This enables safe recovery.

Observability platforms provide visibility into pipeline health. Metrics to track: latency (total time, per-stage breakdown), throughput (rows/sec), resource usage (CPU, memory, disk I/O), data quality (nulls, duplicates, schema violations), and freshness (how old is the most recent data?). Dashboards should answer: "Is my pipeline healthy?" (quick glance), "Where is the bottleneck?" (dive deep), "What changed?" (compare to yesterday). Set alert thresholds: if latency increases by 50%, if quality metrics degrade, if data freshness lags. Modern platforms like Datadog, New Relic provide end-to-end observability; open-source alternatives (Prometheus + Grafana) work for smaller deployments. Invest in observability early—it's invaluable for troubleshooting and capacity planning.

Data warehousing is foundational for AI/ML. A data warehouse centralizes data from multiple sources (databases, APIs, log files) into a unified repository. Tools like Snowflake, BigQuery, Redshift provide scalable SQL engines. Data lakes (unstructured or semi-structured storage) complement warehouses for raw data. A modern data stack: raw data in a lake, curated data in a warehouse, features in a feature store (specialized system for ML features). Each layer adds structure and enables different queries. Data warehouse design for ML differs from traditional OLAP: instead of supporting real-time transactions, they support batch feature extraction and historical analysis. Star schemas (fact tables with dimensions) work well; often you need nested data (JSON columns) for complex attributes. Partitioning and clustering tables by common query patterns (e.g., partition by date, cluster by user) improves query performance. A 1 billion row table that takes 5 minutes to scan is useless; partition and cluster to make scans sub-second.

Data freshness and latency are critical trade-offs. Real-time pipelines (Kafka, Flink) provide sub-second latency but are complex. Batch pipelines (daily or hourly runs) are simpler but introduce lag. For most ML systems, hourly or daily batches are sufficient. But for recommendation systems, fraud detection, or real-time personalization, lower latency is necessary. Hybrid approaches: batch process most data (fast, cheap), stream process high-value subsets (expensive, low-latency). Incremental processing (only process new/changed data since the last run) reduces compute vs. reprocessing everything. Logging and debugging ETL requires extensive instrumentation: log every error, every schema issue, every unusual pattern. Tools like Apache Spark provide execution plans that reveal bottlenecks. For production systems, 99.9% uptime (9 hours of downtime per year) is a reasonable SLA; 99.99% (1 hour downtime per year) requires serious infrastructure.

Data lineage tools (Apache Atlas, Collibra, custom solutions) track data provenance: where did this data come from, what transformations did it undergo, where is it used? This is essential for compliance (GDPR right to access), debugging (why is this model making wrong predictions? trace back to source data), and understanding data flow. Visual lineage graphs show dependencies: if source system A is down, which downstream processes are affected? Lineage enables impact analysis: before making a breaking change (removing a column), see what depends on it. Lineage also supports certification: mark data as "certified" (verified, high-quality) and track its flow to identify which models benefit from certified data. Building lineage is overhead initially (requires instrumentation, tooling) but pays dividends in operations, debugging, and governance. For production systems handling petabyte-scale data, lineage is essential; for small-scale projects, simpler documentation suffices.