System design interview questions for Data Engineer roles

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.

Put the events into Kafka, run a streaming job that writes every raw event to a bronze Delta table, and a second streaming job that computes per-minute aggregates with a watermark for late events and writes them to a small gold table the dashboard reads. Size Kafka partitions for the peak, keep state bounded with watermarks, and rely on checkpoints plus Delta for exactly-once results.

1. Clarify

• Latency: "every minute" for the dashboard; raw data can be minutes behind.
• Correctness: are counts allowed to change when late events arrive? How late can events be (mobile apps offline)?
• Volume: 50,000 events per second at peak at about 1 KB is roughly 50 MB/s, about 4 TB a day before compression.
• Retention and privacy: how long raw events are kept, and which fields are personal.

2. The design

Apps and web ──▶ collector API ──▶ Kafka topic "clicks" (keyed by user_id)
                                      │
               ┌──────────────────────┴─────────────────────┐
     Stream 1: raw events                      Stream 2: per-minute metrics
     ──▶ bronze.clicks (Delta, append)         watermark 15 min, 1-minute windows
                                               ──▶ gold.clicks_by_minute ──▶ dashboard

3. Key decisions

• Kafka partitions set the parallelism: Spark reads one partition per task by default. Pick enough for peak throughput with headroom (for example 48 to 96), because adding partitions later changes which partition a key goes to.
• Key by user ID so each user's events stay in order within a partition, which matters for sessions and funnels.
• Two streams, not one. The raw stream is simple and must never fall behind; the aggregate stream holds state. Separate checkpoints mean you can change or restart one without touching the other.
• Event time and watermark. Group by the time the event happened, not when it arrived. A 15-minute watermark lets late events update recent windows and lets Spark drop older state, so memory stays bounded. Events later than that are not counted in the live view; the bronze table still has them.
• Exactly-once. Kafka offsets in the checkpoint plus Delta's transactional sink mean a restart neither loses nor duplicates results.
• Output mode. Update mode lets the dashboard see a minute's count as it grows; append mode writes each window once, after the watermark passes.

4. Operations

• Back-pressure: cap maxOffsetsPerTrigger so a restart after downtime catches up in steady batches.
• Monitor input rate against processing rate, batch duration, and the lag between latest event time and now. Alert before lag grows.
• Small files: compaction or optimized writes on bronze.
• Corrections: the dashboard is the fast view; a nightly batch job over bronze produces the final daily numbers for finance.
• Retention: Kafka keeps days for replay; bronze keeps the long-term record, with personal fields restricted.

What interviewers want: back-of-envelope sizing, Kafka partitioning and keys, event time with a watermark and what it does to late data, separate raw and aggregate streams, exactly-once with checkpoints and a transactional sink, and a batch layer for final numbers.

Treat deletion as a pipeline, not a manual task. Requests go into a deletion requests table; a scheduled job deletes the customer from bronze first and lets the deletes flow to silver and gold; table maintenance then removes the deleted rows from old files and history; and a report proves each request was completed. Design the data so personal fields are few and easy to find, and remember that the data also lives outside the lakehouse.

1. Where personal data hides in a lakehouse

• Current tables in every layer, and copies made by people (exports, sandbox tables).
• Old table versions: a Delta DELETE writes new files, but the old files still hold the rows until VACUUM removes them, 30 days of history by default.
• Deletion vectors: deleted rows are marked, not physically removed, until the files are rewritten.
• Change feeds, Kafka topics, raw files and backups upstream and downstream.
• Logs, ML training sets and models built from the data.

2. Shrink the problem by design

• Use a surrogate key (customer_id) everywhere, and keep names, emails, phones and addresses in a few PII tables. Deleting those rows severs the link; facts such as order amounts can stay.
• Tag PII columns in the catalog so you can list every table that holds them.
• Pseudonymise in analytics layers: gold tables rarely need names at all.

3. The deletion pipeline

