PC
Back to projects

Case study / 06

CatalogFlow

Confidence-gated ETL for messy merchant catalog data.

CatalogFlow is a full-stack ETL and data-quality system that transforms inconsistent merchant CSV feeds into canonical product data. It preserves raw evidence, applies deterministic schema mapping and normalization, flags duplicate or ambiguous records for review, and publishes only records allowed by a centralized data-quality policy.

  • Python
  • FastAPI
  • Pydantic
  • Pandas
  • Pandera
  • RapidFuzz
  • SQLModel
  • Alembic
  • React
  • TypeScript
  • Vite
  • Tailwind
  • SQLite / PostgreSQL
  • S3
  • Databricks
  • Delta
GitHub Demo
Deterministic mappingHuman reviewPolicy-gated export

Raw evidence preserved

Original bytes and source values remain replayable and auditable.

Confidence-gated automation

Only sufficiently supported transformations are applied automatically.

Ambiguity is explicit

Low-confidence, duplicate, or invalid records route to review.

One publish policy

Gold data and clean exports use the same publication rules.

System architecture

CatalogFlow architecture showing a React workspace connected to FastAPI orchestration, workflow state, storage and processor ports, Bronze, Silver and Gold layers, human review, and a central publish policy.

System architecture

View full diagram

React and FastAPI orchestrate workflow state, storage, processing, review, and publication across explicit local and production adapters.

FastAPI owns upload, processing-run, review, and export state. The current Databricks job runs the shared Pandas kernel; native distributed Spark transforms remain future work.

Engineering decisions

DecisionWhyTradeoff
Deterministic mapping instead of LLM inferenceExact names, reviewed aliases, and gated fuzzy matches are reproducible and inspectable.Novel semantic column names may remain unresolved.
Transactional workflow state separate from bulk dataReview and batch state need relational transactions; raw and processed artifacts fit object or lakehouse storage.Cross-system consistency must be orchestrated.
Human review as the publication boundaryAmbiguous records stay outside clean output until resolved under canonical validation rules.Publication may depend on reviewer capacity.

Under the hood

Schema + quality

  • Schema profiling and drift detection
  • Exact, alias, and RapidFuzz mapping
  • Pandera and canonical validation

Confidence + review

  • Deterministic weighted confidence
  • Duplicate candidates flagged, never auto-merged
  • Automation evidence remains separate from review

Execution + storage

  • Pandas local processor
  • Databricks production adapter
  • Local / S3 artifacts; SQLite / PostgreSQL state

Technical deep dives

CatalogFlow medallion and publish boundary: merchant CSV flows to Bronze and Silver, then through a central publish policy to Gold and clean CSV while attention-required records remain in review or hold.

Processed does not mean publishable

View full diagram

Bronze preserves the exact source. Silver keeps every processed row and its evidence. The central policy alone determines what reaches Gold and the canonical export.

  • Raw evidence is preserved.
  • Automation confidence is immutable.
  • Review cannot waive invalid canonical data.
  • Gold and clean CSV use the same policy.
CatalogFlow deterministic confidence routing: five evidence components feed a confidence calculation that routes records to auto-approved, needs review, duplicate, or invalid states, followed by human review where appropriate.

Confidence routes automation. Review resolves ambiguity.

View full diagram

Schema mapping, completeness, validity, normalization certainty, and entity evidence produce a deterministic routing score—not a learned probability.

Current heuristics · not calibrated production benchmarksFuzzy candidate 0.72Auto-map 0.90Duplicate 0.88Publish 0.85

Duplicates are never auto-merged. Review may approve, correct, reject, or hold, but it never rewrites the original automation confidence and still must pass validation and policy.

Local ↔ production contract

Local

  • LocalCatalogStorage
  • PandasCatalogProcessor
  • SQLite + local JSONL artifacts
  • Credential-free development
Same deterministic business rules

Production path

  • S3CatalogStorage
  • DatabricksCatalogProcessor
  • PostgreSQL + Delta artifacts
  • Async processing lifecycle

Cloud adapters are covered by tests and mocks; live AWS and Databricks validation still requires an owned environment.

System checks

  • Exact source data is preserved.
  • Silver retains every automation outcome.
  • Gold and export share one publish policy.
  • Duplicate candidates are never auto-merged.
  • Unresolved and invalid records remain excluded.
  • Human review cannot waive canonical validation.
  • Automation confidence remains unchanged after review.
  • Latest-batch queries remain isolated.

Demo data · not a production metric

20processed16initially publishable4requiring attention

Next at scale

Move large-scale transforms to native distributed Spark and calibrate mapping and confidence thresholds against labeled merchant data.