Composite keys in business event data

Data Modeling

Multiple columns together identify one event when the combination is unique, minimal, stable, and matches the event grain you intend to model. The mistake is to ask whether a set of columns “looks unique” in today’s data. The better question is whether the business process and source semantics make duplicate events impossible for that combination, including the edge cases where local counters reset, identifiers repeat in different scopes, and late corrections arrive.

What a composite key means for event data

A composite key is a set of two or more columns that identify one row together. In business event data, that row should represent one occurrence at a specific grain: one payment authorization, one item scan, one ticket status change, one subscription invoice event, or another clearly named business event.

The important part is not that the key has multiple columns. The important part is that the columns express the identity boundary of the event. A receipt number might identify an event only inside a store, register, and business date. A sequence number might identify an event only inside a transaction. A source event ID might be globally unique, or it might be unique only inside a tenant.

Composite keys are common in event data because operational systems often use local identifiers. That is not a flaw. A local identifier is useful when the model carries the columns that define its scope.

Start by declaring the event grain

You cannot evaluate a key until you know what one row is supposed to mean. The same source table can often support several valid grains. For example, a point-of-sale stream might contain transaction-level events, item-level events, tender-level events, and device heartbeat events. Each grain needs a different identity rule.

Write the grain as a sentence before choosing a key:

• One row per checkout lifecycle event, such as sale started, item scanned, payment captured, or receipt printed.
• One row per item scan event, where scanning the same item twice is two events.
• One row per payment attempt, where split tender payments create multiple payment events for one receipt.
• One row per latest receipt state, which is not event data at all but a current-state summary.

Many bad composite keys come from mixing these grains. If the table contains event rows but the key only identifies a receipt, the key is too coarse. If the table contains one receipt summary but the key includes item sequence or payment sequence, the key is too detailed.

A small hypothetical dataset with counterexamples

Consider a hypothetical point-of-sale event feed. The source emits one row for each event that occurs during checkout. The receipt number is printed for the customer, but it is assigned locally by each register and can reset by business date. The register also emits an event sequence within each receipt.

The goal is to model one row per checkout event. A candidate composite key might be store_id, register_id, business_date, receipt_no, event_sequence. The rows below show why narrower keys fail.

row	store_id	register_id	business_date	receipt_no	event_sequence	event_type	amount
1	S1	R1	2026-09-01	1042	1	sale_started
2	S1	R1	2026-09-01	1042	2	item_scanned	12.00
3	S1	R1	2026-09-01	1042	3	payment_captured	12.00
4	S1	R2	2026-09-01	1042	1	sale_started
5	S1	R1	2026-09-02	1042	1	sale_started
6	S2	R1	2026-09-01	1042	1	sale_started
7	S1	R1	2026-09-01	1042	3	payment_captured_replay	12.00

Test candidate keys against counterexamples

Do not start by searching for any combination that happens to be unique. Start with the business rule you believe identifies the event, then try to break it with plausible counterexamples.

For the hypothetical point-of-sale feed, these are the key tests:

• Receipt number alone fails because receipt numbers can repeat across registers, stores, and dates.
• Business date plus receipt number fails because two registers in the same store can print the same receipt number on the same date.
• Store, register, business date, and receipt number fail for event grain because one receipt contains multiple events.
• Adding event_sequence reaches event grain if the source guarantees that the sequence is unique within a receipt on a register for a business date.

The final phrase matters: if the source guarantees. Observed uniqueness is useful evidence, but it is weaker than a documented or validated process rule. A week of clean data does not prove that the key is valid under month-end resets, offline device replay, migrations, or special transaction types.

candidate key	result	why it passes or fails
receipt_no	Fails	The same receipt number appears in different stores, registers, and dates.
business_date + receipt_no	Fails	The same receipt number can appear on more than one register on the same date.
store_id + register_id + business_date + receipt_no	Fails for event grain	It identifies the receipt scope, but one receipt contains multiple checkout events.
store_id + register_id + business_date + receipt_no + event_sequence	Passes if source sequencing is guaranteed	It identifies one event inside the locally scoped receipt, except that duplicate delivery should be treated as replay.
store_id + register_id + business_date + receipt_no + event_sequence + loaded_at	Too wide	It may make every replay unique even when the business event is the same.

Check minimality so the key is not too wide

A valid composite key should be minimal. If a column can be removed and the remaining columns still identify the same event, that column is not part of the candidate key. Extra columns make the key harder to reason about and can hide data problems.

Common too-wide event keys include descriptive columns such as amount, status, event_type, loaded_at, file_name, or updated_at. These columns may be useful for auditing, deduplication, or lineage, but that does not make them part of the event identity.

For example, suppose a payment capture event is resent with a corrected amount. If amount is included in the key, the corrected resend may appear as a second event instead of a correction to the same event. If loaded_at is included, every replay can become unique. That is not identity; it is accidental uniqueness.

Separate event identity from changeable attributes

