Unit 02 of 03
Transformation
Counting where, in which layer and with which guarantee the transformation is done: the effect of running the transformation at the source or at the target on the transported rows and the cells read, the tasks that dependency resolution orders in query-based modeling and what happens when a cycle slips through, the rows each of the raw, refined and serving layers keeps and which layer can stop a corruption, the corruption classes caught by data tests and expectation definitions against the residue that none of them catches, and the way applying a rule change to only two periods shifts the report by 16.98 to 29.57 from full application while both runs look internally consistent and trip no check.
Published Updated
Lessons
- 01 ETL and ELT Counting whether the transformation runs at the source or at the target: when the boundary is moved to the source, the rows transported to the target fall from 2400 to 55 and the transported cells from 14400 to 165, but the total number of cells read by all three setups stays fixed at 33110 and only the identity of the reader changes; the hybrid boundary lowers the transported rows to 2200 while raising the transported cells to 15400; when a reporting rule changes, the number of rules that can be recomputed at the target is 3 for the setup that keeps the raw data, 1 for the hybrid setup and 0 for the setup that transforms at the source, and the last setup has to go back to the source and can recompute only three periods. 14 min read
- 02 Transformation Frameworks Counting how transformations are written as models that refer to one another and where the order comes from: over eleven models and thirteen declared dependencies the resolver order gives 0 violations, while the hand-kept write order violates 1 dependency and runs 7 models with missing input, and the fixed list violates 5 dependencies and runs 10 models with missing input; the violating run produces no error and lowers the serving output in every region by 17.46 to 28.37; when one more reference is added to a query text, the resolver orders only 3 of the 11 models and does not start the run, while the manual order runs all eleven and silently breaks one edge of the cycle; because a single undeclared read never appears in the graph, the resolver reports 0 violations while the actual violation is 1. 10 min read
- 03 Staged Layers Counting the rows each of the raw, refined and serving layers keeps and the layer in which a corruption can be stopped: the sound pipeline leaves 2400 rows in the raw layer, 2200 rows in the refined layer and 55 summary cells in the serving layer; of the six corruption classes four stop in the raw layer, one in the refined layer and one only in the serving layer, and three of them do not change the served value at all; the three-layer setup stores 4655 rows and stops all six classes, while the two-layer setup stops the same six with 2455 rows but catches the unit change one layer late, and the single-layer setup stores 55 cells, stops only two classes and never sees four. 12 min read
- 04 Testable Transformations Counting what expectations placed on the transformation's own output catch and what they do not: six expectations never fire on the sound pipeline and catch four of the six corruption classes, while duplication and type drift are swallowed by the transformation itself and never appear in the output. The unit change is caught by an expectation on the output at the third stage, and no baseline is needed for it, although the serving stays exactly the same. A rule error that shifts the period label back by one period leaves 2200 rows, 55 cells and five serving values exactly the same, so it trips none of the six expectations; only a unit test on a fixed input catches it. 5 min read
- 05 Backfill Counting the application of a changed rule to past periods: when the rule is applied to all twelve periods, 12 periods and 2200 rows are recomputed, while applying it to only two periods takes 2 periods and 400 rows, but the report deviates from the full application by 16.98 to 29.57. The mixed report breaks none of the six expectations, comes out internally consistent, and trips no check, because the 2.75% jump the rule creates in the central cells stays below the median of the natural period volatility. The source of the deviation is not the data but which periods were recomputed; when the row is stamped with the rule version, the mixing becomes visible with a single comparison. 9 min read