Data platform engineer system design interview questions

As asked

Your company has 40 data teams writing Airflow or Dagster jobs in different styles. How would you build a shared orchestration platform without becoming a ticket queue?

Sample answer outline

The platform should provide paved roads: project templates, deployment automation, secrets access, standard retries, alerting, and lineage out of the box. Teams should own their DAG code while the platform owns runtime reliability, conventions, and guardrails. Multi-tenancy needs isolation for dependencies, quotas, credentials, and noisy workloads. Adoption is won by making the easiest path the best path, not by banning all custom jobs on day one. Candidates often miss the product-management side of platform work: documentation, migration support, and measuring whether teams are actually faster.

Expect these follow-ups

• What should be centralised and what should remain team-owned?
• How do you prevent one team's job from starving everyone else?
• What platform metrics would you report after six months?

As asked

Design a lakehouse architecture from scratch for a 500-person company where data engineers, data scientists, and analysts all need access. The system must support batch and streaming ingestion, row-level security, cost allocation per team, and query SLAs for business-critical dashboards. Walk me through your architecture.

Sample answer outline

A strong answer covers: landing zone on S3 with raw, bronze, silver, and gold layers; Iceberg as the table format for ACID support and time travel; Unity Catalog or Apache Polaris for governance and row-level security; a streaming ingestion lane (Kafka to Flink to Iceberg) and a batch lane (Airflow-orchestrated Spark); compute separation with Trino for interactive queries and Spark for heavy ETL; compute pools reserved for SLA-bound dashboards; and a tagging strategy for cost allocation by team using S3 prefix and query tags.

Expect these follow-ups

• How would you enforce storage budget quotas per team without blocking urgent ad-hoc queries?
• What happens to your SLA guarantees during a major Spark job that saturates the shared compute pool?

As asked

You need to backfill five years of transaction data into a new warehouse model while the daily pipeline keeps running. Walk me through the plan.

Sample answer outline

Separate the historical backfill from the incremental daily path, but make them use the same transformation logic where possible. Chunk by a natural partition such as transaction_date, run in bounded parallelism, and record per-partition status so failed chunks can be retried. Keep the target isolated until validation passes, then cut over views or downstream consumers. Compare row counts, sums, null rates, and sampled record-level values against the old source of truth. The common mistake is treating the backfill as a one-off script with no resume, validation, or cutover plan.

Expect these follow-ups

• How do you handle late-arriving records during the backfill window?
• What validation would convince finance to trust the new model?
• How do you avoid overwhelming the source database?

As asked

Design a streaming ingestion platform that must handle 100,000 events per second from 200 microservices, deliver events to both a real-time query layer and a lakehouse, guarantee at-least-once delivery, and support schema evolution without taking the platform down.

Sample answer outline

The architecture should route all events through Kafka as the backbone, with producers using Avro and a Schema Registry for schema enforcement and evolution. A Kafka Connect cluster handles delivery to S3/Iceberg via the Iceberg Sink connector. A separate Flink cluster reads from Kafka for real-time analytics, applying watermarks and stateful aggregations. Schema evolution uses backward-compatible additions enforced at the registry. Monitoring covers consumer lag, schema registry error rates, and partition throughput.

Expect these follow-ups

• How do you handle a burst that triples the event rate for 10 minutes?
• What is your plan when a producer accidentally publishes a breaking schema change?

As asked

Your company has 50 analysts computing the same metric (monthly active users) in 30 different ways in ad-hoc SQL, causing contradictory numbers in board presentations. Design a centralized metrics layer that defines metrics once, makes them accessible to BI tools and Python notebooks, and prevents conflicting definitions.

Sample answer outline

A metrics layer (dbt metrics, Cube, or MetricFlow) centralizes metric definitions as versioned YAML: the formula, time dimensions, filters, and entity relationships are declared once. A semantic layer API translates downstream queries into optimized SQL against the lakehouse. BI tools connect via JDBC or REST. Governance around metric changes requires PR review and approval, and metric deprecation is announced with a migration period. The candidate should discuss how to handle metrics with different grains (user-level vs. account-level aggregations).

