Data Warehousing interview questions
Data Warehousing questions a Data engineer can be asked, from first job to senior. Open one to see the strongest answer and why it works.
A fact table is queried by event_date. How do you store it?
The strongest answer: Partitioned (or clustered) on that date so a day filter can prune — I do not dump one giant unpartitioned heap “for simplicity”
Why it works: Exactly. Partition for the filter you run. Random and user-only partitions make a day scan a full read.
- Partition for the common filter.
- Date facts want a date partition.
- Do not one-file “for simplicity.”
- Random partitions do not prune a day.
A cloud warehouse such as Snowflake or BigQuery does not rely on B-tree indexes on fact tables the way an OLTP database does. How do queries over a billion rows still come back quickly?
The strongest answer: Columnar storage reads only the columns asked for, and blocks carry min/max metadata so a filter skips most of them; partitioning and clustering by the filter columns make that skipping effective
Why it works: Exactly — column pruning plus block-level metadata pruning is the engine; partitioning and clustering exist to make the pruning bite.
- Prune columns and blocks, not index rows.
- Columnar: read only needed columns.
- Block min/max metadata skips data.
- Partition and cluster for the filters.
A teammate points the app’s primary key lookups at the warehouse. What do you say?
The strongest answer: Warehouses are for scans and models, not 5ms point reads — I will not make checkout wait on a slot
Why it works: Exactly. Different engines, different jobs. Bigger slots do not make a warehouse OLTP.
- Warehouse ≠ OLTP.
- Do not put checkout on a slot.
- Bigger is not 5ms.
- Keep an operational store for point reads.
Snowflake, BigQuery and Databricks all separate storage from compute. What does that actually buy a data team?
The strongest answer: One shared copy in cheap object storage, with compute started, sized and billed per workload — ETL, BI and ad-hoc users each scale or stop without copying data or fighting each other
Why it works: Right — one copy of the data, many independent compute pools; isolation without duplication is the whole pitch.
- One copy, many compute pools.
- Storage is cheap and shared.
- Size and bill compute per workload.
- Do not copy data for isolation.
A job’s bill doubled. bytes_scanned doubled. What do you inspect first?
The strongest answer: A lost partition filter, a SELECT *, or a new column that widened the table — I do not first buy a bigger reservation
Why it works: Exactly. Shape first. Hiding the bill and panic-vendors are not EXPLAIN.
- Find the lost prune or *.
- Watch widening.
- Reservation last.
- Do not hide the bill.
In an MPP warehouse where you choose how a table is distributed across nodes (Redshift, Synapse), how do you pick the distribution key for a large fact table?
The strongest answer: The column it is most often joined on with the other large table, so matching rows sit on the same node and the join moves no data; a skewed key is rejected because one node would get most rows
Why it works: Exactly — co-locate the join, avoid skew, and replicate small dimensions to every node.
- Distribute on the big join key.
- Co-located joins move no data.
- Reject skewed keys.
- Replicate small dimensions.
When do you cluster / Z-order instead of adding another partition column?
The strongest answer: When the second filter is high-cardinality or would create a partition explosion — partitions are for coarse prune; clustering is for the next column
Why it works: Exactly. Partitions too fine become a file storm. Clustering is a real (vendor-shaped) tool for the next key.
- Coarse partitions.
- Cluster the next high-card filter.
- Do not partition on user_id by habit.
- Do not ignore clustering as “lock.”
A vendor proof-of-concept shows a dashboard query returning in 40 ms. In production the same query takes 12 seconds.
What most likely happened in the demo, and how do you benchmark honestly?
The strongest answer: The result cache: identical queries on unchanged data are served from cache. I benchmark with caching disabled or varied parameters, cold and warm, on production-sized data, and report the range
Why it works: Right — every major warehouse caches results for identical queries on unchanged data; a benchmark that reruns one query measures the cache.
- Benchmark past the cache.
- Vary parameters or disable the cache.
- Cold and warm runs, production size.
- Report the range, not the best.
Warehouse spend is everyone’s and no one’s. What do you install?
The strongest answer: Showback by team and by job, plus a prune/roll-up review — I do not start with a shame Slackbot
Why it works: Exactly. Visibility plus a road. Shame, freezes, and hidden bills grow shadows.
- Showback by team and job.
- Review prunes and roll-ups.
- No shame bot first.
- Do not hide the bill.
At 09:00 hundreds of small BI queries queue for minutes. The nightly transform, one heavy query, also takes too long.
Bigger compute, more compute, or both?
The strongest answer: Different levers: the queue is concurrency, so add clusters or a separate BI compute; the heavy transform is one query, so give it a larger compute for its window and stop it afterwards
Why it works: Exactly — concurrency wants more parallel compute; a single heavy query wants a bigger one; isolate the workloads.
- Concurrency: scale out. Heavy: scale up.
- Separate BI from transform compute.
- Size per workload window.
- Do not make users move.
Ad-hoc exploration starves the 06:00 SLA job. What do you change?
The strongest answer: Separate warehouses / queues / admission — interactive and SLA do not share a best-effort pool
Why it works: Exactly. Isolation. Hope-pools, guilt, and bans are how someone loses badly.
- Isolate SLA from ad-hoc.
- Queues or separate computes.
- Do not guilt first.
- Do not ban explore.
A warehouse vendor wants you all-in on their runtime. What do you keep portable?
The strongest answer: The models, the contracts, and the right to export — a runtime can help; it must not own the only copy of meaning
Why it works: Exactly. Meaning is yours. Screenshots and held passwords are not an exit.
- Keep models and contracts.
- Keep an export path.
- Runtimes help; they do not own meaning.
- Passwords are not leverage.
Give the one-sentence residual you would say at a warehouse cutover.
The strongest answer: “We proved the certified gold we named on a dual-run; we did not prove every shadow sheet, and the GM accepted that gap.”
Why it works: Exactly. Proved, not proved, accepter. “Good,” counts, and shrugs are not residuals.
- Proved / not proved / accepter.
- Name the gap.
- Do not say “looks good.”
- Do not hide behind a table count.
A job burned a month of budget in a weekend. How do you learn?
The strongest answer: Which guardrail would have stopped the scan, and one default on the road — I do not start with who forgot a WHERE
Why it works: Exactly. Missing guardrail, one default. Hunts and vendor-Fridays teach fear, not a ceiling.
- Add a scan guardrail.
- One owned default.
- No last-runner hunt.
- No Friday vendor panic.