Data Modeling Approach Trade-offs

The reasoning

Kimball builds bottom-up: pick a business process, model it dimensionally, conform its dimensions against what exists, ship it. Value arrives early and integration is achieved through conformance rather than through a central model. The risk is that without the bus matrix discipline you get a pile of marts that cannot be combined.

Inmon builds top-down: a normalized, integrated enterprise layer first, with dimensional marts derived from it. The integration is explicit and durable, and it costs time before anything reaches a user — which is why it suits organisations with many conflicting sources, long horizons and the patience to fund a layer nobody queries directly.

In practice most modern warehouses are Kimball-shaped at the serving layer regardless of what happens underneath, because that is the shape BI tools and analysts want. The real question is usually not which methodology but whether you need an integrated layer *beneath* the marts, and that depends on how badly your sources disagree.

What would change my answer: a dozen source systems with conflicting customer definitions, a regulated industry needing full auditability of transformations, or an organisation where marts are built by teams who will not coordinate. Any of those makes an integration layer earn its cost.

The reasoning

Data Vault separates hubs (business keys), links (relationships) and satellites (attributes with history). Everything is insert-only and everything carries its source and load time, which makes the model auditable by construction and highly tolerant of sources changing shape — a new source is new satellites, not a redesign.

The costs are proportionate. The table count multiplies, a query that would be one join in a star becomes five or six, and nobody queries the raw vault directly — so you are committing to build and maintain a dimensional presentation layer on top of it. That is two models, not one.

It fits: regulated industries where lineage must be provable, organisations integrating many systems whose schemas change without warning, and teams doing large parallel loads where insert-only and hash keys avoid coordination. It does not fit a single-source warehouse with a stable schema and three engineers, where it is pure overhead.

The pattern worth naming is that Data Vault and Kimball are not competitors at the same layer. A common architecture is a raw vault for integration and auditability with Kimball marts as the serving layer — which is Inmon's shape with different mechanics.

What would change my answer, in either direction: a regulator asking to see the provenance of a specific value would push me toward it; a single source with a stable schema and a team of three would rule it out, because the presentation layer alone is more work than that team's entire warehouse.

The reasoning

The case for OBT is real: columnar engines read only the columns touched, so width is nearly free, and no joins means no fan-out, no as-of predicate to forget and nothing for a BI tool to get wrong. For a single consumer with a fixed access pattern it is genuinely the better serving shape.

The case against is what happens over time. An attribute change becomes a rewrite of the largest table you own rather than one dimension row; history has to be frozen into the row or lost; and two teams building their own wide tables produce two definitions of every metric with no conformed dimension to reconcile them.

Layering resolves it, and the key property is that the wide table must be *derived*. If it is materialised from the star, it cannot disagree with it and can be dropped and rebuilt when a definition changes. If it is loaded by its own pipeline from source, you have two models and they will diverge — which is the failure mode dressed as an optimisation.

So the answer flips on whether there is a modelled layer underneath. Wide tables on top of a star: good engineering. Wide tables *instead of* a star: a decision to have no model, which shows up the first time someone asks a question the table was not shaped for.

The reasoning

Normalization exists to prevent update anomalies, and an update anomaly needs two writers touching two copies of the same fact. An application database has thousands of concurrent writers, so redundancy is genuinely dangerous and 3NF is the right default there.

A warehouse is written by one controlled pipeline. If `region` appears on a million rows, one process put it there and one process refreshes it, so the copies cannot disagree. The redundancy is real and the anomaly is structurally impossible — which is the actual justification for denormalizing, and a much stronger one than 'joins are slow'.

What you buy is legibility and stability: fewer joins means query paths an analyst can follow, and a self-describing dimension row. What you pay is storage and the fact that changing an attribute rewrites many rows rather than one — which is a load-design problem, not a correctness one.

The line I would draw: denormalize attributes onto dimensions without hesitation; denormalize dimension attributes onto facts only when you have decided that freezing the value at load time is what you want, because that is a Type 0 choice made implicitly. And keep facts at their declared grain regardless, because that is not a normalization question at all.

The reasoning

**Bronze** is the landing zone: exactly what arrived, with ingestion metadata, never modified. Its purpose is replay — when a transformation turns out to be wrong, bronze is what lets you rebuild without going back to a source that may no longer have the data.

**Silver** is where cleaning, typing, deduplication and light conformance happen, and it stays close to the source shape. This is the layer that rots, because 'cleaned' has no natural boundary and business logic drifts into it one exception at a time. The discipline that works is that silver may not contain business definitions — no metric logic, no 'active customer' — only correctness of the data as received.

