Revenue was up 18% after the tracking migration.

There was a slide for it. Someone used the phrase "excellent early signal." Acquisition efficiency looked better too, because conversion appeared to be climbing in parallel.

Then a deeper audit showed that purchase events were firing twice for logged-in users and once again after page refresh. The business had not grown. The instrumentation had multiplied.

That is uniqueness.

Uniqueness asks whether one real-world thing is represented once when it should be represented once. One customer. One transaction. One order. One event occurrence. The exact grain depends on the question, but the principle is stable: if duplication enters before you define the counting unit, your metrics become fiction with excellent formatting.

LLMs can help you spot duplicate patterns and suggest deduplication logic. What they cannot do automatically is decide what the entity is supposed to be. That is why the conceptual work matters. The hard part is not removing repeated rows. The hard part is knowing whether they are actually repeats of the same thing.

Duplicate rows are not always duplicate entities

People talk about duplicates as if the problem is obvious. It often is not.

Two identical event rows may indeed be a duplicate fire. Two rows for the same customer may be legitimate because the table is at the address level. Two transaction records may reflect retries, amendments, or partial captures. A row can be repeated without the underlying entity being duplicated, and an entity can be duplicated without the rows looking identical.

That is why SELECT DISTINCT * is usually a confession that the grain was never understood properly.

One sharp opinion here: if DISTINCT is your first response to duplication, you are treating a modeling problem like a formatting problem.

Event duplicates versus customer duplicates versus transaction duplicates

These failures behave differently.

Event duplicates inflate activity. Customer duplicates inflate audience size and fragment lifecycle analysis. Transaction duplicates manufacture revenue. Product catalog duplicates make assortment reporting incoherent. Each one distorts a different business belief.

In the e-commerce example, duplicate purchase events do more than overstate sales. They distort conversion rate, attribution efficiency, and retention signals. Teams then start celebrating campaigns that did not work or fixing funnel stages that were never broken.

The damage is multiplicative because duplicated facts often feed multiple dashboards at once.

Choosing the grain before counting anything

Before you count, define what one row is supposed to mean.

That sounds basic. It is not practiced basic enough.

Ask:

  • Is this table one row per event, session, user, order, or line item?
  • If the same order can emit multiple states, which state counts as the business event?
  • If the same user appears with multiple identifiers, what constitutes one person for this analysis?

Without that definition, every aggregation is unstable. You are not "measuring conversion." You are measuring whichever duplicated structure happened to survive the join.

How analysts usually miss it

They trust a familiar identifier without asking how it was produced.

An event_id may not be globally unique. An order_id may repeat across retries. An email may not represent one customer. A cookie may split one person across devices. A CRM contact may merge several real people under one company record.

Analysts also miss uniqueness failures because duplicates often produce good news. Spikes in growth get less skepticism than sudden drops. Inflated performance passes through meetings faster because nobody enjoys being the person who asks whether the celebration is technically impossible.

That is exactly why duplicate-driven metrics are so dangerous. They flatter the business before they mislead it.

How to identify the real unique key

The right key depends on the unit of meaning, not on whichever column happens to exist.

Sometimes a natural key is enough, like one immutable payment processor transaction ID per settled payment. Often you need a candidate key assembled from several fields, such as user, event type, timestamp bucket, and product SKU. In messy systems, the "real" uniqueness rule may live in business logic rather than schema.

That is not elegant, but it is normal. Analytics usually sits downstream of operational compromises.

A practical duplicate audit

Start with candidate keys rather than assumptions. Audit how often each supposedly unique combination repeats.

SELECT
  user_id,
  event_name,
  event_timestamp,
  COUNT(*) AS duplicate_count
FROM analytics.events
WHERE event_name = 'purchase'
  AND event_date >= CURRENT_DATE - INTERVAL '7 day'
GROUP BY 1, 2, 3
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC, event_timestamp DESC;

Then pressure-test the result:

  • Are repeats legitimate retries or obvious double-fires?
  • Do duplicates cluster around a release?
  • Are they isolated to one platform or logged-in state?
  • Does a refresh, webhook retry, or ETL replay explain the pattern?

The goal is not merely to delete extra rows. The goal is to understand what mechanism created multiple representations of one business event.

What uniqueness breaks in business terms

Uniqueness failures manufacture confidence. They tell teams they have more customers, more orders, more engagement, and better marketing efficiency than reality supports.

That changes behavior. Budgets get defended. migrations get declared successful. Product changes get credited for phantom gains. Later, when finance or operations fail to observe the same growth, trust between teams erodes.

Once you have one row per thing, another problem appears. The same thing may still mean different things in different systems. That is consistency.