Gold Data Warehouse Models Reference

The Gold Layer is the curated, business-level analytical tier of Ordermatic’s data warehouse. Built by dbt on top of Silver-layer normalized tables, Gold models are materialized as columnar Parquet files in Cloudflare R2 (or local disk on DGX appliances) and partitioned by tenant (connection_id).

These models form the primary semantic knowledge base queried by Gopher, our embedded DuckDB engine, and downstream partner APIs.

s3://{R2_DATA_CATALOG_BUCKET}/gold/
├── dim_customers/
│   └── connection_id={connection_id}/data.parquet
├── dim_orders/
│   └── connection_id={connection_id}/data.parquet
├── dim_products/
│   └── connection_id={connection_id}/data.parquet
├── fact_order_lines/
│   └── connection_id={connection_id}/data.parquet
├── fact_ar_invoices/
│   └── connection_id={connection_id}/data.parquet
├── fact_ar_aging/
│   └── connection_id={connection_id}/data.parquet
├── fact_dormancy/
│   └── connection_id={connection_id}/data.parquet
├── fact_erp_adoption/
│   └── connection_id={connection_id}/data.parquet
└── fact_price_tier_variance/
    └── connection_id={connection_id}/data.parquet

Model Catalog

1. `dim_customers`

Description: Canonical customer dimension enriched with transaction recency and order counts.

Column	Type	Description
`connection_id`	`VARCHAR`	Partition key / ERP connection identifier
`erp_customer_id`	`VARCHAR`	ERP account identifier
`name`	`VARCHAR`	Primary customer account name
`email`	`VARCHAR`	Billing / order contact email
`company_id`	`VARCHAR`	ERP operating entity / company code
`company_name`	`VARCHAR`	ERP operating company name
`pricing_method_code`	`VARCHAR`	Pricing matrix / agreement tier code
`order_count`	`BIGINT`	Total historical orders placed by account
`last_order_date`	`TIMESTAMP`	Timestamp of most recent order

2. `dim_orders`

Description: Order header records representing confirmed, submitted, or in-flight ERP sales orders.

Column	Type	Description
`connection_id`	`VARCHAR`	Partition key / ERP connection identifier
`order_no`	`VARCHAR`	ERP sales order number
`customer_id`	`VARCHAR`	Foreign key to `dim_customers.erp_customer_id`
`company_id`	`VARCHAR`	Operating company ID
`order_date`	`TIMESTAMP`	Timestamp order was booked in ERP
`total_amount`	`DECIMAL(12,2)`	Total net value of order
`status`	`VARCHAR`	ERP processing status

3. `dim_products`

Description: Comprehensive product catalog including SKUs, descriptions, units of measure, standard costs, and moving average costs.

Column	Type	Description
`connection_id`	`VARCHAR`	Partition key / ERP connection identifier
`erp_product_id`	`VARCHAR`	ERP item identifier / item code
`sku`	`VARCHAR`	Stock keeping unit
`name`	`VARCHAR`	Short item title
`description`	`VARCHAR`	Full line-item product description
`upc`	`VARCHAR`	Universal Product Code / barcode
`unit_of_measure`	`VARCHAR`	Standard selling UOM (EA, CS, PL)
`standard_cost`	`DECIMAL(12,4)`	Location-level standard replacement cost
`moving_average_cost`	`DECIMAL(12,4)`	Real-time moving average inventory cost
`avg_selling_price`	`DECIMAL(12,4)`	Historical quantity-weighted average selling price
`margin_pct`	`DECIMAL(5,2)`	Current gross margin percentage

4. `fact_order_lines`

Description: Individual order line items capturing quantities, unit pricing, unit cost, and extended values.

Column	Type	Description
`connection_id`	`VARCHAR`	Partition key / ERP connection identifier
`order_no`	`VARCHAR`	Sales order number
`line_no`	`INTEGER`	Order line item position
`order_date`	`TIMESTAMP`	Order header booking date
`customer_id`	`VARCHAR`	Customer ID
`item_id`	`VARCHAR`	Item as written on the line; E4 includes the revision (`S19720-4 Rev A`). Join and filter on `base_item_id`
`base_item_id`	`VARCHAR`	`item_id` minus any E4 revision suffix; join key to `dim_products.erp_product_id`
`qty_ordered`	`DECIMAL(12,4)`	Quantity ordered on PO
`qty_shipped`	`DECIMAL(12,4)`	Quantity fulfilled
`unit_price`	`DECIMAL(12,4)`	Invoiced price per unit
`unit_cost`	`DECIMAL(12,4)`	Cost basis at time of order
`extended_price`	`DECIMAL(12,2)`	Total line value (`qty_ordered * unit_price`)
`order_total`	`DECIMAL(12,2)`	Header order total

