The Hardest Part of Automation Isn't the Automation

I was presented with an interesting reconciliation problem.

Five custodians each supplied an end-of-day position file. A separate security master mapped external identifiers to internal security IDs. The requested result was one canonical CSV containing normalized positions across all five sources.

The explicit criteria sounded straightforward:

• read each source format
• map every resolvable position to the security master
• normalize quantities, market values, dates, and position types
• consolidate repeated rows conservatively
• label anything uncertain with needs_review=true and explain why
• produce one deterministic output file

The data set was small. There were only a few dozen positions, eighteen securities in the master, and no stated throughput target, latency requirement, memory ceiling, or distributed-processing constraint.

At first glance, this looked like a CSV transformation:

five source files
-> one schema
-> one output file

Then I opened the files.

One source identified securities by ticker, another by ISIN, another by CUSIP, and another by a description containing a typo. Dates arrived as as-of dates, trade dates, and settlement dates. Short positions used different sign conventions. One file embedded subtotals and a final total among its position rows. Another began with a report preamble instead of a header. Two contained repeated positions that might have represented tax lots, separate accounts, corrected records, or accidental duplicate deliveries. One row was missing both its market value and date.

The code required to read those files was not the interesting part. The interesting part was deciding what the output was allowed to claim.

That changed the shape of the work. The Python proof of concept still needed to meet every stated criterion, but it also became a way to turn ambiguous expectations into visible, testable contracts. A later implementation could evolve the runtime and operating model, but it should not have to rediscover what the data means.

A canonical file is not the same as reconciled truth

Reconciliation is a control process. It tries to establish that records which should describe the same economic fact agree, and it makes every unexplained difference visible.

In a payment system, that might mean comparing an internal ledger entry with a processor's settlement record. In a position system, it might mean comparing an internal book of record with what a custodian says the firm holds.

That is different from putting several files into the same table.

Several related operations are often compressed into the word reconciliation:

The supplied inputs contained custodian observations and a security master, but not an independent internal ledger.

The security master could establish canonical identity. It could tell the program that several external representations referred to the same security. It could not prove that a reported quantity was correct, that every expected position arrived, or that a custodian file was complete.

The POC could therefore do several useful things:

1. Normalize heterogeneous source files into one data contract.
2. Resolve positions to canonical security identities.
3. Apply duplicate, completeness, and data-quality controls to the evidence available.
4. Preserve every decision the available evidence could not safely settle.

It could meet the requested criteria without pretending that a cleanly shaped row was automatically reconciled truth. Full two-sided reconciliation would require an expected side, such as an internal ledger, a prior accepted snapshot adjusted for known activity, or another independently captured record of what should exist.

That distinction is more than terminology. It determines which rows the system may publish, which rows need another source of evidence, and which decisions still belong to a person.

The schema defined columns, not business meaning

The target schema contained eight fields:

security_id
quantity
market_value_usd
position_type
as_of_date
source_custodian
needs_review
review_reason

The schema described the output shape. It did not answer what every value meant.

For example, quantity had to be negative for a short position, but the sources expressed shorts differently. Some sent a negative number. One sent a positive quantity plus an explicit SHORT label. Market value was even less consistent. One short position had a negative market value while another had a positive market value.

The date field had the same problem. An as-of date, a trade date, and a settlement date can contain the same sample value while representing different points in a position's lifecycle. Renaming all three to as_of_date does not make them semantically interchangeable.

Repeated rows raised a larger question. If two rows share a security, source, date, and position type, are they duplicates? They might instead represent separate accounts or tax lots that the target schema has no field to preserve.

The files exposed questions the schema could not answer:

Code can choose a default for every one of these questions. It cannot manufacture the authority to make that default correct.

The safest first implementation had to make its assumptions visible, behave deterministically, and preserve uncertainty instead of converting it into plausible-looking data.

The Python POC was executable requirements discovery

I used Python, its standard library, and in-memory lists and dictionaries for the proof of concept.

That was not a claim that Python and memory were the final architecture. It was a bounded decision based on the constraints that actually existed. The workload was tiny, while the rules were still being discovered. Readability, testability, and rapid iteration mattered more than theoretical throughput.

The processing path stayed simple enough to inspect from end to end:

raw source row
-> source-specific adapter
-> normalized position candidate
-> identity decision
-> field and duplicate checks
-> accepted row or explained exception
-> deterministic output and run manifest

Each source adapter owned only its file format. It located the header, mapped source columns, parsed dates and decimals, converted local currency where required, and identified rows that were not positions. Shared code handled identity, validation, duplicate policy, review reasons, and output.

