Data Warehouse Interviews Are Won on the Second Question

Anyone can define a fact table. Interviewers know that, so they ask once, hear the textbook line, and move straight to the second question: the one that shows whether you have actually built something. That second question is where offers are decided, and almost nobody prepares for it.

So below is each term in a single line, followed by the question that tends to come next and the answer that survives it.

The fundamentals

1. Database vs data warehouse
A database records what is happening, one row at a time, for an application. A warehouse stores history so the business can analyse it: sales, revenue, churn.
Follow-up: "Why not just report off the production database?" Because your report scans the rows checkout is trying to lock, and because row-based storage forces you to read every column to sum one. The difference is workload, not size.

2. OLTP vs OLAP
OLTP is online transaction processing: many small, fast, real-time writes against a database. OLAP is online analytical processing: fewer, heavier reads across large historical data, feeding Power BI or Tableau. OLAP's source is the warehouse.
Follow-up: "Which one is harder to scale, and why?" OLTP scales on concurrency and lock contention. OLAP scales on scan volume and shuffle.

3. Data warehouse vs data mart
The warehouse holds data for the entire organisation. A mart is a curated, refined slice shaped for one department: marketing, finance, ops.
Follow-up: "Who owns the mart?" Marts are an organisational answer as much as a technical one. They exist so finance stops arguing with marketing about whose revenue number is correct.

4. Data lake vs warehouse vs lakehouse
A lake holds raw data of any type: images, video, text, logs, files. Examples are S3, GCS and ADLS, object stores rather than query engines. A warehouse holds structured, cleaned data ready for analysis; BigQuery, Snowflake and Redshift sit here. A lakehouse is low-cost lake storage with warehouse behaviour on top: tables, schemas and transactions over open files.
Follow-up: "What did the lakehouse actually fix?" Lakes rotted into swamps because nobody could tell which file was current or correct. Table formats like Iceberg and Delta added the metadata layer that makes a folder of Parquet behave like a real table.

How a model gets built

5. Conceptual vs logical vs physical modelling
Conceptual is high level: which entities exist and how they relate. Logical adds structure: attributes, keys, relationships, still platform-agnostic. Physical is implementation: CREATE TABLE, data types, indexes, partitions.
Follow-up: "Which one survives a platform migration?" Conceptual and logical do. Physical is the throwaway layer, which is why teams that model straight into DDL pay twice.

6. ER diagram
The structure at a glance. Customer to Order to Product, the blueprint before building.
Follow-up: "What does it not tell you?" Volume, grain and update frequency. An ER diagram makes a 10-row table and a 10-billion-row table look identical.

7. Cardinality
How rows relate: one-to-one, one-to-many, many-to-many. Many-to-many needs a junction table in between.
Follow-up: "What breaks if you get it wrong?" Fan-out. A join at the wrong cardinality silently multiplies your fact rows and inflates every number downstream.

8. Kimball vs Inmon
Kimball is bottom-up: build dimensional marts against business needs first, deliver value fast. Inmon is top-down: build the normalised enterprise warehouse first, then pull department marts from it. Kimball trades long-term consistency for speed; Inmon trades speed for long-term integration.
Follow-up: "Which would you pick here?" The right answer names a constraint, not a camp. Kimball when the business needs an answer this quarter. Inmon when many sources must reconcile and conflicting definitions are already costing money.

Dimensional modelling

9. Fact vs dimension
A fact table holds measurable events: what happened and how much. A dimension holds the descriptive context: who, what, where, which product.
Follow-up: "Additive, semi-additive or non-additive?" Revenue sums across every dimension. An account balance sums across accounts but not across time. A ratio sums across nothing. This one line separates people who have modelled from people who have read.

10. Grain
What exactly one row of the fact table represents. One row per order line per day.
Follow-up: "State the grain of this table in one sentence, with no 'and' in it." Declare grain before anything else. Get it wrong and every number downstream is wrong in a way that is expensive to find.

11. Star vs snowflake schema
Star: one fact table, denormalised dimensions around it, fewer joins, fast and simple to query. Snowflake: those dimensions normalised further into sub-tables.
Follow-up: "When is snowflake worth it?" Rarely for performance. It earns its place when a dimension is genuinely large and volatile, or when a hierarchy is shared across several dimensions and must not drift.

12. The four fact table types
Transactional records individual events as they happen. Periodic snapshot records state at regular intervals, such as an account balance each day. Accumulating snapshot tracks one process through its stages and updates rows as it progresses. Factless fact records that an event happened with no measure attached.
Follow-up: "Give me a factless example." Student attended class. Promotion was offered but not redeemed. The value is in counting occurrences and, more importantly, absences.

13. Conformed dimension
One shared definition of a dimension, Date or Customer, used identically by multiple fact tables.
Follow-up: "Why does it matter?" It is what lets you compare the sales fact and the support fact in a single query without a reconciliation meeting. Without it, every mart has its own Customer and none of them agree.

14. Role-playing dimension
One physical dimension used in several roles. A single Date dimension serving order_date, ship_date and delivery_date.
Follow-up: "How do you expose it?" Views or aliases per role, so the model reads as Order Date and Ship Date rather than three joins to the same table with confusing column names.

Keys and integrity

15. Primary key
The column, or set of columns, that uniquely identifies one row. Never null, never duplicated.
Follow-up: "What is the primary key of a fact table?" Usually a composite of the foreign keys that define the grain, or a degenerate identifier like order number. If you cannot name it, you have not pinned the grain.

16. Foreign key
A column in one table that points at the primary key of another. The fact table's dimension keys are foreign keys.
Follow-up: "Does BigQuery enforce it?" This is the trap. Postgres and MySQL enforce foreign keys. BigQuery, Snowflake and Redshift accept the declaration but do not enforce it, so a fact row can point at a customer that does not exist and nothing complains. You enforce it in tests, not in DDL.

17. Surrogate key vs natural key
A natural key comes from the business: an email, an order number. A surrogate key is a meaningless integer or hash you generate.
Follow-up: "Why prefer the surrogate?" Natural keys get reused, reformatted and merged when a source system migrates. And history tracking needs a key that can point at one version of a row, which a natural key cannot do.

18. Referential integrity
Every foreign key value actually exists in the table it points at. No orphan rows.
Follow-up: "How do you guarantee it in a warehouse that will not enforce it?" A relationship test in dbt, a late-arriving-dimension strategy, and an unknown-member row (key -1) so orphan facts join to something rather than vanishing from an inner join.

Change over time

19. Normalisation vs denormalisation
Normalising removes redundancy by splitting data across tables. Denormalising deliberately reintroduces it to avoid joins.
Follow-up: "Which does a warehouse want?" Denormalised, mostly, because analysts write the queries and joins cost. Normalisation optimises for write integrity, and a warehouse barely writes.

20. Slowly changing dimensions
A customer moves city. Type 1 overwrites the old value and history is lost. Type 2 closes the old row and opens a new one with validity dates. Type 3 keeps a previous-value column alongside the current one, so you get one step of history and no more.
Follow-up: "Which do you use, and write me the merge." Type 2 when history changes the answer, and it usually does: last quarter's sales should stay attributed to the region that earned them. Type 3 only when the business genuinely cares about exactly one prior value, such as a previous sales region. Expect to write the MERGE on a whiteboard.

The one to take into the room

Most candidates can recite these. Very few can say which decision each term commits them to, and what it costs when it is wrong.

Grain, cardinality and SCD type are the three that quietly decide whether your numbers are correct. Know those cold and the rest of the interview goes easier.