Untangling Health Insurance Claims

What happens after you hand over your insurance card

You have probably done this. You see a doctor, hand your insurance card to the front desk, and a few weeks later a letter arrives explaining what your plan paid and what you still owe. From where you sit, that is one visit, one bill, and one payment. Done.

Now picture the same visit from inside the insurance company. The doctor's office sends in a claim describing the visit. A week later their billing team spots a wrong code and sends a corrected copy. Months after that, your employer reports that you had already left the job before the appointment, and the question of who should have paid is open again. None of this overwrites what came before. Each step is written down as a new record, because the old records explain why money moved when it did.

So one ordinary visit can leave a trail of records behind it. Most days nobody notices. Then someone asks a plain question, like how much we spent on MRIs last year or how many claims we turned down, and the honest answer depends on knowing which records describe the same visit and which of them is true today.

An email thread is a good way to picture it. The first message asks for something. The second corrects a detail, and the third adds one more item. To know what was finally agreed, you read the whole thread in order. The first message alone is out of date, and the third alone makes no sense without the two before it. Claims data works the same way, except the thread is scattered across several tables, and some of its messages arrive months late.

Gathering every record that belongs to the same visit into one chain, and then working out what that chain adds up to today, is called claims chaining. It sounds like bookkeeping. It turns out to be a graph problem with real engineering trade-offs, and getting it wrong quietly skews cost reports, claim counts, and the money a health plan is owed.

Three visits. Nine rows. Four are real.

Here is what that trail looks like in data. The table below is a small synthetic sample from the working project this guide is built on, modelled on CMC_CLCL_CLAIM, the main claim table in Facets. No real member data is used. Facets is a claims system that many health plans run on, and section 2.1 looks at it closely. For now, you only need to read the table.

Insurers call patients members, so M01 to M03 in the table's header are three different patients. Each row is one record the insurance company stored. Reading left to right, you get the claim's ID number, the date of the appointment, the date the insurer decided what to pay, and the amount it paid. The last column is not in the Facets table. It is the answer you only get after untangling the rows, and working it out is what the rest of this guide is about. Give the table a moment and it will show you which rows are real.

Three patients, one appointment each, nine rows in the claims table. Add the rows up and you overstate what you paid by 113%. Take the newest row from each and you lose half of a hospital stay. This page is about why that happens, how to state it precisely, and how to solve it in a way that stays solved.

How this guide is laid out

The guide moves in three phases, and each one builds on the one before.

1. The business problem. Who is involved in a claim, and the four everyday forces that turn one claim into many. No data background needed.
2. The data problem. How that mess becomes a precise problem an engineer can solve, using the real tables and a little graph vocabulary.
3. The solutions. Three ways to solve it, what each one costs as the data grows, and how to choose between them and prove the answer is right.

Short on time? Read Phase 1, then skip to the five takeaways at the end of section 3.7.

Why one visit turns into a pile of rows

Nobody designed it this way. It falls out of the fact that the people who deliver care and the people who pay for it are different companies, keeping different records, learning the truth at different times.

1.1 Who is in the room

How a claim is supposed to work

1. The member sees a doctor.
2. The provider sends us a claim, a standardised message describing what happened. 98% arrive electronically.^[1]
3. We confirm the member was covered on the date of service. This is an eligibility check.
4. We apply the benefit rules and decide what to pay. This is adjudication.
5. We pay the provider and send a remittance advice.
6. One claim, one row, one payment.

That path is real and it happens often. It is not what your data looks like, because four independent forces keep pushing claims off it, and each one adds rows.

1.2 The structural reason this is hard

The payer and the provider are separate companies with separate systems. They do not share a database. They communicate by sending each other standardised messages, and neither can force the other to correct anything. Add the employer group, which knows things we don't, and the member, who may hold a second policy we've never heard of. Four parties, four partial views, no shared source of truth.

The scale rules out doing that by hand. CAQH, an industry non-profit that benchmarks administrative transactions, put 2024 electronic medical claim submissions at about 9.6 billion.^[1] Read that word carefully: every correction and every blind resubmission is counted again. The number of distinct episodes of care behind them is smaller and nobody publishes it, which is this entire page in one sentence. Plans covering 217 million lives, about 63% of everyone insured in the US, reported 4 billion claims in a single year.^[2]

1.3 Four forces that turn one claim into many

They are orthogonal: each happens on its own, and any combination happens at once. Learn them as four separate shapes.

