FinLearn Database Documentation

1business schema

14base tables

4analytics views

1,075,526loaded rows

2022-01-01first activity date

2026-08-31latest seeded activity date

Schema Purpose

finlearn models a midsize retail financial institution. It includes customers, deposit accounts, cards, loans, transactions, risk history, support interactions, marketing campaigns, and exchange rates. The data is designed for SQL learning, dashboard development, segmentation, cohort analysis, operational reporting, and introductory risk analytics.

Core grain examples: one row per customer in customers, one row per account in accounts, one row per posted or attempted money movement in account_transactions, one row per scheduled loan installment in loan_payments, and one row per customer-campaign send in campaign_responses.

Data Model

customersCentral party table for households and consumers.

accountsDeposit and brokerage accounts owned by customers.

account_transactionsHigh-volume money movement fact table for account activity.

loansLoan accounts for credit exposure and delinquency analysis.

loan_paymentsMonthly loan payment performance history.

credit_score_historySemiannual bureau snapshots for risk modeling.

campaign_responsesMarketing funnel events tied to customers and campaigns.

support_ticketsCustomer service history for experience analytics.

Schema: `finlearn`

Owns every object in the training database. Scripts intentionally drop and recreate only this schema, leaving other schemas in test untouched.

schemaPostgreSQL

`branches`

Branch dimension used to analyze deposits, loans, acquisition, and regional performance.

15 rowsdimension

Column	Type	Key	Nullable	Description
`branch_id`	smallint identity	PK	No	Surrogate branch identifier.
`branch_code`	text	Unique	No	Short operational code for the branch.
`branch_name`	text		No	Readable branch name.
`region`	text		No	Sales and operations region.
`state_code`	char(2)		No	US state abbreviation.
`city`	text		No	Branch city.
`opened_date`	date		No	Date when the branch opened.

`financial_products`

Product catalog for deposits, cards, loans, and investments. Referenced by accounts, cards, and loans.

10 rowsdimension

Column	Type	Key	Nullable	Description
`product_id`	smallint identity	PK	No	Surrogate product identifier.
`product_code`	text	Unique	No	Short product code used in business logic.
`product_name`	text		No	Customer-facing product name.
`product_family`	text		No	One of Deposit, Credit Card, Loan, or Investment.
`risk_weight`	numeric(5,3)		No	Simplified relative risk weight for analysis exercises.
`monthly_fee`	numeric(8,2)		No	Standard monthly account or product fee.
`standard_rate`	numeric(7,4)		No	Nominal product rate or APR baseline.

`customers`

Customer master table with demographics, financial profile, onboarding channel, KYC status, and active flag.

15,000 rowsdimension

Column	Type	Key	Nullable	Description
`customer_id`	integer identity	PK	No	Surrogate customer identifier.
`customer_number`	text	Unique	No	External customer number.
`first_name`	text		No	Synthetic first name.
`last_name`	text		No	Synthetic last name.
`email`	text	Unique	No	Fake email address using the training domain.
`phone`	text		No	Fake phone number.
`birth_date`	date		No	Date of birth for age-based analysis.
`gender`	text		No	Gender category.
`state_code`	char(2)		No	Customer residence state.
`city`	text		No	Customer residence city.
`postal_code`	text		No	Five-digit synthetic postal code.
`annual_income`	numeric(12,2)		No	Estimated annual income in USD.
`employment_status`	text		No	Employment category.
`risk_segment`	text		No	Low, Medium, or High risk segment.
`acquisition_channel`	text		No	How the customer joined.
`joined_date`	date		No	Customer onboarding date.
`kyc_status`	text		No	Know-your-customer review status.
`is_active`	boolean		No	Whether the customer relationship is active.

`accounts`

Account-level table for deposit and brokerage products. Useful for balance, retention, product mix, and branch analysis.

26,000 rowsfact/dimension

Column	Type	Key	Nullable	Description
`account_id`	integer identity	PK	No	Surrogate account identifier.
`customer_id`	integer	FK customers	No	Owner customer.
`branch_id`	smallint	FK branches	No	Originating or servicing branch.
`product_id`	smallint	FK products	No	Financial product assigned to the account.
`account_number`	text	Unique	No	Synthetic external account number.
`account_type`	text		No	Checking, Savings, Money Market, or Brokerage.
`opened_date`	date		No	Account opening date.
`closed_date`	date		Yes	Account closure date when closed.
`status`	text		No	Open, Closed, Dormant, or Frozen.
`currency_code`	char(3)		No	Account currency, seeded as USD.
`current_balance`	numeric(14,2)		No	Current balance snapshot.
`overdraft_limit`	numeric(12,2)		No	Available overdraft line for checking accounts.

