SC Data Hub
The Core Idea
One system reads NetSuite — everything else reads from here. The Data Hub eliminates the pattern where every tool independently calls the NetSuite API, competing for rate limits and getting inconsistent snapshots of the same data.
Before the Data Hub, if Pennywise, Hawkeye, and the Platform API all needed work order data, each one called NetSuite separately. They'd fight over rate limits and sometimes see different data depending on when they pulled. The Data Hub creates a single source of truth: one sync, one pipeline, one set of verified data that all consumers share.
Architecture: Three Layers
Data flows through three distinct layers, each with a clear purpose:
| Layer | Purpose | Technology |
|---|---|---|
| Raw | Data exactly as received from NetSuite — no transformations. Acts as an auditable record of what was synced. | Airflow DAGs → PostgreSQL |
| Core | Cleaned, normalized, and business-logic applied. This is where dbt models transform raw data into consistent, typed tables. | dbt SQL models → PostgreSQL |
| Published | Consumer-ready views designed for specific use cases. Versioned so downstream tools don't break when transforms change. | dbt → PostgreSQL views |
What It Syncs
Currently focused on NetSuite data. The Data Hub syncs ~11 entity types on two cadence tiers, all serialized through a single-slot Airflow pool (the platform never occupies more than one NetSuite concurrency slot):
| Entity | Cadence | Rows | Strategy |
|---|---|---|---|
| Work Orders | Every 2h | ~330K | Delta (lastmodifieddate) |
| Work Order Lines | Every 2h | ~1.6M | Delta on parent |
| Item Stock (warehouse) | Every 2h | ~17K | Full pull (snapshot) |
| Corrective Maintenance | Every 2h | ~24 | Delta |
| Compressor Units (assets) | Nightly | ~4K | Delta |
| Customers | Nightly | ~1.4K | Delta |
| Employees | Nightly | ~671 | Delta |
| Items (parts catalog) | Nightly | ~10K | Delta |
| Locations (warehouses) | Nightly | ~50 | Delta |
| Region/Area | Nightly | ~29 | Full pull |
| List Values (labels) | Nightly | ~150 | Full pull |
Sync Pipeline (5 Steps Per Entity)
- 1. Pull — TypeScript sync engine asks NetSuite via SuiteQL: "give me records changed since last successful pull"
- 2. Save as-is — entire record stored in JSONB (new custom fields land automatically instead of being dropped)
- 3. Clean — dbt SQL transforms read raw JSON, write properly typed tables with SC business names
- 4. Publish — further SQL copies clean data into consumer-shaped, denormalized views
- 5. Verify — row counts + key-sets for ops data; penny-exact sums per period x account for GL. Mismatch stops the pipeline — load unverified, watermark held, no downstream transforms run
Deletion Detection
A weekly reconciliation job (Sunday 02:37 CT) pulls full ID lists per entity from NetSuite, diffs against every key ever landed in raw, and records vanished keys in a deletions table. Incomplete keysets abort before diffing — partial data must never mass-flag deletions. Raw data stays append-only; core transforms stamp deleted_at from the facts.
Penny-Exact Verification
Financial data must be exact — not "close enough." The Data Hub includes verification gates:
- Rows inserted must equal NetSuite's reported total per entity, or load fails unverified
- GL verification: penny-exact sums per period x account against NetSuite general ledger
- Mismatch stops the entire pipeline — watermark never advances past an unverified load
- Watermark is derived (never stored) — max verified-complete window per entity; a crashed/partial load cannot advance it
- Full audit trail of every sync and transform run
Consumers
Once data passes through the pipeline and is published, these systems consume it:
| Consumer | What It Reads | Status |
|---|---|---|
| Platform API | Work orders, assets, inventory — replacing direct NetSuite calls | Coming |
| Pennywise | GL data, financial records — replacing direct NetSuite reads | Coming |
Operations & Monitoring
- Airflow dashboard for DAG monitoring, run history, and alerting
- Ops console for pipeline health at a glance
- Automated alerts on sync failures or verification mismatches
- Full run history with timing and row-count metrics
- API key management for consumer authentication
The Data Hub is the foundation for the proactive intelligence vision. When both operational data (from the Platform API) and financial data (from Pennywise) flow through a single, verified pipeline, background agents can couple the two — "battery replacements are trending high" meets "here's the margin impact" — and surface it to the right person.