Why Good Data Models Depend on Knowing What to Repeat

Jul 05, 2026

9 min read

3 views

84%

The strange truth about clean data: sometimes you have to keep the duplicates

Most people assume that good data design means eliminating repetition. That instinct is understandable. Repeated rows feel messy, inefficient, and suspicious. But in analytics, repetition is not always noise. Sometimes it is the very structure that makes a model usable, fast, and accurate.

That creates a deeper question: when should data be deduplicated, and when should it be preserved? The answer turns out to be less about tidiness and more about purpose. A data model is not a warehouse where every item must be stored once. It is a system for asking questions quickly and reliably. In that system, repeated values are not a flaw if they help you count, group, filter, and relate events.

This is where two ideas meet in a surprisingly useful way. One is the humble set operation that says, in effect, keep the repeated rows if you need them. The other is the star schema, which organizes data so that repetition is pushed into the right place: the fact table. Together they suggest a powerful design principle: do not remove all duplication, remove duplication only where it blocks meaning.

Repetition is not the enemy, ambiguity is

A lot of analytical confusion comes from treating all repeated data as equal. In reality, there are at least two kinds of repetition. The first is accidental repetition, where the same row appears twice because of poor data hygiene. The second is structural repetition, where the same product, customer, or date appears many times because the system is recording many events.

Those are very different situations. If a sales table records 10,000 transactions, you should expect to see the same customer ID and product ID again and again. That is not a sign of failure. It is a sign that the table is doing its job: preserving each event as its own row. If you forced that table to keep only unique rows, you would destroy the very history you need to analyze.

This is why UNION ALL matters conceptually, even beyond SQL syntax. It represents a refusal to pretend that repeated rows are always redundant. In a transactional world, each occurrence can carry distinct meaning. Two identical-looking sales lines may still differ by time, store, promotion, or customer. The row is not just a container of values. It is a record of an event.

In analytics, the question is rarely “Should we keep duplicates?” The real question is, “Are these repeats evidence of many events, or evidence of a bad model?”

That distinction is the foundation of everything that follows.

The star schema is really a theory of where repetition belongs

The star schema is often taught as a tidy way to organize tables, but its deeper purpose is philosophical. It decides which parts of the business should be repeated and which parts should remain unique.

Fact tables hold the events: orders, shipments, payments, visits, clicks. Because events happen repeatedly, fact tables are naturally repetitive. The same product, customer, employee, or date may appear many times. Dimension tables, by contrast, hold the stable descriptors: product names, customer regions, employee roles, order types. These tables aim for uniqueness, one row per entity, because they are meant to filter and group, not to store every event.

This separation produces more than neatness. It creates a model that is easier to explore, simpler to aggregate, and more accurate to query. If you want total sales by product, you do not want product details embedded in every transaction row in a way that creates ambiguous joins and inconsistent labels. You want a clean path from many events to one descriptive table.

A useful way to think about this is to imagine a library. The fact table is the checkout log, full of repeated book IDs, patron IDs, and dates. The dimension tables are the catalog entries for each book, each patron type, each branch. You would never store a complete book description in every checkout record. That would make the system bloated and harder to maintain. Instead, the checkout log points to the catalog. The repetition lives in the log because that is where repetition belongs.

This is why the benefits of a good data model feel almost too practical to be profound. Faster exploration, simpler aggregations, more accurate reports, less maintenance. But those outcomes all come from one hidden design truth: put repetition where events live, and uniqueness where meaning lives.

The real cost of removing repetition is losing context

Many broken models are built by an overzealous desire to normalize everything into uniqueness. That instinct sounds logical. Why store the same customer name 5,000 times if one customer table can store it once? But taken too far, this approach can make analysis feel like solving a puzzle with half the pieces removed.

The problem is that business questions are rarely about isolated objects. They are about relationships across time and category. What happened last month? Which region is growing fastest? Which product sells best when promoted? These are questions about aggregates, not just entities. To answer them well, the model needs to retain the multiplicity of events.

If you strip repetition too early, you may lose the ability to count correctly. For example, suppose a retailer wants total units sold by month. If the raw data has one row per order line, repeated product and customer IDs are essential. They allow the model to sum every occurrence. If you deduplicate on product ID, you would collapse distinct purchases into one and undercount sales. The apparent cleanliness would be catastrophic.

This is the paradox: duplication can preserve truth when the truth is that something happened many times.

