
SQL and data modeling
- Write a query for the top three products by revenue in each category. A good answer uses a window function that ranks rows within each category, explains how ties are handled, and filters on the rank in an outer query. See an approach for SQL questions.
- What is the difference between a fact table and a dimension table? A good answer says facts record measurable events at a stated grain and dimensions describe them, and states the grain before anything else.
- A customer changes their address. How does the warehouse keep both? A good answer explains slowly changing dimensions: overwrite the value, or keep history with valid-from and valid-to dates, chosen by how reports must behave.
- When would you denormalize? A good answer denormalizes for read-heavy analytics and simpler queries, and accepts the duplicated data and the cost of updates.
- How do you find and remove duplicate rows? A good answer first defines what makes a row unique, numbers the rows within that key, keeps the newest, and fixes whatever produces the duplicates.
- Star schema or one wide table? A good answer weighs a star schema's flexibility against a wide table's simplicity for one use, and decides by who queries it and with which tools.
- How do you speed up a slow analytical query? A good answer reads the query plan, filters early, partitions or clusters on common filters, avoids joins that multiply rows and pre-aggregates where it pays.
Pipelines
- Batch or streaming for a new pipeline? A good answer starts from the freshness the business needs against cost and complexity, notes that frequent batches meet most needs, and keeps streaming for alerts and real-time features.
- What makes a pipeline idempotent, and why does it matter? A good answer explains that rerunning a day gives the same result, by overwriting partitions instead of appending and using deterministic keys, which makes reruns and backfills safe. See idempotency.
- How do you backfill a year of data without breaking anything? A good answer runs it partition by partition, writes to a separate target or overwrites, throttles it, validates each partition and tells the people who use the data.
- What is the difference between ETL and ELT? A good answer explains transforming before loading against loading raw data and transforming in the warehouse, and why keeping raw data makes replays possible.
- How do you handle data that arrives late? A good answer separates event time from processing time, reprocesses a window or uses watermarks, and agrees on how late is too late.
- A source system changes its schema without warning. How do you cope? A good answer checks the schema when data arrives, lands raw data first, agrees a contract with the producing team, and alerts instead of failing silently.
- What do at-least-once and exactly-once delivery mean in practice? A good answer explains that most systems deliver at least once, so consumers must be idempotent, and that exactly-once has to hold from end to end, not only in the message broker.
Data quality
- How do you know the data is correct? A good answer tests freshness, row counts, empty values, uniqueness and accepted values, reconciles totals with the source and alerts the owner.
- A number on a dashboard is wrong. How do you trace it? A good answer follows the lineage back from the dashboard through each model and compares counts at every step until the numbers diverge.
- What goes into a data contract? A good answer lists the schema, the meaning of each field, freshness, ownership and how changes are announced.
- How do you monitor a pipeline? A good answer watches run status, duration, row counts, freshness and data tests, with alerts that reach the owner.
- How do you handle personal data in a warehouse? A good answer collects only what is needed, masks or hashes it, restricts access by role, and handles retention and deletion requests.
Storage and processing
- Why are columnar formats such as Parquet faster for analytics? A good answer explains reading only the needed columns, compression per column and statistics that let the engine skip data.
- How do you choose a partition key? A good answer matches the most common filter, often the date, and avoids both thousands of tiny partitions and a few huge ones.
- What is data skew in a distributed job, and how do you fix it? A good answer explains that one key holds most rows so one worker does most of the work, and fixes it by salting the key, broadcasting the small side of a join or handling hot keys separately.
- What causes the small files problem? A good answer explains that many tiny files from frequent writes slow every read, and fixes it with compaction, larger batches and sensible partitions.
- Data lake, warehouse or lakehouse? A good answer compares cost, schema enforcement, query speed and tooling, and recommends a clear default rather than all three.
- How would you structure a daily orchestration workflow? A good answer uses small tasks with clear dependencies, retries, tasks that are safe to rerun, alerts, and no hidden links between workflows.
Behavioral, for data roles
- Tell me about a pipeline people relied on. A good answer says who used it, the design choices, how it failed and how they made it reliable.
- Tell me about a time the data was wrong in front of stakeholders. A good answer owns it, communicates quickly, fixes the number and the cause, and adds the test that would have caught it.
- Analysts need data faster than you can model it. What do you do? A good answer gives raw access with clear caveats, agrees priorities and models first what is used most.
- How do you explain a data delay to a non-technical manager? A good answer starts with the impact, gives the cause in plain words and a new time, and says what will change. See explaining technical work.
- You inherit a messy data platform. What do you fix first? A good answer asks users what hurts, makes the most-used data reliable, adds tests and owners, and leaves big rewrites for later.
How to practice these
- Pick the five questions you would find hardest, not the ones you already know.
- Answer each one out loud in about two minutes, and record yourself if you can.
- Compare your answer with the note: did it cover what a good answer shows?
- For every question, write down one real example from your own work that you could use.
For interviewers: the notes work as a scoring guide. Decide before the interview what a strong answer covers, and score the evidence you heard, not the confidence it was said with. See how to run a fair technical interview.
More roles and the questions every engineer gets: interview questions by role.