**Gold** is the dimensional model: conformed dimensions, facts at declared grain, history where required, and the semantic definitions on top. This is the only layer most consumers should see, and everything in it is derivable from silver.

Whether you need three depends on scale rather than fashion. A single-source pipeline can reasonably collapse bronze and silver. What you should not collapse is silver and gold, because that is where source shape and business meaning get mixed, and after that no layer can be rebuilt independently.

The reasoning

The logical model does not care. A fact at line grain with conformed dimensions and Type 2 history is the same design on Snowflake, BigQuery, Databricks or Iceberg on S3, and a model that changes shape because of the storage engine usually had a physical concern smuggled into it.

What genuinely differs is mutation. A warehouse updates rows cheaply, so Type 2 closes and accumulating snapshots are routine. A lakehouse table is append-oriented with MERGE support layered on top, so in-place updates mean rewriting files — which makes an accumulating snapshot or a Type 1 overwrite noticeably more expensive and pushes some teams to rebuild derived tables instead.

The second difference is file-level maintenance, which has no warehouse equivalent. Small files from frequent micro-batches degrade reads until compacted; partition evolution lets the layout change without a rewrite; and snapshot expiry and orphan cleanup are real operational jobs. None of these affect the model and all of them affect whether it performs.

The third is that a lakehouse gives you open formats and engine choice, which matters when several engines read the same tables — and brings the obligation to agree a table format and a catalog, because that is what makes the model shared rather than per-engine.

The reasoning

The purpose is pruning: a query for one day should read one partition. That means partitioning on the column the `WHERE` clause uses, and analytical queries filter on when something *happened*, not when it was loaded. Partitioning by load date is common because it is convenient for the pipeline, and it means a query for one event day touches every partition that might contain it.

Granularity trades pruning against file count. Too coarse and every query reads more than it needs; too fine and you get thousands of small files, which costs metadata operations and read throughput far more than the pruning saves. Daily is the default because it matches how people query and keeps partitions at a sensible size; the check is whether a typical partition is at least a few hundred megabytes.

The consequence of event-date partitioning is that late-arriving data writes into old partitions, so the load has to handle out-of-order writes and the small files they create. That is a real operational cost and it is the right one to pay, because the alternative is that every query is a full scan.

Clustering or sort order within the partition is the second lever, and it targets the next most common filter — usually a high-cardinality key such as customer or product. Partitioning decides how much data is read; clustering decides how efficiently that data is scanned, and they are chosen from the same query analysis.

The reasoning

Try the cheaper things first: partition pruning that actually prunes, clustering on the common filter, and materialised views where the engine maintains them for you. A hand-built aggregate is the most expensive option to own, so it should not be the first one reached for.

When you do build one, it must be derived from the atomic fact by a query, not loaded independently. That is what makes it impossible for the aggregate and the detail to disagree, and it means the aggregate can be dropped and rebuilt when a definition changes rather than becoming a second source of truth.

Distinct counts are the trap that catches people. They are not additive, so a daily aggregate storing distinct customers cannot be rolled up to a month — the same customer active on two days is counted twice. Either compute them from the atomic fact, or store a mergeable sketch such as HyperLogLog and accept approximation.

The last consideration is that each aggregate is a grain you have committed to. A new question at a grain you did not anticipate either misses the aggregate entirely or, worse, gets answered from the nearest one and is wrong. That is an argument for few aggregates chosen from real query patterns rather than many built speculatively.

The reasoning

With one BI tool and one team, defining metrics there is perfectly reasonable and adds nothing to maintain. The argument only starts when a second consumer appears: a data scientist in a notebook, a reverse-ETL job pushing to a CRM, an application reading the warehouse. Each re-implements the metric, and they will not match.

The layer's job is to hold definitions rather than data: `net_revenue = gross_revenue - returns`, `active_customer = ordered in the last 90 days`, along with the dimensions each can be sliced by. Everything consumes the same definition, so a disagreement between two dashboards becomes structurally impossible rather than a recurring investigation.

It also makes the semi-additive and non-additive rules enforceable in one place. A raw `balance` column can be summed by anyone; a semantic metric that declares its time behaviour cannot be, which is the only way that rule reliably survives contact with a new analyst.

The costs are real: another system, another deployment, another thing that can be wrong, and a query path that some engines optimise less well. So the honest trigger is more than one consuming tool plus a history of two dashboards disagreeing — before that it is speculative infrastructure.

The reasoning

A central team gives you consistency by construction: one set of conformed dimensions, one definition of customer, one review. It fails on throughput — the team becomes the queue for every domain's request and the people writing the models know the least about the business processes they are modelling.

