Data Modeling Interview Questions for Data Engineers: Facts, Dimensions, and Slowly Changing Dimensions

A strong answer to data modeling interview questions starts with what one row means. Then it explains which measures belong at that grain, how dimensions describe them, and whether a report needs today's attributes or the attributes that applied when an event occurred. Even a clear star diagram cannot resolve an ambiguous revenue definition.

This guide develops those decisions through one order-line example. Use it alongside PracHub's Data Engineer interview questions to practise explaining a model before writing SQL.

Evidence boundary: technical facts below come from official Microsoft documentation and primary Kimball Group guidance. The worked examples and suggested answers are original preparation exercises and reasoned recommendations. Candidate-reported practice records appear in the final table; they do not establish any employer's current interview format or predict your questions.

What should you clarify before drawing a schema?

Practice question: “Design a warehouse for an online retailer's sales reporting.”

First identify the decision: does finance want booked merchandise sales, recognized revenue, or cash collected? Those measures can occur on different dates and require different business processes. Ask about returns, cancellations, currency and the historical meaning of customer region.

For this exercise, assume completed order lines, one currency, merchandise amounts after line discounts, and region at order time. Tax, shipping and subsequent refunds are separate. This is an illustrative reporting contract, not an accounting policy.

State the grain aloud: “One row represents one completed line within an order, identified by order ID and line ID.” Kimball's guidance establishes grain before dimensions and facts; the grain describes the measurement represented by a row. Kimball: Grain

Test that grain with sample rows. One order containing two products generates two rows. Two units of the same product on one line remain one row with quantity two. A source retry does not create another business event. These examples define uniqueness before you write the schema.

What belongs in a fact table versus a dimension?

For our model, fact_order_line contains order_id, line_id, ordered_at, customer, product and date keys, quantity, and line_amount. Customer region belongs in a customer dimension; product category belongs in a product dimension. The measures describe the line, while dimensions provide the attributes used to filter and group it.

A numeric column is not automatically a measure: a product identifier is a label even if its storage type is integer. Conversely, an event table can be useful without a numeric amount. Counting qualifying rows may answer the business question.

Practice answer: “I would preserve order ID on the line fact for order-level grouping, without creating an otherwise empty order dimension. I would document which date the date key represents and retain the timestamp when sub-day analysis matters.”

The calendar dimension can serve several roles, such as order date and delivery date. Microsoft documents this role-playing pattern in its star-schema guidance. Distinct role names help prevent an analyst from interpreting delivery month as purchase month. Microsoft: Star schema guidance

Can every measure be summed?

Kimball distinguishes additive measures, measures additive over only some dimensions, and non-additive measures. Ratios often work better when their additive components are stored and the ratio is calculated after aggregation. Kimball: Additive, semi-additive and non-additive facts

In this example, line amounts sum across products and dates under the agreed currency and status rules. Average order value requires total merchandise amount divided by distinct orders; averaging line amounts answers a different question. A daily inventory balance can be summed across compatible products or locations, but summing successive daily balances does not produce month-end stock.

Watch for mixed grain. Order A has lines worth $60 and $40, plus $10 shipping. Copying $10 shipping onto both lines produces $20 when summed. Keep shipping in an order-grain fact, or explicitly allocate $6 and $4 using merchandise value. An allocation needs a rule for zero-value orders and rounding residuals. Neither adding DISTINCT nor hiding duplicate rows defines that business rule.

When do you choose a transaction or snapshot fact table?

The required question determines the table pattern. Kimball describes a transaction fact as a measurement event. Our completed order lines fit that pattern.

A periodic snapshot summarizes a defined period. A product-location-day inventory table supports daily stock reporting, including days without sales. Specify whether its balance is opening, closing or another agreed observation; “daily inventory” alone leaves the measure ambiguous.

An accumulating snapshot follows a process through milestones and updates its row as that process progresses. For fulfilment, a line might acquire ordered, packed, shipped and delivered timestamps. If a line splits across shipments, reconsider the grain or add a shipment-line table rather than squeezing several deliveries into one timestamp.

These tables can coexist. Shared customer and product definitions make their results comparable. Joining raw sales lines directly to raw shipment lines, however, can multiply rows. Aggregate each process to the intended comparison grain before combining the results.

How do you choose SCD Type 1 versus Type 2?

Practice question: “A customer changes region. Should old sales move to the new region?”

Ask which report the business wants. Type 1 overwrites the stored attribute. Type 2 preserves versions by adding a new dimension row. Microsoft documents both approaches and notes that different attributes in one dimension can use different change policies. Microsoft: Slowly changing dimensions

For our original example, customer C42 moves from West to East on February 1. Region-at-purchase reporting requires history. A current-territory report intentionally groups that customer's historical purchases under East. Both are valid questions; label them separately.

A Type 2 dimension needs a unique version reference. Kimball specifies a new surrogate key for the new row, together with effective and expiration times and a current-row indicator. The business key still identifies the same customer across versions. Kimball: Type 2

