Accounts Receivable Analytics data model
Kimball warehouse for accounts receivable: an accumulating-snapshot invoice fact (issued → due → paid roles, DSO and aging measures), a transaction-grain receipt fact, and a monthly aging periodic snapshot over conformed customer/account/sales-rep/entity dimensions. Customer tax IDs and contact details are masked.
Order-to-cash accounts-receivable analytics covering the customer-invoice lifecycle from issuance through payment, cash-receipt application, and a monthly AR aging snapshot across a multi-entity, multi-currency ledger. A Kimball star schema with a conformed SCD2 customer dimension and account, sales-rep, and legal-entity dimensions supporting DSO, aging buckets, and collections-effectiveness analysis. Customer tax IDs and contact data are classified 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
- 101
- columns
- 13
- relationships
- 6
- 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 AR invoice has well-defined milestones (issued, due, paid) updated on the same row as it ages; Kimball's accumulating-snapshot pattern with multiple date-role keys yields DSO and days-aging lag measures directly.
Separate monthly AR aging periodic snapshot
Aging balances are semi-additive (sum across customers, not across months); a periodic snapshot at customer/entity/month grain gives fast aging-bucket and reserve reporting independent of invoice volume.
Distinct transaction-grain receipt fact
Cash receipts are a separate process whose grain (one application of cash) differs from invoice grain; a dedicated fact supports cash-inflow forecasting and short-payment analysis without overloading the invoice grain.
SCD2 on the customer dimension
Credit rating, collections status, and segment change over time; type-2 history preserves the as-was customer profile so historical aging and exposure are computed against the policy in force at the time, as required for SOX-compliant reserve estimates.
Tables in the Accounts Receivable 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 (3)
fct_ar_invoice
Grain: one row per AR invoice (accumulating snapshot: issued → due → paid)
Accumulating snapshot tracking the receivable lifecycle for DSO and collections.
| Column | Type | Keys |
|---|---|---|
| ar_invoice_key | bigint | PK |
| customer_key | bigint | |
| account_key | bigint | |
| sales_rep_key | bigint | |
| entity_key | bigint | |
| payment_terms_key | bigint | |
| invoice_date_key | bigint | |
| due_date_key | bigint | |
| paid_date_key | bigint | |
| invoice_number | varchar | |
| sales_order_number | varchar | |
| currency | varchar | |
| invoice_amount | decimal | |
| tax_amount | decimal | |
| amount_received | decimal | |
| amount_outstanding | decimal | |
| write_off_amount | decimal | |
| days_sales_outstanding | integer | |
| days_aging | integer | |
| aging_bucket | varchar | |
| is_paid | boolean | |
| is_paid_late | boolean | |
| is_disputed | boolean | |
| invoice_status | varchar |
fct_ar_receipt
Grain: one row per cash-receipt application line
Transaction-grain receipt fact — one row per cash applied to a customer invoice.
| Column | Type | Keys |
|---|---|---|
| ar_receipt_key | bigint | PK |
| date_key | bigint | |
| customer_key | bigint | |
| account_key | bigint | |
| entity_key | bigint | |
| receipt_id | varchar | |
| invoice_number | varchar | |
| currency | varchar | |
| receipt_amount | decimal | |
| discount_allowed | decimal | |
| write_off_amount | decimal | |
| exchange_rate | decimal | |
| receipt_method | varchar | |
| is_short_payment | boolean |
fct_ar_aging_snapshot
Grain: one row per customer per entity per month-end (periodic snapshot)
Monthly periodic snapshot of AR aging — balances are semi-additive across months.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| customer_key | bigint | |
| entity_key | bigint | |
| total_outstanding | decimal | |
| balance_current | decimal | |
| balance_1_30 | decimal | |
| balance_31_60 | decimal | |
| balance_61_90 | decimal | |
| balance_over_90 | decimal | |
| balance_disputed | decimal | |
| open_invoice_count | integer | |
| weighted_avg_days_outstanding | integer | |
| collections_status | varchar |
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_customer
Grain: one row per customer per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_id | varchar | BK |
| customer_name | varchar | |
| tax_id | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| customer_segment | varchar | |
| industry | varchar | |
| credit_rating | varchar | |
| credit_limit | decimal | |
| billing_country | varchar | |
| state | varchar | |
| collections_status | varchar | |
| is_active | boolean |
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_sales_rep
Grain: one row per sales representative per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| sales_rep_key | bigint | PK |
| sales_rep_id | varchar | BK |
| sales_rep_name | varchar | PII |
| varchar | PII | |
| sales_team | varchar | |
| region | varchar | |
| manager_name | varchar | PII |
| is_active | boolean |
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 (1)
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 |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_ar_invoice.invoice_date_key → dim_date (many-to-one)
- fct_ar_invoice.customer_key → dim_customer (many-to-one)
- fct_ar_invoice.account_key → dim_account (many-to-one)
- fct_ar_invoice.sales_rep_key → dim_sales_rep (many-to-one)
- fct_ar_invoice.entity_key → dim_entity (many-to-one)
- fct_ar_invoice.payment_terms_key → dim_payment_terms (many-to-one)
- fct_ar_receipt.date_key → dim_date (many-to-one)
- fct_ar_receipt.customer_key → dim_customer (many-to-one)
- fct_ar_receipt.account_key → dim_account (many-to-one)
- fct_ar_receipt.entity_key → dim_entity (many-to-one)
- fct_ar_aging_snapshot.date_key → dim_date (many-to-one)
- fct_ar_aging_snapshot.customer_key → dim_customer (many-to-one)
- fct_ar_aging_snapshot.entity_key → dim_entity (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.