Domain ownership fixes both by putting modelling where the knowledge is, and it fails on integration unless something replaces the consistency the central team was providing. That replacement is federated governance: conformed dimensions owned centrally, data contracts between domains, and a platform team that supplies tooling rather than building models.

So the prerequisites are what the answer turns on. Domains need the capability to model, an agreed set of conformed dimensions they must use, contracts with consumers, and a platform that makes doing it correctly the easy path. Without those, decentralisation is just a permission to build silos.

In practice the useful middle is a central team owning the conformed dimensions and the platform, with domains owning their facts. That keeps the integration point central — which is where it has to be — while moving the volume of work to the people who understand it.

The reasoning

The workloads are opposite. An application does many small concurrent reads and writes of individual rows and needs integrity enforced by the database, because many code paths write the same data. A warehouse does few large scans written by one pipeline and needs shape optimised for reading.

So normalization comes back: 3NF, real foreign keys, unique constraints, check constraints, and transactions. All of these are cheap in OLTP and are what stop concurrent writers producing contradictory state — which is precisely the anomaly a warehouse does not have to worry about, because it has one writer.

Indexing differs too. An application wants indexes for point lookups and the specific queries it issues, at a write cost per index. A warehouse relies on partition pruning and clustering over full scans of columns. Applying either strategy to the other workload performs badly.

The overlap worth naming is that both need a clear idea of what an entity is and what makes it unique. Good key design transfers directly, and the operational schema's natural keys are what the warehouse will use to build its dimensions — so an application that models identity carelessly makes the warehouse harder forever.

What would change my answer: an access pattern that is always the whole aggregate and never queries across them — an event log, a document store — where a denormalized document per entity beats a normalized schema. That is a genuine case, and it depends on the access pattern rather than on the workload being operational.

The reasoning

In a relational or dimensional model you design the data and query it however you like afterwards. In a key-value store the query capability is fixed by the key design: partition key plus sort key gives you point lookups and range scans within a partition, and nothing else is efficient. So the access patterns have to be enumerated before any modelling.

That inverts the normalization instinct. Data is duplicated across item collections so each access pattern is one lookup, and consistency between the copies is the application's problem — usually handled by writing them together or by accepting eventual convergence. Redundancy is the design, not a compromise.

The single-table design pattern follows from it: several entity types share one table with a composite key scheme that lets one query fetch an object and its related items together. It is powerful and it is unreadable to anyone who has not seen the access-pattern list, which makes documenting that list part of the model.

What would change my answer is the arrival of ad-hoc querying as a requirement. The moment someone needs to ask questions the key design did not anticipate, a key-value store is the wrong home for that workload and the answer becomes a warehouse projection alongside it.

What this means for a data engineer is that the warehouse and the serving store are modelled from opposite ends and both are right. The pipeline's job is to project the dimensional model into whatever shape the access patterns need — which is the same layering argument as OBT, with a different storage engine.

The reasoning

A relational model represents relationships fine — a link table is a graph edge. What it handles badly is traversal of unknown depth: 'find everyone connected to this account through any number of shared devices' becomes a recursive CTE whose cost grows unpredictably, and expressing path constraints in SQL is painful.

A graph database makes those queries first-class and fast, because the storage is adjacency rather than tables. If the core questions are shortest path, connected components, centrality or pattern matching over paths, that is what it is for.

The costs are the usual ones for a specialised store: another system, another skill set, and weaker support for the aggregate reporting that everything else in the warehouse does. So the common architecture is not either/or — the warehouse remains the source of truth and a graph projection is built from it for the specific analysis.

Worth noting that fixed-depth questions do not justify it. 'Customers who share an address' is one join; 'customers connected through any chain of shared attributes' is a traversal. The boundary is whether the depth is known when you write the query.

The reasoning

The failure mode is leakage. Joining a customer's *current* attributes to a historical training example gives the model information it could not have had at the time — the classic case being a churn model trained with an attribute that only changes after churn. The model scores brilliantly in evaluation and fails in production.

The fix is exactly the as-of join from Type 2 dimensions: every feature value must be selected as of the event timestamp, using the version that was valid then. A warehouse with proper Type 2 history already has what is needed, which is a strong argument for keeping history on attributes that feed models.

The second requirement is train/serve consistency. The feature computed at training time and the one computed at serving time must be produced by the same logic, or the model sees a different distribution in production. That is what a feature store provides — a definition computed once, materialised offline for training and online for serving.

What would change the answer is whether the features are time-dependent at all. A feature computed from immutable attributes — a product's launch date, a country's region — needs no as-of logic and can be read straight from the dimension; it is only attributes that change that require the machinery.