That boundary matters. A renamed custodian column should require a change to one adapter, not a rewrite of the matching rules. A new publication target should not change how a CUSIP is resolved. Infrastructure can evolve behind the contracts once those contracts are explicit.

The POC also used Decimal instead of binary floating-point arithmetic, preserved deterministic output ordering, isolated individual source failures, retained the raw rows behind normalized and consolidated positions, and generated a run manifest with a unique run ID and SHA-256 hashes for the inputs.

It had no third-party runtime dependencies. That decision was contextual, not ideological.

In a regulated environment, a dependency adds more than an import. It adds direct and transitive components to inventory, provenance and integrity checks, vulnerability and patch ownership, license review, version pinning, and another release channel trusted by the data path. The Python runtime still needs governance and patching, so standard-library-only does not eliminate supply-chain risk. It narrows the surface.

For this workload, the standard library already supplied CSV parsing, date handling, exact decimal arithmetic, hashing, identifier normalization, and string similarity. A dataframe or fuzzy-matching package would have added deployment and review work without solving a demonstrated problem.

There was also a correctness benefit. Financial identifiers can contain leading zeroes, and a date-looking string does not automatically carry the correct business-date semantics. Named parsers made coercion policy visible and independently testable.

Dependencies should solve a demonstrated need. The same standard applies to larger infrastructure and to AI.

Identity resolution is an evidence problem

The security master was indexed by ISIN, CUSIP, normalized ticker, and normalized name. Matching followed an explicit order:

1. Exact ISIN
2. Exact CUSIP
3. Exact normalized ticker
4. Exact normalized name
5. Fuzzy name candidate

That order reflects the relative strength of the available evidence. It does not mean the first matching field should silence every other field.

If a row contains an ISIN for one security and a ticker for another, returning the ISIN match is not enough. The resolver should collect the available signals, identify the contradiction, and block automatic publication. An authoritative identifier is valuable because it is strong evidence, not because it grants permission to ignore conflicting evidence.

The security master itself also needs controls. If two master records claim the same normalized identifier, a dictionary can silently keep one of them. The POC instead detects duplicate lookup keys, records a run-level warning, and flags any row whose match depends on that ambiguous key.

Fuzzy matching needs an even clearer boundary. The highest score is not enough. A candidate should clear an absolute threshold and have meaningful separation from the runner-up. A score of 0.91 is not persuasive when another security scores 0.90.

Even a strong fuzzy candidate is still a candidate. A previously unseen fuzzy match should enter review. If a reviewer approves it, the source value can become a versioned alias and resolve deterministically in later runs.

The goal is not to make the same probabilistic guess every morning. It is to turn repeated judgment into explicit policy.

The duplicate problem follows the same logic. A set can tell the system that a key appeared twice. It cannot explain why.

For the requested output, the POC consolidated rows only within the same source, security, date, and position type. It never deduplicated across custodians because independent custodians can legitimately report separate holdings in the same security. Every consolidation stayed flagged, and the original component rows remained attached as evidence.

That met the requested shape while making the missing contract visible. If the business needs account-level or tax-lot-level positions, consolidation is wrong, and the schema needs another identity field. Reconciliation can also require one-to-many matching for split records, many-to-one matching for aggregated settlements, or balance-level checks when detailed records cannot be paired directly.

Cardinality is part of the business relationship. It cannot be inferred safely from whichever columns happen to be present.

needs_review is a result, not a failure of determinism

The completed POC emitted 54 canonical-shaped rows and labeled four for human review:

Those rows are the most important output of the POC because they show where deterministic execution and human evaluation meet.

Deterministic does not mean the system can decide everything. It means the same input and the same rules produce the same result, including the same decision to stop and ask for judgment.

The incomplete row should not disappear. The fuzzy match should not silently become truth. The duplicate rows should not be declared either valid or invalid when the schema does not contain the account or lot evidence needed to decide.

In each case, the deterministic result is not a guessed answer. It is an explained boundary:

evidence satisfies an authorized rule
-> accept

evidence may become complete later
-> keep pending

evidence is plausible but requires judgment
-> review

evidence violates a hard contract
-> reject

That is still deterministic behavior. The software is making the decision it has authority to make: whether the available evidence is sufficient for automatic trust.

Human-in-the-loop is therefore not an escape hatch around the system. It is one of the system's explicit control paths.

A review flag needs an operational lifecycle

