A monthly job widens the same table, and its output columns differ from last month's. Why does unchanged code produce different columns?

## Schema from code, schema from data Almost every operation on a table has an output schema you can predict by reading the program. Select three columns and you get three columns. Derive a new measure and you get one more. **A widening** — turning one column's distinct values into new column headers, filled from a second column — is the exception, and it is the only common operation where this is true. Its output columns are the distinct values of **the header source**, and those are data. | what decides it | an ordinary operation | a widening | |---|---|---| | which columns exist | the code | the data's distinct values | | how many columns exist | the code | how many distinct values there were | | the order of the columns | the code | derived by the tool from the data | | stability between runs | stable | moves whenever the categories move | ## Three things move, not one - **Presence.** A category with no rows in this run produces no header. It does not come back as an empty column — it is simply not in the result, and anything referring to it by name now refers to nothing. - **Count.** A category appearing for the first time adds a column that is written down nowhere in the program. - **Order.** The header order is derived too: commonly from the sorted distinct values, commonly from order of first appearance, and which of those you get is a property of the tool rather than something your code stated. Either way, position is not yours to rely on. ## The header text is not guaranteed either With exactly one column supplying the cells, each new header is usually the distinct value as it appears in the data. That is a habit of the common case, not a law. As soon as more than one column supplies cells, a tool must distinguish one measure for a category from another measure for the same category, and it does that by composing the header from the distinct value together with the name of the column the cells came from. Some tools additionally let you hand in a template for the composed name. So the durable statement is not *what the headers will be called*. It is that **the names, the count and the order are all a function of the input**. ## How this actually breaks 1. **In exploration it never bites.** You widen, look at the result, and write the next step naming a column you can see on screen. It works, because the data in front of you produced it. 2. **Scheduled, it bites loudly.** The job runs next month, a category is gone, and the step *after* the widening fails on a column that does not exist. The error names a later step than the one that caused the problem, which is why this costs more time than it should. 3. **Worst, it bites silently.** A step that operates on "every column except the identifying ones" keeps working and quietly takes in a new category nobody approved. A report loses a column and still renders. Nothing raises, and the number on screen is now answering a slightly different question. ## Making the schema yours again - **Decide which one you want.** A discovered schema is right for exploration and wrong for anything scheduled. Say which you are building. - **Declare the headers instead of inheriting them.** Build **the complete grid of key-and-header pairs** — every combination of a row key with every header you expect, whether or not the data holds it — so that a category with no rows comes back as a column whose cells the widening had to invent, rather than as a column that is not there. The set of columns is then a function of your code again. - **State the order** if anything downstream depends on position, rather than accepting whatever the tool derived. - **Check at the boundary.** Compare the set of headers you got against the set you expected and fail on the difference immediately, so the error names the widening rather than the step three places later. - **Keep the widening near the edge.** A conversion in the middle of a pipeline puts a data-derived schema between two pieces of code that both assume a fixed one. ## What an interviewer is listening for The short answer — "the headers come from the data" — is only half of it. The half that separates candidates is noticing that the *order* and the *names* move too, and that the loud failure is the lucky one: the expensive version of this bug is the pipeline that keeps running with a column more or a column fewer than anyone intended.