So the dimensional model is the right foundation and not the whole answer. Features are definitions over it, with a timestamp requirement, and they belong in a layer that guarantees both point-in-time correctness and train/serve parity — which is the same shape of argument as the semantic layer for metrics.

The reasoning

**Model the process, not the source.** Source systems get replaced; the business process of taking an order does not. A model shaped like the current CRM has to be rebuilt when the CRM is, and one shaped like 'orders placed' absorbs a new source as a new load.

**Declare the grain and keep it atomic.** Aggregation throws away the answer to every question nobody has asked yet, and the request for finer detail always arrives. Keeping the finest defensible grain is what makes a new dimension a join rather than a reload.

**Use surrogate keys.** They absorb natural key changes, source system replacements and multi-source integration — three of the most common changes — without touching a fact row.

**Put definitions above the tables.** Metrics in a semantic layer can be changed, versioned and parameterised. Metrics materialised as tables become new tables every time the definition moves.

The habit underneath all four is to ask, for each decision, what it makes expensive later. A grain choice is expensive to change; a column addition is not; a metric definition should be free. Spending the design effort where the reversal cost is high is what separates a model that lasts from one that is rewritten every two years.

The reasoning

The costs of modelling are real: design time before anything is delivered, a pipeline to maintain, and a structure that constrains what can be asked. Those are worth paying when many consumers ask many questions over a long period, because the model is what makes each new question cheap.

When there is one analyst answering one question that will not be asked again, none of that applies. Querying the raw data directly is faster, and building a star schema for it is ceremony. The same is true during genuine exploration, where you do not yet know the questions and imposing a grain would be premature.

The trap is that temporary analyses become permanent. A notebook that answers a question becomes a weekly report, then a dashboard, then something three teams depend on — with no grain, no tests and no conformance. The judgement is not really 'model or not' but 'has this crossed the line from exploration to production', and noticing that transition is the skill.

A reasonable rule: the moment a second consumer depends on a number, or the analysis is scheduled rather than run by hand, it needs a model. Before that, the discipline costs more than it returns.

The reasoning

Grain, dimensions and history are unchanged: a fact at line grain with conformed dimensions is the same design whether it is loaded nightly or continuously. Anyone who redesigns the model for streaming is usually solving a load problem in the wrong place.

What does change is that every batch-era safety net disappears. There is no nightly full rebuild to correct yesterday's mistakes, so the load must be idempotent — a merge key that makes a replayed event a no-op rather than a duplicate — and it must be, not merely usually be, because a stream will replay.

Late and out-of-order data becomes an explicit policy rather than an accident of timing. You need a watermark defining how long you will wait, a decision about what happens to data arriving after it, and an understanding that any aggregate you publish is provisional until the watermark passes. Batch pipelines make that decision implicitly at the batch boundary; streaming forces it into the open.

Dimension lookups get harder too. Resolving a Type 2 version as-of the event time requires the dimension version to exist when the event arrives, which for a fast-moving stream frequently it does not — so inferred members and a repair process stop being an edge case and become part of the normal path.

The reasoning

The departments' request is usually legitimate — different naming, different default filters, different metrics that matter to them — and none of that requires a separate model. Views, or a semantic layer with per-department metric sets, deliver all of it over one set of conformed dimensions and facts.

What must not fork is identity and grain. Three customer dimensions means three definitions of customer, three counts that disagree, and no way to combine departmental facts. That is the failure conformance exists to prevent, and it is very hard to undo once each department has a year of reporting behind it.

The pattern that works is one core plus thin presentation layers, with the rule that a presentation layer may rename, filter and define metrics but may not redefine an entity or change a grain. That gives departments real autonomy over how they see the data and keeps the integration point intact.

What would change my answer is genuine organisational separation: an acquired business running its own systems with no shared customers and no cross-entity reporting requirement. There, a separate model is honest, and the integration cost is only paid if and when the businesses actually merge.

Where a department genuinely needs a different grain — finance at month-end balances, operations at event level — that is an argument for an additional fact, conformed through shared dimensions, not for a separate model. Different facts over conformed dimensions is exactly what the architecture is for.

The reasoning

Tests are unilateral and always available: assert the grain is unique, no orphan keys, no overlapping Type 2 intervals, volumes within range. They need no cooperation from anyone, and they tell you something is wrong after it has arrived — which is late but far better than a stakeholder telling you.

Contracts are bilateral and change accountability. An agreed schema with a versioning policy means the producer knows that renaming a column breaks a consumer, and that breaking changes are announced rather than deployed. Without one, a schema change is your emergency; with one, it is a negotiation before the fact.

