The Business Challenge
In the automotive aftermarket parts industry, SEMA (Specialty Equipment Market Association) is a critical data source — publishing standardized product and vehicle-fitment data that brands rely on for accurate cataloging and compatibility information.
The data itself follows two established industry formats: PIES (Product Information Exchange Standard), covering detailed product attributes, descriptions, digital assets, and pricing; and ACES (Aftermarket Catalog Exchange Standard), covering vehicle fitment — which parts fit which vehicles.
Doing that by hand doesn't scale, and it doesn't fail loudly when it breaks — it fails quietly, in the form of a stale report nobody noticed for three weeks.
The Solution at a Glance
We built a fully automated pipeline using Oracle Data Integrator (ODI), landing data into Oracle Autonomous Data Warehouse (ADW). The goal wasn't just "move files from A to B" — it was to build something that could run unattended, tell you clearly when something needed attention, and never lose data even when something went wrong mid-run.
Technical Design
Ingestion and classification. The pipeline retrieves every available file from the SFTP server, automatically identifies which brand and which data standard (PIES or ACES) each one belongs to, and registers it in a control layer that tracks every file's status from arrival through completion. Nothing about routing is hardcoded — which file goes to which destination, what counts as a valid file, what a duplicate looks like — all of it lives in configuration, not code. That's a deliberate architectural choice: adding a new data source later should mean updating a table, not shipping new logic.
Per-file failure isolation. With multiple brands' data flowing through the same pipeline each cycle, one design decision mattered more than any other: a problem with one file should never stop the rest. Each file is processed independently — validated, and if needed, automatically repaired for known data-quality issues (like rows that arrive split across multiple lines) — so a single malformed file gets flagged on its own, while everything else completes normally.
Duplicate detection. Source systems don't always send only new data — sometimes the same file gets re-delivered with no actual changes. Rather than silently reprocessing identical content every cycle, the pipeline checks incoming files against recently completed ones and skips true duplicates, while still keeping the audit trail and archiving process intact for them.
A two-phase design, built for safe retries. This is the detail that separates a pipeline that merely works from one that's actually production-grade: what happens when a run fails halfway through? We split processing into two distinct phases — everything gets extracted, validated, and loaded first; only after the entire batch succeeds does the pipeline begin archiving source files. Get that sequencing wrong, and a mid-run failure can permanently delete source files you can never recover. Get it right, and "fix the bug, re-run the job" is always a completely safe operation, with nothing lost regardless of where the previous run stopped.
Full historical tracking with SCD Type 2. Loading current data isn't enough — the business needed to know not just what a value is today, but what it used to be and when it changed. We implemented Slowly Changing Dimension (Type 2) logic across the historical tables, so every tracked attribute keeps a complete, queryable version history rather than just overwriting the last known value.
Observability by design, not as an afterthought. Every failure, anywhere in the pipeline, is captured two ways — logged to a permanent, queryable record, and sent as an immediate alert with enough detail to act on without digging through execution logs. And access to the finished data is scoped through role-based permissions, so downstream consumers get exactly the data they need without broader access to unrelated systems.
The Result
What used to require manual file handling, brand-by-brand, is now a scheduled process that runs unattended, self-validates, maintains full change history, and tells the team immediately if anything needs a human look — with nothing about its design assuming the happy path is the only path.
If you're facing a similar manual data-integration challenge and want to talk through how an approach like this could apply to your environment, I'd be glad to connect.
#OracleDataIntegrator #OracleADW #DataEngineering #ETL #DataArchitecture #AutomotiveAftermarket #SEMA #CloudDataWarehouse
Sharing the architecture and key design decisions in the deck below. If you're looking to implement something similar, happy to talk.
https://www.bizinsightinc.com/contactus
Slide show is available at our LinkedIn page: https://tinyurl.com/u77hfx3b