deletion_requests (customer_id, requested_at, status)
   ──▶ daily job:
       1. DELETE from bronze PII tables and raw events for those IDs
       2. propagate to silver and gold (MERGE/DELETE, or refresh materialized views)
       3. REORG ... APPLY (PURGE) where deletion vectors are on
       4. VACUUM within the retention window
       5. mark request done, with table names and row counts

• Bronze first, so a later rebuild from bronze cannot bring the person back.
• Streaming tables expect append-only sources. Delete from the source and from the streaming table, and set the streaming read to skipChangeCommits so it ignores the delete commits.
• Retention fits the deadline: with 30 days to comply, a 7-day VACUUM retention gives you time to run the job weekly and still finish.

4. Outside the lakehouse

• Set Kafka retention and raw file lifecycle rules shorter than the deadline, or delete there too.
• Backups: either expire within the deadline or keep a suppression list that is applied whenever a backup is restored.
• ML: record which training sets included the person, and retrain on schedule without them.

5. Prove it

An audit table per request: what was deleted where, when, and by which job run. Run a monthly check that searches every PII-tagged table for completed IDs. Zero hits is the evidence regulators and auditors ask for.

What interviewers want: knowing that DELETE alone leaves data in history and deletion vectors, a request-driven pipeline starting at bronze, purge and vacuum with retention matched to the deadline, surrogate keys to limit where PII lives, upstream copies and backups, and an audit trail.

Fix the code, then rebuild the affected tables from the raw layer in date-range chunks into a separate copy, compare the old and new numbers, and swap each range in atomically. Every chunk must be idempotent, so a failed or repeated chunk is harmless, and you must be able to roll back. Most of the work is in planning the scope and the comparison, not the compute.

1. Scope it

• Which tables are wrong? Follow lineage (Unity Catalog records it down to the column) from the buggy transformation to every downstream table, job and dashboard, and ask about exports it cannot see.
• Is the raw input still there? Reprocessing needs bronze for the whole 3 years. If part of it is gone, decide what to do about that period before you start.
• Who consumes the numbers? Finance may need to approve changed historical figures; some may have been reported externally.

2. Build next to production, not on top of it

Write the fixed pipeline to new tables (for example gold_v2.revenue_daily). A shallow clone of the current table is a cheap starting point for testing; the backfill itself writes fresh data.

3. Run in chunks

INSERT INTO gold_v2.revenue_daily
REPLACE WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01'
SELECT ... FROM silver.orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';

• One month at a time: small enough to rerun cheaply, large enough to finish in reasonable time.
• Idempotent: REPLACE WHERE replaces exactly that month atomically, and fails if the query produces rows outside the range, which catches filter bugs. Rerunning a month gives the same result.
• Track progress in a control table (range, status, row counts, run ID), so the backfill can stop and resume.
• Throttle so the backfill does not starve daily jobs of compute.

4. Compare before switching

For every month: row counts, totals by key dimensions, and the difference between old and new. Expect differences only where the bug applied; investigate any others. Share the summary with the people who own the numbers.

5. Switch with a way back

• Point the dashboards at the new table (or swap with a view), in one change.
• Keep the old table, and rely on Delta history, until the new numbers have been accepted.
• New daily runs write with the fixed code from the moment of the switch, so there is no gap.

6. Prevent the next one

Add a test that would have caught the bug, and a reconciliation of revenue against the finance system, so a calculation drift shows up in days, not years.

What interviewers want: scoping by lineage, rebuilding from raw data, building beside production, chunked idempotent overwrites, a control table, numeric comparison and sign-off, an atomic switch with rollback, and a test that stops a repeat.

Choose the isolation model by what each tenant needs, then enforce it in the platform, not in each query. For most SaaS analytics, keep shared tables with a tenant_id on every row and attach a row filter that the platform applies to every query; give large or regulated customers their own schema or catalog. Map users to tenants in one table, test the boundary continuously, and never rely on a dashboard's WHERE clause for security.

1. Three isolation models

With 2,000 tenants, a schema each means 2,000 copies of every table and every pipeline change applied 2,000 times. A common answer is shared tables for most, with the option to move a large customer to its own catalog.

2. Enforce it in the platform

