Customer Data Workbench — Design

Status: Draft (under /autoplan review) Branch: claude/modest-swirles-88f861 PR base: staging

Problem

The iterative data-engineering work of cleaning each customer’s raw extracted ERP data into dbt transformations — before crystallizing it into Dagster-orchestrated pipelines — is currently invisible. It lives in one-off scripts, ad-hoc SQL, and tribal knowledge. There is no home for it, it is not tracked, and it is not documented.

Evidence (all real, all from the last quarter):

CK UPC corruption (Excel sci-notation + check-digit loss) — fixed across scattered scripts.
CK two-series collapse — name-bridge pricing + Typesense reconcile, multiple PRs.
CK $0 pricing root cause — days of exploratory querying, documented only in memory.
Pricing-tier→gold projection gap — diagnosed ad hoc, no reproducible artifact.

Each new customer repeats this exploration from scratch with no reusable method.

Goal

Give the data-cleaning stage a first-class workbench with an explicit promotion path, so exploratory cleaning is tracked, reproducible, shareable, and graduates cleanly into production dbt + Dagster.

Non-goals (explicitly out of scope):

Replacing Dagster orchestration or the DLT extraction layer.
Building a new BI/dashboards product.
AR/AP, accounting, or ecommerce data work (that is the deferred platform expansion).

Proposal

A per-customer cleaning workbench with four parts:

Engine + sharing — MotherDuck. Cloud-hosted DuckDB. We already query R2 Parquet with DuckDB in apps/dagster/erp_pipeline/assets/typesense.py, so this hosts the exact engine we run, plus collaboration. Confirmed: SOC2 Type II + GDPR, shares with read replicas, runs in AWS us-east-1 only.
Tracked format — marimo (or jupytext). Notebooks are diffable .py in git, not raw .ipynb. This directly addresses the “not tracked / not documented” complaint.
Bridge — dbt-duckdb adapter. Same SQL dialect runs notebook → dbt → prod. Reads R2 directly via httpfs; MotherDuck connection via path: md:....
Tracking — per-customer MANIFEST.md. Records data-quality issues found, transforms applied, and lifecycle status (exploring / drafted / crystallized).

Lifecycle gradient

Stage	Tool	Artifact	Tracked where
Explore	MotherDuck (shared, live)	scratch queries	MotherDuck share
Draft	marimo + dbt-duckdb (local/MD)	candidate dbt model	git (customer folder)
Crystallize	dbt → R2/Iceberg	prod dbt model	dbt project
Orchestrate	Dagster	asset + schedule	dagster app

A graduation gate between Draft and Crystallize prevents notebook/dbt drift (the “shadow pipeline” risk where prod logic lives in MotherDuck and Dagster never sees it).

Folder shape

data-engineering/
  customers/
    cleveland-kitchen/            # keyed to connection_id(s)
      MANIFEST.md
      01_profile_raw.py           # marimo
      02_clean_products.py        # marimo
      03_draft_dim_products.sql   # candidate dbt model (dbt-duckdb)
      README.md

Tied to onboarding SOP-OB-002-new-erp-integration.md / SOP-OB-003-...build-checklist.md so the new-customer process produces this folder as a deliverable.

Governance

MotherDuck becomes a new data sub-processor over customer ERP data, in AWS us-east-1 — versus our existing R2 (Cloudflare) + Neon + GCP footprint. Requires a DPA and a Drata sub-processor entry before adoption.

Mitigation (built into the design): every notebook must run on plain local DuckDB. MotherDuck is the sharing/compute layer only — not a hard dependency. This keeps adoption incremental and exit cheap (no lock-in).

Pilot

Start with Cleveland Kitchen (richest data-quality history). Prove the explore→crystallize loop on a known problem (e.g. re-derive the UPC overlay or the two-series collapse as a tracked workbench artifact), then templatize the folder for the next customer.

Platform context