1.4 What it costs to get this wrong

• You overstate spending. Sum every row in a correction chain and you count the same care three times. That lands in medical cost trend, in pricing, in reserves.
• Your counts are wrong. "How many claims did we process" and "what is our denial rate" both depend on what counts as one claim.
• You miss money you're owed. A retroactive termination creates recoverable overpayment inside a bounded window.^[7] If the pipeline can't see the change, the window closes.
• Providers stop trusting you. A record that says one visit was paid three times becomes a phone call, then a dispute.

One more distinction before the trap below, because it changes how money adds up. A correction can be recorded in two ways. A restatement is a complete new copy of the claim with the right amounts, and it replaces the row before it. A delta records only the change, say minus 50 dollars, and is meant to be added to what came before. Both appear in claims data, sometimes inside the same chain.

Turning that mess into a problem you can solve

Phase 1 was stories. This phase converts them into a definition precise enough to hand to an engineer: the real table, the real shape, the real constraints.

2.1 Where it lives: the Facets claim table

Facets adjudicates claims for many health plans.^[11] Its base claim table is CMC_CLCL_CLAIM, and three of its columns do all the work.^[12]

The working project is designed against the Facets concepts in Cognizant's documentation, but it uses simplified names: claim_id rather than CLCL_ID, and a separate link table rather than the pointer pair on every row. Facets itself uses the names in the table above, though they can differ by release and installation, and other claims systems use their own. Map the ideas, not the spellings.

Here is how the pair would work on the first patient in the sample table, if those rows lived in Facets. When C1001 is corrected, the system creates C1002, writes C1002 into C1001's CLCL_ID_ADJ_TO, and writes C1001 into C1002's CLCL_ID_ADJ_FROM. The same happens again when C1002 becomes C1003. Each row knows only its immediate neighbours, so finding all three means starting from any one of them and following pointers.

The suffix is where people go wrong. The dominant in-Facets adjustment reuses the ten-character claim number and bumps the suffix, so …789000 adjusts to …789001. It is common, and it tempts everyone into chaining on the first ten characters. That catches the easy cases and silently misses the hard ones: a void-and-rebill under a new claim number, a claim reprocessed after coordination of benefits, a claim migrated from a legacy platform. Each has a different CLCL_ID and belongs in the same chain. Chain on the pointers. They are right in both cases; the prefix is right only in the easy one.

2.2 The other tables you need

The claim table alone cannot answer the business question, because Force B lives somewhere else. The minimum input set:

The grain column says what a single row stands for. Eligibility is kept as one row per restatement: each time an employer resends a member's coverage, a new row is added and the old one is left alone. That habit of adding rather than editing is what later lets you ask what we knew, and when.

The payment ledger never helps build the chain, which is exactly what makes it valuable: if the chain logic is right, the cash that actually moved must equal the reconciled amount. That is verification from a different direction, and Phase 3 leans on it.

Two things are deliberately out of scope. Facets also stores claim lines (CMC_CDML_CL_LINE), and a lot of real chaining happens there: an adjustment changes some service lines and not others, and splits are often line-driven. Everything here generalises to line grain; modelling it would double the work and bury the ideas. Coordination of benefits and BlueCard host/home claims add further identity layers by the same mechanism: more edge sources, same graph.

In plainer terms: a claim line is one service on a claim, so an office visit and the blood test done during it can arrive as two lines on one claim. BlueCard is how a member of one Blue Cross Blue Shield plan (the home plan) gets care in another Blue plan's area (the host plan), so the same claim sits in both plans' systems under different numbers.

One constraint you get for free bounds the lookback your pipeline needs. Timely filing limits cap how late a genuinely new claim can arrive, commonly 90 to 365 days from service. Corrections and retroactive eligibility changes are not bounded that way, which is why Phase 3 keys the incremental design on arrival time rather than service date.

Incremental means each run processes only what is new since the last run, instead of rebuilding every chain from scratch. Section 3.6 shows how defining "new" by the wrong date loses data without a single error.

2.3 What the graph looks like

Look at those six pictures together and the abstraction is obvious. This is not a list and not a tree. It is a graph, and "which rows describe the same claim" is the question "which nodes are connected."

Put the sample table from the start of this guide into those words. Its nine rows are nine nodes, and they form three connected components, one per patient: a linear chain, a void and rebill, and a split, three of the six shapes above. Finding those components is the first half of the job. Section 3.6 covers the second half: which claims in each chain are real, and what the chain is worth.

