Interview Questions

Explain it in your own words, with no editor open. What an anti-join does that NOT IN does not, what GROUP BY actually does to a row, what a window function can see that an aggregate cannot.

The same requirement solved four ways — CTE against subquery, EXISTS against IN, DISTINCT against GROUP BY. Which one do you ship, and what does each cost in correctness under NULLs and duplicates?

Here is a query and here are the input rows. What comes back? Chosen from the places where the intuitive answer is wrong: a NULL inside a NOT IN, a join that fans out, HAVING doing what WHERE was expected to.

A query that runs, returns a plausible number, and is wrong. Find the reason and repair it — usually a duplicate on one side of a join, a temporal condition that leaks the future, or a filter applied after the aggregation it was meant to reduce.

Same answer, less money. Read the plan, name what is expensive and why, and change the query or the physical layout rather than guessing at hints.

The questions with no code attached. Why a generator is not a list, what a context manager guarantees that a try/finally does not, and when the GIL is the thing that is actually slowing you down.

A dict of lists against a defaultdict against a Counter; a generator against a list; a dataclass against a tuple. Given the shape and the size of the data, which do you reach for and what does the wrong choice cost?

What does this print? Drawn from the language corners that trip people who use Python daily — a mutable default argument, a closure over a loop variable, a generator consumed twice, a shallow copy shared between rows.

Code that fails, or worse, code that succeeds with the wrong answer. Find it and fix it: the swallowed exception, the retry that retries a non-idempotent write, the encoding assumption that holds until one file does not.

Working code that would not survive a production incident. Make it reviewable: name the contract with types, make the failure observable with logging, make the retries safe, and make the whole thing testable without a live warehouse.

The vocabulary, used correctly. What the grain of a table is and why it is declared before anything is built, what makes a dimension conformed, and what each SCD type actually costs to query.

A business described in a paragraph; produce the model. The work is in the questions asked before drawing anything — what one row means, which facts are additive, and where history has to be kept.

A diagram somebody already built, with something wrong in it. Say what breaks, when, and to which number — a mixed grain, a bridge that double-counts, a dimension that overwrites the history a report depends on.

The model was right and the business changed. Add an attribute that needs history, split a dimension in two, or move a fact to a finer grain — without rewriting every downstream query on the same day.

Star or One Big Table, Kimball or Data Vault, normalize or widen. The answer is never the name of a methodology — it is the query pattern, the load pattern and the team that has to maintain it.

The distinctions that decide an architecture. What idempotency buys you, why exactly-once is a property of the write rather than of the transport, and what a watermark is actually tracking.

A one-line prompt — "ingest clickstream and serve it to analysts within an hour" — and a whiteboard. Graded on the boundaries you draw and the assumptions you state, not on the tools you name.

The job died mid-write. The upstream re-sent yesterday. A schema changed at 3am. For each one: what state is the system in now, what happens on rerun, and what you would have built so the answer was boring.

Kafka or a nightly extract, Airflow or the warehouse scheduler, streaming or a five-minute batch. The signal is in what you give up and how you would know later that you chose wrong.

Numbers on the back of an envelope: rows per day, bytes per row, what that costs to store, scan and reprocess, and which part of the design falls over first at ten times the volume.

What actually happens between your code and the cluster. Driver and executors, lazy evaluation, why a transformation is free until an action forces it, and what makes a transformation wide.

Write the job. Marked on whether you stayed in the DataFrame API where the optimizer can see you, and on whether the shape of the output is what was asked for.

Here is the output of .explain(). How many shuffles, which join strategy was chosen and why, where the filter ended up, and what would change if one side got bigger.

Two hundred tasks finished in a minute and one has been running for an hour. Executors dying with OOM. A job that writes forty thousand files. Diagnose from the symptom, then fix the cause.

Make it cheaper without making it wrong. Partition counts, broadcast thresholds, what is worth caching and what caching costs, and cluster sizing that is argued from the data rather than doubled until it works.

What dbt is doing on your behalf. Why ref() exists rather than a table name, how the DAG is derived rather than declared, and what each materialization actually writes.

Write the model, or the macro that stops five models repeating themselves. Jinja is the part that goes wrong: the signal is knowing when a macro clarifies and when it hides the SQL somebody has to debug at 3am.

Lay out a project from nothing: staging, intermediate and mart layers, naming that survives a hundred models, where tests live, and how environments and targets keep development off production.

