Putting Two Tables Together

Equality compares stored values, not the rendering. Two codes that print alike can differ by letter case, leading or trailing spaces, an invisible character, a lost leading zero, or one side holding digits as text and the other as numbers.

Losing 60 rows means the match kept only rows partnered on both sides. The three alternatives keep the orders table whole, the customer list whole, or both whole, and unpartnered rows then survive with the other side's columns holding the absent-value marker.

Stacking adds records, so the result is longer and the hazard is two pieces whose column sets do not agree. Gluing side by side adds attributes, so the result is wider and the hazard is rows paired in an order nobody checked.

A key match returns, for each key value, the first side's occurrence count multiplied by the second side's. If a customer identifier appears more than once in the reference table, the orders carrying it are copied and the total grows.

Rows identical in every column carry nothing you would lose by collapsing them. Rows identical only on the identifying columns disagree somewhere else, so collapsing one discards a fact and forces a decision about which row is right.

Compare the key value sets, not the rows: how many distinct values each side has, how many are shared, and how many each side holds that the other does not. Then inspect a sample of the unshared ones.

Either the order's key found no partner and the match manufactured those cells, or it did find a partner whose own values were already absent. The cells look identical, so distinguish them with a provenance marker or by testing the key against the other table's key values.

The column is named differently on the two pieces, so a stack that reconciles by column name treated them as two columns and took the union of the two column sets, filling each gap with the absent-value marker. Nothing was wrong from the tool's point of view, so nothing was raised.

Sum, over every key value present on both sides, the product of its occurrence count on each side. In practice you first ask the cheap question: is either side unique on the key? If one is, the output cannot exceed the other side's rows.

On whatever order the rows happen to be sitting in when the step runs, which is a by-product of the previous operation rather than anything you stated. Put the rows in a deliberate order on a column that encodes precedence, or the survivor is not reproducible.

Six rows come back for that label. Every occurrence on one side is paired with every occurrence on the other, so the output row count for a label is the product of its two occurrence counts, not the larger of them.

Do not count on it. Some designs refuse a cross-representation key comparison at the call, others compare, find nothing equal and hand back an empty or badly thinned result, and some convert one side - which can change the code itself.

Use the keep-if-partnered filter: a match used purely as a membership test, where rows are kept or dropped by whether a partner exists and no columns are added. An ordinary widening match adds the other table's columns and can change the row count.

Dropping is destructive and unmeasured: it produces a clean table and no evidence. Counting tells you whether this is a handful of stray copies or a uniformly doubled delivery, and marking keeps every row so a later step, or a person, can still decide.

Discarding labels is right only when position genuinely is the correspondence and you can name the step that guarantees it. Otherwise it converts a visible failure — holes, or a result longer than either input — into wrong values paired row by row.

The two sides were paired by row label and their label sets barely overlap. Equal row counts prove nothing: an upstream filter or rebuild left each side carrying identifiers the other does not have, so almost nothing found a partner.

Because it ships. An empty result stops everything and is fixed the same day; a 94% match returns a plausible table whose loss is systematic rather than random, so the totals are consistently wrong, consistently believable and consistently signed off.

A non-key column with the same name on both sides collided, and the tool kept both copies under decorated names rather than choosing between them. Decide before the match which side is authoritative, and drop or rename the other there — never ship a table carrying both.

Because gluing side by side never compares a customer to a customer. It pairs rows by position, or by the row labels the tool carries, and both were fixed by whatever earlier step produced each side. Once one side was sorted or filtered independently, the pairing was wrong and nothing could say so.

It depends entirely on the reconciliation rule. Reconciled by name, column order is irrelevant and the result is correct. Reconciled by position, the second piece's values land under the wrong headings with no holes and no error. Establish which rule the operation follows before trusting it.

A sum that grew while a distinct count held steady means rows were copied, not added. Bracket the match: before it, assert the side that should be unique really is unique on the key; after it, compare the output row count and an unchanged total against what you predicted.

Nothing was identical on every column, so nothing was removed. The two rows for each customer differ on a column that has nothing to do with the customer — an ingestion timestamp, a batch identifier, a loader's sequence number — and comparing on all columns makes exactly those repeats invisible.

Declare it when a wrong number is more expensive than a stopped run, and when the pairing the step depends on is stable enough that a violation really is a defect. Where it is expected to change, measure and publish the number instead of failing.

There is no single right rule, only three defensible defaults with different costs: drop unpartnered rows, keep the driving table whole and mark them, or stop the run. Whichever you pick, the disposition should be decided at each seam and written down, not inferred later from a number.