The requested interface placed clean and flagged rows in one CSV. That met the criteria and kept uncertain data visible. It also created a downstream risk: another consumer could ignore needs_review and process an incomplete or ambiguous row as canonical truth.

A production system should turn that boolean into explicit states with publication behavior:

Only accepted rows should enter the official canonical table. Pending, review-required, and rejected records should live in a durable exception store with the raw evidence, reason codes, owner, timestamps, comments, disposition, approval history, and the rule or alias created by the resolution.

PENDING matters because not every mismatch is an error. A file can be late, a settlement window can still be open, or an enrichment step can finish after the first pass. Re-evaluating later is safer than rejecting the row immediately or asking a person to investigate timing noise the system can resolve itself.

REVIEW_REQUIRED also needs an owner and a clock. A boolean does not say who is responsible, when the case becomes stale, what evidence was considered, or whether a prior decision already answered the same question.

If operations approves the same custodian description every morning, the system has not learned from its own workflow. A recurring approval should become a versioned alias or business rule, with the same review and release controls as any other change.

Human review is a budget, not an infinite fallback. If every harmless rounding difference or ordinary timing delay enters the queue, reviewers eventually rubber-stamp it. The useful metrics are not just row counts. They include exception rate, exception age, recurrence, resolution time, false-positive rate, and the percentage of decisions that can safely become deterministic rules.

Automatic correction needs the same boundary. The system can safely normalize punctuation, apply a documented source-specific sign rule, or replay a known missing delivery. It should not invent a market value, choose between conflicting authoritative identifiers, or erase a genuine amount mismatch without investigation.

The objective is neither maximum automation nor maximum review:

automate what can be trusted
+
spend human judgment where the remaining uncertainty matters

Where an LLM could help without becoming the authority

The observed source variation did not require an LLM. Punctuation differences, known identifiers, common legal suffixes, several date formats, and one misspelled name were enumerable. Explicit normalization and a deterministic similarity function handled them with stable, testable behavior.

If variability is enumerable, encode it explicitly.

An LLM becomes more interesting at the exception boundary, especially if the review queue grows faster than operations can clear it. It could:

• suggest likely security candidates for a previously unseen description
• compare a source row with several master candidates and summarize the distinguishing evidence
• classify free-form custodian notes into operational categories
• draft a concise explanation of why a row was held
• identify recurring exception patterns that may deserve a deterministic rule

Those are useful forms of assistance. None of them gives the model publication authority.

The safer pattern is:

deterministic normalization and matching
-> unresolved exception
-> model returns a structured suggestion
-> deterministic validation checks identifiers and conflicts
-> human approval when policy requires it
-> durable alias or rule
-> deterministic handling on future runs

The model should not write directly to the canonical table, invent a missing amount, choose between conflicting authoritative identifiers, or override a genuine value mismatch. Its suggestion should be recorded with the model and prompt version and should pass through the same decision, audit, and publication controls as every other input.

There is also a deeper limit. An LLM can summarize evidence and suggest an interpretation. It cannot supply missing business authority. If the unresolved question is whether the firm wants tax-lot-level output or whether one custodian reports on behalf of another, better language prediction does not answer it. A person who owns the process still has to decide, and the system then needs to encode that decision.

The goal is not to pay for the same ambiguity forever. The useful role of an LLM is to reduce the cost of novel review while helping turn repeated judgment into governed, deterministic policy.

Controls move canonicalization toward reconciliation

One source embedded subtotals and a final total among its position rows. The POC correctly kept those rows from becoming fake securities, but simply discarding them would lose useful evidence.

They are not positions. They are control records.

An evolved system should capture them separately and compare them with normalized detail:

reported source total
vs.
sum of normalized detail rows
=
control status, delta, and tolerance

The same principle applies to row conservation. Every input row should end in a named category:

rows seen
= accepted positions
+ pending positions
+ review-required positions
+ rejected positions
+ control rows
+ explicitly ignored non-position rows

If that equation does not balance, the reconciliation path may have lost data while checking whether another system lost data.

The control layer can grow without changing the normalized position contract:

Tolerances are contracts too. A one-cent difference can be harmless for one instrument and material for another. Price, FX, date-window, and aggregate tolerances should be owned by the business and configurable by source or asset class. A hardcoded global tolerance merely hides policy in code.

Drift is not proof of an error. A large position move may reflect a legitimate trade. The control's job is to surface a change the current batch cannot explain and carry the evidence into the same exception workflow.

Once an internal ledger or another expected position source becomes available, the system can compare expected and observed quantities, values, dates, and match cardinalities. That is the point where the design moves from canonicalization plus data-quality controls into full financial reconciliation.

