Data Engineer Interview Questions

A nightly ingestion DAG failed roughly twice a week from upstream schema drift. I added contract tests and a quarantine step so bad records were isolated instead of failing the whole run, and I set up alerts on the source schema. Failures dropped to near zero and analysts stopped getting paged. The fix was treating data quality as a first-class part of the pipeline, not an afterthought.

Stakeholders wanted real-time dashboards, but streaming everything would have tripled our warehouse bill. I profiled which datasets actually needed sub-minute freshness versus hourly, and used streaming only for the fraud-critical feeds. That gave the business what it needed while keeping cost flat. I framed it as matching freshness to business value rather than defaulting to real-time everywhere.

An analyst asked for a one-off pipeline that duplicated logic in three existing marts. Instead of building a fourth, I showed how a small change to an existing dbt model met their need and reduced maintenance. They got their data faster and we avoided drift between near-identical tables. I try to consolidate rather than proliferate pipelines.

Finance stopped trusting a revenue table after numbers shifted unexpectedly. I traced it to a late-arriving-data issue, added row-count and freshness checks, and published a documented SLA. I also exposed the lineage so they could see exactly where each number came from. Once they could verify the numbers themselves, adoption recovered.

We moved from on-prem Hadoop to Databricks. I ran the two systems in parallel and reconciled outputs row-by-row before cutting over a dataset, migrating in slices rather than all at once. When a discrepancy appeared I could isolate it to one table instead of the whole platform. The phased approach meant zero analyst-visible breakage.

We had a custom Scala framework wrapping Spark that only one person understood. I replaced the common patterns with standard dbt models and Airflow operators that the whole team could maintain. Onboarding time for new pipelines dropped from days to hours. I optimized for the team's ability to operate it, not for cleverness.

I'd land events in Kafka, then fork into two paths: a streaming consumer writing to a real-time store for dashboards, and a batch sink to cloud storage as the immutable raw layer. From storage I'd run Spark/dbt transformations into a warehouse with bronze/silver/gold layering. This lambda-style split gives low-latency views plus reprocessable history, with schema validation at the Kafka boundary.

For attributes where history matters, I'd use SCD Type 2: each change creates a new row with effective-from/effective-to dates and a current flag, so facts join to the version that was valid at event time. For attributes where only the latest value matters, Type 1 overwrites in place. dbt snapshots handle Type 2 cleanly. The choice depends on whether the business needs to reconstruct the past.

I'd open the Spark UI and look for skew — a few tasks taking far longer than the rest usually points to an unbalanced join key. I'd also check for excessive shuffles, spills to disk from undersized executors, and small-file problems on read. Often the fix is repartitioning on a better key, salting a skewed join, or right-sizing partitions.

ELT loads raw data into the warehouse first and transforms in-place using the warehouse's compute, which suits modern cloud warehouses like Snowflake or BigQuery where storage is cheap and SQL transforms are scalable. ETL transforms before loading, which makes sense when the target is constrained or when you must mask sensitive data before it lands. Most new stacks favor ELT with dbt for transparency and easy reprocessing.