5. `fact_ar_invoices` & `fact_ar_aging`

Description: Accounts receivable and outstanding unpaid invoices aged by days past due.

Column	Type	Description
`connection_id`	`VARCHAR`	Partition key / ERP connection identifier
`invoice_no`	`VARCHAR`	ERP invoice identifier
`customer_id`	`VARCHAR`	Customer ID
`balance_due`	`DECIMAL(12,2)`	Remaining unpaid balance
`due_date`	`TIMESTAMP`	Net payment terms due date
`days_open`	`INTEGER`	Days past due (negative if not yet due)
`current_amount`	`DECIMAL(12,2)`	Open balance <= 30 days
`aging_31_60`	`DECIMAL(12,2)`	Open balance 31–60 days past due
`aging_61_90`	`DECIMAL(12,2)`	Open balance 61–90 days past due
`aging_90_plus`	`DECIMAL(12,2)`	Seriously delinquent balance > 90 days
`total_balance`	`DECIMAL(12,2)`	Total open receivable across aging buckets

6. `fact_dormancy`

Description: Account recency model flagging production customers that have stopped submitting purchase orders or whose order volume has dropped below threshold.

Column	Type	Description
`clerk_organization_id`	`VARCHAR`	Customer organization identifier
`connection_id`	`VARCHAR`	ERP connection identifier
`erp_type`	`VARCHAR`	Underlying ERP platform (P21, WhereFour, etc.)
`days_since_last_usage`	`INTEGER`	Trailing days elapsed since last billable order
`is_dormant_7d`	`BOOLEAN`	True if zero orders in the trailing 7 days
`is_dormant_30d`	`BOOLEAN`	True if zero orders in the trailing 30 days

7. `fact_erp_adoption`

Description: Weekly adoption metrics tracking the proportion of total booked ERP orders and dollar volume automated through Ordermatic.

Column	Type	Description
`week`	`DATE`	Beginning of the calendar week (Monday)
`connection_id`	`VARCHAR`	Partition key / ERP connection identifier
`erp_type`	`VARCHAR`	Underlying ERP platform (P21, WhereFour, etc.)
`erp_orders`	`BIGINT`	Total order headers booked directly in the ERP
`erp_order_value`	`DECIMAL`	Total monetary value of all booked ERP orders
`ordermatic_orders`	`BIGINT`	Orders created and submitted via Ordermatic
`ordermatic_value`	`DECIMAL`	Total monetary value of Ordermatic-processed orders
`order_adoption_rate`	`DOUBLE`	Ratio of Ordermatic orders to total ERP orders (`0.0` - `1.0`)
`value_adoption_rate`	`DOUBLE`	Ratio of Ordermatic order value to total ERP order value

8. `fact_price_tier_variance`

Description: Pricing discrepancy audit model identifying line items invoiced below agreed contractual tiers.

Column	Type	Description
`connection_id`	`VARCHAR`	Partition key / ERP connection identifier
`order_no`	`VARCHAR`	Order identifier
`line_no`	`INTEGER`	Line item index
`item_id`	`VARCHAR`	Item code
`unit_price`	`DECIMAL(12,4)`	Actual unit price on order
`expected_price`	`DECIMAL(12,4)`	Contractually agreed minimum tier price
`variance_pct`	`DECIMAL(5,2)`	Percentage difference (`(expected - actual) / expected * 100`)
`lost_margin`	`DECIMAL(12,2)`	Estimated lost revenue on line

Multi-Tenant Partitioning Rules

Hive Directory Conventions: Models materialized as external must format output locations as:

location=(
  var('connection_id', none) is not none
    and 's3://' ~ env_var('R2_DATA_CATALOG_BUCKET', 'erp-data-catalog') ~ '/gold/<model>/connection_id=' ~ var('connection_id') ~ '/data.parquet'
) or none

Pruning Performance: Partitioning directly on connection_id allows DuckDB’s vectorized Parquet reader to skip 99%+ of non-target tenant row groups during analytical excavations.
Physical Isolation: Multi-tenant queries in Gopher mount session-scoped views targeting only the authenticated customer’s partition directory, ensuring zero data leakage.

Gold Data Warehouse Models Reference

Gold Data Warehouse Models Reference

Model Catalog

1. dim_customers

2. dim_orders

3. dim_products

4. fact_order_lines

5. fact_ar_invoices & fact_ar_aging

6. fact_dormancy

7. fact_erp_adoption

8. fact_price_tier_variance

Multi-Tenant Partitioning Rules

1. `dim_customers`

2. `dim_orders`

3. `dim_products`

4. `fact_order_lines`

5. `fact_ar_invoices` & `fact_ar_aging`

6. `fact_dormancy`

7. `fact_erp_adoption`

8. `fact_price_tier_variance`