2.4 The problem, stated precisely

Here is everything so far as one statement. It borrows a little set notation: V is the set of claim rows, E is the set of pointers between them, and G = (V, E) is simply the graph they make together. An append-only log is one where rows are only ever added, never edited or deleted.

Naming a chain

A component needs a name that comes out the same every run. Label it with the minimum claim id it contains: deterministic, stateless, identical whichever node you start from. One property to design around: when a late edge merges two components, the smaller id wins and the other chain's name changes. If a downstream system must hold a durable key, it holds a surrogate from a mapping table that records merges, not this label.

An example makes the design point concrete. Say two chains exist on Monday, named X5001 and X5007. On Tuesday a late link arrives that connects them. Now they are one chain, named X5001, and any report that saved the name X5007 points at a chain that no longer exists. A surrogate key avoids that: a separate table gives each chain a stable number of its own, and records that X5007 was merged into X5001.

Two clocks

Both clocks let you compute two answers for one claim and compare them:

• Eligible at adjudication: using only eligibility rows recorded on or before the moment we adjudicated. What the adjudicator saw, and why the money went out.
• Eligible as known now: using every row recorded up to today.

True then, false now: that is your recoupment exposure, exactly. The project works out both answers for every claim in one eligibility model.

Run the hospital stay from Force B through both. On 1 March, when the claim was adjudicated, the log held no termination, so eligible at adjudication is true. Today the log includes the termination recorded on 15 June and backdated to 31 January, so eligible as known now is false for a 10 February service. That claim is exactly the exposure the plan may try to recoup.

2.5 Size, shape, and how often you redo it

The algorithm you choose depends entirely on these numbers, so they belong in the problem statement.

Non-negotiable

1. Terminates. Cycles exist in production data.
2. Deterministic. Same input, same chain_id, every run.
3. Idempotent. Re-running changes nothing and double-counts nothing.
4. Catches retroactive arrivals. Old business dates with new arrival timestamps must trigger recomputation. This is where most implementations quietly fail.
5. Refuses to guess. When links contradict themselves, publish nothing.

That is the whole problem: an enormous but sparse graph, a precise list of outputs, and five rules any solution has to follow. Phase 3 puts three standard algorithms against it.

Three ways to solve it, and how to choose

Every number in this phase was measured. The benchmark setup is in [15], and the working dbt project is described in 3.7 and linked in [17].

All three methods do the same job: take the claims and their pointers, and give every claim the name of its connected component. They differ in how much work and memory that takes as the data grows. Two tools appear throughout. dbt builds warehouse tables from models, which are mostly SQL select statements, and keeps tests alongside them. One of the three walks in this project, union-find, is a Python model. DuckDB is a database that runs inside a single program on one machine. The timings below were measured with it, so read them for how cost grows rather than for exact seconds.

3.1 Notation

A handful of symbols make the three methods easy to compare. Here they are, with the values they take on real claims data.

In a warehouse the unit that matters is often the number of passes over the data and the peak size of intermediate results. Beautiful asymptotics lose to a clumsier method that never materialises a huge intermediate table. You will see that happen below.

Put plainly: the best O(…) growth does not always win. Union-find has the best growth of the three, yet at a million shallow claims in 3.5 it ran slowest. Materialising means building and storing a table of rows on the way to the answer. The recursive query in 3.2 builds the biggest such table, and on dense data that is what sinks it.

One graph, three films

Each method below is shown as a filmstrip over the same seven claims. Time runs left to right, one frame per step. Every frame has the same three layers, so the methods can be read against each other:

• The graph. Whatever lights up green is where the method is working at that moment. Bold edges are edges it is reading.
• The state strip. Seven cells, one per claim, holding whatever the method actually stores. Amber cells changed in that step.
• The memory gauge. Live rows held at that moment, on one scale shared by all three figures.

3.2 Method 1: Recursive query

The database-native answer. A recursive common table expression takes a starting set and a rule for one more step, and applies the rule until no new rows come back. Start from every claim, walk outward over the symmetrised pointers, label each claim with the smallest id it can reach.

The query reads two tables: claims, with one row per claim, and edges, with two rows per pointer. Symmetrised means every pointer is stored twice, once in each direction, which is how the walk can travel from an old claim to its replacement and back again.

