One row per what? Why granularity should be decided before cleanup

A spreadsheet shows the same order number on three rows. The instinct is understandable: select the column, click “Remove Duplicates,” and make the file look cleaner.

That operation may also erase two valid sales lines.

The order number repeats because the table may be recording one row per invoice line, not one row per order. One order can include several products. If it ships in pieces, the same order-line number may appear again in a shipment table. Repetition is not proof of duplication. It is often a clue about what the table is actually recording.

Before cleanup begins, ask a question that sounds small but controls almost every later decision: What does one row stand for?

A row is not inherently a company, customer, order, or location. It is an assertion at a chosen level of detail. Until that level is stated, a duplicate rule is guesswork.

Write a row contract

A useful row contract is one sentence that describes the subject, relevant time, and any relationship captured in the table.

For example:

• One row per invoice line.
• One row per store per month.
• One row per company-location relationship.
• One row per business location per monthly source capture.

Compare the last example with “one row per company.” The first says that locations and observation dates matter. The second leaves both unanswered. Those omissions invite someone to collapse records that may be needed for local coverage, historical analysis, or audit work.

This is often called defining the table’s grain. In dimensional modeling, grain is the declaration of what a single fact-table row represents. It should be set before choosing the fields and measures used in the table, because those choices need to fit the same level of detail.

Repeated values mean different things in different tables

Four common table types make the point clear.

An entity table may aim for one row per company, store, or product. Here, a repeated stable entity identifier can be a warning sign. But even then, it may reflect a bad import, a merger history, or a versioned record rather than an exact duplicate.

An event table records things that happened: sales, calls, filings, payments, or shipments. Repeated customer IDs and order IDs are expected. A customer can place many orders; an order can contain many lines.

A snapshot table records an entity at a moment or period in time. A table with one row per store per month should repeat the store ID every month. Keeping only one row per store would not clean the data. It would destroy the time series.

A bridge table records relationships. In a company-to-location table, a company ID can repeat across locations, and a location ID might repeat if several companies share or manage it. The meaningful identity may be the pair: company_id + location_id. If the relationship changes over time, an effective date may also be part of the rule.

The same value can therefore be a defect in one table and required structure in another.

A key is a test, not a formatting choice

Once the row contract is written, state the fields that should jointly identify one row. This is the expected key.

For an invoice-line table, it might be invoice_number + line_number. For a monthly location snapshot, it might be location_id + observation_date. For a relationship table, it might be customer_id + account_id.

The word “jointly” matters. Many valid tables do not have a single column that is unique. Trying to force one identifier to be unique can remove meaningful records. Modeling guidance from Microsoft makes the same caution: duplicated keys can be duplicated for a good reason, and removing rows solely to create uniqueness can lose information.

If an expected key appears more than once, do not jump straight to deletion. There are at least three possibilities:

1. The row contract is incomplete or wrong. 2. An additional field belongs in the key, such as a shipment sequence or observation timestamp. 3. The source contains repeated or erroneous records.

Only the third case supports removing a record as a duplicate, and even then the decision should be reviewable.

The business unit matters

Consider a directory with several branches under the same brand. Deduplicating on the parent-company name would make sense only if the question is about corporate accounts. It would be harmful if the task is mapping local service coverage.

The distinction resembles the difference between an establishment and a firm in U.S. labor statistics. An establishment is generally a single location, while a firm may include one or more establishments. Neither is the universally correct unit. They answer different questions.

That distinction also explains why row counts need a label. “We found 500 records” says little. “We found 500 location-month observations” says what can be counted, compared, and aggregated without changing the meaning.

Clean with a way back

Cleanup should preserve both the original import and a traceable working version. When rows are removed, merged, or consolidated, record the reason and the count affected. Keep uncertain cases in an exception table rather than making them disappear because they look repetitive.

That restraint matters when a later question changes the unit of analysis. A team that needs one row per corporate account may build that view from a location table. It cannot reliably reconstruct discarded locations after they have been collapsed away.

Before using “Remove Duplicates,” pause for four questions:

1. What is the subject of this row? 2. What makes two rows meaningfully different? 3. Which columns should be unique together? 4. At what date or period is this row’s claim true?

The answers may lead to deletion, aggregation, a separate entity table, or a review queue. The point is not to keep every repeated value. It is to make sure the rows being removed were never carrying information you intended to keep.