CREATE FUNCTION gov.tenant_filter(t STRING)
RETURN EXISTS (
  SELECT 1 FROM gov.user_tenants u
  WHERE u.user_email = current_user() AND u.tenant_id = t
) OR is_account_group_member('platform_admins');

ALTER TABLE analytics.orders SET ROW FILTER gov.tenant_filter ON (tenant_id);

• Every query, from a dashboard, a notebook or an API, gets the filter. A forgotten WHERE cannot leak data.
• Keep the function simple and fast: it runs for every query. Where a lookup is slow, give each tenant's users a group and test group membership instead.
• Across many tables, apply the same rule through attribute-based policies on tagged tables, instead of setting it table by table.
• Column masks hide sensitive columns (emails, card numbers) even inside a tenant, for users without the right role.

3. Identity and mapping

• One user_tenants table is the source of truth, fed from the product's account system, with an owner and an audit trail.
• Service accounts for embedded dashboards act on behalf of one tenant at a time, with the tenant taken from the authenticated session, never from a URL parameter.

4. The pipelines

• tenant_id is required (not null) in every silver and gold table, and checked by expectations.
• Aggregates are computed per tenant. Cross-tenant tables (benchmarks, internal reporting) live in a separate schema with no customer access.
• Caches and extracts are keyed by tenant.

5. Prove it

Automated tests log in as a user of tenant A and query every exposed table, expecting zero rows from tenant B. Alert on any table without a filter or policy, and review access logs for queries returning several tenants.

What interviewers want: weighing shared, per-schema and per-account isolation, platform-enforced row filters rather than query discipline, a single user-to-tenant mapping, tenant IDs enforced through the pipelines, column masks, and continuous cross-tenant tests.

The training data leaks the future. Joining labels to today's features means a customer who churned in March is described by data from after March, including signs of the churn itself (zero logins, a cancelled plan). The model learns from information it will never have at prediction time. The fix is to keep features as a history with timestamps and build training sets with point-in-time joins: for each label, take the latest feature values known before the label's time.

1. What goes wrong

logins_30d = 0 was computed after C17 left. In production, at the moment of prediction, the model never sees anything like that, so accuracy collapses.

2. Store features as history

Each feature table has a key and a timestamp:

customer_features(customer_id, as_of_ts, logins_30d, tickets_90d, plan, ...)

A daily (or streaming) job appends a row per customer when values change, computed only from events up to as_of_ts. Never overwrite history.

3. Build training sets with an AS OF join

For each label row (customer_id, label_ts, churned), join the feature row with the same customer and the greatest as_of_ts that is earlier than label_ts. In plain SQL:

SELECT customer_id, label_ts, churned, logins_30d, tickets_90d, plan
FROM (
  SELECT l.customer_id, l.label_ts, l.churned,
         f.logins_30d, f.tickets_90d, f.plan,
         ROW_NUMBER() OVER (PARTITION BY l.customer_id, l.label_ts
                            ORDER BY f.as_of_ts DESC) AS rn
  FROM labels l
  JOIN customer_features f
    ON f.customer_id = l.customer_id
   AND f.as_of_ts < l.label_ts - INTERVAL 1 DAY
) t
WHERE rn = 1;

The - INTERVAL 1 DAY represents the delay between an event and the feature being available in production. Match the real delay, or leakage creeps back in. Databricks' feature store performs this AS OF join for you when the feature table has a timestamp key.

4. Serve the same features online

The model in production reads the latest row per customer from the same feature definitions, from an online store or a fresh table. One definition for training and serving prevents a second kind of skew: the same feature computed differently in two places.

5. Guardrails

• A leakage check: features with suspiciously high importance, or that change right at the label time, get reviewed.
• Backtesting: train on data up to a date, test on the period after, the way the model will actually be used.
• Performance: cluster feature tables by key and timestamp so point-in-time lookups stay fast as history grows.
• Monitoring: compare the distribution of features in production with the training set, and alert on drift.

What interviewers want: naming the problem as label leakage, features stored as timestamped history, point-in-time joins with a realistic availability delay, one feature definition for training and serving, and backtesting as the evaluation.