Data engineering · multi-source normalization

hardware-parts-data-pipeline

Two robotics retailers describe the same motor in two different vocabularies. This pipeline collapses 192 distinct spec labels into one typed schema of 380 products, then loads it into SQLite so re-runs never duplicate.

Public · live retailer data

The problem

You cannot compare parts that are not described the same way.

Pololu writes Stall torque @ 6V and expresses gear ratio as 224:1. Adafruit writes Diameter with rubber tire and states 1:48. One lists torque in oz·in, the other in kg·cm; one gives millimetres, the other millimetres and inches in the same string. Across the two catalogues there are 192 distinct spec field names. Until that is reconciled, "find the best torque per dollar" is not a query — it is a manual afternoon.

Input

The same motor, as the source hands it to you

Solarbotics GM2 224:1 Gear Motor Offset Output   (pololu, raw)
{
  "Size":                 "48.2 × 44.8 × 22.7 mm",
  "Weight":               "32 g",
  "Gear ratio":           "224:1",
  "No-load speed @ 6V":   "46 rpm",
  "No-load current @ 6V": "50 mA",
  "Stall current @ 6V":   "710 mA",
  "Stall torque @ 6V":    "57 oz·in",
  "No-load speed @ 3V":   "24 rpm",     <-- same field, second voltage
  "Stall current @ 3V":   "400 mA"
}

Every dimension is trapped in one string. Every performance figure exists once per voltage. Nothing here is a number a database can sort on.

The money shot

Actual committed output of `normalize.py` and `pipeline.py`

Products in one schema380

Duplicates dropped0

Typed columns26

Spec labels collapsed192

The same motor, after

source              pololu
sku                 180
name                Solarbotics GM2 224:1 Gear Motor Offset Output
brand               Solarbotics
category            Plastic DC Gearmotors
price_usd           12.3
weight_g            32.0
length_mm           48.2      width_mm  44.8      height_mm  22.7
gear_ratio          224.0
rated_voltage_v     6.0       <-- @3V figures dropped; one comparable basis
no_load_speed_rpm   46.0
no_load_current_ma  50.0
stall_current_ma    710.0
stall_torque_ozin   57.0      <-- kg·cm sources converted (x13.8874)
spec_count          11        <-- traceability: raw specs kept in raw_specs_json

Because every product now sits on the same basis, the pipeline can answer a real question. Best stall torque per dollar in the committed catalogue: Solarbotics GM8 143:1 Gear Motor — $12.30 for 76.0 oz·in.

Field coverage

What the merge actually filled in — including where it is thin

Field	Filled	Coverage
sku · name · category	380 / 380	100%
brand	378 / 380	99%
price_usd	378 / 380	99%
weight_g	319 / 380	83%
length_mm · width_mm	303 / 380	79%
shaft_diameter_mm	293 / 380	77%
height_mm	277 / 380	72%
rated_voltage_v · motor performance	270–272 / 380	71%
encoder	195 / 380	51%
stepper fields (NEMA, steps, holding torque)	23–29 / 380	6–7%
status	23 / 380	6%

The low rows are not failures to hide — they are what the sources publish. Stepper-specific fields only apply to the 23–29 steppers in the catalogue, and most listings carry no status label at all. A coverage table you can read is worth more than a schema that looks full.

How it's verified

Re-running is safe, and that is measured, not asserted.

The ETL stage upserts on a stable business key and writes a run log. Running pipeline.py twice in a row on the committed CSVs leaves the products table at 380 rows with 2 entries in run_log — the loads are recorded, the data is not duplicated. Extraction counts are printed each run (310 from Pololu, 70 from Adafruit) so a silent short read is visible immediately.

Check	Actual result
Merge without duplication	380 products, 0 dropped as duplicates
Idempotent load (2 consecutive runs)	products = 380 both times · run_log = 2
Row counts per source	pololu 310 · adafruit 70
Unit harmonization	torque → oz·in · current → mA · dimensions → mm · weight → g
Traceability	original specs preserved in `raw_specs_json`

Honest limitations