Expect these follow-ups

• How would you handle a metric that requires a complex funnel join across five tables?
• How do you expose metrics to a data scientist who prefers Python over SQL?

As asked

Design a production CDC pipeline that replicates 50 MySQL databases (each with 200 tables) into a lakehouse in near real time, handles schema changes in MySQL automatically, and allows analysts to query data that is no more than 5 minutes stale.

Sample answer outline

The design uses Debezium connectors managed by a Kafka Connect cluster, one connector per MySQL server writing to per-table Kafka topics. A Flink job reads from these topics, applies schema-on-read with Avro deserialization, and upserts into Iceberg tables using the primary key. Schema changes in MySQL are emitted as DDL events by Debezium; the Flink job handles ADD COLUMN via schema evolution in Iceberg and alerts on breaking changes. The lakehouse layer uses Iceberg MoR tables for fast upserts, with compaction running every 30 minutes.

Expect these follow-ups

• How do you handle a MySQL table that has no primary key?
• What is your recovery plan if the Debezium connector falls behind by more than the binlog retention window?

As asked

Your pipelines cover 200 tables and stakeholders only find out about data quality issues when a dashboard breaks. Design a proactive data observability platform that detects anomalies before they reach consumers, routes alerts to owners, and tracks incident history.

Sample answer outline

The platform collects table-level statistics (row count, null rate, schema hash, freshness timestamp) after each pipeline run and compares them to a rolling baseline using statistical anomaly detection (z-score or CUSUM). Alerts route to dataset owners via Slack or PagerDuty, tagged with severity. An incident registry tracks open issues, links to the lineage graph to identify downstream impact, and closes incidents automatically when a clean run passes. The candidate should mention tools like Monte Carlo, Bigeye, or custom solutions built on dbt tests and Airflow callbacks.

Expect these follow-ups

• How would you set different freshness SLAs for different tables based on their criticality?
• How do you avoid alert storms when a single upstream failure cascades to 50 downstream tables?

As asked

You are building a platform that allows 20 domain teams to own, publish, and share data products autonomously. Each team should be able to provision infrastructure, register their data product in a catalog, set access policies, and monitor quality without opening a ticket to the central platform team. How do you design this?

Sample answer outline

The design includes a control plane with a Terraform-based infrastructure vending machine that provisions Iceberg namespaces, Kafka topics, and IAM roles per team from a self-service UI. A data product registry (built on DataHub or a custom catalog) accepts declarative product specs via git. Policy enforcement is automated via Open Policy Agent or Unity Catalog row filters applied at registration time. Monitoring dashboards are auto-provisioned per product using pre-built templates. The central team maintains the control plane and policies but is not in the critical path for product creation.

Expect these follow-ups

• How do you prevent a domain team from accidentally sharing PII in a public data product?
• What guardrails prevent a team from provisioning infrastructure that blows the compute budget?

As asked

You operate a shared Trino cluster used by 50 teams. The monthly bill is 200K USD but no one knows which team is responsible for which cost. Design a cost attribution system that gives each team visibility into their spend and creates incentives to write efficient queries.

Sample answer outline

The design captures Trino query events (user, query, bytes scanned, CPU time, wall time) via the event listener plugin and writes them to a cost attribution table in the lakehouse. A nightly job joins query metrics with cloud pricing to produce per-user and per-team cost summaries. Teams receive weekly email reports and a self-service dashboard. Showback (informational) is introduced first; chargeback (actual billing back to team budgets) is introduced after teams have time to optimize. Query policies enforce scan limits per team to prevent runaway costs.

Expect these follow-ups

• How do you handle a shared BI tool where all queries appear under a single service account?
• How would you incentivize partition pruning without mandating it in a way that blocks legitimate full scans?