Payments & Card-Fraud Analytics data model
Kimball warehouse for payments and fraud: payment-transaction and authorization facts, merchant settlement, an accumulating chargeback snapshot, and fraud alerts over conformed customer, card, merchant, device, and fraud-rule dimensions — built for approval rates, chargeback ratios, and fraud-model performance.
Payments and card-fraud analytics covering multi-rail payment transactions, card authorizations with real-time fraud scoring, merchant settlement, the chargeback/dispute lifecycle, and fraud alerting — a Kimball star schema with conformed customer, card, and merchant dimensions where the PAN is tokenized and masked for PCI-DSS. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 13
- tables
- 115
- columns
- 22
- 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: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Tokenized card, never the raw PAN
PCI-DSS forbids storing the primary account number in the clear; the card dimension holds a token (restricted, fully masked) plus the non-sensitive BIN and last-four.
Authorization separate from settled transaction
Authorizations include declines and never-settled holds; keeping them distinct from settled payments supports approval-rate and decline analysis and real-time fraud scoring.
Accumulating snapshot for chargebacks
A dispute progresses initiated → represented → won/lost with shifting liability; an accumulating snapshot captures resolution time and outcome for chargeback-ratio monitoring.
Tables in the Payments & Card-Fraud 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 (5)
fct_payment_transaction
Grain: one row per payment transaction
Transaction-grain payment event across rails.
| Column | Type | Keys |
|---|---|---|
| payment_transaction_key | bigint | PK |
| date_key | bigint | |
| customer_key | bigint | |
| card_key | bigint | |
| merchant_key | bigint | |
| payment_method_key | bigint | |
| channel_key | bigint | |
| device_key | bigint | |
| transaction_id | varchar | |
| amount | decimal | |
| amount_usd_equivalent | decimal | |
| currency | varchar | |
| is_cross_border | boolean | |
| is_recurring | boolean |
fct_authorization
Grain: one row per authorization request
Authorization-grain fact — approval and real-time fraud scoring.
| Column | Type | Keys |
|---|---|---|
| authorization_key | bigint | PK |
| date_key | bigint | |
| card_key | bigint | |
| merchant_key | bigint | |
| channel_key | bigint | |
| device_key | bigint | |
| authorization_id | varchar | |
| authorization_amount | decimal | |
| currency | varchar | |
| is_approved | boolean | |
| response_code | varchar | |
| decline_reason | varchar | |
| fraud_score | decimal |
fct_settlement
Grain: one row per merchant settlement batch line
Merchant settlement — daily batch funding.
| Column | Type | Keys |
|---|---|---|
| settlement_key | bigint | PK |
| date_key | bigint | |
| merchant_key | bigint | |
| batch_id | varchar | |
| transaction_count | integer | |
| gross_amount | decimal | |
| interchange_fee | decimal | |
| processing_fee | decimal | |
| net_amount | decimal | |
| currency | varchar | |
| settlement_status | varchar |
fct_chargeback
Grain: one row per chargeback / dispute (accumulating snapshot)
Accumulating snapshot of the dispute lifecycle.
| Column | Type | Keys |
|---|---|---|
| chargeback_key | bigint | PK |
| date_key | bigint | |
| card_key | bigint | |
| merchant_key | bigint | |
| resolution_date_key | bigint | |
| chargeback_id | varchar | |
| reason_code | varchar | |
| dispute_amount | decimal | |
| currency | varchar | |
| dispute_status | varchar | |
| liability | varchar | |
| days_to_resolution | integer |
fct_fraud_alert
Grain: one row per fraud alert
Fraud alerts and confirmed-loss outcomes for model tuning.
| Column | Type | Keys |
|---|---|---|
| fraud_alert_key | bigint | PK |
| date_key | bigint | |
| customer_key | bigint | |
| card_key | bigint | |
| fraud_rule_key | bigint | |
| device_key | bigint | |
| alert_id | varchar | |
| alert_score | decimal | |
| alert_status | varchar | |
| disposition | varchar | |
| exposure_amount | decimal | |
| confirmed_loss_amount | decimal |
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 | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | 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 |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| country | varchar | |
| risk_segment | varchar | |
| account_tenure_days | integer | |
| is_active | boolean |
dim_card
Grain: one row per card per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| card_key | bigint | PK |
| card_id | varchar | BK |
| card_token | varchar | PII |
| card_bin | varchar | |
| last_four | varchar | PII |
| card_type | varchar | |
| card_network | varchar | |
| issue_date | date | |
| expiry_date | date | |
| card_status | varchar |
dim_merchant
Grain: one row per merchant
| Column | Type | Keys |
|---|---|---|
| merchant_key | bigint | PK |
| merchant_id | varchar | BK |
| merchant_name | varchar | |
| mcc | varchar | |
| merchant_category | varchar | |
| acquirer_name | varchar | |
| country | varchar | |
| risk_level | varchar |
dim_device
Grain: one row per device fingerprint
| Column | Type | Keys |
|---|---|---|
| device_key | bigint | PK |
| device_id | varchar | BK |
| device_type | varchar | |
| operating_system | varchar | |
| ip_country | varchar | |
| is_trusted | boolean |
Lookup tables (3)
dim_payment_method
Grain: one row per payment method / rail
| Column | Type | Keys |
|---|---|---|
| payment_method_key | bigint | PK |
| method_code | varchar | BK |
| method_name | varchar | |
| is_real_time | boolean |
dim_channel
Grain: one row per channel
| Column | Type | Keys |
|---|---|---|
| channel_key | bigint | PK |
| channel_code | varchar | BK |
| channel_name | varchar | |
| is_card_present | boolean |
dim_fraud_rule
Grain: one row per fraud detection rule
| Column | Type | Keys |
|---|---|---|
| fraud_rule_key | bigint | PK |
| rule_code | varchar | BK |
| rule_name | varchar | |
| rule_category | varchar | |
| default_severity | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_payment_transaction.date_key → dim_date (many-to-one)
- fct_payment_transaction.customer_key → dim_customer (many-to-one)
- fct_payment_transaction.card_key → dim_card (many-to-one)
- fct_payment_transaction.merchant_key → dim_merchant (many-to-one)
- fct_payment_transaction.payment_method_key → dim_payment_method (many-to-one)
- fct_payment_transaction.channel_key → dim_channel (many-to-one)
- fct_payment_transaction.device_key → dim_device (many-to-one)
- fct_authorization.date_key → dim_date (many-to-one)
- fct_authorization.card_key → dim_card (many-to-one)
- fct_authorization.merchant_key → dim_merchant (many-to-one)
- fct_authorization.channel_key → dim_channel (many-to-one)
- fct_authorization.device_key → dim_device (many-to-one)
- fct_settlement.date_key → dim_date (many-to-one)
- fct_settlement.merchant_key → dim_merchant (many-to-one)
- fct_chargeback.date_key → dim_date (many-to-one)
- fct_chargeback.card_key → dim_card (many-to-one)
- fct_chargeback.merchant_key → dim_merchant (many-to-one)
- fct_fraud_alert.date_key → dim_date (many-to-one)
- fct_fraud_alert.customer_key → dim_customer (many-to-one)
- fct_fraud_alert.card_key → dim_card (many-to-one)
- fct_fraud_alert.fraud_rule_key → dim_fraud_rule (many-to-one)
- fct_fraud_alert.device_key → dim_device (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.