with recursive walk (root, node, depth, visited_path) as (
    select claim_id, claim_id, 0, '|' || claim_id || '|' from claims
  union all
    select w.root, e.dst, w.depth + 1, w.visited_path || e.dst || '|'
    from walk w join edges e on e.src = w.node
    where w.depth < 25
      and position('|' || e.dst || '|' in w.visited_path) = 0   -- cycle guard
)
select root as claim_id, min(node) as chain_id from walk group by root

Simplified from the project's recursive model: it uses longer names (stg_claims, int_claim_edges) and reads its depth cap from a project variable.

The highlighted line is the part people forget. Without it, only the depth cap stops the walk. Because every pointer is stored both ways, even a plain eight-claim correction chain produces about 108 million rows before it does, and the three-claim cycle from Phase 2 about 200 million. The breadcrumb path records every node visited so far, and the delimiters stop C100 matching inside C1001. The project's full version is the recursive model.

Two more lines are worth reading closely. w.depth < 25 is a second safety net, a hard cap on how far any single walk can go. The final select turns the pile of walks into the answer: for each starting claim, the smallest id it reached becomes its chain_id.

Watch it run

Read the strip and the gauge together. The anchor plants one row per claim. Each depth joins the previous depth's rows to the edge table and keeps every result, so the frontier of active claims narrows (six, then four, then two, then none) while the total keeps climbing. By depth 3 the four-claim chain alone has produced sixteen rows: every simple path from every starting point. The singleton G never appears in a join and never produces another row, which is exactly why the anchor must start from the claims table rather than from the edges.

Two things to notice. The method never computes an answer as it goes; the strip holds only counts of evidence, and the labels appear in one aggregation at the very end. And the memory line is monotone. On a chain that is fine. On a dense cluster, the number of simple paths is what explodes, and this figure is showing you the mechanism in miniature.

What it costs

This decides whether you should use it. The recursive query does not walk each component once. It enumerates every simple path in every component, from every starting node. For a fully cross-linked component of k nodes:

In words: a clique is a group in which every claim links directly to every other. Starting from any one claim, the walk can step to any of the other k−1, then to any of the k−2 not yet visited, and so on, keeping every partial path as a row. P(k−1, L) counts the paths that take L steps. Adding those up for every length, then multiplying by the k starting claims, gives the total. For k = 4 that is 4 × (1 + 3 + 6 + 6) = 64, the first row of the measured table below.

Where would a cross-linked cluster come from? Facets adjustment pointers are one-to-one, so raw Facets gives you paths, never cliques. Density arrives the moment you add anything else, and you will: fuzzy duplicate matching to fix Force C links suspects pairwise and builds a clique by construction; a many-to-many migration crosswalk does the same; a join with a missing predicate turns a link table into a near-Cartesian product. This is what happens on the day you ship duplicate detection.

Measured against the formula:

Complexity. Time and space are both O(simple paths): O(n·k!) worst case, a comfortable O(n·k) when components are paths, which they usually are. Merits. One query, portable, termination is the engine's problem. For sparse, shallow, path-shaped claim data it is the right default. Risk. Cost is governed by the worst component in the data, and you cannot see that component coming from a row count.

3.3 Method 2: Iterative fixpoint

The literal version of "keep going until nothing changes." Every claim starts labelled with its own id. Each pass, every claim adopts the smallest label among itself and its neighbours. Count the labels that changed. Zero means every component now carries its minimum id.

-- one pass; repeat until changed = 0
create or replace table next as
select w.node, least(w.chain_id, coalesce(min(nb.chain_id), w.chain_id)) as chain_id
from work w
left join edges e  on e.src  = w.node
left join work  nb on nb.node = e.dst
group by w.node, w.chain_id;

select count(*) from next a join work b on a.node = b.node
where a.chain_id <> b.chain_id;

Simplified from the project's fixpoint materialization, which wraps this pass in a loop and names the same tables more fully.

The highlighted expression is the whole rule: keep your own label unless a neighbour's is smaller, and coalesce covers claims with no neighbours, which keep their own. The code is one pass. Before the first pass, work holds every claim labelled with its own id. After each pass, the second query counts how many labels differ between next and work, and only then does next take the place of work. Something has to repeat the pass until that count reaches zero. In this project that is a custom dbt materialization, which stops with an error if 25 passes are not enough.

Watch it run

