Data Engineer Interview Questions

I start by ruling out infrastructure causes: was there a cluster resize, a warehouse suspend/resume, or a concurrent workload spike that would explain the slowdown without any query change? Then I pull the execution plan for the current slow run and compare it to a cached plan or a fresh EXPLAIN from last week if available, looking for plan regression, the optimizer choosing a different join strategy or index path. The most common cause of spontaneous regression without a data volume change is stale statistics: if the table was heavily updated and statistics were not refreshed, the optimizer's cardinality estimates will be wrong and it may choose a hash join that spills to disk over a merge join that would fit in memory. I also check for data skew changes: if a new customer with 10 million orders joined last week and the query joins on customer_id, one partition or worker node may now be processing orders of magnitude more rows than others. Finally I look at locking and concurrency, a new batch process writing to the same table can cause read waits that look like query slowness.

A star schema centers a fact table surrounded by fully denormalized dimension tables, forming a single join hop from fact to any dimension. I reach for it in OLAP workloads where query performance and analyst simplicity matter more than storage efficiency, because denormalized dimensions eliminate multi-table joins and let engines like Snowflake or BigQuery prune aggressively. A snowflake schema normalizes dimensions into sub-dimensions, which saves storage and enforces referential integrity but adds join complexity that slows ad-hoc queries. In practice, if the dimensions are stable and wide (hundreds of attributes), star wins on read speed and BI tool compatibility. I only snowflake when dimension cardinality creates genuinely unmanageable duplication or when write velocity on dimension attributes is high enough that update anomalies become a real operational risk.

In ETL (Extract, Transform, Load), data is cleaned and shaped in a dedicated transformation layer before landing in the destination, which keeps the warehouse lean but tightly couples your logic to the pipeline. In ELT (Extract, Load, Transform), raw data lands first and transformations happen inside the target system using its own compute, which is the default approach on modern cloud warehouses like BigQuery, Snowflake, and Redshift where storage is cheap and SQL-at-scale is fast. I reach for ETL when the destination system has strict storage costs, when data must be masked or filtered before it ever touches the warehouse for compliance reasons, or when transformation logic requires imperative code that SQL cannot express cleanly. ELT wins when I want full raw history available for reprocessing, when business definitions change frequently (I just redeploy a dbt model rather than rerunning the entire pipeline), and when the warehouse has enough compute headroom to absorb the transformation cost. The real deciding factor is where your bottleneck lives: if compute in the destination is cheap and elastic, ELT reduces operational surface area significantly.

Apache Spark is an open-source distributed compute engine that processes large datasets in memory across a cluster of machines, using a resilient distributed dataset (RDD) abstraction and a higher-level DataFrame / Dataset API on top of it. I reach for Spark when my workload involves complex multi-step transformations, machine learning pipelines, or custom Python/Scala logic that a warehouse's SQL dialect cannot express cleanly. Warehouses like BigQuery or Snowflake are the better default for ad-hoc analytical SQL because they handle optimization, scaling, and storage automatically with near-zero ops overhead. Spark becomes the right call when I need fine-grained control over partitioning and execution, need to join data that lives outside the warehouse (object storage, Kafka, APIs), or am running iterative ML workloads where in-memory caching across iterations yields a large performance advantage. The key tradeoff is operational cost: Spark clusters require tuning and infrastructure management that a managed warehouse abstracts away.

I treat data quality as a first-class concern at every layer of the pipeline. At ingestion I enforce schema validation and null checks using tools like Great Expectations or dbt tests, failing fast before bad data propagates downstream. For ongoing monitoring I track row count anomalies, distribution drift, and freshness SLAs with either a purpose-built observability platform like Monte Carlo or custom Airflow sensors that alert on threshold breaches. When an issue surfaces I triage by severity: critical issues trigger an immediate circuit breaker that halts downstream jobs and pages on-call, while lower-severity issues are quarantined into a dead-letter table for async review so the pipeline keeps running. After remediation I always do a reconciliation pass comparing source record counts and checksums to the target to confirm the fix is complete, and I write a post-mortem that feeds back into the test suite so the same class of issue can never slip through silently again.

I would start at the producer side by instrumenting the application to emit events to a Kafka topic, partitioned by user or session ID to preserve ordering guarantees. A Flink or Spark Structured Streaming job then reads from Kafka, applies lightweight transformations like sessionization and deduplication, and writes micro-batches to a columnar format such as Parquet or Delta Lake on object storage. For the serving layer I expose two surfaces: a hot path backed by something like ClickHouse or Druid for sub-second dashboard queries on the last 24 hours, and a cold path that lands the full history in a data warehouse like BigQuery or Snowflake for longer-range analysis. I keep end-to-end latency targets explicit in the design document and wire in dead-letter queues and alerting on consumer lag from day one so the team can catch backpressure before it becomes an incident.