Design a pipeline that brings the orders, customers and products tables from the company's PostgreSQL database into a lakehouse for daily reporting, with history and room to grow.

Capture changes from the database, land them unchanged in a bronze layer, apply them in order to clean silver tables (current state and history), and build gold tables for reports on top. Make every step incremental, idempotent and replayable from bronze, check quality at the boundaries, and monitor freshness. Before drawing anything, pin down the requirements, because they decide most of the design.

1. Clarify first

Assume: daily reports with an hourly target later, 200 GB with 2 million changes a day, history needed for customers, and log-based CDC allowed.

2. The design

PostgreSQL ──CDC (logical replication / Debezium)──▶ Kafka or files
   ──▶ bronze: raw change events, append-only, with source position and load time
   ──▶ silver: customers (SCD2), orders and products (current state), cleaned and typed
   ──▶ gold: daily sales by region and product, customer metrics
   ──▶ BI dashboards and finance extracts

• Ingestion: change data capture, not full reloads. Reading the database's log captures every insert, update and delete in order, without heavy queries on the production database. An updated_at watermark misses deletes and long transactions.
• Bronze keeps everything as it arrived, append-only, with the source's log position (LSN) and the load time. It is the replay point: any silver or gold table can be rebuilt from it.
• Silver applies changes in order. AUTO CDC (or a MERGE that deduplicates by key and compares sequence numbers) handles duplicates and late events. Customers as SCD type 2 for history, orders and products as type 1.
• Gold is modelled for questions: facts and dimensions, or the few aggregates dashboards need. Fewer tables, stable names, documented definitions.

3. Quality and trust

• Expectations at silver: keys not null, amounts not negative, valid statuses. Drop or quarantine bad rows with a count, and fail the run on rules that must never break.
• Reconciliation: daily row counts and sums per table, source against silver.
• Freshness and volume alerts per table, so "the dashboard is stale" is caught by you first.

4. Operations

• Orchestrate as one job with dependencies: ingest, silver, gold, checks. Retries on each task.
• Idempotent steps: rerunning a day gives the same result, because silver merges by key and sequence and gold overwrites its own date range.
• Backfills read from bronze for a date range; no need to touch the source again.
• Access: personal data stays in silver with restricted access; gold exposes what reports need. Plan deletion requests from the start.

5. Growing to hourly or near real time

The same layers work with streaming: run the bronze and silver steps continuously and gold more often. Nothing in the design assumes daily.

What interviewers want: clarifying questions before boxes, CDC over full reloads, a raw replayable layer, ordered and idempotent change application, SCD where history matters, quality checks and reconciliation, and a design that moves from daily to streaming without a rewrite.