Query performance
0x
Versus the system it replaced
Loading...
Web Application
PLM Digital Thread — 143K Lines, 430x Faster, Built Solo
Project Context
Role
Sole Architect & Developer
Team
Solo — design, implementation, testing, deployment, documentation
Duration
Apr 2024Mar 2026
24 months
50 days of core implementation, within a 2-year architecture role
Tools
BigQueryGoogle Apps ScriptTerraform
A cloud-native product master data platform for a flagship door product line: 999 product specifications × 58 attributes, unified across 51 BigQuery tables and serving 4 global factories. Designed, implemented, tested, and documented by one person — with queries 430x faster than the system it replaced.
Challenge
Product master data for 999 specifications × 58 attributes lived across scattered spreadsheets, desktop databases, and an aging PLM system. Answering a basic question — 'which products use this part?' — meant opening multiple files and reconciling them by hand. The division needed a single source of truth that four global factories could rely on, with the change history and integrity guarantees that regulated manufacturing demands. Prior attempts had stalled on vendor dependence and unclear requirements — so this time, the prototype had to be built in-house, fast, and to production quality.
Solution
A hub-and-spoke data model: 16 attribute masters normalized around a central specification hub, replacing recursive lookups with O(n+m) joins — the source of the 430x query speedup. Full change history via SCD Type 2 on every master. BOM forward/reverse explosion, 3-way diff, and a constraint-rule engine for valid product variations. The interface stayed in spreadsheets (bidirectional sync with BigQuery), so factory staff needed no new tools. Built test-first: 139 automated tests at 100% pass, and a 5,400-line developer guide so the platform outlives its builder.
Challenge & Solution
Challenge
Product master data for 999 specifications × 58 attributes lived across scattered spreadsheets, desktop databases, and an aging PLM system. Answering a basic question — 'which products use this part?' — meant opening multiple files and reconciling them by hand. The division needed a single source of truth that four global factories could rely on, with the change history and integrity guarantees that regulated manufacturing demands. Prior attempts had stalled on vendor dependence and unclear requirements — so this time, the prototype had to be built in-house, fast, and to production quality.
Solution
A hub-and-spoke data model: 16 attribute masters normalized around a central specification hub, replacing recursive lookups with O(n+m) joins — the source of the 430x query speedup. Full change history via SCD Type 2 on every master. BOM forward/reverse explosion, 3-way diff, and a constraint-rule engine for valid product variations. The interface stayed in spreadsheets (bidirectional sync with BigQuery), so factory staff needed no new tools. Built test-first: 139 automated tests at 100% pass, and a 5,400-line developer guide so the platform outlives its builder.
Design Philosophy
Build the boring guarantees first: normalization, change history, referential integrity, and tests — the glamour features (variation generation, constraint rules) only matter if the foundation never lies. And meet users where they are: a data platform adopted through the spreadsheets people already trust beats a beautiful app nobody opens.
Key Features
Central hub, 16 attribute masters
Product specifications normalized around a single hub — one join path from any attribute to any product, 430x faster than recursive lookups.
BOM explosion & reverse search
Product → parts and part → products in seconds, with multi-condition search and 3-way diff between any two products.
Constraint-rule engine
PROHIBITED / REQUIRED / RECOMMENDED rules validate product variation combinations before they reach production.
Full change lineage
SCD Type 2 history on every master table — every value answerable with 'who changed it, when, from what'.
Technology Stack
Backend
Google Apps ScriptPython (validation & integrity checks)
Database
BigQuery (51 tables)SQL (324 files)
Tools
Terraform (IaC)Cloud Build / Bitbucket Pipelines
Development Process
Two months of data investigation and architecture design, then 50 days of test-driven implementation: red-green-refactor across 8 test suites, shipping one verified capability at a time. AI-assisted development (as early adopter, 2024–2026) compressed the solo timeline — but every architectural decision, from the hub model to SCD Type 2, is human judgment recorded in the commit history. Finished with handover: a 5,400-line developer guide and a 3-day onboarding path for successors.
Results & Metrics
Lines of code
0K
GAS / SQL / Python, built solo
Data model
0 tables
999 specifications × 58 attributes, 4 global factories
Test coverage
0 / 100%
Automated tests, all passing (TDD)