`merchants`

Merchant dimension for card purchases and debit activity. Supports category spend, online spend, and fraud-pattern exercises.

800 rowsdimension

Column	Type	Key	Nullable	Description
`merchant_id`	integer identity	PK	No	Surrogate merchant identifier.
`merchant_name`	text		No	Synthetic merchant name.
`merchant_category`	text		No	Spend category such as Grocery, Fuel, Travel, or Utilities.
`risk_tier`	text		No	Merchant risk classification.
`state_code`	char(2)		Yes	Merchant state when location-based.
`online_only`	boolean		No	True for online-only merchants.

`cards`

Credit card account records tied to customers and their checking relationship.

13,520 rowscredit

Column	Type	Key	Nullable	Description
`card_id`	integer identity	PK	No	Surrogate card identifier.
`customer_id`	integer	FK customers	No	Cardholder customer.
`account_id`	integer	FK accounts	No	Linked account relationship.
`product_id`	smallint	FK products	No	Credit card product.
`card_number_last4`	char(4)		No	Last four digits only, synthetic.
`issued_date`	date		No	Issue date.
`expiration_date`	date		No	Expiration date.
`credit_limit`	numeric(12,2)		No	Approved credit limit.
`apr`	numeric(6,4)		No	Annual percentage rate.
`status`	text		No	Active, Closed, Blocked, or Expired.

`loans`

Loan fact table for credit exposure, product mix, delinquency, branch performance, and portfolio analysis.

7,000 rowscredit

Column	Type	Key	Nullable	Description
`loan_id`	integer identity	PK	No	Surrogate loan identifier.
`customer_id`	integer	FK customers	No	Borrower customer.
`branch_id`	smallint	FK branches	No	Originating branch.
`product_id`	smallint	FK products	No	Loan product.
`loan_number`	text	Unique	No	Synthetic loan number.
`loan_type`	text		No	Auto, Mortgage, Personal, or Small Business.
`origination_date`	date		No	Loan start date.
`maturity_date`	date		No	Contractual final payment date.
`original_amount`	numeric(14,2)		No	Original principal amount.
`interest_rate`	numeric(6,4)		No	Annual interest rate.
`term_months`	smallint		No	Contract term length.
`outstanding_balance`	numeric(14,2)		No	Current unpaid balance snapshot.
`delinquency_days`	integer		No	Days past due.
`status`	text		No	Current, Late, Default, Paid Off, or Charged Off.

`account_transactions`

Largest fact table. Contains deposit, debit, card, transfer, fee, interest, wire, and fraud-flag activity from 2022 through August 2026.

500,000 rowsfact

Column	Type	Key	Nullable	Description
`transaction_id`	bigint identity	PK	No	Surrogate transaction identifier.
`account_id`	integer	FK accounts	No	Account affected by the transaction.
`merchant_id`	integer	FK merchants	Yes	Merchant for card purchase or ACH debit transactions.
`transaction_ts`	timestamp		No	Transaction timestamp.
`transaction_type`	text		No	Business transaction type.
`channel`	text		No	ATM, Branch, Card, Mobile, Online, or System.
`amount`	numeric(12,2)		No	Signed amount; credits are positive and debits are negative.
`balance_after`	numeric(14,2)		No	Account balance after the transaction.
`status`	text		No	Posted, Pending, Rejected, or Reversed.
`description`	text		No	Readable transaction description.
`is_fraud_flag`	boolean		No	Training flag for suspected fraudulent card activity.

`loan_payments`

Installment schedule and performance history for loans. Supports delinquency, collections, and cash-flow analytics.

248,693 rowsfact

Column	Type	Key	Nullable	Description
`payment_id`	bigint identity	PK	No	Surrogate payment identifier.
`loan_id`	integer	FK loans	No	Loan being billed or paid.
`due_date`	date		No	Scheduled installment due date.
`paid_date`	date		Yes	Actual payment date when paid or late.
`scheduled_amount`	numeric(12,2)		No	Total amount scheduled for the installment.
`principal_amount`	numeric(12,2)		No	Principal portion.
`interest_amount`	numeric(12,2)		No	Interest portion.
`late_fee`	numeric(10,2)		No	Late fee assessed.
`status`	text		No	Scheduled, Paid, Late, Missed, or Partial.

`credit_score_history`

Semiannual credit bureau snapshots for customer risk features and score migration analysis.

150,000 rowsrisk

Column	Type	Key	Nullable	Description
`score_id`	bigint identity	PK	No	Surrogate score record identifier.
`customer_id`	integer	FK customers	No	Customer being scored.
`score_date`	date		No	Score snapshot date.
`credit_score`	smallint		No	Credit score from 300 to 850.
`bureau`	text		No	Equifax, Experian, or TransUnion.
`utilization_pct`	numeric(5,2)		No	Credit utilization percentage.
`inquiries_12m`	smallint		No	Number of hard inquiries in the prior 12 months.

