Specify data migration and reconciliation

Start with the questions the data must answer

Before choosing tables or writing a transformation, state the business questions the target must answer. In this lesson’s fictional exercise, a team needs to identify each account’s positions, participants and signed quantities by unit at a reference date. It also needs to explain which source identities contribute to a participant-and-unit total. “Copy the file” specifies neither capability. A summary of totals may answer the second question only partly: it shows the result but may have lost the contributions explaining it.

Define the grain of each collection. One source row represents one position at its stated effective date. The supplied small dataset contains no multiple revisions of the same position. An account may have several positions, and a position may be allocated to several participants. In the target, a position row and an allocation row represent different things. Four positions therefore do not require four rows in every table. A useful comparison starts with the object or relationship represented by each row.

Produce a short dictionary covering entity, attribute meaning, identity, references, unit, dates and required queries. For each removed or aggregated field, ask which query can no longer be answered. Record the answer with a concrete example before approving the requirements design. The product of this Analysis activity is a justified specification for a future migration; there is no delivered solution being evaluated here.

Preserve identity and cardinality

Under the teaching contract, an account is identified by the ordered pair entity and account_id. N/0012, S/0012 and N/12 are therefore three different accounts. Identifiers are text, and leading zeros form part of identity. Converting account_id to an integer would collapse two N accounts. Joining only on the text code 0012 would mix N and S. The target may create its own technical key if it retains an unambiguous correspondence with the compound identity and uses that correspondence for position references.

A position’s identity is entity and position_id. N/P1 and S/P1 are distinct positions. Repeating the same pair is invalid input in this exercise; there is no authorization to deduplicate by row order, quantity or displayed name. A uniqueness rule protects identity but does not by itself determine the correct relationship. Position N/P2 references account N/12. Linking it to N/0012 would create an existing reference that nevertheless differs from the association specified by the source.

Model accounts, positions and participant allocations separately. N/P1 has two participants, G1 and G2: it remains one position and produces two allocations. Each allocation retains the position identity and participant identity. Additional rows are an expected consequence of the relationship, not the creation of another position. The specification must state which pairs are permitted and require their preservation instead of demanding identical physical row counts everywhere.

Write a dictionary with meaning

A mapping must explain what it preserves as well as name the columns. The exercise uses a nonnegative integer magnitude, direction IN or OUT, and unit UNIT-A or UNIT-B. IN means a positive quantity and OUT a negative quantity. These are invented lesson conventions with no universal accounting meaning. Magnitude 30 with direction OUT and unit UNIT-B becomes -30 UNIT-B. Having a sign does not authorize changing the unit or converting one measure into another.

Specify the permitted units and the absence of conversion between UNIT-A and UNIT-B. No combined total has a defined meaning. For account states, A maps to ACTIVE and D to DORMANT; any other code lacks an authorized mapping. For positions, code K depends on the rule’s validity, developed in the next section. Copying label K or choosing the state with the most similar name does not explain the business condition to preserve. Under this contract, a READY position requires an ACTIVE account.

Include input, selection condition, output, exception and retained information in the dictionary. A target position retains compound identity, account reference, effective and receipt dates, original code, direction, magnitude, unit, mapped state and selected rule identifier. These fields make the transformation explainable. Another representation that answers the same queries with equivalent evidence can be discussed during requirements review; equivalence must be demonstrated rather than assumed from compatible types.

Separate effect, receipt and rule validity

The input population is the frozen extraction of 2 July 2026, containing eight positions. The business snapshot is 30 June. Every supplied row belongs to the extraction, but only positions effective on or before 30 June are eligible for that snapshot. Receipt must occur by the extraction date. N/P1 is effective on 30 June and was received on 1 July: it belongs to the snapshot known on 2 July. N/P5 is effective on 1 July: it receives the authorized exclusion FUTURE_EFFECTIVE. It does not disappear from the extraction’s accounting.

The contract uses calendar dates without times or time zones. If a real project needs instants, retroactive corrections or successive revisions, those rules must be specified separately. Here, the position’s effective date selects the mapping version. K-before-june is valid from 1 January inclusive to 1 June exclusive and produces OPEN. K-from-june begins on 1 June inclusive, ends on 1 January 2027 exclusive and produces READY. S/P1, effective on 31 May, uses the first rule despite being received in June.

Add examples exactly at boundaries and outside the intervals. Require one applicable rule: zero matches produce UNKNOWN_MAPPING and several produce AMBIGUOUS_MAPPING. The specification does not permit resolving an overlap by choosing the latest rule. Selecting by execution time would also change historical meaning. These conditions belong in the transformation contract before any production execution exists.

Define splits and summaries without losing contributions

Each migrated position retains one detail row and produces one allocation per declared participant. Weights must be positive integers, sum to 100 and refer to known participants without repetition within the position. Signed quantity multiplied by weight and divided by 100 must yield an integer. No rounding is authorized. Splitting quantity 1 into two halves does not fit this contract: the specification must route it as an exception or be revised by someone authorized to change the rule, without inventing a tolerance during implementation.