Third leg of a four-part story: borrow (docs/integrations/leverage-catalog.md, written) → clean (this workbench) → standardize (order-automation playbook, not yet written) → orchestrate (Dagster).

Stack facts

Monorepo (pnpm). Dagster (apps/dagster/erp_pipeline, Python). DLT → R2 Iceberg bronze/silver/gold. dbt transforms. Typesense for reads. Temporal (apps/ts-temporal-worker) for writes. Nango planned for self-hosted auth.

GSTACK REVIEW REPORT

/autoplan — CEO + Eng + DX (Design skipped: no UI scope). Dual voices: codex + Claude subagent each phase. DX emphasized per request.

Dual-voice consensus

CEO (strategy)

Dimension	Claude	Codex	Consensus
Premises valid?	NO	NO	DISAGREE w/ plan
Right problem?	reframe→primitives	reframe→primitives	CONFIRMED: reframe
Scope calibration?	over-built	over-built	CONFIRMED
Alternatives explored?	NO	NO	CONFIRMED gap
6-month trajectory?	regret risk	regret risk	CONFIRMED risk

Eng (architecture)

Dimension	Claude	Codex	Consensus
Engine/dialect fit	sound (prod IS dbt-duckdb)	overstated (parquet≠iceberg path)	PARTIAL — fixable
Graduation gate enforced?	NO (honor system)	NO (vibes)	CONFIRMED: must automate
Multi-tenant isolation	leak risk	weak (filter≠boundary)	CONFIRMED: per-tenant DB
Reproducibility	data not pinned	diffable≠reproducible	CONFIRMED: pin snapshot
Crystallization trust	no tests	not_null only	CONFIRMED: need real tests

DX (priority)

Dimension	Claude	Codex	Consensus
TTFCT < 15 min?	NO (procurement-gated)	NO (30-60min+)	CONFIRMED fail
Reduces friction vs SQL file?	NO (adds ceremony)	NO	CONFIRMED
MANIFEST won’t rot?	manual=rots	manual=rots	CONFIRMED: derive it
Onboard run <15 min?	NO	NO	CONFIRMED
Highest-leverage change	generator + local-first	drop MD/marimo v1, generator	CONFIRMED

Key correction (Eng, from repo)

Prod dbt is dbt-duckdb reading R2 Iceberg via iceberg_scan + unsafe_enable_version_guessing (apps/dagster/dbt/profiles.yml, dbt/macros/iceberg_source.sql). So the engine choice is right — but the doc’s “read R2 Parquet via httpfs” skips the Iceberg layer prod uses, and gold materializes external parquet not Iceberg. Mirror the prod profile exactly or dev results lie.

Decision Audit Trail

#	Phase	Decision	Class	Principle	Rationale
1	CEO	Reframe to reusable cleaning-primitives library	USER CHALLENGE	P1/P4	Both models critical; workbench makes bespoke work neater not rarer
2	CEO	Defer MotherDuck; local-DuckDB-first	USER CHALLENGE	P3/P4/P5	Both: local does 80%, MD adds sub-processor+DPA+residency for vague “collab”
3	CEO	Sequence behind order-automation playbook	USER CHALLENGE	P2	Both: playbook is revenue-bearing; workbench is internal tooling
4	Eng	Graduation gate = CI check (crystallized⇒committed dbt model + parity test)	AUTO (mechanical)	P1	Both HIGH; unenforced gate rots
5	Eng	Per-customer/connection MD DB or share; never shared multi-tenant	AUTO	P1/security	Both HIGH; filter≠boundary
6	Eng	Crystallize requires dbt tests (golden-output, idempotency, parity)	AUTO	P1	Both HIGH
7	Eng	Pin Iceberg snapshot-id in MANIFEST; iceberg_scan not raw parquet	AUTO	P1/P5	reproducibility + prod parity
8	DX	MANIFEST status derived from filesystem/git, not manual	AUTO	P5	Both HIGH; manual rots
9	DX	Ship generator + one-command run + creds via `op`; local-only happy path	AUTO	P5/P3	Both: make blessed path the lazy path
10	DX	Define outcome metrics (time-to-correct-catalog, defect recurrence, hrs/customer)	AUTO	P1	codex CEO+DX
11	—	marimo notebook layer vs SQL-files-only for v1	TASTE	P5	codex says drop marimo v1; subagent keeps it behind generator

