Why Duplicates Are Not the Enemy of Good Data Models

Aug 02, 2026

9 min read

1 views

74%

The Strange Power of Repetition

What if the fastest way to improve a data model is not to eliminate repetition, but to understand when repetition is doing useful work?

That question sounds almost wrong at first. In data work, repetition is usually treated like waste. Duplicate rows are often the first thing we delete, high cardinality columns are often the first thing we compress, and many modelers spend a surprising amount of time trying to make every table feel tidy, unique, and elegantly minimal. Yet some of the most important operations in analytics depend on preserving repeated values, and some of the most efficient models depend on knowing which kinds of uniqueness are actually expensive.

This creates a deeper tension: data systems need both duplication and reduction. If you only chase uniqueness, you can damage meaning. If you only preserve everything, you can destroy performance. The real craft lies in knowing which repetitions are signal and which are noise.

The goal of modeling is not to make data unique. The goal is to make meaning cheap to retrieve.

That shift in framing changes everything.

Uniqueness Is a Costly Luxury

Cardinality sounds like a technical term, but it is really a question about how much variation a column carries. A low cardinality column, like country or order status, repeats the same values many times. A high cardinality column, like transaction ID or timestamp down to the second, is close to unique for each row. That difference matters because systems have to work harder when every row looks different.

Think of a bookshelf. A shelf full of the same paperback edition is easy to sort, scan, and organize. A shelf full of individually labeled manuscripts, each with a distinct identifier, takes more time and memory to manage. The second shelf is not better because it is more unique. It is only better when you need that exact uniqueness.

This is why reducing cardinality is often a performance win. Less uniqueness can mean simpler relationships, smaller storage footprints, faster joins, and more efficient calculations. In a business model, the most useful fields are often not the most precise ones, but the ones that group reality into manageable patterns. A sales dashboard does not need every row to shout its individuality. It needs columns that help the model aggregate, compare, and answer questions quickly.

But there is a trap here. When people hear that lower cardinality improves performance, they sometimes overgeneralize. They start seeing uniqueness as inherently bad. That is where the model begins to break down, because uniqueness is not waste in every context. Uniqueness can be the very thing that allows a system to anchor meaning.

A product table needs one row per product because that row acts as a stable reference point. A customer dimension needs one row per customer because it gives the rest of the model something to point toward. In other words, uniqueness is not the enemy. Uncontrolled uniqueness is the enemy.

Why Repeated Rows Matter More Than They Seem

At first glance, keeping repeated rows sounds like a compromise, something you do only when the system forces you to. But repeated rows often represent the actual shape of the question you are trying to answer.

Suppose you have a table of purchases. A customer named Aisha buys three items on the same day, and each item appears as a separate row. If you combine those rows too aggressively, you may lose the very structure that makes revenue analysis possible. If you keep the repeated transaction information, you preserve the granularity needed to ask questions like: How many line items were purchased? Which categories tend to appear together? What does basket composition look like?

This is where repeated rows become more than a storage choice. They become a faithful representation of multiplicity. Reality is not always one thing per entity. A person can have many orders, a product can appear in many carts, and a region can have many transactions. Repetition captures that manyness.

The connection to set operations is subtle but important. A UNION ALL does not pretend the repeated row did not happen. It says, in effect, “Keep the full record of what occurred.” That restraint is powerful. In analytics, the urge to deduplicate everything can flatten history into an overcleaned abstraction. But business events are not always unique in the way a modeler wishes them to be. Sometimes the repetition is the data.

Deduplication is not a virtue by itself. It is a decision about what kind of reality your model is allowed to see.

Consider customer support tickets. If five users report the same bug, those are five separate experiences, not one fact that should be compressed into a single row because the description matches. The repetition is the evidence. In this sense, repeated rows are not a flaw in the dataset. They are a sign that the dataset is preserving event level truth.

The Real Problem: Confusing Identity With Structure

Most data modeling mistakes come from confusing identity with structure. Identity asks: what is this thing? Structure asks: how does this thing relate to other things?

Cardinality sits at the intersection of both. A high cardinality column often feels like an identity field because it distinguishes one record from another. A low cardinality column feels like structure because it groups many records under shared values. But these categories can be misleading if treated too rigidly.

Imagine a library catalog. ISBN is high cardinality, because each book edition is distinct. Genre is low cardinality, because many books share the same label. If you try to use genre as an identifier, the model collapses because too many books share it. If you try to use ISBN as the primary lens for every analysis, the model becomes too granular for most questions. The right model needs both: unique anchors and repeating groupings.

