Data Engineer Interview Prep: Questions and a Mock Test

Almost every data engineering loop has a live SQL round, and the questions are chosen so that a plausible-looking query gives a wrong answer. The recurring traps are worth rehearsing until they are reflexes.

NULL handling is the first. NULL is not a value, it is the absence of one, so any comparison with it yields unknown rather than true or false. That means a WHERE clause of the form status != 'closed' silently discards rows where status is NULL, and NOT IN against a subquery containing a single NULL returns no rows at all. COUNT(column) skips NULLs while COUNT(*) does not.

The second is join fan-out. An inner join to a table with duplicate keys multiplies rows, so any SUM computed afterwards is inflated. Interviewers introduce this deliberately and watch whether you check cardinality before aggregating.

The third is the difference between filtering in WHERE and filtering in the ON clause of an outer join. A predicate on the right-hand table placed in WHERE turns a LEFT JOIN into an inner join, because the NULLs the join produced fail the test.

Window functions are expected fluency at mid level: running totals, ranking within a group, deduplicating by taking the first row per key, and knowing why ROW_NUMBER, RANK and DENSE_RANK differ on ties.

Modelling rounds ask you to design tables for a described business, and the mark is for the reasoning rather than the diagram. Star schemas are still the common vocabulary: facts hold the measurements and the foreign keys, dimensions hold the descriptive attributes. Normalising a dimension into a snowflake reduces duplication and costs you joins, and on columnar warehouses that tradeoff usually favours the flatter shape.

Slowly changing dimensions come up constantly because they force you to say what the business actually needs. Type 1 overwrites and loses history. Type 2 adds a new row with validity dates and a current flag, preserving history at the cost of every downstream query having to filter correctly. The good answer starts by asking whether anyone needs to reconstruct the past, and only then picks.

Grain is the question behind the question. Being able to state "one row per order line per day" precisely, and to explain what breaks if the grain is mixed, is what separates a modelling answer from a table-drawing answer. The most common real defect in warehouses is a fact table whose grain nobody wrote down.

Spark questions are rarely about the API. They are about knowing which operations move data across the network and what happens when the data is unevenly distributed.

A shuffle is the expensive event: any join or aggregation that needs matching keys on the same executor writes intermediate data and reads it back over the network. Spark's documented default for spark.sql.shuffle.partitions is 200, which "configures the number of partitions to use when shuffling data for joins or aggregations". That number being fixed regardless of data size is why it is a classic tuning question.

Broadcast joins avoid the shuffle entirely by sending the small side to every executor. The documented threshold, spark.sql.autoBroadcastJoinThreshold, defaults to 10485760 bytes, which is 10 MB, and "by setting this value to -1, broadcasting can be disabled".

Adaptive Query Execution has changed several stock interview answers, so it is worth being current. It has been "enabled by default since Apache Spark 3.2.0". It coalesces post-shuffle partitions using runtime statistics toward an advisory size that defaults to 64 MB, and it handles skew: with skewJoin enabled by default, "Spark dynamically handles skew in sort-merge join by splitting (and replicating if needed) skewed partitions", with the skew threshold defaulting to 256 MB. Salting a skewed key is still a valid manual answer, but a candidate who presents it as the only answer sounds several years out of date.

Streaming rounds converge on delivery guarantees, and the answers are more specific than the labels suggest.

At most once means a message may be lost but never processed twice, which is what you get when the consumer commits its offset before doing the work. At least once means a message is never lost but may be processed more than once, which is what you get when the work happens before the commit. Exactly once is not a property of the message bus alone; it is a property of the whole path, and it is achieved either by transactional writes that atomically commit the output and the offset together, or by making the consumer idempotent so that duplicates are harmless.

Interviewers like this because at-least-once plus idempotency is usually the right engineering answer, and reaching for it shows you understand what exactly-once costs.

Ordering is the second recurring topic. Order is guaranteed within a partition and not across partitions, so any requirement to process a given entity's events in order becomes a partitioning decision: key by that entity. The follow-up is usually about what happens when you need more parallelism than you have keys, or when one key is far hotter than the rest.

Expect event time versus processing time, and watermarks as the mechanism for deciding how long to wait for late data before closing a window.

The most reliable senior signal in a data engineering interview is treating idempotency and backfill as design constraints rather than as afterthoughts. Every pipeline is eventually re-run: because it failed halfway, because upstream sent a correction, because someone found a bug in a transformation that has been live for a month.

The practical form is that a task for a given time window must produce the same result whether it runs once or five times. That usually means writing partitioned output and replacing the partition rather than appending to it, and it means avoiding transformations that depend on the wall clock at execution time, since a backfill runs today for data that belongs to March.

Testing is the other half. dbt's framing is worth borrowing whatever tool you use: "Data tests are SQL select statements that seek to grab failing records, ones that disprove your assertion", and "if the data test returns zero failing rows, it passes". Its four built-in generic tests, unique, not_null, accepted_values and relationships, cover the assertions that catch most real breakage, and a singular test is just a one-off query asserting something specific to one model.

Be ready to say where the tests run. Testing after loading catches problems that have already reached consumers; testing in a staging area before promotion is what stops a bad load being visible at all.