26 Data Engineer Interview Questions and Answers

What's the difference between the main SQL JOIN types?

INNER JOIN returns only matching rows; LEFT JOIN keeps all left-table rows and fills nulls where there's no match (RIGHT does the reverse); FULL JOIN keeps unmatched rows from both sides. Choosing the right one depends on whether you need unmatched rows preserved.

What they're testing: and which is correct for a given question

What's the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters after aggregation, so it can reference aggregate results like COUNT or SUM. You use WHERE for raw-row conditions and HAVING for conditions on grouped results.

What they're testing: a fundamentals check that reveals real SQL familiarity

What is a primary key versus a foreign key?

A primary key uniquely identifies each row in a table. A foreign key references a primary key in another table, enforcing a relationship and referential integrity between them.

What they're testing: basic data-modeling literacy

What's the difference between a transactional and an analytical database?

Transactional (OLTP) databases handle many small, fast reads and writes for application operations. Analytical (OLAP) systems are optimized for large, complex queries over lots of data for reporting. They're tuned for opposite workloads.

What they're testing: OLTP versus OLAP workloads

What does a data pipeline do, in plain terms?

It moves data from where it's produced to where it's used, transforming and cleaning it along the way so it's ready for analysis or a model. Reliably getting the right data into the right shape and place is the whole job.

What they're testing: explaining the job clearly before the jargon

What's the difference between a database and a data warehouse?

A database usually powers an application with fast transactional operations; a data warehouse is built to store large volumes of historical data and run analytical queries efficiently. One serves the app, the other serves analytics.

What they're testing: purpose-built storage for transactions versus analytics

Write a query to find the second-highest value in a column.

One clean way uses a window function: rank the values with DENSE_RANK ordered descending, then select where the rank equals 2. Alternatively a subquery finds the max below the overall max. I'd mention handling ties and nulls.

What they're testing: subqueries or window functions

Explain window functions and give a real use case.

Window functions compute across a set of rows related to the current row without collapsing them like GROUP BY does. Real uses include ranking within groups, running totals, and comparing a row to the previous one — for example numbering each customer's orders by date.

What they're testing: ranking, running totals, per-group logic

What's the difference between ETL and ELT, and when do you choose each?

ETL transforms data before loading it into the destination; ELT loads raw data first and transforms it inside a powerful warehouse. ELT suits modern cloud warehouses that can transform at scale; ETL fits when you must clean or shrink data before it lands.

What they're testing: modern data-architecture judgment

How would you optimize a slow SQL query?

Read the query plan to see what's actually slow, add or fix indexes on filtered and joined columns, avoid scanning more than needed, and reduce work — select only required columns, filter early. I optimize based on the plan, not guesses.

What they're testing: indexing, reading the query plan, reducing scanned work

What is data partitioning, and why does it matter?

Partitioning splits a large table into smaller pieces by a key like date, so queries only scan the relevant partitions instead of the whole table. It dramatically improves performance and manageability at large scale.

What they're testing: performance and scale at large volumes

How do you model data for a warehouse?

Typically a star schema: a central fact table of events or measurements surrounded by dimension tables describing them. It denormalizes for fast analytical queries, unlike the heavily normalized design of a transactional database.

What they're testing: normalization versus star/snowflake schemas

How do you handle duplicate or dirty data in a pipeline?

Deduplicate on a stable key, validate records against expected types and ranges, and decide clearly what to do with bad rows — reject, quarantine, or fix. The key is doing this consistently and visibly, not silently dropping data.

What they're testing: deduplication and validation strategies

What's the difference between batch and streaming processing?

Batch processes data in chunks on a schedule; streaming processes each event as it arrives, in near real time. Batch is simpler and fine for periodic reporting; streaming suits low-latency needs like live dashboards or alerts.

What they're testing: latency, throughput, and when each fits

How do you write a query to find duplicate rows?

GROUP BY the columns that define a duplicate and use HAVING COUNT(*) > 1 to keep only groups that appear more than once. That surfaces exactly which values are duplicated and how many times.

What they're testing: grouping and having-count logic

What is a slowly changing dimension, and how do you handle it?

It's a dimension whose attributes change over time, like a customer's address. Common handling is Type 2 — keep history by adding a new row with validity dates — versus Type 1, which just overwrites. The choice depends on whether you need history.

What they're testing: tracking history in a warehouse

A pipeline failed halfway and left partial data. How do you handle it?

Design the pipeline so reruns are safe — idempotent loads keyed so a rerun replaces rather than duplicates, or transactional/atomic writes so partial data is never visible. Then I fix the cause and rerun. The principle is never leaving downstream consumers with silently incomplete data.

What they're testing: idempotency, recovery, and 'never silently corrupt'

How do you guarantee a pipeline never loses or double-counts records?

Use exactly-once handling: idempotent writes keyed on a stable identifier, checkpoints to track what's been processed, and reconciliation checks comparing source and destination counts. If something fails, you can resume from the checkpoint without loss or duplication.

What they're testing: exactly-once thinking, checkpoints, reconciliation

How would you design a pipeline that ingests millions of events per hour?

Stream or micro-batch the events, buffer through a queue to absorb spikes and provide backpressure, process in parallel, and write to storage optimized for the volume. I'd design for horizontal scaling and for graceful behavior when a downstream system slows.

What they're testing: throughput, batching versus streaming, backpressure

How do you detect when your data quietly became wrong?

Add data-quality checks — row counts, null rates, value ranges, and freshness — and alert when they deviate from expected. Validating completeness and distribution, not just that data arrived, is what catches silent corruption before analysts do.

What they're testing: quality checks and validating completeness, not just presence

How do you handle a schema change in an upstream source?

Detect it early with schema validation, version the schema, and design consumers to tolerate additive changes without breaking. For breaking changes, coordinate a migration so downstream jobs and tables update together rather than failing silently.

What they're testing: evolution, versioning, not breaking consumers

How do you optimize a query that joins two very large tables?

Join on indexed keys, filter and pre-aggregate before joining to shrink the data, partition on the join key so only relevant data is scanned, and be aware of how the engine shuffles versus broadcasts. The goal is to reduce the data each side brings to the join.

What they're testing: partitioning, broadcast versus shuffle, and pre-aggregation

Design a system to process and warehouse clickstream data for analytics.

Ingest events through a streaming queue to handle volume, process and enrich them, and load into a warehouse partitioned by time for efficient analytical queries. I'd cover deduplication, late events, schema evolution, and keeping query performance good as data grows.

What they're testing: ingestion, transformation, storage, query performance at scale

Design a daily reporting pipeline that must be correct even when a source is late.

Make runs idempotent so a late source triggers a safe rerun that overwrites rather than duplicates, and design the schedule to wait for or backfill late data. Correctness comes first: the report reflects complete data, even if that means it runs a bit later.

What they're testing: late-data handling, idempotent reruns, reliability

Build a pipeline whose output analysts trust without manually checking it.

Bake validation into the pipeline — completeness, range, and anomaly checks — and alert when something looks off, plus surface data freshness and lineage. Trust comes from the pipeline proving its own output is correct, not from analysts spot-checking it.

What they're testing: validation, anomaly alerting, data observability

Design deduplication for a stream where the same event can arrive more than once.

Give each event a stable unique key and track seen keys within a time window, or use a store that enforces uniqueness, so repeats are ignored. Combined with idempotent downstream writes, this gives effectively exactly-once processing.

What they're testing: keys, windows, and exactly-once processing