Here NULL means no known end, and intervals include their start but exclude their end. This convention assigns a purchase exactly on February 1 to key 205 once. Production timestamps also need an agreed timezone and precision.

How do you join a fact to the correct historical version?

Suppose three staged lines for C42 contain $60 and $40 on January 20, then $80 on February 10. Resolve the dimension version during the fact load using the event timestamp:

SELECT
  s.order_id,
  s.line_id,
  d.customer_key,
  s.line_amount
FROM staged_order_line AS s
LEFT JOIN dim_customer AS d
  ON s.customer_id = d.customer_id
 AND s.ordered_at >= d.valid_from
 AND (s.ordered_at < d.valid_to
      OR d.valid_to IS NULL);

The example's output assigns the January lines to 101 and the February line to 205. Persist that version key in the fact; ordinary historical reporting can then join on customer_key without repeating the interval lookup.

In this fixture, historical reporting returns West $100 and East $80. Filtering to the current customer version instead assigns all $180 to East. Joining only on customer_id to both versions produces six rows and $360. We verified these outcomes in a local SQLite fixture, including a purchase exactly at the February boundary. The fixture checks query logic, not warehouse concurrency or performance.

A LEFT JOIN exposes unmatched rows instead of silently dropping them. Before publishing the fact load, check that every accepted staged line matches exactly one dimension version. Zero matches need an explicit handling policy; multiple matches indicate overlapping history. Do not let a successful SQL statement substitute for those checks.

What changes when facts or dimensions arrive late?

A January purchase arriving in March still needs January's customer version. Kimball's late-arriving-fact guidance calls for finding the dimension keys effective when the measurement occurred. Arrival time and event time answer different questions. Kimball: Late arriving facts

A missing customer dimension is a different problem. Depending on the reporting contract, quarantine the line, use a designated unknown member, or create an inferred member with a durable customer identity. Microsoft describes updating an inferred member when its details arrive rather than automatically treating that enrichment as a new Type 2 change. Microsoft: Loading dimensional tables

Reasoned design recommendation: record unresolved mappings and repair them deliberately. Track how long each mapping remains unresolved so missing regional sales can be investigated.

Suppose the warehouse learns in March that the move actually happened on January 15. This is a retroactive change to effective history. Under our region-at-purchase contract, split or repair the affected intervals, reassess January facts, and rebuild affected aggregates. Appending a new row effective in March would preserve the wrong January classification.

Ask whether consumers need corrected business history or the report as it was known at an earlier load time. A single effective-time interval does not automatically preserve both. Keeping ingestion audit records or an additional system-time history requires an explicit design decision.

For repeatable processing, distinguish duplicate source delivery from a new attribute change. Establish deterministic ordering for multiple updates to one customer, make expire-and-insert changes atomic where supported, and ensure replay cannot create extra versions. The exact mechanism depends on the warehouse and ingestion system.

How would you defend the model under follow-up questions?

Begin with the failure that would change a business answer. For this case, check unique (order_id, line_id) values, valid dimension references, non-overlapping customer intervals, and exactly one open-ended version for each active customer. Reconcile line totals to the accepted source after applying the same exclusions.

Then test meaning, not just structure: a boundary-date purchase, an old purchase arriving late, two legitimate equal-value lines, a zero-value order, and a customer correction. Equal-value lines are particularly useful for exposing an incorrect SUM(DISTINCT line_amount) fix.

Practice question: “Why a star schema instead of a fully normalized design?”

For this reporting workload, a star keeps analytical joins and grouping attributes understandable. Normalizing a shared hierarchy may reduce duplicated attributes, but it introduces additional relationships and update dependencies. Microsoft describes these snowflake-dimension trade-offs in its star-schema guidance. Defend the choice using representative queries and maintenance requirements; neither layout guarantees better performance everywhere.

Finish by explaining what would change your design. New multi-currency reporting needs an exchange-rate policy. Partial refunds need their own event grain and link to the original line. A changed requirement should produce a deliberate model change, not an undocumented exception in dashboard SQL.

Practise the reasoning on PracHub

These candidate-reported practice records provide related exercises, not verified predictions of your interview. The prompts below span warehouse foundations and distinct analytical domains; apply the grain-and-history reasoning rather than memorizing a schema.

Choose one Data Engineer practice question, declare its grain, and create three rows that could break your first design. Explain the expected result before writing the query. That is a concrete way to turn dimensional-modeling vocabulary into an answer you can defend.

Sources and Further Reading

• Kimball Group: Grain
• Kimball Group: Additive, semi-additive and non-additive facts
• Kimball Group: Transaction fact tables
• Kimball Group: Periodic snapshot fact tables
• Kimball Group: Accumulating snapshot fact tables
• Kimball Group: Type 2 dimensions
• Kimball Group: Late arriving facts
• Microsoft: Star schema guidance
• Microsoft: Loading dimensional tables