Cross-phase themes (flagged in 2+ phases independently — high confidence)

T1: Unenforced lifecycle decays. CEO (“teams stop at draft”) + Eng (“gate is vibes”) + DX (“MANIFEST rots”). The plan’s two selling points (gate, tracking) are doc conventions with zero enforcement.
T2: MotherDuck is premature. CEO (sub-processor cost) + Eng (tenancy regression) + DX (procurement-gates the pilot). All three say local-DuckDB-first.
T3: Adds ceremony, loses to revealed preference. CEO (entrenches bespoke) + DX (slower than a SQL file). Without a generator that’s faster than ad-hoc, adoption fails.

NOT in scope (confirmed)

Dagster/DLT replacement; BI product; AR/AP/accounting/ecommerce cleaning.

What already exists (leverage)

apps/dagster/dbt/profiles.yml — dbt-duckdb-over-R2-Iceberg is already the prod transform engine. The “bridge” is mostly built.
build_typesense_product_document — existing pure-function + unit-test pattern is the model for “crystallized = tested.”
CK connection-gated UPC override — existing per-connection isolation pattern to follow (not regress).

REVIEW RESOLUTION (approved direction)

User decisions at the /autoplan gate:

Reframe → HYBRID (primitives are the spine). The deliverable is a reusable cleaning-primitives library (UPC repair, sci-notation fix, tier→gold projection, validation tests) that every customer inherits. The per-customer workbench is the authoring surface for that library, not the product. Customer N+1 inherits fixes instead of re-exploring.
Engine → SELF-HOSTED, stack-native (MotherDuck dropped). User is open to any self-hostable workbench that fits the stack. MotherDuck is managed-only (no self-host), so it’s out — which also removes the sub-processor / DPA / Drata / us-east-1 governance burden entirely. Resolved shape:
- Engine: local/embedded DuckDB — already the prod transform engine (apps/dagster/dbt/profiles.yml), inherently self-hosted. Reads shared R2 Iceberg via iceberg_scan + the exact prod profile settings (unsafe_enable_version_guessing, extension_directory) so dev ≠ lies.
- Shared state: the existing R2 Iceberg catalog (shared storage, local compute).
- Optional notebook UI (only if a hosted surface is wanted): self-host marimo (OSS) or JupyterHub — both self-hostable. Default v1 is SQL files + a one-command runner; no hosted UI required.
Sequence → PLAYBOOK FIRST. Write the order-automation playbook next. Build the cleaning-primitives library opportunistically as real per-customer pain surfaces, not speculatively from CK alone. This workbench design is deferred behind the playbook; when resumed, build the v1 below.

Resolved v1 (when resumed, post-playbook)

A data-engineering/ package with: reusable cleaning primitives (Python/SQL) + dbt tests; a generator (workbench new <customer>) that scaffolds a runnable, R2-wired starting point with creds pulled from op at runtime (no secrets in files); one-command run against local DuckDB over R2 Iceberg.
Crystallization is enforced mechanically: a CI check asserting every crystallized entry maps to a committed dbt model + passes golden-output / idempotency / parity tests. No enforcement = no “crystallized.”
MANIFEST status derived from filesystem/git, not hand-maintained.
Per-connection isolation preserved (follow the CK UPC-override pattern; never a shared multi-tenant store).
Outcome metrics defined: time-to-first-correct-catalog, defect recurrence, analyst-hours per customer.

Status: design APPROVED with reframe; implementation DEFERRED behind the order-automation playbook.