Run a mock round

CDC captures row-level changes from a source database and streams them downstream with low latency. A typical design:

• Capture: read the database transaction log with a tool like Debezium on the MySQL binlog or Postgres logical replication, rather than polling, so you get every insert, update and delete without load on the source.
• Transport: publish change events to Kafka, keyed by primary key to preserve per-entity ordering.
• Process: consume events, land them in a bronze table, then merge into silver entities. Handle deletes, schema evolution and ordering.
• Serve: expose current state and history for analytics.

{"op":"u","before":{"id":42,"city":"Paris"},"after":{"id":42,"city":"Lyon"}}

Considerations: initial snapshot plus streaming handoff, idempotent merges, tombstones for deletes, and monitoring replication lag.

Batch processing handles bounded datasets on a schedule. It is simple, high-throughput and easy to reprocess, but results are delayed by the batch interval. Examples include nightly aggregations with Spark or SQL.

Stream processing handles unbounded events continuously, producing low-latency results. Tools include Kafka Streams, Flink and Spark Structured Streaming. It must handle out-of-order events, exactly-once semantics, windowing and state.

spark.readStream.format("kafka").load() \
  .withWatermark("ts", "10 minutes") \
  .groupBy(window("ts", "5 minutes"), "region").count() \
  .writeStream.format("delta").option("checkpointLocation", path).start()

Trade-offs: streaming adds operational complexity and cost, while batch is cheaper and easier to reason about. Common patterns are lambda and kappa architectures, using streams for speed and batch for correctness, or one replayable stream for both.

Dimensions change over time: a customer moves city, a product changes category. A slowly changing dimension strategy decides how history is preserved.

• Type 1: overwrite the old value. Simple, but history is lost.
• Type 2: add a new row per change with valid_from and valid_to dates plus a current flag. This preserves full history and supports point-in-time reporting.
• Type 3: keep a previous_value column, limited to one prior state.
• Type 4 and 6: hybrids using history tables or current plus historical rows.

Type 2 is the most common for analytics. A fact row stores the dimension surrogate key valid when the event occurred, so joining facts to the correct historical version is automatic.

SELECT * FROM dim_customer
WHERE customer_id = 42 AND is_current = TRUE;

The downside is that the dimension grows and merge logic is more complex.

Data quality checks should run as first-class pipeline steps, failing or quarantining bad data rather than silently loading it.

Dimensions to test:

• Completeness: no unexpected nulls in required columns.
• Uniqueness: primary keys are unique.
• Validity: values fall in allowed ranges or formats, such as ISO dates.
• Consistency: totals reconcile across tables.
• Freshness: the table was updated within the expected window.
• Referential integrity: foreign keys exist in the dimension.

Implement with tools like Great Expectations, dbt tests, Soda or custom assertions. Add a circuit breaker that halts downstream jobs on critical failures, and route failed rows to a quarantine table for inspection. Publish metrics and alerts so issues are caught before they reach dashboards. Record expectations as code and review them like application tests.

Partitioning splits a large dataset into smaller physical chunks so queries and maintenance target only relevant data. Common strategies are range (by date), list (by region) and hash (for even distribution). In warehouses, partition pruning means a query filtered by date scans only matching partitions, cutting cost and time dramatically.

CREATE TABLE events (
  event_id BIGINT,
  event_date DATE,
  user_id BIGINT
)
PARTITION BY RANGE (event_date);

Considerations:

• Choose a partition key used in most filters, usually a date.
• Avoid too many tiny partitions, which add metadata and planning overhead.
• Watch for skew: one huge partition becomes a bottleneck.
• Clustering or sort keys inside a partition further speed range scans.

Partitioning is separate from bucketing or clustering, which organize data within partitions by another key for joins.

An idempotent operation produces the same result whether it runs once or many times. Pipelines retry on failure, run backfills and get replayed, so non-idempotent logic creates duplicates or double counting.

Techniques:

• Overwrite partitions instead of appending: write to a date partition and replace it atomically.
• MERGE or upsert on a natural key rather than a blind INSERT.
• Deduplicate by a deterministic event id with a unique constraint.
• Use deterministic run identifiers and write to temporary tables before swapping.

MERGE INTO sales t
USING staging s ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET amount = s.amount
WHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (s.order_id, s.amount);

Test idempotency by rerunning the same task and confirming row counts and checksums stay identical. It is the foundation of safe retries and reliable backfills.

The medallion architecture organizes a lakehouse into three refinement layers.

• Bronze (raw): data landed as-is from sources, append-only, with ingestion metadata. It is the source of truth and enables replay.
• Silver (validated): cleaned, deduplicated, conformed data, joined into entities and typed correctly. Analysts and engineers build on this layer.
• Gold (curated): business-level aggregates and marts optimized for BI, dashboards and machine learning.

Each layer is a table, often in Delta Lake or Iceberg, and transformations move data bronze to silver to gold. Benefits include clear ownership, incremental processing, and the ability to rebuild downstream layers from raw data. Costs come from storing multiple copies and maintaining transformation jobs. Keep schemas explicit with a catalog or data contract so consumers can rely on silver and gold tables.

Airflow models a workflow as a DAG: a directed acyclic graph of tasks with dependencies. A scheduler parses DAG files, creates DAG runs per schedule interval, and queues tasks whose upstream dependencies succeeded. Workers execute tasks and the metadata database tracks state.

with DAG("daily_sales", schedule="@daily", start_date=dt(2024,1,1)) as dag:
    extract = PythonOperator(task_id="extract", python_callable=extract_fn)
    transform = SQLOperator(task_id="transform", sql=TRANSFORM_SQL)
    load = PythonOperator(task_id="load", python_callable=load_fn)
    extract >> transform >> load

Key concepts: idempotent tasks, retries with backoff, backfills, sensors that wait for data, and catchup for historical runs. Keep each task atomic and avoid heavy work at parse time. Failures surface per task with logs, and SLA alerts notify on lateness. Alternatives include Dagster, Prefect and cloud schedulers.

Late data breaks the assumption that all events for a time window have arrived. Common strategies:

• Watermarks: define how long to wait for late events before closing a window. A ten-minute watermark holds windows open ten minutes past the latest event time. Events after that are dropped or routed separately.
• Allowed lateness: keep window state longer and update results when late events arrive, emitting revised aggregates downstream.
• Reprocessing: store raw immutable events in the lake, then rerun the affected partition to correct results. Idempotent writes and partitioning by event date make this safe.
• Reconciliation: combine a streaming near-real-time view with a batch correction that overwrites the same partitions.

The right choice balances latency, cost and correctness. Billing pipelines usually favor a batch correction layer, while dashboards may accept approximate streaming results.