Data Pipeline Reference

ETL (Extract-Transform-Load) transforms data before loading it into the destination. The transformation happens in a separate processing layer, using tools like Informatica, Talend, or SSIS. ELT (Extract-Load-Transform) loads raw data first into the data warehouse, then transforms it inside the warehouse using its native compute power. Tools like dbt, Fivetran, and Airbyte follow the ELT pattern. ELT is preferred in modern cloud data warehouses because warehouse compute is cheap and transformations can be rerun without re-extracting data.

Batch processing runs periodically (hourly, daily, weekly) and processes large volumes of accumulated data at once. It is simpler to implement, tolerates higher latency, and suits use cases like nightly reports and daily aggregations. Streaming processes data continuously with low latency (seconds or milliseconds) as it arrives. Use streaming for real-time dashboards, fraud detection, live recommendations, or any use case where acting on fresh data immediately has business value. Tools: Spark Batch and Airflow for batch; Kafka, Flink, and Kinesis for streaming.

CDC captures every insert, update, and delete operation from a database transaction log and streams those changes to downstream systems in real time. Instead of querying entire tables periodically (which is slow and misses deletes), CDC reads the database binlog (MySQL) or WAL (PostgreSQL) to produce a stream of precise change events. Debezium is the most popular open-source CDC connector, typically paired with Kafka. CDC is essential for keeping data warehouses, caches, and microservices in sync with low latency.

The Medallion Architecture organizes data in a lakehouse into three quality tiers. Bronze receives raw, unmodified data as ingested from sources — preserving full history, including duplicates and errors. Silver applies cleaning, deduplication, type casting, and basic transformations to create a reliable, normalized dataset. Gold contains business-level aggregations and domain-specific views ready for BI tools and machine learning. This layered approach makes data lineage traceable and allows re-processing any layer without losing the original data.

A Data Lake stores raw data in its original format (CSV, JSON, Parquet, images, logs) without enforcing a schema upfront — this is schema-on-read. You apply the schema when querying. Tools: S3, ADLS, GCS. A Data Warehouse stores structured, pre-modeled data with schema enforced at write time — this is schema-on-write. It uses columnar storage for fast analytical queries. Tools: Snowflake, BigQuery, Redshift. A Data Lake is flexible but harder to maintain quality; a Data Warehouse is highly performant for structured analytics but less flexible for raw data exploration.

A Lakehouse is an architecture that combines the low-cost, flexible storage of a Data Lake with the ACID transaction semantics, schema enforcement, and query performance of a Data Warehouse. Delta Lake (Databricks), Apache Iceberg, and Apache Hudi are open table formats that add transactional capabilities (ACID transactions, time travel, schema evolution) on top of cloud object storage like S3. They enable concurrent reads and writes, rollback, and incremental processing while keeping data in open file formats like Parquet.

Exactly-once processing guarantees that each event in a stream is processed exactly one time — not zero times (at-most-once) and not more than once (at-least-once). Achieving exactly-once requires two things: idempotent producers (so retried sends do not create duplicates) and transactional consumers (so partial processing can be rolled back). Kafka supports exactly-once semantics through transactional APIs and idempotent producers. This is critical for financial transactions, billing, and any use case where duplicate processing has real consequences.

An idempotent pipeline produces the same result when run multiple times as it does when run once. This is essential for fault-tolerant pipelines because failures and retries are inevitable. Achieving idempotency typically means using UPSERT (MERGE) statements instead of INSERT, using a unique key to detect and ignore duplicates, and designing transformations so re-running them on already-processed data does not create errors. Idempotent pipelines are safe to retry on failure, safe to backfill, and safe to re-run after a schema change.