So they cover different failures. A contract does not stop a source sending semantically wrong data that conforms to its schema — a currency mislabelled, a duplicate batch — and a test does not stop a producer renaming a column next Tuesday. You need both, and the tests remain necessary even with the best contract.

What would change the balance is who you can negotiate with. An internal source owned by a team on the same roadmap can be held to a contract cheaply; a third-party vendor feed usually cannot, and there the answer is heavier tests plus a quarantine path, because prevention is not available to you.

The practical starting point is tests, because they need nobody's agreement, and then contracts with the sources that have broken you most. Trying to establish contracts across every source at once is a governance programme; adding one where it has already hurt is a conversation.

The reasoning

Start by separating the request from the requirement. 'We want one big table' is a proposed solution; the requirement underneath is usually 'our analysts find the joins hard' or 'the dashboard is slow'. Both are satisfiable without giving up the model, which makes most of these disagreements resolvable rather than adversarial.

Then be concrete about the trade rather than the principle. 'Attribute changes become a rewrite of the largest table we own' and 'we lose the ability to report as-was' are checkable consequences that a stakeholder can weigh. 'Dimensional modelling is best practice' is not an argument and will not survive contact with someone senior who disagrees.

State what would change your mind, and mean it. If there is exactly one consumer, the access pattern is fixed, nobody needs as-was history and the table is rebuilt daily from source, a wide table is genuinely the right answer and defending the star would be dogma.

And if the decision goes against you, implement it properly and instrument it — where the definitions live, how a change to an attribute is handled, what happens when a second consumer appears. Being right later is worth much less than having made the failure mode visible in advance.

The reasoning

**1 · Which workload.** OLTP or OLAP, and say why. An operational wallet needs normalised tables, constraints and short transactions; a reporting model needs a star schema built for scans. Designing the wrong one is a whole-answer error, and the interviewer often has not told you which they meant — so asking is a point, not a delay.

**2 · The business process and the grain.** "One row per wallet transaction event" or "one row per ledger movement" — one sentence, said out loud, before any column. **Every subsequent decision depends on it**, and a model whose grain was never stated is the defect that shows up in a critique question later. This is also where you ask the clarifying questions that matter: does history need to be reconstructable, what is the reporting latency, what volume are we at.

**3 · The tables, named but not detailed.** `dim_user`, `dim_wallet`, `dim_asset`, `dim_date`, `fact_transaction`, `fact_ledger_entry`, `fact_balance_snapshot`. Listing them first gives the interviewer the shape and lets them redirect you before you have spent ten minutes on columns they did not care about.

**4 · Facts versus dimensions, with the reasoning.** Facts are the measurable events — transactions, ledger entries, balances. Dimensions are the descriptive context you filter and group by — user, wallet, asset, status, date. Saying *why* each one is where it is, rather than reciting the split, is what shows you can apply it to a domain the interviewer invents.

**Then go deep where they push.** Keys, SCD choice on the attributes that change, additivity of the measures, and the physical layer last. The method matters more than the tables: a candidate who works in this order can model a domain they have never seen, and one who starts from remembered tables cannot.

The reasoning

**Real columns** are typed, cheap to scan, visible in the catalog, and testable — you can assert not-null and uniqueness on them. The cost is that every new attribute is a schema change, coordinated with the producer, and a table with three hundred sparse columns is its own problem.

**A semi-structured column** absorbs anything without a migration, which is what you want for the long tail of attributes that arrive, get used by one team for a month, and vanish. The cost is that nothing is typed, nothing is validated, the catalog says nothing about what is in there, and every consumer writes their own extraction expression — so the same field ends up parsed three different ways.

**The answer is a split, applied per attribute.** Promote a field to a column when it is queried regularly, filtered or joined on, or needed by a contract. Leave it in the payload when it is rare, exploratory, or genuinely producer-specific. That makes it an ongoing curation decision rather than a one-time architecture choice — and the review that promotes fields is the thing teams forget to schedule.

**The engine moves the split point, which is the part worth knowing.** Snowflake's `VARIANT` shreds semi-structured data into columnar sub-columns internally and collects statistics on them, so querying a payload field is much closer to querying a real column than people expect. BigQuery gives you `JSON` and repeated/nested `STRUCT`s with similar benefits. On raw Parquet in S3 read by Spark, a JSON string column is genuinely opaque — no pushdown, no pruning, parsed per row — so the case for promoting attributes early is much stronger there.

**Either way, partition by time and keep the raw payload.** Time partitioning is what makes the table queryable at all at event volumes, and keeping the original payload alongside the promoted columns is what lets you backfill a new column from history instead of asking the producer to resend.