Publication is its own decision

The POC isolated source failures. If one custodian file was missing or malformed, it recorded the failure and continued processing the remaining sources. That was operationally useful because a single bad input did not erase all diagnostic output.

Partial processing is not permission to publish.

A run can produce useful evidence while still being ineligible to replace the official result. An evolved system should distinguish at least three run outcomes:

SUCCEEDED  all blocking controls passed
DEGRADED   useful results exist, but one or more non-blocking controls failed
BLOCKED    canonical publication is not allowed

The publisher should write to a temporary target and promote the accepted result atomically only after blocking controls pass. A partial or review-heavy result can be retained for investigation without becoming downstream truth.

The run manifest is part of that control surface. The POC recorded a unique run ID, completion time, processed and missing sources, input checksums, output row count, and review count. A production manifest should also retain the security-master version, ruleset or code version, decision counts, control results, output hash, and publication status.

A checksum identifies bytes. It does not preserve them. Replaying a run requires immutable raw inputs, the exact security-master version, the ruleset or code version, and the output produced from that combination.

The system should not merely produce an answer. It should preserve the path to that answer.

Tests preserve the encoded contract

The POC included 153 tests covering normalization, decimal and date parsing, short-position signs, exact and fuzzy identity resolution, duplicate security-master keys, all five source adapters, FX arithmetic, duplicate consolidation, malformed rows, partial source failure, manifests, deterministic ordering, and target-schema behavior.

I ran the complete suite against the reviewed code:

$ python -m pytest test_reconcile.py -q
153 passed in 0.25s

The test suite matters because the dangerous failure in reconciliation is often not a crash. It is a clean-looking number produced from the wrong assumption.

A passing test proves that the program follows the contract encoded in the assertion. It cannot prove that the contract has business authority. A test can verify that the settlement date maps into as_of_date; it cannot decide whether that mapping is appropriate for the accounting process.

The business decision comes first. The test makes that decision repeatable.

This is why the POC is not disposable. It is a working reference implementation of the currently understood behavior. A later runtime should run the same fixtures and preserve parity across canonical rows, review reasons, decision states, control results, and manifests. If the results differ, the difference must be an intentional contract change or a regression to fix.

Scale the system after the meaning stabilizes

The Python POC met the demonstrated criteria at the demonstrated scale. That does not make it the most permanent runtime, and it does not justify pre-building a distributed platform.

The questions that should govern scale decisions are measurable:

• records and input bytes per run
• schema width
• run frequency
• delivery-to-publication SLA
• peak-memory ceiling
• CPU and temporary-disk budget
• persistence and replay requirements
• number of concurrent reviewers
• source-failure and recovery expectations

The next evolution of this prototype would be built in Go, but not because the Python solution was the wrong choice. The Python POC completed the job it was meant to do: it met the criteria, exposed missing business decisions, and created the reference behavior.

Go becomes a natural next step when the system needs more operational headroom. A single deployable binary, explicit domain types, predictable runtime behavior, straightforward concurrency, and strong profiling tools fit a reconciliation process that may grow from a bounded script into a scheduled service with multiple inputs, persistent exception workflows, and tighter publication deadlines. Its first responsibility is to preserve the contracts the POC discovered while creating clear boundaries for the changes that real operations may later require.

From there, the path remains incremental:

Storage, concurrency, and publication mechanisms can evolve as measurements justify them. The decision states and financial meaning should remain stable while those mechanisms change.

Performance parity includes meaning, not just output shape. A faster engine that changes duplicate ownership, exception classification, tolerances, or canonical totals is not an optimization.

Do not optimize compute before validating meaning. Do not scale confusion.

The real deliverable is the contract

The interesting part of this problem was not parsing five files.

It was using a working solution to expose the decisions hidden behind a seemingly clear request:

• what identifies the same security
• which evidence is authoritative
• what a repeated row represents
• when a value can be normalized safely
• what proves an input is complete
• what should wait for more data
• what requires human evaluation
• where an LLM may assist without becoming the authority
• what blocks publication
• what must survive for replay and audit
• what measurements justify a different runtime or architecture

The Python POC met the stated criteria and made those decisions inspectable. To scale, the next implementation can carry them into an evolvable Go system without redefining the financial meaning of the output.

The transformation was an implementation detail. The assumptions around the transformation became the system.

Automation does not remove decisions. It encodes them.

The hardest part of automation is deciding which decisions are safe to turn into software.