Medallion Architecture
A layered data platform built on open-source tools — no vendor lock-in.
🥉
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
Pipeline Orchestration
JDE SQL Server→Node.js Extractor→Bronze (PostgreSQL)→dbt Core (Silver)→dbt Core (Gold)→Fastify API→Next.js Dashboard
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.