This is where many-to-one relationships become quietly elegant. The lookup table holds the unique identifiers, while the fact table contains repeated references to them. The repeated foreign keys are not a problem. They are the mechanism that lets many events point back to one stable concept. A model with no repetition in relationships would be suspicious, not efficient, because it would imply a world where nothing is shared.

Many-to-many relationships push this idea further. In the real world, some things refuse to fit into simple one-to-many patterns. A student takes many classes, and a class has many students. A product belongs to many categories, and a category contains many products. Many-to-many structures admit that reality is not always hierarchical. They acknowledge that meaning often emerges from overlap, not isolation.

The practical lesson is this: do not ask whether a column is unique or repeated in the abstract. Ask what role it plays in the story your model is telling.

A Mental Model: The Economy of Repetition

A useful way to think about cardinality and repeated rows is to imagine an economy.

In this economy, every unique value has a maintenance cost. The more distinct a column becomes, the more effort the system needs to track it, compare it, and store it efficiently. Meanwhile, repeated values create economies of scale. Once the model knows how to interpret a shared value, it can reuse that interpretation across many rows.

This is why low cardinality columns often perform well. They are like common currencies. Everyone uses them, so the model only has to learn them once.

But there is a second side to the economy. If you compress too much, you create inflation of meaning. Different things start looking the same. A table of sales by month is great for high level trend analysis, but it is useless for inventory forecasting by day. The model becomes cheap to query because it has thrown away the distinctions that matter.

The best models balance these two forces:

Shared categories for efficient aggregation.
Unique keys for reliable identity.
Repeated event rows for faithful history.
Carefully chosen relationships for scalable structure.

This balance is not accidental. It is the result of designing for the question, not for the aesthetic of tidiness.

A common mistake is to optimize for the wrong kind of elegance. Some people want every table to look clean in a spreadsheet sense, with no duplicates and no messy overlaps. But analytics is not artful minimalism. It is a disciplined compromise between reality and computation. The mess is often telling you that your model is close to the world.

When to Keep, When to Reduce, When to Relate

The practical challenge is knowing what to do with repetition in a real model. A useful rule is to classify every repeated pattern into one of three roles.

1. Keep it when repetition is evidence

If the repeated rows are events, keep them. Orders, clicks, page views, support tickets, sensor readings, and inventory movements often need row level fidelity. Removing duplicates here can erase the actual behavior you are trying to study.

2. Reduce it when repetition is merely descriptive noise

If a column repeats too many distinct values without helping analysis, it may belong elsewhere. Free text, timestamps with excessive precision, and overly granular labels can slow the model while adding little insight. Reduce cardinality by binning, grouping, standardizing, or moving details into separate structures.

For example, instead of storing every exact timestamp in a summary table, you might store date, week, or month. The precise timestamp still exists in the fact table if needed, but the reporting layer uses a lower cardinality view that is faster and easier to aggregate.

3. Relate it when repetition reveals belonging

If repetition reflects a real relationship, model that relationship rather than flattening it away. Many orders belong to one customer. Many students belong to one course. Many transactions belong to one store. The repetition in the foreign key is not redundancy. It is the bridge between layers of meaning.

This is the point where cardinality becomes a design language. A well structured model uses repetition to express belonging and uniqueness to express identity. It does not fight repetition everywhere. It channels it.

Clean data is not data without repetition. Clean data is data where repetition has been given a job.

Key Takeaways

Do not treat duplication as inherently bad. Sometimes repeated rows preserve the actual event history you need for analysis.
Reduce cardinality strategically. Lower cardinality can improve performance, but only if the simplification does not erase meaning.
Separate identity from structure. Unique keys identify entities, while repeated values organize them into relationships and aggregates.
Use many-to-one relationships to scale meaning. Let detailed events point to stable lookup tables instead of forcing everything into one flat structure.
Ask what question the model is meant to answer. The right level of repetition depends on whether you are studying events, categories, or relationships.

Conclusion: The Best Models Are Selectively Repetitive

The deepest insight hiding inside cardinality is not about optimization. It is about epistemology, how a system knows what is true.

A model that removes every repeated row becomes brittle because it mistakes compression for understanding. A model that preserves every unique detail becomes unwieldy because it mistakes completeness for clarity. The craft lies in selecting where repetition should survive and where it should be abstracted away.

That is why the best data models are selectively repetitive. They repeat what needs to be remembered, reduce what only needs to be grouped, and preserve uniqueness where identity matters. In other words, they are not trying to make the world less redundant. They are trying to make the world more legible.

And once you see that, duplicates stop looking like a nuisance. They start looking like one of the most important design signals in the entire system.