The run failed, or worse, it passed and the numbers moved. A circular ref, a test that never had rows to fail on, an incremental model quietly missing yesterday because the filter reads the model it is building.

The project runs for two hours and costs more than the warehouse it feeds. Choose the incremental strategy and the unique key, decide what full-refreshes and when, and cut CI down to what actually changed.

The model underneath the UI. What the scheduler is doing between runs, why a daily DAG for Monday runs on Tuesday, and what an executor actually decides.

Write the DAG. Dependencies, branching and a fan-out whose width is only known at runtime — plus the discipline that keeps a task idempotent so a retry is free.

Stuck in queued. Not triggering. Ran twice. Skipped when it should not have. Each one has a small set of real causes, and knowing them is the difference between reading logs for an hour and fixing it in five minutes.

Backfill two years without starving the daily runs. Rotate a credential without editing every DAG. Rerun one task in the middle of a finished run. The questions somebody who has been on call can answer immediately.

Two hundred sensors holding every worker slot. Where the limits actually are — pools, DAG and task concurrency, parallelism, executor choice — and which one to move when the queue stops draining.

The log underneath everything. What a partition promises and what it does not, what happens to a consumer group when one member dies, where an offset is stored, and what acks=all actually waits for.

Consumer lag is climbing, one partition is hot, a rebalance produced duplicates, a bad message is blocking a partition. Diagnose it from the symptoms, then design the topic, the keys and the delivery guarantee for a stated load.

What a table format adds to a folder of Parquet files. How an Iceberg snapshot finds its files without listing a directory, what the Hudi timeline records, what copy-on-write and merge-on-read each cost, and how two writers avoid overwriting each other.

Pick the format for a stated workload: CDC upserts every five minutes, BI scans over five years, GDPR deletes by customer. Then fix what goes wrong once it runs: small files, slow reads after a million updates, a storage bill that grows from expired snapshots.

What each service is for and where its limits are. Why S3 is not a file system, what a Glue job bookmark actually remembers, how Redshift decides which node a row lives on, and when Athena is the cheaper answer.

The questions asked with numbers. Land a nightly 2 TB export from an on-prem database, cut an Athena bill in half, keep a Kinesis consumer from falling behind, lock a data lake down by column — and say which service does each part, and why.

How the managed pieces actually behave. What a BigQuery slot is, why clustering prunes differently from partitioning, what Pub/Sub guarantees about duplicates and order, and what a Beam watermark is waiting for.

The BigQuery bill doubled, a Dataflow job is an hour behind Pub/Sub, a table needs deletes for GDPR. Design the stack for a stated load and defend each choice: Dataflow or Dataproc, streaming or batch loads, on-demand or reserved slots.

What each piece of the Azure data stack does and where it stops. When an integration runtime has to be self-hosted, what a dedicated SQL pool distributes on, how Fabric relates to Synapse, and why ADLS needs the hierarchical namespace.

Copy 500 on-prem tables incrementally with one parameterised pipeline. Rerun a failed day without duplicates. Move secrets out of linked services. The Data Factory and Databricks scenarios Azure loops actually ask, answered with the activity or setting that does it.

How the machinery works when nobody is looking. What a hash aggregation does when it runs out of memory, what a Bloom filter can and cannot tell you, and why a columnar engine reads a predicate before it reads a column.

A system that is slow, stuck or wrong, described by its symptoms. Name the mechanism before proposing a fix: head-of-line blocking, a skewed partition, a watermark that cannot advance, a join that spilled.

Design the procedure, not the snippet. Deduplicate more rows than fit in memory, backfill two years without saturating the warehouse, or replace a scalar watermark that one missing date can block forever.

Now write it, in bounded memory, with a defined behaviour when it is interrupted halfway. Checkpointing, resumability and idempotence are the parts that separate a working prototype from something that can run nightly.

What does it cost, and what does it give up. Exact counts against a sketch that is one percent wrong for a thousandth of the memory, a full scan against an index that has to be maintained, accuracy against recovery time.

Stories about your own work. The pipeline you owned end to end, the incident you ran, the mistake you made and what changed after it, and the number that moved because of you.

Stories about other people. A stakeholder who disputed your numbers, a deadline you could not meet, three teams wanting the same week, a senior engineer you disagreed with.