How We Implemented Electronic Car Parts Catalogs for an Automotive Supplier

The client has been acquiring electronic parts catalogs for years, spending $5000-50000 per month on each catalog.

Our team accepted the challenge to develop a solution that generates and updates the catalogs from five manufacturers based on source data.

• Each feature was implemented irrespective of the solution as a whole. This happened due to the lack of a thorough approach towards the solution’s architecture;
• It was impossible to locate a specific feature as a mixed bunch of tools and technologies were used on the project: a mix of both cloud/on-premise environments and languages (Python, Perl, SQL, and Bash);
• The documentation was missing.

Each manufacturer has their source data in a specific format - text files, XML, Excel, databases (.MDF), and even EBCDIC - the format used since the 50s on IBM mainframes. The goal was to build ETL pipelines that would transform diverse source data and enable a single set of APIs to query all catalogs.

Stage raw data is imported into MS SQL Database, then cleaned and validated. The stage only contains a set of processed data that is cleared at the beginning of each ETL run. Data Warehouse is a permanent data storage that includes integrated and normalized data sets (not a canonical data warehouse).

Deliverable Database contains data with applied business rules and transformations required to enable access via APIs. In their turn, APIs are implemented as SQL procedures.

Technical Details

1. The ETL process runs once a month for each manufacturer, with the processing time of 1-8 hours.
2. TeamCity is configured to run integrity checks, autotests, and build catalogs based on full data sets.
3. APIs response time is 300ms which required a lot of optimization effort.
4. Autotests cover the ETL process and all APIs implemented on SpecFlow.
5. The largest Deliverable Database is 150Gb.