Both tables in a match carry a column named region, and the output carries two of them: what happened, and what should you have done first?
answer
- the clash is a column name, not a row
- same non-key name on both sides
- decorate, demand, or refuse — tool-dependent
- select the columns you want before matching
basics
~20 sA 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.
solid answer
~50 sA **collision** happens when a column that is not part of the key exists under the same name on both sides of a match. Designs handle it three ways: decorate both copies with a default marker and keep them, require you to supply the decoration explicitly, or refuse the match until you resolve it. Yours decorated and kept both, which is the quietest and therefore the most dangerous outcome — nothing failed, and the result now carries two nearly-identical columns that disagree on some rows. Whichever one a later step happens to name wins, and that choice was never made by anyone. The fix belongs before the match: decide which side is authoritative for that column, then select only the columns you need from each side, dropping or renaming the other. If both genuinely matter, rename them to say what they are rather than accepting a decoration.
go deeper
Know that if a column name that is not part of the key exists on both sides of a match, the result cannot keep both under that one name, so the tool either decorates them, asks you to, or refuses.
Explain all three behaviours rather than assuming one, and show that the silent decoration is the risky case because two near-identical columns survive and a later step chooses between them by name alone.
Resolve it before the call: state which side is authoritative, select only the key and the wanted columns from each side, and refuse to store a result carrying both copies of anything.
Set the convention for how tables meet across the codebase — whether column selection before a match is expected, and whether decorated column names are allowed to reach a stored output at all. Without one, every reviewer re-derives the same answer.
## What a collision is, and what it is not A match brings the columns of two tables into one. If a column name appears on both sides and it is **not** one of the key columns being compared, the output cannot carry two columns with the same name, so something has to give. That is a collision. It is worth separating from two things it is often confused with: - **A repeated key value** on one side, which multiplies output rows. That is about row arithmetic, not column names. - **The same record present twice** in one table. That is a repeat to be resolved, again about rows. A collision is specifically **the same column name arriving from both sides**, and its symptom is a wider table, not a longer one. ## Three behaviours, and which one is dangerous | Behaviour | What you see | Risk | |---|---|---| | Decorate automatically and keep both | two similarly-named columns, no message | highest: nothing signals that a choice was made for you | | Require an explicit decoration | an error until you supply one | low: you are forced to notice | | Refuse the match until resolved | an error naming the colliding column | lowest, and the most annoying | Do not assume the first is universal. Which of the three you get is a property of the tool, and a candidate who says "it appends a marker to each copy" as though that were the rule has described one design as if it were the class. ## Why the decorated pair is worse than a failure The two copies are usually *almost* the same. The reference table's value is the customer's home region; the transactions table's value is the region the order shipped to; they agree on most rows and disagree on the interesting ones. Four things then tend to happen: 1. A later step names one of them, often whichever kept a familiar-looking name, and the choice is made by a typo-level decision rather than by anyone's judgment. 2. A grouping on that column produces plausible numbers under either choice, so nothing looks wrong. 3. The redundant column travels all the way to a stored output, and a consumer picks the other one. 4. A future change to either source table alters the disagreement rate, and the report shifts with no code change. None of that raises. This is the same property that runs through every failure mode around combining tables: the wrong answer is plausible, and plausible results get shipped. ## What to do before the match The repair is always earlier than the symptom: - **Decide who is authoritative.** For each non-key column that exists on both sides, one side is the one you mean. Say which, out loud, before writing the call. - **Select down first.** Take from each side only the key columns and the columns you actually want. This removes most collisions before they can happen, and it makes the intent of the match readable in the code. - **Rename with meaning, not with a marker.** If you genuinely need both values, give them names that say what they are — the home region and the shipping region — rather than accepting a decoration that tells a reader only that a collision occurred. - **Never ship both copies.** A stored table carrying two near-identical columns is a trap for the next person, who has no way to know which one the numbers were built on. ## The key columns themselves There is a related case worth naming. When you name the key column on each side and the two names are the same, the output normally carries one such column, because the values are equal by definition on every partnered row. When the names differ — an account identifier on one side and a customer identifier on the other — several designs return **both** key columns, and under a shape that keeps a side whole one of them holds the absent-value marker on exactly the unpartnered rows. That second copy is often useful as a way of spotting which rows found no partner, but it is redundant data in a stored output and should be dropped once you have used it. ## How to say this in an interview Name the mechanism (a non-key column present on both sides), name the three behaviours rather than asserting one, say that the silent one is the dangerous one and why — two near-identical columns, a later step choosing between them by accident, nothing raising — and put the fix before the call rather than after it. Then close with the discipline that prevents the whole class: select the columns you want from each side before the match, so the output's column set is something you designed rather than something that happened.
- Why do the key columns usually not collide in the same way?Because on every partnered row their values are equal by definition, so one copy carries all the information and designs normally emit one column when the name is the same on both sides. When you name differently-spelled key columns on each side, several designs return both, and under a whole-keeping shape the second one is empty exactly on the unpartnered rows — useful for spotting them, redundant in a stored result.
- The two copies agree on 98% of rows. Is it safe to keep either one?No. A 2% disagreement is not noise, it is the part of the data where the two columns mean different things, and that is usually the part a report is about. Work out what each one actually is before choosing, and expect the disagreement rate to move as the sources change. Agreement being high is a reason to investigate, not a reason to relax.
saying these in an interview costs you the question
- Assumes every tool silently decorates both copies and keeps them
- Renames the copies after the match instead of selecting columns before it
- Keeps both near-identical columns in a stored output for safety
- Confuses a colliding column name with a repeated key value
- Picks whichever copy kept the tidier name, without checking what it means