Skip to content

data-dictionary.md

Latest commit

 

History

History
227 lines (181 loc) · 10.2 KB

data-dictionary.md

File metadata and controls

227 lines (181 loc) · 10.2 KB
Raw

Data Dictionary — Financial Risk & Fraud Detection Platform

This document defines every field across all entities in the platform, including data types, constraints, valid values, and business definitions.

Conventions

Symbol Meaning
🔑 Primary key
🔗 Foreign key
❌ NOT NULL
✅ Nullable

1. Customers

Source table: public.customers (PostgreSQL) BigQuery tables: raw.customers, staging.customers, core.dim_customer

Field Type Nullable Description Example
customer_id 🔑 UUID ❌ Universally unique customer identifier. Generated by PostgreSQL uuid_generate_v4(). Immutable. 3fa85f64-5717-4562-b3fc-2c963f66afa6
name VARCHAR(255) ❌ Full legal name of the customer. Priya Sharma
email VARCHAR(320) ❌ Primary contact email. Unique per customer. Max 320 chars per RFC 5321. priya.sharma@example.com
phone VARCHAR(30) ✅ Phone number in any format. +91-9876543210
country CHAR(2) ❌ ISO 3166-1 alpha-2 country code. IN, US, GB
city VARCHAR(100) ✅ City of primary residence. Mumbai
customer_segment ENUM ❌ Business classification. Values: retail, premium, business, vip, student. Default: retail. premium
date_of_birth DATE ✅ Date of birth. Used for age verification checks. 1990-04-15
created_at TIMESTAMPTZ ❌ When the customer record was created. Auto-set. 2024-01-15T10:30:00Z
updated_at TIMESTAMPTZ ❌ Last modification time. Auto-updated by trigger. 2024-06-20T14:22:00Z

SCD Type 2 additional fields (dim_customer only):

Field Type Description
customer_key INT64 Surrogate key for BQ joins
risk_level STRING low / medium / high — changes tracked via SCD2
valid_from TIMESTAMP Start of validity for this record version
valid_to TIMESTAMP End of validity (NULL = current active record)
is_current BOOL TRUE for the active record version

2. Accounts

Source table: public.accounts BigQuery tables: raw.accounts, staging.accounts, core.dim_account

Field Type Nullable Description Example
account_id 🔑 UUID ❌ Unique account identifier. 7c9e6679-7425-40de-944b-e07fc1f90ae7
customer_id 🔗 UUID ❌ FK to customers.customer_id.
account_type ENUM ❌ checking, savings, credit, loan, investment. checking
account_status ENUM ❌ active, inactive, suspended, closed. Default: active. active
currency ENUM ❌ Default currency for this account. See currency codes below. USD
opened_at TIMESTAMPTZ ❌ When the account was opened. 2023-03-01T09:00:00Z
closed_at TIMESTAMPTZ ✅ When closed. NULL if still open. Must be after opened_at. 2024-12-31T23:59:59Z
created_at TIMESTAMPTZ ❌ Record creation time.
updated_at TIMESTAMPTZ ❌ Last update time.

3. Merchants

Source table: public.merchants BigQuery tables: raw.merchants, staging.merchants, core.dim_merchant

Field Type Nullable Description Example
merchant_id 🔑 UUID ❌ Unique merchant identifier.
merchant_name VARCHAR(255) ❌ Display name of the merchant. Amazon India
merchant_category VARCHAR(100) ❌ Business category description. Online Retail
mcc_code CHAR(4) ✅ ISO 18245 Merchant Category Code. 4-digit numeric. 5411 (Grocery Stores)
country CHAR(2) ❌ Country where merchant operates. US
city VARCHAR(100) ✅ City of operation. Seattle
risk_category ENUM ❌ Compliance-assigned risk: low, medium, high. Tracked via SCD2. medium
created_at TIMESTAMPTZ ❌ Record creation time.
updated_at TIMESTAMPTZ ❌ Last update time.

High-risk merchant categories (examples):

  • Cryptocurrency exchanges
  • Online gambling / gaming
  • Money transfer / remittance
  • Luxury goods (high-value single purchases)
  • Anonymous gift cards

4. Devices

Source table: public.devices BigQuery tables: raw.devices, staging.devices, core.dim_device

Field Type Nullable Description Example
device_id 🔑 UUID ❌ Unique device fingerprint identifier.
customer_id 🔗 UUID ❌ FK to customers.customer_id.
device_type ENUM ❌ mobile, desktop, tablet, atm, pos_terminal. mobile
os VARCHAR(50) ✅ Operating system. Android 14, iOS 17.4, Windows 11
browser VARCHAR(100) ✅ Browser name + version (for web transactions). Chrome/123.0
ip_address INET ✅ Last known IP address. May change over time. 192.168.1.100
user_agent TEXT ✅ Full User-Agent string for browser fingerprinting.
first_seen_at TIMESTAMPTZ ❌ First time device was used for a transaction.
last_seen_at TIMESTAMPTZ ❌ Most recent transaction with this device.
created_at TIMESTAMPTZ ❌ Record creation time.

