Cleaning foreign trade data: a real-world case

Reality was very different. What we found along the way forced us to rethink the technical solution, to question assumptions about cloud infrastructure and, above all, to understand something fundamental: when data is entered by people, the real challenge is not technological, it is human.

Cleaning and organizing foreign trade data may seem simple, but when it runs into the reality of multiple countries and zero standards, it becomes a significant challenge. This was the challenge Arcetec faced when working with customs data from seven Latin American countries so that a multinational consumer goods company could make better purchasing decisions.

The challenge: seven countries, six products, heterogeneous data

Our client needed visibility into the import and export behavior of six key products. The information existed in each country's customs records, but each nation had its own data structure, its own fields, and its own reporting rules.

The biggest challenge was in the free-text fields. Customs descriptions are written by people, with abbreviations, typos, and formats that vary from one operator to the next. Sometimes the relevant information was not in a single field but scattered across supplementary descriptions that had to be concatenated to find useful patterns.

And there was an additional complexity: the required calculations and output columns changed depending on the product and the country. We were not building a single pipeline, but a system able to adapt to multiple input and output schemas simultaneously.

First challenge: deploying to the cloud

The original plan was to deploy the solution on Google Cloud Platform. It seemed like the logical decision: scalable, robust, automatable. But two factors quickly held us back.

First, restrictions in the client's environment limited the tools available within GCP. We did not have complete freedom to choose services, which narrowed our architecture options. Second, we had developed with a small sample of data, and when we processed the full historical dataset, the infrastructure fell short.

This is a lesson we see repeated in many projects: the development sample does not always reveal scaling problems. And discovering this late comes at a high cost in time and expectations.

Second challenge: variable input and output data

What we had not anticipated, even though the sales proposal had already assumed that the input data would come in different formats, was that once we began requirements gathering and Discovery, the output schemas turned out to vary as well. In other words, not only did we have input data with different schemas for each country, but we would also have output schemas with product-specific calculations, which added a significant layer of complexity.

In practice, this meant that a single pipeline could not serve every case. Each country-product combination could produce a different output schema, with additional columns, specific calculations, and its own validation rules. Designing a rigid solution would have meant rewriting logic every time a new scenario appeared, something unsustainable in a process that had to run automatically every month.

Third challenge: key information trapped in open-text fields

The most important information for categorization and calculations was not in structured fields. It was inside customs descriptions written by people, and in many cases split across more than one free-text field.

To illustrate: a description might read "RLL PPL HIG BL 40M X30 DBL HJ", and from that the system had to infer the product, color, length, number of units per package, and sheet type. But the next record for the same product could be written in a completely different way, with other abbreviations or with the information split between the main description field and a supplementary field that had to be concatenated.

This is where the human influence on data entry becomes most evident. There is no real standard among customs operators in different countries, and often not even among operators in the same country. Each person writes in their own way, abbreviates however they see fit, and sometimes leaves out information that is critical for the analysis. Any extraction rule we designed had to treat that variability as the norm, not the exception.

The turning point: when the contingency plan outperforms the original plan

Faced with the limitations in GCP, the team had designed an alternative information system in parallel, initially intended only to validate the historical data volumes. It was a support tool, not the main solution.

However, as we refined it, this system showed capabilities that the cloud solution did not offer: it allowed the business user to configure parameters directly within the tool to adjust the transformation rules without relying on the technical team for every change. It also added dashboard visualization, something the original scope had not included.

Deciding to pivot was not easy. It meant reassessing scope, timelines, and expectations with the client. But the result was a more complete solution, more autonomous for the end user and, ultimately, superior to what we had originally conceived.

What this project taught us

Final thoughts

Behind every piece of data there is a human decision: someone who wrote a description at a customs office, someone who chose a format, someone who abbreviates in a particular way. The technology we build has to be smart enough to understand that variability, not to ignore it.

At Arcetec, we continue to work with companies facing these kinds of challenges: turning scattered, messy data into clear information that drives decisions. If your organization is dealing with data from multiple sources, multiple countries, or multiple formats, we would love to talk.