Frequently asked questions
I am Lukas Rozado, a data engineer. Here are direct answers about my work and about the technical problems the case studies published on this site solve: a spreadsheet with no stable primary key, an API with no delta filter, a quota shared across keys, pagination offset drift and on-chain history completeness. Every answer stands on its own and points to the case study where the decision was made.
About the work
What kind of data problems does Lukas Rozado solve?
I am a data engineer working on ingestion, reconciliation and integration of critical financial and operational data. The published case studies cover a crypto asset data lake with continuous auditing, a unified banking and PIX ledger platform, OTC desk reconciliation, fiscal document consolidation and multi-blockchain integration. What they have in common is a hostile source: an API with no delta filter, a spreadsheet with no primary key, pagination that silently drops records, and an API quota shared across every key.
Which databases, languages and clouds does Lukas Rozado work with?
I use Python and PostgreSQL in nearly every published case study, with SQL and REST APIs on top. My portfolio positions itself on Azure and AWS, and the case studies detail Azure in practice: Azure Key Vault to isolate credentials per account and Azure Active Directory for corporate authentication between systems, as in the multi-bank financial settlement pipeline. Outside the financial core I also work with machine learning in scikit-learn and web scraping with BeautifulSoup, Selenium and pandas.
Does Lukas Rozado build real-time pipelines or only batch?
I build both, and usually combine them in the same system. The unified banking ledger platform runs minute by minute as a persistent process, while the financial reconciliation pipeline uses a hybrid model: a near real-time API path driven by a high-water mark, plus a daily CSV path that recovers the gap the API let through. Purely batch pipelines show up where the source itself is batch, such as the daily OTC desk spreadsheet and the monthly fiscal document consolidation.
Technical questions
How do you reconcile a spreadsheet that has no stable primary key?
With no stable primary key, the way out is to stop trying to identify the row at the source and reload the whole set instead. In the OTC desk case study the fact table is truncated and fully reloaded on every execution through the PostgreSQL COPY protocol, and each transaction gets a SHA-256 fingerprint over the logical signature of the row combined with an occurrence counter (nonce), which preserves identical legitimate splits instead of collapsing them as accidental duplicates. A schema-shielding layer extracts strictly the closed list of expected financial columns, so a new column created by the business side does not break the next day's ingestion.
When should you use Full Replace instead of an incremental upsert?
Full Replace makes sense when the source offers no stable primary key and rows can be inserted, deleted and reordered with no traceability. In that scenario an incremental upsert becomes a source of ghost records: the row disappears at the source and stays alive in the database forever. Truncating and reloading in full guarantees the database is an exact mirror of the current source state, at the cost of reprocessing the whole volume on every run, which is only viable while the set fits an acceptable load window. When a reliable key does exist, the pattern used in the other case studies is the opposite: COPY into a temporary table and then UPSERT.
How do you capture only what is new from an API with no date filter?
The technique used is watermark early-exit: reading starts from the most recent page and stops on its own as soon as N consecutive pages fall entirely inside a date already covered before. That avoids both bad extremes, reprocessing the entire history on every run and stopping too early and losing records. For transactions that change status after they were already captured, a separate and cheaper auditor reads the API only from the oldest pending record onward and upserts whatever changed, without rescanning the full history. Both pieces live in the unified ledger platform and in the institutional custody integrator.
How do you ingest from an API with a shared quota without getting the IP blocked?
The common mistake is estimating consumption by counting calls on the client side. In the crypto asset data lake architecture the rate limiter reads, on every HTTP response, the official consumed-quota header the provider itself returns, and keeps that state shared across processes, respecting a single per-minute ceiling instead of a per-key one. A priority carousel gives the turn to accounts with recent activity and pushes idle ones to wider cycles, spending no quota on what did not change. That architecture eliminated the IP blocking pattern that existed before it.
What do you do when API pagination drops or duplicates records?
When the source suffers pagination offset drift, records vanish or duplicate without the API reporting anything, and no check made only against the API itself will detect it. The solution applied in the financial reconciliation pipeline was an independent second path: a daily routine downloads the consolidated CSV report and runs an anti-join against the database, filling exactly the gap the API path let through. The checkpoint is stored per page rather than per day, so a failure resumes from the exact point of interruption.
How do you prove the on-chain history stored in the database is complete?
Row counts do not prove completeness, which is why the multi-chain integrator closes with an accounting invariant: it compares the total token 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 and compares the on-chain event count against the database count, range by range; whenever they diverge it deletes the whole batch and re-extracts from scratch. Extraction reads the node directly over raw RPC, with eth_getLogs filtered by event topic, without depending on a third-party indexer.
How do you load millions of rows into PostgreSQL without blocking production writes?
The pattern used across the case studies is native COPY into a temporary table, followed by an UPSERT into the final table, with in-memory deduplication by primary key before each batch. Published batch sizes range from 5 thousand records per flush on the ledger platform up to 50 thousand on the financial reconciliation pipeline. New indexes are created with CONCURRENTLY so production writes are never blocked, and the historical backfill runs in a process separate from the incremental one, so it neither contends for locks nor overwrites the checkpoint of the ingestion already live.
Why can a pipeline look green in monitoring while it is broken?
A pipeline looks green when the health signal travels the same path it is supposed to audit. In the case I documented in the portfolio's writing section, the dashboard stayed green for 14.71 days while serving was down, and degrading the system actually strengthened the green, because the monitor measured its own execution rather than the result of it. The fix is to measure the effect at the destination, meaning rows written, counts reconciled and invariants closed, instead of measuring that the job finished without an exception.
How do you get in touch about a data project?
Through the contact form on the site, by email or on LinkedIn. The contact page states a reply within 2 business days. I also publish the Dado Bruto newsletter on LinkedIn, covering data engineering case studies.
Your question is not here? Describe the data problem you have today and I will answer with the technical path.
Discuss your case