How I built an autonomous system that gathers official information for a tax consultancy firm

[ME]\

How much time do we have?

Can I have a few meetings with the team that currently handles this work?

OK. Let's do it.

I needed to better understand the company's current workflow in order to figure out how to improve the process. To do that, some of the questions I needed to answer were the following:

1. What was the current workflow and what constraints shaped it? To answer that, I interviewed the most experienced employee doing this work, since the methodology was the same across the 3 of them.

2. Which sources contain the necessary information and in what formats? I needed to know whether the sources exposed that information through an API, or in CSV, XML, PDF, etc. I needed to know whether I could extract it easily and programmatically, or whether I would instead need to rely on scraping or similar solutions.

3. How frequently are those sources updated and how stable are they? I needed to know whether the URLs would be predictable, whether they changed often, and whether we might run into issues that would require us to build fallbacks, etc.

4. What software do they use to store client information, and in what way? It was important to know whether the company's ERP and/or CRM was off-the-shelf or custom-built, and whether it exposed an API I could use to cross-reference the data.

5. Are there any data protection limitations? I assumed that in the case of self-employed individuals (natural persons), we would have certain limitations on data capture or storage. I wanted to confirm that.

Some of the most important findings from my research were the following:

• Some sources offered an API, but most only exposed an XML file or a PDF.
• In the case of older PDFs, they were sometimes literally scanned printed sheets, so in order to build historical coverage I had to integrate a mix of methods to process that data.
• The information sources did not offer normalized data. In fact, they were highly inconsistent. And in several cases there were typos and spelling mistakes already present in the source data, which made normalization harder.
• They used a commercial ERP, so the parsed and normalized information could be integrated through its API.
• There were indeed data protection limitations in the case of self-employed individuals.

The resulting product could be summarized as follows:

An autonomous system that consolidates different official information sources in a modular and scalable way, and reconciles each new update with the profile of the corresponding client in the consultancy firm's ERP.

I built an initial prototype to test the quality of the information, the reliability of reconciliation between data coming from different sources, and to determine the probability of failure and uncover extreme cases we would need to solve.

I chose a design pattern called "Medallion" to organize the data logically in a Data Lakehouse, with the goal of progressively improving data quality, structure and reliability as it flows through three distinct layers: Bronze, Silver and Gold.

Bronze Layer (Raw Data)

This is the entry point for all information. Data is stored exactly as it comes from the source (in raw format), without applying filters or transformations.

• Purpose: Keep a complete, immutable history. It makes it possible to reprocess data if failures occur or to run audits at any time.
• Type of data: JSON, flat files (CSV, Excel), or continuous streaming flows from APIs, databases, etc.

Silver Layer (Cleaned Data)

This is where data from the Bronze layer is filtered, cleaned and transformed. Different information sources are integrated and standardized into a coherent model.

• Purpose: Create a single, trustworthy source of business-grade data.
• Key operations: Duplicates are removed, errors are corrected, format issues are resolved, and data integrity is validated.

Gold Layer (Aggregated Data)

This is the presentation and final consumption layer. The refined data is modeled into business-oriented schemas (fact and dimension tables).

• Purpose: Optimize the data for reports, dashboards, LLMs, APIs, etc.
• Type of data: Information that, in this case, is ready to be consumed by the API that will store it inside each company's profile in the consultancy firm's ERP.

Some of the most important edge cases were:

• What happens if the data starts from scarce, incorrect or orthographically different input? For those cases, I created a triple-check system. It first tries to solve the reconciliation deterministically. If it cannot reconcile it, the item moves to an AI fallback. If the AI system still cannot reconcile it, it falls back to a manual system. Technically, it is a system that scores similarities and establishes possible information matches which, if they are not reconciled automatically, are shown in a manual validation dashboard. After many tests with hundreds of records, roughly 4% of the information ended up in this unavoidable manual fallback because of the limitations of the source data itself.
• What happens when a new company becomes a client of the consultancy and some historical data is needed for it? Thanks to the Medallion system, we store raw information. For these cases, I created a simple form where a date range can be entered and, when an action button is pressed, the system goes through the files for those dates and gathers the historical information about that company from the different sources.
• What happens if a data source stops updating because of a failure, a URL change, or any other reason? The system knows the update frequency of each source, and if it does not receive data 12 hours after the expected time, it sends a notification to the consultancy firm and the technical team, which in this case was me.
• What happens if the ERP API changes? I implemented a solution similar to the previous point. In this case, the notification also returned the API changelog within a short date range in which the system had been interrupted.