Data Warehouse Interview Questions 2026: 16 Answers for the Architecture Round

A data warehouse is a central store that integrates data from multiple operational systems and keeps full history, structured for analytical queries rather than transactions. Inmon's classic definition captures it: subject-oriented, integrated, time-variant, and non-volatile. Reporting directly on a production OLTP database fails for four reasons. First, heavy analytical scans compete with live transactions for CPU, I/O, and locks, so a month-end report can slow down checkout or order placement. Second, OLTP schemas are normalized for write correctness, so a simple business question needs ten-plus joins. Third, OLTP keeps current state — when a customer's address is updated, the old value is gone, so historical analysis is impossible. Fourth, real questions span systems (CRM plus billing plus support), and only an integrated store can join them consistently.

OLTP (online transaction processing) systems run the business: many concurrent, small reads and writes — insert an order, update a balance — each touching a few rows, with strict integrity and millisecond latency. OLAP (online analytical processing) systems analyze the business: fewer queries, each scanning millions of rows for aggregations across history. The design consequences follow from the workload. OLTP databases use normalized schemas to avoid update anomalies and row-oriented storage so a whole record is written in one operation. OLAP warehouses use denormalized or dimensional schemas and columnar storage so scans read only the columns they need. OLTP holds current state; OLAP retains history. Performance is measured in transactions per second for OLTP, and in scan throughput and query latency for OLAP. A good closing line: OLTP is optimized for the write path, OLAP for the read path.

A data mart is a subject-scoped slice of analytical data serving one department or business process — a sales mart, a finance mart — while the warehouse is enterprise-wide. The distinction interviewers probe is dependent versus independent marts. A dependent mart is built from the central warehouse, so every mart inherits the same cleansed, conformed data and numbers agree across departments. Independent marts are loaded straight from source systems; they are quick to stand up but drift apart — finance and sales each compute 'revenue' differently and dashboards contradict each other. That drift is exactly the problem conformed dimensions solve in a Kimball architecture. In modern cloud stacks, marts are usually just schemas or dbt mart models inside one warehouse platform rather than separate physical databases, but the ownership and conformance questions are unchanged.

The staging area is a landing zone where source extracts arrive as-is before transformation. It exists for operational reasons: you can reload and reprocess without re-hitting source systems, audit what a source actually sent, and decouple extract schedules from transform schedules. It is typically transient or short-retention and never exposed to business users. An ODS (operational data store) is different: it holds integrated, current-valued data refreshed near real time for operational reporting — 'what is this customer's status right now across systems.' Unlike a warehouse, an ODS is volatile (rows are updated in place) and keeps little or no history. The warehouse itself is the opposite: non-volatile, full history, optimized for analysis. In cloud stacks the staging role is played by a raw layer in object storage or raw schemas, and dedicated ODSs are rarer, but the concept still gets asked.

The difference is where transformation runs. In ETL, a separate engine (Informatica, SSIS, Spark) transforms data before loading the warehouse. In ELT, raw data is loaded first and transformed inside the warehouse with SQL, orchestrated by tools like dbt. Cloud warehouses made ELT the default because their MPP compute is elastic and cheap enough to do transformation at scale — there is no longer a reason to maintain a second engine. ELT also preserves raw data, so when business logic changes you re-run transforms against history instead of re-extracting from sources. ETL still has legitimate uses: masking or tokenizing PII before it lands anywhere, transforming formats a warehouse cannot parse, or shops with fixed-capacity warehouses where transform load would starve BI queries. A strong answer names the tradeoff, not just the acronym expansion.

Inmon's approach is top-down: build a normalized (3NF) enterprise data warehouse as the single integrated source of truth first, then feed dependent, department-specific marts from it — the Corporate Information Factory. It gives strong integration and flexibility, but the upfront modeling effort is heavy and time-to-first-dashboard is long. Kimball is bottom-up: deliver dimensional marts one business process at a time, tied together by a bus architecture of conformed dimensions — a shared customer or date dimension makes independently built marts behave as one warehouse. Faster value, but discipline about conformance is what keeps it from decaying into silos. In practice almost everyone runs a hybrid: raw and integration layers that are Inmon-ish in spirit, with a Kimball-style dimensional presentation layer on top. The dbt staging/intermediate/marts convention is essentially this hybrid, so it is fine to say the debate is settled by combining them.

