Run a mock round

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.

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.

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.

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.

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.

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.

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.

Both are dimensional models. A star schema has one central fact table surrounded by denormalized dimension tables. Each dimension is a single table, so joins stay simple and queries run fast. A snowflake schema normalizes dimensions into multiple related tables, for example splitting a product dimension into product, category and supplier tables.

Star advantages: fewer joins, simpler SQL, better query performance, easier for BI tools. Snowflake advantages: less redundancy, smaller storage, and dimensions that are easier to maintain when hierarchies change.

Most analytics warehouses use star schemas because storage is cheap and query simplicity matters. Snowflaking helps when a dimension is genuinely shared or very wide. The fact table holds foreign keys and numeric measures at a defined grain, and declaring that grain precisely is the most important modelling decision.

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.