EVErica Vaughn

Case study 14 · Data engineering · Pipelines

Store-Level On-Hand Inventory Pipeline

Store-level on-hand inventory for a major retailer’s currently live items. On-prem inventory and item master data get joined to a snapshot of active items, which first meant solving an identifier mismatch.

01 · The challenge

Item numbers on one side. UPCs on the other.

The inventory system keyed on item numbers while the active-item snapshot used UPCs, and the codes were stored in different EAN and UPC formats. Early queries ran for minutes and returned nothing.

02 · What I did

What I owned and built.

  • Built join logic that handles exact matches, 13-digit EANs trimmed to 12-digit UPCs, and UPCs padded with a leading zero.
  • Pre-filtered the snapshot to live items in a CTE and corrected the output to the true store-item grain (no summing).
  • Used small diagnostic queries to confirm code formats before running full queries.
  • Connected Databricks to on-prem SQL Server, translated T-SQL to Databricks SQL, and set the project up in Asset Bundles.
  • Wrote success and failure notification emails for the analytics team and built options for automated query delivery by email.

03 · Architecture

How it fits together.

On-prem SQL Server
Store on-hand · item master
JDBC → Databricks
Active-item snapshot
Pre-filtered in a CTE
Identifier matching
Exact · EAN-13 → UPC-12 · zero-padded
Store × item output
Workflows + email notifications

04 · Key decisions

The judgment behind the build.

Why diagnose with small queries first?

Running the full join to find out the formats don’t match wastes minutes each time. Tiny probes confirmed the formats in seconds.

Why pre-filter in a CTE?

Shrinking the snapshot to live items before the join cut the work dramatically.

Why keep store-item grain?

Summing hides the store-level signal the business actually needs. The right grain is the whole point.

05 · Impact

What changed because of the work.

3identifier match rules
Store × itemtrue output grain
Instantsuccess / failure alerts
  • Accurate store-level on-hand for active items.
  • The analytics team hears right away whether the pipeline succeeded or failed.

06 · What’s next

Where it goes from here.

Keep monitoring, and reuse the matching logic for other retailers.

This case study is a sanitized reconstruction. Organization names, proprietary code, data, and internal schemas are intentionally left out.