The strip is now the answer under construction. In pass 1 each claim looks at its neighbours' labels and takes the smallest: B sees A and adopts it, C sees B, D sees C. The label A is three hops from D, so it takes three passes to arrive, and a fourth pass to confirm nothing moved. Count the amber cells: 4, 2, 1, 0. That sequence is the diameter of the longest chain, made visible.

Now look at the graph layer. Every edge is bold in every pass. The method does not know which labels are still travelling, so it re-reads everything, and that is the price of its flat memory line: two tables of seven rows, overwritten each round, never a third.

A pass touches every node and edge once: O(n + m). A label moves one hop per pass, so the passes needed equal the diameter D, plus one to observe that nothing moved. Time O(D·(n + m)), space O(n + m), and space does not grow with depth because each pass overwrites the working table instead of accumulating paths. Both predictions are testable. Fix the data at 60,000 claims and vary only the chain length:

Merits. Cost you can predict before running. Immune to the density blow-up, since it never enumerates paths. Flat memory. It reports its own progress. Costs. Slower on shallow data, because each pass carries query overhead. Needs something to run the loop. In the project that is a custom dbt materialization, and it follows the advice below.

3.4 Method 3: Union-find

The classical answer. Union-find keeps a pointer from each item to a representative of its group. Find follows pointers up to the representative; union merges two groups by pointing one representative at the other. Process every edge once and the groups are the connected components.

If the terms are new, picture every group as a team with a captain, and every claim as reporting to someone. find(x) follows who reports to whom until it reaches someone who reports to nobody: that is the captain. A union has one captain start reporting to the other, and at once both teams share a captain.

def find(x):
    root = x
    while parent[root] != root: root = parent[root]
    while parent[x] != root:                 # path compression
        parent[x], x = root, parent[x]
    return root

for src, dst in edges:
    a, b = find(src), find(dst)
    if a != b:
        if size[a] < size[b]: a, b = b, a       # union by size
        parent[b] = a; size[a] += size[b]
        label[a] = min(label[a], label[b])       # chain_id = min claim id, as in SQL

Path compression re-points every node you climbed through straight at the representative. Union by size keeps trees shallow. Together they give O((n + m)·α(n)) total, effectively linear, proved by Tarjan in 1975.^[13] The label is tracked separately from the representative, so it can be the minimum claim id without disturbing the balancing. The project runs the same routine as a dbt Python model.

The snippet leaves out the first and last steps. Before the loop, every claim is its own team of one: parent[x] = x, size[x] = 1 and label[x] = x. After the loop, a claim's chain_id is label[find(x)], the label kept at its captain.

Watch it run

This film has a different rhythm. The graph layer lights up one link at a time, never the whole graph. Each step asks two questions, who is A's root, who is B's root, and if the answers differ, hangs the smaller tree under the larger. The arcs are the data structure: D is joined by walking up to C's root, finds A immediately because the previous union hung C directly under A, and gets attached in one move.

The strip converges to the same A A A A E E G as the fixpoint, but by a different route. The fixpoint moved labels one hop per pass across the whole graph; union-find moved pointers one link per step and never revisited a link. Depth of the chain did not appear anywhere in the work, and neither did density: a fifth link inside an already-joined group would return the same root twice and cost nothing.

The decisive property: cost does not depend on depth or density. An edge is an edge.

Merits. Immune to depth and density both. Cycles need no guard: an edge inside a group is a no-op. Costs. The logic leaves SQL. In dbt it runs as a Python model: locally on dbt-duckdb with pandas installed, which is how the project runs it, and in production on Snowflake, Databricks or BigQuery.^[14] Interpreted Python pays a constant-factor tax on easy data: at a million shallow claims it was the slowest of the three. And it needs every claim and link in memory: hundreds of gigabytes at a billion claims in one process.

3.5 Head to head, and how to choose

On seven claims the differences look small. The shapes are what matter, because each one is a rule about how cost grows. The recursive line rises with the number of simple paths; the fixpoint's flat line is bought by re-reading the whole graph every pass, so its length grows with diameter; union-find's flat line comes with a step count equal to the number of links, and nothing else. The measurements below are those three rules at a million claims.

Peak memory below includes about 52 MB of fixed process overhead, measured on an empty connection, and union-find carries another 55 MB or so for pandas, so compare figures to each other and to those floors. These are single-node DuckDB timings: the shapes transfer to a distributed warehouse, the seconds do not.