For N/P1, +100 UNIT-A with weights 60 for G1 and 40 for G2 produces +60 and +40 UNIT-A. S/P1 contributes +50 UNIT-A to G1; N/P6 contributes another +50 UNIT-A to G1. N/P2 contributes -30 UNIT-B to G2. Detail contains four positions and five allocations. The derived summary contains G1/UNIT-A = 160, G2/UNIT-A = 40 and G2/UNIT-B = -30. The supplied dataset has no other migrated allocations. No single balance is calculated by adding UNIT-A to UNIT-B.

Order carries meaning: resolve identity and the applicable rule, validate required relationships, and only then form allocations eligible for the summary. Aggregating by participant before retaining position and rule would lose the explanation of contributions. A summary view may be useful and irreversible on its own if the necessary detail remains available. The preservation requirement concerns agreed queries and relationships, not reproducing every byte of the original file.

Give every exception an explicit outcome

The specification distinguishes structurally invalid input from a structurally valid row that fails a business rule. In this model, an incorrect type or repeated primary identity prevents input validation. A structurally valid, eligible position may enter quarantine because of an unmapped code, an absent or quarantined account, an invalid participant or split, or READY status linked to a DORMANT account. These outcomes are defined for the exercise; they are not mandatory rules of a certification provider or a real institution.

In the base dataset, N/P3 uses Z, which has no mapping. N/P4 depends on account N/0099, whose state X has no authorized mapping and puts that account in quarantine. S/P2 refers to S/9999, which does not exist. The three positions enter quarantine and produce neither target positions nor allocations. Future-effective exclusion is assessed before semantic rules; N/P5 has the previously defined exclusion outcome. Output must retain source identity and every applicable reason under the contract, without replacing an unknown account with another existing one.

Also document the effect on dependent relationships. “Account rejected” is insufficient if the specification leaves a position pointing to an account that was never created. Keep exceptions inside the reconciled population with a verifiable outcome and reason. Who may authorize a mapping correction and what evidence they need are process-preparation questions; this lesson specifies the required information without executing or approving those corrections.

Reconcile identities, relationships and quantities

The reconciliation contract starts by fixing the population and grain of each comparison. The extraction’s eight identities receive exactly one outcome each: four migrated, three quarantined and one authorized exclusion. This partition does not mean eight target positions. For the four migrated identities, require one position per identity and the exact declared participant set. Each position’s allocation sum, in its own unit and with its sign, must equal its source quantity. Tolerance is zero because the contract uses integers and prohibits rounding.

Construct a concrete counterexample to the design. If a proposal replaces N/P6 with a second copy of S/P1, it still shows four positions and +200 UNIT-A. Even the participant summary stays the same because both contribute +50 UNIT-A to G1. However, N/P6 is missing, S/P1 is repeated, and provenance changes from rule K-from-june to K-before-june. The requirement must compare identities and the selected rule as well as sums. In another variation, linking N/P2 to N/0012 satisfies account existence but fails the required association with N/12.

Relationships may also require quantities on the pairs themselves. In a separate example, A contributes 30 to X and 10 to Y; B contributes 10 to X and 50 to Y. Changing those four contributions to 20, 20, 20 and 40 preserves the totals of A, B, X and Y. It nevertheless loses each relationship’s value. If the agreed query asks who contributed how much to each target, the criterion must compare pairs and their values. This choice follows from the required information rather than adding generic checks.

Validate the specification before implementation

Take the dictionary, relationship model and expected-outcome ledger to a review with the users who need the queries and people who understand the data. Ask them to explain N/P1, S/P1, N/P2 and one exception without relying on unwritten assumptions. For each step, identify the requirement, fields used, expected outcome and rationale. If the model returns only a participant total, can the team still show the contributing account, position and mapping version? An absent answer exposes a specification gap before an operational failure occurs.

Vary one condition at a time. Use 1 June to examine the temporal boundary; create overlapping rules to challenge the uniqueness requirement; replace a reference with another existing account to distinguish structural integrity from the correct association; remove the position-participant link to check whether the summary remains explainable. A variation is another declared example, not a silent change to the eight base records. Record the expected result first. A later model execution can then be compared with an expectation developed outside the program.

Close the review by identifying preserved queries, unresolved rules and who needs to decide each one. A well-formed tabular schema can still be insufficient for the business need. Small cases help uncover omissions but do not establish that every real record has been represented, that the implementation scales or that production can be authorized. Public references support requirements and data-quality analysis; the identities, quantities and policies used here are original and fictional.

Common pitfalls

Confuse a position with a movement or allocation; lose identifier zeros; join on only part of a key; use receipt or execution to select validity; combine different units; aggregate before retaining contributions; accept a reference merely because it exists; remove exceptions from the denominator; compare only margins when the query requires values per relationship.