Accounts Payable Analytics data model
Kimball warehouse for accounts payable: an accumulating-snapshot invoice fact (received → approved → paid milestone date roles, DPO and aging measures) plus a transaction-grain payment fact over conformed vendor/account/cost-center/entity/payment-terms dimensions. Vendor tax IDs and bank account numbers are masked.
Procure-to-pay accounts-payable analytics covering the vendor-invoice lifecycle from receipt through approval to payment, and the resulting disbursements, across a multi-entity, multi-currency ledger. A Kimball star schema with a conformed SCD2 vendor dimension and account, cost-center, legal-entity, and payment-terms dimensions supporting DPO, aging, and early-pay discount-capture analysis. Vendor tax IDs and bank accounts are classified restricted and masked. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 9
- tables
- 94
- columns
- 11
- relationships
- 3
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Accumulating snapshot for the invoice lifecycle
An AP invoice passes through well-defined milestones (received, approved, due, paid) and we update the same row as it progresses; Kimball's accumulating-snapshot pattern with multiple date-role keys is the canonical fit and yields lag measures like DPO directly.
Separate transaction-grain payment fact
Payments are a distinct business process with their own grain (one disbursement, possibly settling several invoices); modeling them as a separate fact avoids overloading the invoice grain and supports cash-outflow and method-mix reporting.
Date-role keys carried as bigint FKs to a single dim_date
Each milestone (received/approved/due/paid) is a role-playing view of the conformed date dimension; we keep one physical dim_date and one declared relationship, exposing other roles as commented FK columns per the catalog convention.
SCD2 on the vendor dimension
Vendor attributes such as remit-to country, terms eligibility, and diversity classification change over time; type-2 history preserves the as-was vendor profile for accurate period spend and 1099 reporting under SOX.
Tables in the Accounts Payable Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (2)
fct_ap_invoice
Grain: one row per AP invoice (accumulating snapshot: received → approved → paid)
Accumulating snapshot tracking the invoice lifecycle for DPO and discount-capture.
| Column | Type | Keys |
|---|---|---|
| ap_invoice_key | bigint | PK |
| vendor_key | bigint | |
| account_key | bigint | |
| cost_center_key | bigint | |
| entity_key | bigint | |
| payment_terms_key | bigint | |
| invoice_date_key | bigint | |
| received_date_key | bigint | |
| approved_date_key | bigint | |
| due_date_key | bigint | |
| paid_date_key | bigint | |
| invoice_number | varchar | |
| po_number | varchar | |
| currency | varchar | |
| invoice_amount | decimal | |
| tax_amount | decimal | |
| discount_available | decimal | |
| discount_taken | decimal | |
| amount_paid | decimal | |
| amount_outstanding | decimal | |
| days_to_approve | integer | |
| days_payable_outstanding | integer | |
| days_aging | integer | |
| is_disputed | boolean | |
| is_paid | boolean | |
| is_paid_late | boolean | |
| invoice_status | varchar |
fct_ap_payment
Grain: one row per AP payment disbursement line
Transaction-grain disbursement fact — one row per payment applied to a vendor.
| Column | Type | Keys |
|---|---|---|
| ap_payment_key | bigint | PK |
| date_key | bigint | |
| vendor_key | bigint | |
| account_key | bigint | |
| entity_key | bigint | |
| payment_method_key | bigint | |
| payment_id | varchar | |
| invoice_number | varchar | |
| currency | varchar | |
| payment_amount | decimal | |
| discount_taken | decimal | |
| withholding_amount | decimal | |
| exchange_rate | decimal | |
| payment_status | varchar | |
| is_early_payment | boolean |
Dimension tables (5)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| fiscal_year | smallint | |
| fiscal_period | smallint | |
| is_business_day | boolean | |
| is_period_end | boolean |
dim_vendor
Grain: one row per vendor per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| vendor_key | bigint | PK |
| vendor_id | varchar | BK |
| vendor_name | varchar | |
| tax_id | varchar | PII |
| vendor_category | varchar | |
| vendor_status | varchar | |
| bank_account_number | varchar | PII |
| remit_to_country | varchar | |
| state | varchar | |
| is_1099 | boolean | |
| is_preferred | boolean | |
| diversity_classification | varchar |
dim_account
Grain: one row per GL account per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| account_id | varchar | BK |
| account_number | varchar | |
| account_name | varchar | |
| account_type | varchar | |
| statement_section | varchar | |
| is_active | boolean |
dim_cost_center
Grain: one row per cost center per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| cost_center_key | bigint | PK |
| cost_center_id | varchar | BK |
| cost_center_name | varchar | |
| cost_center_type | varchar | |
| manager_name | varchar | PII |
| region | varchar | |
| parent_cost_center_id | varchar |
dim_entity
Grain: one row per legal entity per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| entity_key | bigint | PK |
| entity_id | varchar | BK |
| entity_name | varchar | |
| entity_type | varchar | |
| country | varchar | |
| functional_currency | varchar | |
| tax_jurisdiction | varchar |
Lookup tables (2)
dim_payment_terms
Grain: one row per payment-terms code
| Column | Type | Keys |
|---|---|---|
| payment_terms_key | bigint | PK |
| terms_code | varchar | BK |
| terms_name | varchar | |
| net_due_days | smallint | |
| discount_percent | decimal | |
| discount_days | smallint |
dim_payment_method
Grain: one row per disbursement method
| Column | Type | Keys |
|---|---|---|
| payment_method_key | bigint | PK |
| method_code | varchar | BK |
| method_name | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_ap_invoice.invoice_date_key → dim_date (many-to-one)
- fct_ap_invoice.vendor_key → dim_vendor (many-to-one)
- fct_ap_invoice.account_key → dim_account (many-to-one)
- fct_ap_invoice.cost_center_key → dim_cost_center (many-to-one)
- fct_ap_invoice.entity_key → dim_entity (many-to-one)
- fct_ap_invoice.payment_terms_key → dim_payment_terms (many-to-one)
- fct_ap_payment.date_key → dim_date (many-to-one)
- fct_ap_payment.vendor_key → dim_vendor (many-to-one)
- fct_ap_payment.account_key → dim_account (many-to-one)
- fct_ap_payment.entity_key → dim_entity (many-to-one)
- fct_ap_payment.payment_method_key → dim_payment_method (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.