At the same time, keeping repetition in the wrong place can make a model fragile. If product names are repeated directly in the fact table, a typo or naming change can create multiple versions of what should be the same category. Reports become inconsistent. Filters become unreliable. Maintenance becomes painful. So the goal is not to maximize or minimize duplication in the abstract. The goal is to route it correctly.

One can think of this as an economy of attention. The model should spend repetition only where it buys analytical power. Repeated events belong in the fact table because the repetition is the data. Repeated descriptions belong in dimensions only if they are truly descriptors, not accidental copies.

A useful mental model: the model as a conversation between events and identities

The clearest way to unify these ideas is to see a data model as a conversation between two different kinds of truth.

Events answer: what happened, when, how much, and how often?

Identities answer: what kind of thing was involved, and how should we group it?

Fact tables are event memory. Dimension tables are identity memory. Set operators like UNION ALL remind us that event memory must be allowed to contain repeated records because events are repeatable by nature. Star schemas remind us that identity memory should be compact and unique because identities are used to interpret those events.

This conversation becomes especially important in dashboards. A report consumer does not usually care whether a product appears 1,000 times in a fact table. They care whether the total sales for that product are correct, whether the filters work, and whether the numbers change predictably when they slice by region or time. A well built model makes those questions boring in the best possible way.

Imagine a hospital system. Each patient visit is a fact. The patient itself is a dimension. If a patient visits ten times, their patient ID should appear ten times in the visit table, because there were ten visits. But the patient name, birth date, and insurance type should live in one place. Otherwise, a simple rename or insurance update would create inconsistencies across thousands of rows.

That structure does something subtle: it separates changeable reality from stable reference. The visit table changes constantly. The patient table changes less often. A good model respects that difference instead of flattening everything into one undifferentiated mass.

Good analytics design is not about making data identical. It is about making each kind of data behave according to its nature.

Why this matters for speed, accuracy, and trust

The practical benefits of this design follow naturally from the logic above.

First, data exploration is faster because a star schema reduces the number of places a question can go wrong. Analysts can drag a field from a dimension and pair it with a measure from a fact table without wrestling with messy joins or conflicting labels.

Second, aggregations are simpler because the fact table already contains the events at the right grain. You do not have to reconstruct the count of sales from a complicated web of repeated descriptive columns. The model is ready to sum.

Third, reports are more accurate because uniqueness lives where it should. Product names are not scattered across transaction rows. Customer attributes are not copied into every sale line. The chance of accidental divergence drops sharply.

Fourth, reports are easier to maintain because a change in a dimension table propagates cleanly. If a product category is renamed or a customer moves to a new region, the reporting layer can update without rewriting event history.

These are not separate advantages. They all come from the same architecture of repetition and uniqueness. A model that understands repetition as structural, not accidental, is easier to use precisely because it is easier to reason about.

Here is the deeper payoff: the model becomes trustworthy. And trust is the real currency of analytics. A dashboard that is technically impressive but feels uncertain will not be used. A report that is modest but consistently correct will become part of how decisions get made.

Key Takeaways

Do not treat all duplicates as errors. Ask whether repetition reflects real events. If it does, preserving it may be necessary.
Place repetition where action happens. Fact tables should contain repeated event rows, because each row often represents a distinct occurrence.
Keep descriptive uniqueness in dimensions. Dimension tables should store one row per entity so filtering and grouping remain reliable.
Use UNION ALL as a mindset, not just a command. Sometimes the right analytical move is to keep repeated rows because every occurrence matters.
Design for the question, not for abstract neatness. If the goal is counting, comparing, and aggregating, the model should preserve the grain of the original events.

The best models do not eliminate repetition, they domesticate it

There is a temptation in data work to believe that the cleanest model is the one with the least redundancy. But that is only half true. The best model is the one that knows where redundancy belongs and where it becomes distortion.

UNION ALL and the star schema seem, at first glance, to live in different corners of the data world. One is about combining rows without removing repeats. The other is about organizing tables so repetition is isolated in the fact layer. But both are really about the same principle: repetition is not a design failure when it faithfully represents reality.

That is a useful way to rethink data modeling more broadly. Not as a battle against duplication, but as a discipline of placement. Keep repeated rows where they represent repeated events. Keep unique rows where they represent stable identities. Then let the model do what it was meant to do: turn the messy abundance of reality into something you can analyze without losing the plot.

In that sense, the smartest data models are not the most compressed ones. They are the ones that preserve the right kind of repetition so that meaning stays intact. And once you see that, you stop asking how to remove duplicates. You start asking a better question: what is this repetition trying to tell me?