Part 2: OLAP – Advanced Analytics System

Goal: Enable complex aggregations, time-series analysis, and low-latency queries for Stripe’s revenue, fraud, and customer insights. Supports ad-hoc analysis, real-time/near-real-time insights, and scalable joins for business intelligence.

---

🎯 Core Objectives

• Complex Queries: Support multi-dimensional aggregations (e.g., revenue by region, product, time).
• Time-Series Analysis: Track trends (e.g., transaction volume over 12 months).
• Low-Latency Responses for critical business queries: Optimize for <500ms query performance.
• Hybrid Data Flow: both historical and near real-time analytics: Combine batch (Spark/Airflow) and streaming (Kafka/Flink) for up-to-date analytics.

---

📂 Data Model

We use a star Schema for the databases. We have both Fact Tables (Quantitative Metrics) and Dimension Tables (Context for Analysis)

Fact Tables

Table	Description	Key Attributes
Transactions_Fact	Aggregates transaction data (amount, fraud, status).	transaction_id (FK), date_id (FK), merchant_id (FK), customer_id (FK), amount, fraud_score, currency, status
Disputes_Fact	Tracks dispute metrics (volume, resolution time).	dispute_id (FK), transaction_id (FK), merchant_id (FK), resolution_time, amount, status

Dimension Tables

Table	Description	Key Attributes
Date_Dim	Time hierarchy (day, week, month, year).	date_id (PK), full_date, day_of_week, month, quarter, year, is_holiday
Merchant_Dim	Merchant attributes and hierarchies.	merchant_id (PK), name, industry, tier, region, compliance_status
Customer_Dim	Customer demographics and segmentation.	customer_id (PK), age, gender, segment, LTV, risk_score
Product_Dim	Product categories and details.	product_id (PK), name, category, price_tier, is_recurring
Geography_Dim	Geographic data (country, region, city).	geography_id (PK), country, region, city, postal_code
Fraud_Dim	Fraud indicators and categorization.	fraud_id (PK), fraud_type, severity, description

Star Schema (Mermaid)

erDiagram
TRANSACTIONS_FACT ||--o{ DATE_DIM : "linked_by"
TRANSACTIONS_FACT ||--o{ MERCHANT_DIM : "linked_by"
TRANSACTIONS_FACT ||--o{ CUSTOMER_DIM : "linked_by"
TRANSACTIONS_FACT ||--o{ PRODUCT_DIM : "linked_by"
TRANSACTIONS_FACT ||--o{ GEOGRAPHY_DIM : "linked_by"
TRANSACTIONS_FACT ||--o{ FRAUD_DIM : "linked_by"
DISPUTES_FACT ||--o{ DATE_DIM : "linked_by"
DISPUTES_FACT ||--o{ MERCHANT_DIM : "linked_by"
DISPUTES_FACT ||--o{ TRANSACTIONS_FACT : "linked_by"

TRANSACTIONS_FACT {
string transaction_id PK
string date_id FK
string merchant_id FK
string customer_id FK
string product_id FK
string geography_id FK
string fraud_id FK
decimal amount
string currency
int fraud_score
string status
    }

DATE_DIM {
string date_id PK
date full_date
string day_of_week
string month
string quarter
int year
boolean is_holiday
    }

MERCHANT_DIM {
string merchant_id PK
string name
string industry
string tier
string region
string compliance_status
    }

Query performance

Strategy for large-scale joins, subqueries,time-series analysis, pre-aggregations, materialized views and summary tables to optimize query performance :

Strategy	Implementation	Tools	Benefit
Partitioning	Transactions_Fact by month, Disputes_Fact by quarter.	Snowflake, Trino	Faster time-based queries.
Columnar Storage	Use Parquet/ORC format.	Snowflake, BigQuery	Efficient scans + compression.
Join Optimization	Broadcast small dimension tables (e.g., Date_Dim); co-locate large fact-to-fact joins on shared keys (merchant_id).	Snowflake, Trino, Spark	Reduces shuffle cost on large-scale joins.
Subquery Rewriting	Convert correlated subqueries into CTEs or window functions where possible.	Snowflake, PostgreSQL	Avoids repeated scans, improves planner efficiency.
Time-Series Analysis	Use window functions (LAG, LEAD, moving averages) and time-bucketing on Date_Dim.	Snowflake, Trino	Efficient trend/anomaly detection without full re-scans.
Materialized Views	Pre-aggregate daily revenue, fraud rates.	Snowflake, Spark	Low-latency responses.
Summary/Rollup Tables	Scheduled batch jobs building daily/weekly aggregates (e.g., merchant-level fraud rate).	dbt, Spark, Snowflake Tasks	Reduces load on fact tables for reporting/BI.
Indexing	Foreign keys (merchant_id, date_id).	Snowflake, PostgreSQL	Faster joins.
Caching	Cache frequent queries (e.g., daily revenue).	Redis, Snowflake Cache	Reduce query latency.

Sample SQL Queries

1.Monthly Revenue by Merchant Tier

SELECT
    m.tier,
    DATE_TRUNC('month', d.full_date) AS month,
    SUM(t.amount) AS revenue,
    COUNT(DISTINCT t.transaction_id) AS transaction_count
FROM Transactions_Fact t
JOIN Date_Dim d ON t.date_id = d.date_id
JOIN Merchant_Dim m ON t.merchant_id = m.merchant_id
GROUP BY m.tier, month
ORDER BY month, revenue DESC;

2 Fraud Rate by Region & Product Category

SELECT
    g.region,
    p.category,
    COUNT(CASE WHEN t.fraud_score > 0.8 THEN 1 END) * 100.0 / COUNT(*) AS fraud_rate_percentage
FROM Transactions_Fact t
JOIN Geography_Dim g ON t.geography_id = g.geography_id
JOIN Product_Dim p ON t.product_id = p.product_id
GROUP BY g.region, p.category
ORDER BY fraud_rate_percentage DESC;