Notice how close they are. On sparse, shallow data all three finish a million claims in a couple of seconds, within a factor of three of each other, and which one leads changes with the setup: here the recursive query led and union-find trailed. That is the trap. A benchmark on realistic average data says the choice doesn't matter. It matters only on the worst component in the dataset, which is why 3.2 exists.

3.6 The walk is half the job

Connecting the rows tells you which claims belong together. It does not tell you which one is real, what the chain is worth, or whether the coverage answer has changed.

Which claim is authoritative

A claim is current if nothing supersedes it, it is not a void record, and it is in a final status. Not every pointer supersedes: an adjustment that layers a delta onto a claim leaves the original standing, so classify link types first. The project keeps that list, and the statuses that count as final, in one small macro file. A chain can have two authoritative claims, from a split, or zero, from a cycle. Zero is the correct answer there.

Two phrases there need unpacking. A final status means the insurer has finished processing the claim. A claim that is pended, parked while someone waits on medical records or a coding review, is still moving and is not an answer yet. Classifying a link means recording what kind of change it made: a replacement, a void, a split, a merge, a reprocess, a crosswalk from a migrated claim's old id, or a delta layered on top. Every kind except the delta supersedes the claim it points at.

Adding up the money

Both obvious rules are wrong, on different scenarios, which is why neither will do:

Read it row by row. In the split, the original 1,200 was replaced by claims of 700 and 500: summing counts the 1,200 on top of its replacements, and the newest row keeps only the 500. Both halves carry the same adjudication time, 2025-02-01 09:00, so the project breaks the tie by claim id and picks C3003. The deltas behave the other way round. Summing gets 1,150 right because deltas are meant to be added, while the newest row alone is just the minus 50 correction. So summing is right only when the rows that no longer count carry no money, and the newest row is right only when every other row that still counts carries none. Real chains mix both, which is why links are classified before anything is added up.

The correct rule: non-superseded restatements, plus deltas still in force. For a chain with a cycle, publish nothing. The chain totals model computes both naive rules next to the correct one, and a test checks all three against hand-worked answers.

Catching retroactive arrivals

Warehouses rebuild incrementally, processing only what is new. The obvious definition of new is by business date: where adjudication_ts > last_run_max. On this problem that is wrong, because retroactive data has old business dates and new arrival timestamps.

Here last_run_max is a high-water mark: the latest adjudication date the previous run saw. Anything dated after it counts as new, and anything dated before it is assumed to have been handled already. A row that arrives today carrying a date from last year fails that test and is skipped.

With the arrival-time filter, an incremental run over those two arrivals rebuilt exactly 2 of 14 chains and left the other twelve untouched. To watch it happen, run the demo script: it builds the project, injects both arrivals, and rebuilds without a full refresh. The filter itself is the last block of the chain totals model.

3.7 How we know it is right

1. Three implementations must agree. A test fails if any two differ on any claim. They agreed on all 37, cycles and splits included.
2. Hand-derived expected answers. Worked out from the scenarios, not from the code, so the comparison is a real check.
3. Cash reconciliation. The payment ledger never participates in building the chain, so sum(payments) = reconciled amount is verification from a different table. It held for all 13 non-cyclic chains, before and after the retroactive batch.
4. A structural invariant. Both ends of every surviving edge land in the same chain, which is what proves the walk ran to completion.

Each check is a file you can open: the agreement test, the expected answers, the cash check, and the invariant.

Taking it to Snowflake

The project writes the portable form directly wherever one exists, and routes the one construct that has no portable form through a macro. The ones that differ:

A macro is a small reusable template in dbt. Where one spelling works on both DuckDB and Snowflake, the project writes it directly. String aggregation does not have one, so it has a version per warehouse, and date difference uses dbt's own datediff macro, which also has one per warehouse. The SQL models call the macros instead of either spelling. The Python model needs its own Snowflake edits, starting with .df() becoming .to_pandas().

Aggregates whose output you compare for equality always carry an explicit order.

Without an order, a string aggregate may list the same values in a different order on another run or another warehouse. A test comparing two results would then fail on answers that are actually the same.

If you remember five things

1. A claim row is a belief at a point in time. Four parties can revise it.
2. A chain is a connected component, not a linked list. Splits and merges branch and rejoin.
3. Coverage has two clocks. Compare what we believed then against what we know now, at the service date.
4. Incremental filters go on arrival time, never business date, or retroactivity is invisible.
5. Pick the algorithm for your worst component, not your average one. Ten cross-linked claims cost 15 seconds and nearly 3 GB in the wrong one.