Medallion Architecture

A layered data platform built on open-source tools. Data flows from ERP source systems through extraction, transformation, and aggregation into live applications.

JDE SQL ServerF0101, F4201, F4211...Node.jsExtractorsBronzePostgreSQLRaw 1:1 copy10 JDE tablesSilverdbt CoreCleaned, typedBusiness vocabularyGolddbt CoreKimball star schema6 Gold modelsFastify APIREST endpointsDashboardNext.jsStatus BoardsShip / ReceiveCustomer PortalSelf-serviceE-CommerceStripe + ClerkApache AirflowNightly orchestration (Mon-Sat 2AM) · Full refresh SundayMDM — Splink Record Linkage5 ERPs → Bronze → Silver → Splink matching→ 251 golden customer records82 duplicates resolved from 333 sourcesOpen-source · No vendor lock-in · All code on GitHub
🥉

Bronze Layer

Raw data extracted directly from JDE SQL Server. No transformations — exact copy of source.

F0101 — Address Book
F0116 — Address by Date
F03B11 — AR Invoices
F4101 — Item Master
F4102 — Item Branch
F41021 — Item Location
F4201 — SO Header
F4211 — SO Detail
F4301 — PO Header
F4311 — PO Detail
🥈

Silver Layer

Cleaned, typed, and deduplicated. JDE Julian dates converted, columns renamed to business vocabulary.

address_book
address_by_date
ar_invoices
item_master
item_branch
item_location
sales_order_header
sales_order_detail
purchase_order_header
purchase_order_detail
🥇

Gold Layer

Business-ready aggregations for dashboards, reports, and the RFQ portal. Optimized for query performance.

sales_by_customer
ar_aging
inventory_status
purchasing_by_vendor
shipment_status
receiving_status

Scheduling

Apache Airflow orchestrates the full pipeline nightly (Mon–Sat at 2AM) and a full refresh every Sunday. Each layer only runs if the previous succeeds.

Design Principles

Open-source only. No vendor lock-in. Entity-first naming conventions. Surrogate keys with _key suffix. Kimball dimensional modeling in Gold.