Loi / Astroselling - Order Audit and Reconciliation ETL

In collaboration with Pro Internacional (prointernacional.com) and Astroselling (astroselling.com), I acted as the sole developer and technical architect on an order audit and reconciliation platform for Loi, an e-commerce brand that sells both on its own site and on marketplaces such as Mercado Livre Brazil.

Loi relied on a multi-channel setup: products were sold on external marketplaces and synchronized with the company's internal ERP (SAP Business One) through integration tools. At the end of each month, however, they consistently found mismatches between Mercado Livre data and SAP B1, which created uncertainty around revenue recognition, stock and performance reporting. They needed a way to audit orders across systems, detect discrepancies and gain visibility into where and why data diverged.

The solution was designed as an ETL-style platform orchestrated with Apache Airflow:

• I implemented extractors for:
  • Mercado Livre, via its public APIs.
  • SAP Business One (SAP B1), using its integration interfaces.
  • Google Sheets, used by the team as an operational source for certain adjustments and reference data.
• All these sources were normalized into a standard order model stored in Postgres, so that orders from different systems could be compared in a consistent way.

On top of the extraction and normalization layer, I implemented load and audit scripts that:

• Persist the consolidated data into Postgres.
• Detect changes across runs (new orders, updates, cancellations, status changes).
• Record those differences explicitly so that every divergence between systems can be traced and audited.

Finally, I created validation stages in the Airflow pipeline that execute SQL-based checks over the normalized data to:

• Identify inconsistencies between Mercado Livre, SAP B1 and the operational data.
• Generate reports of mismatches and anomalies, highlighting orders that require attention.

All of this was exposed to Loi's team through a BI-style dashboard built with Streamlit, where they can:

• See summary metrics of alignment between systems.
• Drill down into problematic orders and discrepancy types.
• Use the platform as a daily and month-end control panel to ensure that marketplace data and ERP records stay synchronized.

This project turned what had been a painful, manual reconciliation process into a repeatable, observable ETL pipeline, giving Loi much stronger confidence in their numbers and a clearer view of how data flows across their marketplaces and internal systems.