5. Transactions

Source table: public.transactions BigQuery tables: raw.transactions, staging.transactions, core.fact_transaction

Field Type Nullable Description Example
transaction_id 🔑 UUID ❌ Globally unique transaction identifier.
customer_id 🔗 UUID ❌ FK to customers.
account_id 🔗 UUID ❌ FK to accounts.
merchant_id 🔗 UUID ✅ FK to merchants. NULL for peer-to-peer transfers.
device_id 🔗 UUID ✅ FK to devices. NULL for ATM / branch transactions.
transaction_timestamp TIMESTAMPTZ ❌ Exact time of transaction initiation (event time). 2024-06-15T14:32:07Z
amount NUMERIC(18,4) ❌ Transaction amount in currency. Must be > 0. 2499.9900
currency ENUM ❌ ISO 4217 currency code. See valid values below. INR
amount_usd NUMERIC(18,4) ✅ Amount normalized to USD using exchange rate at transaction time. 29.94
transaction_type ENUM ❌ purchase, withdrawal, transfer, refund, payment, deposit. purchase
country CHAR(2) ❌ Country where the transaction occurred. IN
city VARCHAR(100) ✅ City of transaction. Bengaluru
payment_method ENUM ❌ card, bank_transfer, wallet, upi, crypto, cheque. upi
status ENUM ❌ pending, completed, failed, reversed, flagged. completed
ip_address INET ✅ IP address at time of transaction.
is_international BOOL ❌ True if customer.country ≠ transaction.country. false
created_at TIMESTAMPTZ ❌ When the record was created.
updated_at TIMESTAMPTZ ❌ Last update time.

Additional fields in core.fact_transaction (added by pipeline):

Field Description
risk_score Composite fraud risk score 0–100
risk_level low (0–30), medium (31–70), high (71–100)
date_key FK to dim_date
ingestion_timestamp When this record was loaded to BQ
pipeline_run_id Airflow DAG run ID or Dataflow job ID for lineage

6. Transaction Events

Source table: public.transaction_events BigQuery tables: raw.transaction_events, staging.transaction_events, core.fact_transaction_event

Field Type Nullable Description Example
event_id 🔑 UUID ❌ Unique event identifier.
transaction_id 🔗 UUID ❌ FK to transactions.transaction_id.
event_type ENUM ❌ See event types below. payment_completed
event_timestamp TIMESTAMPTZ ❌ When the event occurred (event time, may arrive late). 2024-06-15T14:32:09Z
event_metadata JSONB ✅ Flexible key-value payload per event type. {"reason": "insufficient_funds"}
created_at TIMESTAMPTZ ❌ Record creation time.

Event type lifecycle:

transaction_created
       ↓
payment_attempted
       ↓
payment_failed (optional — may repeat)
       ↓
payment_completed  ←── OR ──→  transaction_reversed
       ↓
fraud_flagged (optional — added by risk pipeline)
       ↓
review_initiated
       ↓
review_completed

7. Reference Values

Currency Codes (valid values)

USD, EUR, GBP, INR, JPY, CAD, AUD, SGD, CHF, CNY, HKD, BRL, MXN, AED, SAR

Risk Score Bands

Score Range Risk Level Action
0 – 30 low No action required
31 – 70 medium Soft alert; monitoring
71 – 100 high Hard flag; may trigger account review

Risk Signal Weights

Signal Weight Trigger Condition
High amount 25 Amount > 3× customer's 30-day average
New device 20 Device first_seen_at < 24 hours ago
Geo anomaly 20 Transaction country not in customer's top-3 countries
Velocity burst 15 > 5 transactions in 5 minutes
Repeated failures 10 > 2 payment_failed events in 1 hour
High-risk merchant 7 merchant.risk_category = 'high'
Off-hours 3 Transaction between 00:00–05:00 in customer's local timezone

Total possible score: 100

8. Data Quality Error Schema

Table: staging.data_quality_errors

Field Type Description
error_id UUID Unique error identifier
pipeline_name STRING Name of the pipeline that detected the error
source_table STRING Source entity (e.g., transactions)
record_id STRING The primary key of the bad record
error_type STRING null_check, uniqueness, referential_integrity, validity, freshness, volume_anomaly
error_message STRING Human-readable description
detected_at TIMESTAMP When the error was detected
raw_payload STRING JSON representation of the bad record
pipeline_run_id STRING Run ID for lineage