10 Data Modeling Interview Questions: Grain, History, and Correct Totals
Quick Overview
Practice data modeling with order analytics, SCD boundaries, fact grains, allocation bridges, late events, and tests that catch double counting.
A data model determines what a query's answer means. If an order total is repeated across line items, a perfectly valid sum can overstate sales. If historical purchases join to a customer's current territory, the query may answer a different question from the one finance intended.
These ten questions use an original order-analytics scenario. Start with business meaning, declare a row's grain, then explain keys, relationships, history, and reconciliation. For a public practice prompt that connects schema choices to ingestion, use teleoperations data models and pipelines.
1. When would you choose a star or snowflake schema?
A star connects a fact table to dimensions that are relatively easy for analysts to navigate. A snowflake normalizes parts of dimension hierarchies into additional relations. The decision concerns maintenance, semantic clarity, storage, and representative workloads; neither diagram guarantees better performance.
For order-line analytics, a product dimension carrying category attributes can simplify a BI model. A separately maintained hierarchy can be appropriate when reporting relationships need centralized management. Ask whether a product's old sales should use its historical category or today's hierarchy before deciding how category changes propagate.
Explain the joins and measure behavior to a reader, then benchmark the relevant workload if performance motivates a physical change. The dimensional-modeling practice article expands the trade-offs.
2. How do SCD Types 1, 2, and 3 differ?
Type 1 overwrites an attribute. Type 2 creates a new historical version. Type 3 retains selected prior values in additional attributes. A spelling correction and a real customer relocation may require different policies even when they change the same column.
Kimball's Type 2 guidance assigns each version its own surrogate key and describes effective time, expiration time, and a current-row indicator. The business identifier repeats across versions.
Use nonoverlapping intervals with an explicit boundary convention. For example:
| Customer | Version key | Valid from, inclusive | Valid until, exclusive | Region |
|---|---|---|---|---|
| C7 | 101 | 2026-01-01 | 2026-06-01 | East |
| C7 | 102 | 2026-06-01 | Open-ended | West |
An event dated June 1 belongs to version 102 under this convention. Match event time to the valid interval and store the resolved version key in the fact. Define how the open-ended interval is represented; a nullable end timestamp requires deliberate matching logic.
A late fact still resolves against historical effective time. A backdated dimension correction may require repairing affected fact keys. Ingestion time and business-valid time are separate concepts; keeping both can matter when reconstructing what a report knew at a particular moment.
3. How do you declare a fact table's grain?
State what one row represents before choosing measures. “One source order line” is more precise than “sales data.” Define the source-qualified order identifier and line identifier, then decide whether a correction updates that business event or creates a separate adjustment.
A generated row ID does not prevent two copies of the same business event. Test uniqueness at the declared business grain and make replay behavior explicit. Keep source lineage sufficient to reconcile the event with its input.
Measures must fit that grain. Quantity and the line's historical sale price fit an order line. An order-level delivery charge needs an allocation rule or a separate order-grain fact. Retaining more detail is useful only when the source actually supplies it and the retention and access rules permit keeping it.
4. How would you model order analytics?
Separate purchases, refunds, browsing sessions, and fulfillment when their grains differ. An order-line fact can hold quantities and line amounts; refund events can identify the amount, timing, and source line they adjust. A fulfillment fact may need shipment grain when an order splits.
Preserve original currency and define any reporting-currency conversion policy. Distinguish order, shipment, and refund dates rather than letting one generic date column stand for all three. Define gross sales, discounts, taxes, and net measures explicitly.
Partial refunds must reconcile to the original event without requiring a fabricated negative purchase. Where a refund cannot yet be matched to a line, retain and surface that unresolved state instead of dropping it from reported reconciliation.
Avoid joining raw sessions to raw order lines and then summing money. Aggregate to a compatible grain or use an attribution model with an explicit contract. A plausible foreign key is not proof that a measure remains additive after a join.
5. What do normalization and denormalization protect?
Normalization addresses dependencies and anomalies. As a practical introduction, first normal form avoids repeating groups in a relational row; second normal form removes partial dependencies on candidate keys; third normal form removes transitive dependencies, where a non-key attribute depends on the key only through another non-key attribute (an order row storing customer_id and that customer's region, for example). Precise definitions use all candidate keys, not just whichever primary key was selected.
With key (order_id, line_id), order date depends on the order identifier alone and belongs on the header. A customer's current name belongs with the customer. The historical price paid remains a transaction attribute even if today's product catalog has a different price.
Denormalization creates maintained copies for a purpose. Name the owner, refresh policy, and consistency expectation. A reporting copy of region must say whether it represents event-time region or current region. PostgreSQL's constraints documentation shows how primary keys, unique constraints, and foreign keys enforce those relationships and referential integrity.
6. How do OLTP and OLAP requirements differ?
An operational payment service prioritizes correct, bounded transactions and targeted access. An analytical model prioritizes interpretable historical scans, aggregation, and shared business definitions. This is a workload distinction, not a universal rule that one must be normalized and the other must not.
Explain the change-capture boundary: when does a business transaction become visible, how are deletes represented, what reporting delay is acceptable, and how are corrections reconciled? Streaming ingestion can improve freshness while still exposing provisional or incomplete results if publication semantics are unspecified.
Hybrid engines do not remove those questions. Separate broad reporting work from latency-sensitive paths when the serving contract calls for it, and justify indexes, clustering, or partitioning using actual access patterns.
7. How do conceptual, logical, and physical models relate?
The conceptual model establishes entities and meaning with stakeholders. The logical model makes identifiers, attributes, optionality, and cardinality precise. The physical model maps those decisions onto an engine's types, tables, indexes, storage layout, and access rules.
For a fitness app, ask whether one activity can involve several people or several devices before selecting foreign keys. The public fitness-app tables and relationships prompt is a useful exercise in explaining that distinction to a product audience.
Physical optimization must preserve the logical contract. If two source systems both issue customer ID 42, a globally unique surrogate key does not by itself tell you whether they represent the same person; source-qualified identity and entity resolution need their own rules.
8. How do you prevent double counting across a many-to-many bridge?
A bridge describes relationships, but analysts still need aggregation rules. Suppose one account has a balance of 100 and two owners. Joining that balance to both owners produces two rows carrying 100; summing them yields 200.
| Reporting purpose | Treatment | Result for this example |
|---|---|---|
| Allocated balance | Apply weights 0.6 and 0.4 | 60 and 40, totaling 100 |
| Owner participation | Associate the full balance with each owner | 100 each; not additive across owners |
| Filter accounts by owner | Use existence or a deduplicated account set | Account balance remains 100 |
Allocation weights are a business policy, not a technique to conceal duplicate rows. Validate that the applicable weights sum to the required allocation and match the relationship version valid at the fact's date. Decide what happens when weights are missing or ownership changes.
9. Which fact type fits the business process?
Transaction facts describe individual events. Periodic snapshots describe state at a reporting boundary. Accumulating snapshots track milestones for a process instance, updating the row as that process progresses.
An account balance can be additive across accounts at a particular time while being inappropriate to sum across monthly snapshots. A conversion rate is not safely summed across groups; retain eligible numerators and denominators so a combined rate can be recomputed.
If an order can split into several shipments or revisit a stage, one accumulating order row may lose important detail. Choose a grain that fits the process and retain transition events when the full history matters. Missing, not-yet-observed, and not-applicable milestones should remain distinguishable.
10. How would you choose a warehouse modeling approach?
Ask about sources, historical integration, lineage, delivery needs, and team ownership first. Dimensional marts can make business-process analysis accessible. An integrated normalized layer can serve enterprise integration. Data Vault separates business keys, relationships, and descriptive history into hubs, links, and satellites, often with a separate consumption layer.
Conformed dimensions require compatible definitions and keys across marts, not merely tables with matching names. Additional integration layers require tests, orchestration, reconciliation, and backfill ownership. A hybrid is useful when those responsibilities are clear; complexity is not evidence of maturity.
Use the analytics engineer guide to connect schema choices with incremental models and stakeholder communication.
Test the meaning as well as the tables
For the order scenario, test business-key uniqueness, relationship cardinality, dimension-version interval overlap, boundary-time matches, allocation totals, and reconciliation after a replay or partial refund. A foreign-key check alone will not catch a join that multiplies a valid measure.
Introduce a late refund, a customer relocation, a backdated correction, and a deletion request. Explain which historical answers should change, which reports need rebuilding, and how downstream readers learn about the change. A diagram is useful when someone can implement and test its promises.
For further reading, Kimball's dimensional modeling techniques summarizes the fact-table types, additivity rules, slowly changing dimension types, conformed dimensions, and bridge tables discussed above.
Comments (0)