`support_tickets`

Customer service cases for operational workload, service quality, and churn-signal exercises.

20,000 rowsoperations

Column	Type	Key	Nullable	Description
`ticket_id`	integer identity	PK	No	Surrogate ticket identifier.
`customer_id`	integer	FK customers	No	Customer who opened the case.
`account_id`	integer	FK accounts	Yes	Related account when the case is account-specific.
`opened_ts`	timestamp		No	Ticket open timestamp.
`closed_ts`	timestamp		Yes	Close timestamp for resolved tickets.
`issue_category`	text		No	Primary issue type.
`priority`	text		No	Low, Medium, High, or Urgent.
`channel`	text		No	Customer contact channel.
`status`	text		No	Open, Closed, Escalated, or Waiting on Customer.
`satisfaction_score`	smallint		Yes	Customer rating from 1 to 5 for closed tickets.

`marketing_campaigns`

Campaign definitions for marketing attribution and funnel analytics.

8 rowsdimension

Column	Type	Key	Nullable	Description
`campaign_id`	smallint identity	PK	No	Surrogate campaign identifier.
`campaign_name`	text		No	Readable campaign name.
`product_family`	text		No	Product family promoted by the campaign.
`start_date`	date		No	Campaign start date.
`end_date`	date		No	Campaign end date.
`channel`	text		No	Email, In App, Mail, Paid Search, or SMS.
`budget`	numeric(12,2)		No	Campaign budget in USD.

`campaign_responses`

Customer-level campaign funnel events. A row represents one customer sent one campaign.

59,960 rowsfact

Column	Type	Key	Nullable	Description
`response_id`	bigint identity	PK	No	Surrogate response identifier.
`campaign_id`	smallint	FK campaigns	No	Campaign sent to the customer.
`customer_id`	integer	FK customers	No	Customer who received the campaign.
`sent_ts`	timestamp		No	Send timestamp.
`opened_ts`	timestamp		Yes	Open timestamp when applicable.
`clicked_ts`	timestamp		Yes	Click timestamp when applicable.
`converted_ts`	timestamp		Yes	Conversion timestamp when applicable.
`conversion_value`	numeric(12,2)		No	Estimated value of the conversion.

`fx_rates`

Daily currency conversion reference rates against USD for multi-currency analytics practice.

8,520 rowsreference

Column	Type	Key	Nullable	Description
`rate_date`	date	PK	No	Rate effective date.
`currency_code`	char(3)	PK	No	ISO-like currency code.
`usd_rate`	numeric(12,6)		No	Currency rate relative to USD.

Analytics Views

Convenience views are included for students who are ready to work with prepared analytical datasets before building their own transformations.

4 views

View	Rows	Grain	Description
`v_customer_360`	15,000	One row per customer	Customer profile with account counts, total balances, loan exposure, latest credit score, support tickets, and campaign conversions.
`v_credit_risk_features`	15,000	One row per customer	Risk modeling features including latest score, utilization, inquiries, active loans, delinquency, and non-current payments.
`v_account_activity_monthly`	268,624	One row per account per active month	Monthly transaction volume, inflows, outflows, fraud flags, and exception counts.
`v_monthly_branch_kpis`	1,501	One row per branch per account-opening month	Monthly new account, closed account, and balance snapshot measures by branch and region.

Indexes

Indexes support common joins and date-filtered analytics while keeping the schema readable for learners.

12 indexes

idx_customers_joined_date
idx_customers_state_risk
idx_accounts_customer
idx_accounts_branch_product
idx_transactions_account_ts
idx_transactions_type_ts

idx_transactions_merchant
idx_loans_customer_status
idx_loan_payments_loan_due
idx_scores_customer_date
idx_tickets_customer_opened
idx_campaign_responses_campaign

Starter Exercises

Use these questions to guide lessons or assignments.

Which branch regions have the highest deposit balances and account growth?
Which merchant categories produce the most fraud flags or reversed transactions?
How do credit score, utilization, and delinquency differ by risk segment?
Which marketing channels convert best after normalizing by campaign budget?
What percentage of loan payments are late, missed, or partial by loan type?
Which customers have high balances, recent support tickets, and no campaign conversion?

set search_path to finlearn;

select risk_segment,
       count(*) as customers,
       round(avg(credit_score), 1) as avg_score,
       round(avg(outstanding_loan_balance), 2) as avg_loan_exposure
from v_credit_risk_features
group by risk_segment
order by risk_segment;