Data Engineer Interview Questions

I start by understanding the SLA: how fresh does the data need to be, and what is the cost of a pipeline failure downstream? Those two answers shape every architectural decision. For reliability, I build idempotent pipelines wherever possible, so that rerunning a job after a failure produces the same result without duplicating data. I use checkpointing for long-running jobs so failures do not restart from the beginning. I add data quality checks at each stage: schema validation on ingestion, row count checks between stages, and anomaly detection on key metrics at the output. For scalability, I separate compute from storage so I can scale each independently, and I design for incremental processing rather than full reloads where the data volume makes that practical. Observability is built in from the start: every pipeline emits run metadata so I can answer "what ran, when, and with what result" without digging through logs.

Data quality is not a separate concern from pipeline design; it is part of the pipeline itself. My approach has three layers. First, schema and type validation at ingestion: if upstream data changes shape unexpectedly, I want the pipeline to fail loudly at the entry point rather than propagate bad data silently. Second, business rule checks between stages: row counts that differ significantly from the previous run, null rates above a threshold in mandatory fields, or referential integrity failures between tables. Third, monitoring at the output: I track key business metrics over time and alert when they move outside expected ranges, because sometimes data quality issues are invisible to structural checks but obvious in the numbers. When a quality issue is detected, I prefer to quarantine the affected records and continue processing clean data rather than stopping the whole pipeline, unless the issue is severe enough to invalidate the entire dataset.

I start with the business requirement, not the technology. The key question is: what is the maximum acceptable latency between an event occurring and it being available for analysis or action? If the answer is hours or days, batch processing is almost always simpler and more cost-effective. If the answer is minutes or seconds, streaming becomes necessary. Beyond latency, I consider operational complexity: streaming systems are significantly harder to debug, replay, and maintain than batch jobs. For most analytical use cases I have worked on, micro-batch processing (running every few minutes with something like Spark Structured Streaming or Flink) gives acceptable latency at much lower operational cost than a true streaming architecture. I only commit to a pure streaming approach when the latency requirement genuinely cannot be met by micro-batch, because the operational cost is real and ongoing.

AI tooling has changed a few parts of my workflow in practical ways. For writing and reviewing SQL, particularly complex window functions or optimisation queries, AI helps me iterate faster and catches patterns I might miss. For writing pipeline documentation and data dictionary entries, AI drafts content that I then review and adjust, which saves significant time on a task that engineers typically skip. Where AI has become genuinely useful is in anomaly detection on pipeline outputs: rather than writing hand-coded threshold rules, I can use statistical models to flag when a metric is behaving unusually given its historical pattern. I am careful about using AI for schema design or data modelling decisions, because those decisions have long-term consequences and the model does not have the full business context. I treat AI as an accelerant for implementation and documentation work, but I keep the architectural decisions to myself and the team.

A daily ETL job that fed our marketing attribution model failed silently: it completed without errors but produced a partial result due to a timeout on one of the source API calls. The marketing team ran their weekly spend analysis on incomplete data and caught the discrepancy themselves two days later. When the issue was escalated I diagnosed the root cause in about an hour: the API timeout was set too low and the job had no record-count validation at the end. I fixed the immediate issue by increasing the timeout and rerunning the affected date range. Then I added a final-stage row count check and an alert that fires if the output row count falls below 90% of the seven-day average. The harder part was rebuilding trust with the marketing team: I walked them through exactly what had happened, which data was affected, and what we had put in place to prevent it.

We migrated our core transactional database from a monolithic Postgres schema to a new schema supporting multi-tenancy, while keeping the system live throughout. The challenge was that our analytics pipelines, reporting tools, and three downstream microservices all depended on the old schema. My approach was to run old and new schemas in parallel for six weeks, writing to both and reading from the old schema while the new one was being validated. I built a reconciliation job that ran nightly and compared row counts and key aggregates between the two schemas, alerting on any divergence. Migration of each consumer was done incrementally: analytics first (lowest risk), then reporting, then the microservices. The full migration took ten weeks with zero data loss and no downtime. The key to keeping it on track was treating the reconciliation job as the source of truth rather than trusting that "it looks correct".

A nightly Spark job that aggregated user activity data was taking six hours to complete, pushing into business hours and delaying dashboards the product team relied on. I profiled the job and found three issues: a shuffle-heavy join that was not taking advantage of broadcast joins for a small lookup table, a full table scan on a partitioned table because the partition filter was applied after the scan, and an output step writing thousands of small files. I replaced the large join with a broadcast join (the lookup table was under 100MB), pushed the partition filter into the read step, and coalesced the output to a reasonable number of files. Runtime dropped from six hours to under 45 minutes. The partition filter fix alone accounted for most of the gain, reducing the data scanned from the full table to about 3%.

A data warehouse stores structured, processed data optimised for analytical queries. It enforces schema on write, which means data is transformed and validated before it enters the warehouse. Query performance is predictable and fast because the data is modelled for the access patterns it serves. A data lake stores raw data in its original format, structured or unstructured, and applies schema on read. It is cheaper to store large volumes and more flexible for exploratory analysis or ML use cases where you do not know the access patterns in advance. The practical answer to which to use is: most organisations need both. The data lake holds raw and intermediate data; the data warehouse holds curated, business-ready data that analysts can query directly. The pattern I have used most is a medallion architecture (raw, clean, curated layers) where the curated layer is the warehouse and the earlier layers live in object storage.

I start by understanding who will query this data and how. Analysts and BI tools tend to want wide, denormalised tables that are easy to join and fast to query. Data scientists tend to want access to more granular, lower-level tables. For most analytics use cases I use a dimensional model (fact and dimension tables) because it is well understood, performs well with columnar storage, and makes it easy to add new dimensions without breaking existing queries. I avoid heavily normalised schemas in the analytics layer because the join overhead adds complexity for analysts. I also think about slowly changing dimensions upfront: if a customer changes their segment, do queries want to see what segment they were in at the time of the event, or their current segment? That decision has to be made at modelling time, not when someone asks the question two years later.

I would start by questioning whether it truly needs to be real-time. If the answer is yes, my architecture would have four layers. Ingestion: events flow from source systems into a message queue such as Kafka or Pub/Sub, which decouples producers from consumers and gives replay capability. Stream processing: a Flink or Spark Structured Streaming job reads from the queue, applies transformations and aggregations, and writes results to the serving layer. Serving: a fast analytical store such as ClickHouse, Druid, or BigQuery handles the query load from dashboards or applications. Monitoring: the pipeline emits lag metrics and I alert if lag exceeds the target. The parts I spend the most time on upfront are schema design at the ingestion point and deciding exactly what state needs to be maintained in the stream processor, because both are expensive to change once the system is live.