Event identity should be stable under ordinary corrections. Attributes can change; identity should not. If the source corrects an amount, enriches a timestamp, adds a missing customer ID, or changes an event label, the model should still know whether this is the same event or a new event.

This is where practitioners often confuse three different concepts:

• Event key: the columns that identify the business event.
• Deduplication rule: the logic used to collapse repeated delivery of the same source event.
• Change handling rule: the logic used when an already-known event arrives with changed attributes.

Those rules may use overlapping columns, but they are not the same rule. A composite key should answer, “Which business event is this?” It should not be forced to answer every question about replay, correction, or audit history.

A decision framework for composite event keys

Use this sequence when deciding whether multiple columns together identify one event.

1. Name the grain. State exactly what one row represents.
2. List the source identifiers. Include event IDs, transaction IDs, receipt numbers, sequence numbers, device IDs, tenant IDs, dates, and source-system boundaries.
3. Find the scope of each identifier. Ask where each identifier resets or repeats.
4. Build the smallest plausible combination. Add scope columns only when they are needed to make the identifier meaningful.
5. Search for counterexamples. Test repeated receipt numbers, multiple events per transaction, split payments, retries, offline devices, tenant boundaries, and historical migrations.
6. Remove non-identity columns. Drop descriptive or operational columns that only make the row accidentally unique.
7. Document the rule. Record the grain, the key columns, known assumptions, and what should happen when a duplicate key appears.

The right composite key is the smallest set of columns that still survives the counterexamples implied by the business process.

question	good sign	risk sign
What is one row?	The grain is written as one business occurrence.	The table mixes events, summaries, and current state.
Where do identifiers repeat?	Reset boundaries are known and included when needed.	Local IDs are treated as global IDs.
Can any key column be removed?	Each column has a clear identity role.	Descriptive or operational columns are included only to force uniqueness.
What happens on replay?	Duplicate delivery maps to the same event identity.	Every retry becomes a new event.
What happens on correction?	Changed attributes update or version the same event according to policy.	Changed attributes create a new event because they are part of the key.

When a composite key is not enough

Sometimes no reliable composite key exists in the source data. That can happen when the source emits events without a sequence number, when devices generate ambiguous local identifiers, when historical backfills combine incompatible formats, or when timestamps are used as identifiers even though multiple events can occur at the same time.

In those cases, do not pretend a weak combination is a true event key. You may still create a warehouse surrogate identifier for storage, joins, or downstream convenience, but the model should be honest about the unresolved identity rule. The surrogate ID names a row in your warehouse; it does not automatically prove that the row corresponds to one distinct business event.

A practical model can carry both: a generated row identifier for internal use and a documented natural composite key when the source semantics support one. If the natural key is uncertain, label it as a deduplication heuristic rather than a candidate key.

Implementation checks that keep the model honest

Once you choose a composite event key, enforce or test the assumption where appropriate. The exact mechanism depends on the database, warehouse, and modeling tool, but the principle is durable: key assumptions should be observable.

• Uniqueness test: no two accepted rows should share the same composite key at the declared grain.
• Null test: key columns should not be missing unless the model explicitly handles unknown identity.
• Duplicate diagnosis: when a duplicate appears, classify it as replay, correction, true duplicate source emission, or evidence that the key is incomplete.
• Scope test: check suspected reset boundaries such as business date, store, tenant, source system, device, or register.
• Drift review: revisit the key when the source adds new event types, changes sequencing logic, or migrates systems.

The goal is not to make the key look clean at all costs. The goal is to learn when the model’s identity assumption no longer matches the business process.

Common failure modes

Composite keys fail in predictable ways. Watch for these patterns when reviewing event models.

• Using a transaction key for event grain. One transaction can contain many events, so the key collapses distinct events together.
• Ignoring local scope. A number that is unique inside a device, store, tenant, or date is treated as globally unique.
• Including timestamps as identity without proof. Timestamps often describe when something happened; they do not always distinguish simultaneous or repeated events.
• Making retries unique. Load time, file name, ingestion batch, or retry attempt is added to the key, turning duplicate delivery into new business events.
• Confusing corrections with new events. Changeable attributes are included in the key, so updates create false new events.
• Trusting current uniqueness only. A key passes on recent data but fails when older backfills, edge cases, or new source behavior arrive.

Key takeaways

• Composite keys in business event data should be evaluated against a declared event grain, not against vague row uniqueness.
• Many operational identifiers are locally unique, so the key must include the columns that define scope, such as tenant, source system, store, device, register, or business date.
• A candidate composite key should be minimal; columns such as load time, file name, amount, or status often create accidental uniqueness rather than true identity.
• Retries, corrections, and replays should not automatically become new business events just because additional operational columns differ.
• If the source does not provide reliable event identity, use a surrogate row identifier honestly and document the remaining deduplication assumption.

Next step

Pick one event table you already use and write its grain in one sentence. Then list the current key columns, remove any descriptive columns, and try to break the remaining key with three counterexamples: identifier reset, multiple events in one transaction, and duplicate delivery.