When the financial numbers do not reconcile

The raw payload is always kept

Typed columns are a convenience layered on top of the data, never a replacement for it. In the crypto asset data lake, a mandatory Bronze layer stores the original response as JSONB before any transformation, and that layer feeds the full Medallion model. The test is simple: if answering a new question means calling the provider again for data it already sent once, the raw layer is wrong. Fixing the raw layer is cheaper than burning API quota to recover something that was already in the building.

Reconcile against the source, not against your own pipeline

Row counts do not prove completeness, and a check the pipeline runs against itself only confirms what it already believed. In the multi-blockchain integrator, the closing check is an accounting invariant: total supply read straight from the contract against the net supply PostgreSQL computes by adding mints and subtracting burns. Before that, a self-reconciliation engine sweeps the history in batches, compares on-chain events against the database range by range, and whenever they diverge it deletes the whole batch and re-extracts from scratch.

Fail loudly instead of shipping a false green

A monitor that measures its own execution rather than the result of it lies with confidence. I documented a case where the dashboard stayed green for 14.71 days while serving was down, and degrading the system actually strengthened the green. The fix is to measure the effect at the destination: rows written, counts reconciled, invariants closed. A noisy alert is expensive; a false green is invisible, and the difference between the two shows up at month-end close.

Assume the source is hostile

The source does not cooperate, and designing as if it did only postpones the incident. On the OTC desk, the spreadsheet has no stable primary key, so the load is a Full Replace with a SHA-256 fingerprint plus an occurrence counter, a schema shield ignores any new column added without warning, and hand-typed counterparty names go through a cascade of explicit alias, exact match, and only then fuzzy matching at a 92% similarity threshold. In the financial reconciliation pipeline, against pagination offset drift, a second daily CSV path runs an anti-join on the database and fills exactly the gap the API would never show.

Domains

Fintech, crypto, financial services, backoffice and digital assets. In practice that shows up as a banking and PIX ledger running minute by minute, institutional crypto custody with isolated workspaces, multi-bank financial settlement and fiscal document consolidation. The case studies are anonymised under confidentiality: what is published is the technical decision and the architecture, never the client.

Stack

PostgreSQL and Python at the core, with SQL and REST APIs on top. Azure and AWS in the cloud, dbt for modelling, Dagster for orchestration and Prometheus for observability. The load pattern that repeats across the case studies is native COPY into a temporary table, UPSERT into the final table, in-memory deduplication before each batch, and new indexes created with CONCURRENTLY so production writes are never blocked.

A dataset anyone can audit

An anonymised case study asks for trust. A public dataset does not. The LCK Spring 2024 Players Statistics dataset is on Kaggle under a CC BY 4.0 license, with a maximum usability score (10.0), 300+ downloads and third-party reuse in EDA notebooks. Collected with BeautifulSoup and Selenium, with percentage and MM:SS duration normalised to seconds, deduplicated, and organised into three tables linked by a sequential Player ID. It is all there to check: kaggle.com/datasets/lukasrozado/lck-spring-2024-players-statistics ↗

Scale and outcomes in production systems

An ETL over 206 million records. 15+ blockchains integrated with self-reconciliation, closing at 100% reconciliation against on-chain supply. A Medallion architecture, Bronze, Silver and Gold, built from scratch. And an ingestion pipeline sustaining >99.9% uptime in production, with a full historical backfill since each account's opening, some going back to 2019. The published case studies each carry the problem, the decision and the consequence.

Working in data engineering since 2021. English at C1, certified by EF SET Advanced, which means technical meetings, documentation and code review in English with no intermediary. Day-to-day tools: PostgreSQL, Python, Azure, AWS, dbt, Dagster and Prometheus. I also write about the craft in Writing and in the Dado Bruto newsletter, and answer the most common questions in the FAQ.