Interview questions

Try each one before you open the answer.

Grain and table shape

1What is the grain of a table, and how do you test a grain declaration?

Grain is what one row is. Write it as one sentence: an "and" joining two kinds of thing, or an aggregate word such as "plus totals", reveals mixed grain, which leads to double counting.

Conformed dimensions

6What is a conformed dimension, and what does it save?

One shared dimension that means the same thing in every fact table that uses it. It turns N(N − 1)/2 pairwise reconciliations into N agreements.

History and keys

8What is the difference between 'as was' and 'as is' reporting?

"As was" uses each attribute's value at the time the fact happened; "as is" uses today's value. One stored version of an attribute cannot answer both.

9Summarize slowly changing dimension types 1, 2, 3, 4 and 6 in one line each.

Type 1: overwrite; answers "as is" only.
Type 2: a new row per change; answers both, but versions accumulate.
Type 3: a previous-value column; one step of history.
Type 4: move fast-changing attributes to a mini-dimension; bounds growth.
Type 6: store both the historical and current values on every version; cheap reads, but writes must backfill.

10Why do surrogate keys exist?

Because type-2 history needs a key that tells the versions of one entity apart, and a natural key identifies the entity itself, so it cannot. Faster joins are a minor side benefit.

Measures

11Why can you not sum an account balance over a month?

A balance is a stock: the same amount persists from day to day, so summing over days counts it once per day. Over time, take the last value, the average, or the minimum and maximum.

12How do you roll up a margin percentage?

Sum the numerator, sum the denominator, then divide. Never average the percentages.

13When do you need an accumulating snapshot fact table?

When the question is the time between milestones of a process, such as order placed to shipped to delivered. It is the one fact type whose rows are updated, so it is really a materialized view over events.

Many-to-many relationships

14What is a fan trap?

Joining a table at one grain to a table with several rows per row of the first, then summing the first table's measure: each value is repeated once per child row (the join multiplies rows). Aggregate to the measure's grain before joining.

Modeling styles

16What does Data Vault modeling give you, and what does it cost?

Loading that does not depend on order, attribution of every value to its source, and resilience to schema change, through hubs (business keys), links (relationships) and satellites (attributes over time). The cost is complexity for readers, and you still build a star schema on top for analysis.

Meaning and identity

18Why do you need a semantic (metrics) layer?

A metric is a computation, not a stored value. Without one central definition, each consumer re-derives it, and the versions differ on unwritten edge cases. The layer also guards additivity and safe join paths.

19Why is the match threshold in entity resolution a business decision?

Merging too eagerly mixes two people's data, a privacy risk; splitting too eagerly inflates counts. The threshold sets the exchange rate between those two harms.

Structure inside a model

20How do you store a hierarchy that changes often?

With a parent pointer (moving a node is cheap, finding all ancestors is slow), or with a closure table of all ancestor–descendant pairs if reads dominate (moving a node rewrites every pair under it). Avoid nested sets.

Pipelines

22What does a medallion layer (bronze, silver, gold) need to be worth having?

A published guarantee at each boundary (schema, freshness, uniqueness, null policy, grain) and a named owner. Otherwise it is just three copies of the data.