Sources are OLTP databases, SaaS APIs, and event streams. An ingestion layer moves data in — batch extracts, CDC from database logs, or streaming — landing it in a raw/staging layer that preserves data exactly as received, giving auditability and reprocessing. Next is the integration or core layer: cleansing, deduplication, standardizing types and codes, resolving business keys across sources. Above it sits the presentation layer — dimensional models or wide denormalized tables organized as marts per business domain. Finally a semantic/BI layer defines metrics once (a metric store or the BI tool's model) so 'revenue' means the same thing everywhere, and dashboards, notebooks, and reverse-ETL consume from it. The reason for layering is contract isolation: each layer has one job, so you can test it, rebuild it, and trace lineage without breaking consumers upstream or downstream.

Because analytical queries read a few columns of many rows, and columnar layout turns that into minimal I/O. If a table has 60 columns and the query needs 3, a columnar engine reads roughly five percent of the data a row store would. Compression multiplies the win: values within one column are similar, so dictionary encoding, run-length encoding, and delta encoding shrink data far better than compressing mixed rows — less disk, less I/O, more of the working set in memory. Column engines also keep min/max metadata per block (zone maps), letting queries skip entire blocks whose range cannot match the filter, and they use vectorized execution — processing columns in batches through CPU-cache-friendly loops. The tradeoff: writing or updating a single row touches every column file, which is why OLTP systems stay row-oriented and warehouses prefer bulk, append-style loads.

MPP — massively parallel processing — means a query runs across many nodes, each owning a slice of the data, with results combined at the end. Tables are distributed across nodes by a hash of a chosen key, round-robin, or fully replicated (for small dimensions). Speed comes from every node scanning its slice in parallel; slowness comes from anything that breaks that locality. The big one is shuffle: if two large tables are joined on a key neither is distributed by, rows must be redistributed across the network mid-query. Second is skew — one hot key (a null-heavy column, one giant customer) piles work onto a single node while others idle. Third, broadcasting a table too large to broadcast, and spilling to disk when memory runs out. The fixes are architectural: choose distribution and clustering keys around join patterns, replicate small dimensions, keep statistics fresh.

A data lake is cheap object storage (S3, ADLS, GCS) holding raw files in open formats — schema-on-read, great for ML and unstructured data, weak on transactions, governance, and BI performance. A data warehouse is a managed SQL engine with schema-on-write: data is validated and structured at load, giving fast, governed BI but historically at higher cost and in proprietary formats. A lakehouse adds a table format — Delta Lake, Apache Iceberg, or Hudi — on top of lake storage, bringing ACID transactions, schema enforcement, and time travel to files, so both Spark and SQL engines work off one copy of the data. The honest 2026 nuance: the boundary is dissolving, since major warehouses can now query and manage Iceberg tables directly. In an interview, frame it as a spectrum of governance and performance versus openness and cost, not three rival products.

Legacy MPP appliances coupled them: data lived on the same nodes that did the processing, so growing storage meant buying compute, resizing meant redistributing data, and every team's queries fought over one cluster. Separating them puts data on effectively unlimited object storage while compute is provisioned independently and elastically. That unlocks three things. Scale each dimension alone: ten years of history does not force a bigger cluster. Workload isolation: ELT jobs, BI dashboards, and data-science workloads run on separate compute against the same data, so a heavy backfill cannot freeze the CFO's dashboard. Cost control: compute suspends when idle and resizes per workload, so you pay for query time rather than for a cluster sized for peak. Snowflake's virtual warehouses, BigQuery's slots, and Redshift RA3's managed storage are all implementations of this same idea.

At the architecture level, a star keeps dimensions denormalized — one join from fact to each dimension — while a snowflake normalizes dimensions into sub-tables. The classic snowflake arguments were storage savings and single-point maintenance of shared hierarchies. Columnar compression killed most of the storage argument: repeated values in a denormalized dimension compress extremely well, so the space saved by normalizing is small while every extra join costs query time and makes BI tools harder to model. The modern default is star, with snowflaking reserved for genuinely huge dimensions with volatile sub-entities, or shared reference hierarchies maintained once and joined by several dimensions. Many teams add a further denormalization step — wide serving tables built from the star for hot dashboards. The table-level detail (fact types, SCDs, grain) belongs to the dimensional-modeling round, which is a separate preparation track.

Compare on architecture dimensions rather than brand preference. Compute model: Redshift is historically provisioned clusters you size and manage (RA3 separates storage; a serverless option now exists); Snowflake runs independent virtual warehouses over shared storage, resized or multiplied per workload; BigQuery is serverless — you submit SQL and Google allocates slots. Pricing follows the compute model: cluster-hours, per-second warehouse credits, or per-TB-scanned and reserved slots — which changes how you optimize (BigQuery punishes unpruned scans; Snowflake punishes idle-but-running warehouses). Tuning surface: Redshift exposes distribution and sort keys; Snowflake automates micro-partitioning with optional clustering keys; BigQuery relies on partitioning and clustering columns. Then ecosystem fit — the cloud you already run, open-format and Iceberg support, streaming ingestion paths. Close with how you would actually decide: operational maturity and workload shape usually dominate, and the concepts in this round transfer across all three.

Start with business processes, not tables. Interview stakeholders for the top decisions they cannot make today, and pick one high-value process — say order fulfillment — as the first delivery. Build a bus matrix: processes as rows, shared dimensions (customer, product, date) as columns, so conformance is planned before code. Profile source systems: data quality, change-capture options, volumes, ownership. Then choose platform and architecture: a cloud warehouse, ELT with an orchestration tool, and explicit layers — raw, integration, presentation marts. Define load strategy per table: a one-time historical backfill, then incremental loads keyed on CDC or watermarks. Bake in non-functionals from day one: data quality tests, access control, cost monitoring, freshness SLAs. Deliver the first process end to end — source to dashboard — then iterate process by process. That incremental, Kimball-lifecycle delivery is the expected answer; proposing a big-bang enterprise model before any dashboard ships is the red flag.

First pin down the actual latency requirement, because the answer changes cost by an order of magnitude. Most 'real-time' asks are satisfied by micro-batch: incremental loads every 5–15 minutes, which standard ELT handles with watermarks or CDC. If minutes matter, use continuous ingestion — Snowpipe, BigQuery streaming inserts, or Kafka connectors — appending events into a raw layer, with incremental or materialized-view transforms (Snowflake dynamic tables, BigQuery materialized views) keeping marts fresh. True sub-second requirements usually mean the warehouse is the wrong tool, and a stream processor or real-time OLAP store belongs in front of it. Name the tradeoffs: always-on compute cost, many small loads hurting performance, and late or duplicate events forcing dedupe and watermark logic. A senior-sounding close: ask which decision changes if the data is ten minutes old — that answer sets the architecture.

Treat them as one problem: most cost is wasted scan and idle compute. Reduce scan first — partition and cluster tables so filters prune data; ban SELECT * in scheduled jobs; serve hot dashboards from aggregate or materialized views instead of re-scanning fact tables; make transforms incremental rather than full rebuilds. Then govern compute: right-size per workload, aggressive auto-suspend, and workload isolation so ad-hoc analysts cannot slow production ELT — with resource monitors or query limits to catch runaway queries. Third, observe: every platform exposes query history, so review the top ten most expensive queries and storage growth monthly; unused tables and abandoned schedules are common silent costs. Finally, design-level wins compound — a well-pruned star schema with incremental models is cheaper than any amount of warehouse-size tuning layered on bad structure. Concrete numbers from your own project make this answer land.