Customer 360 Lab with ClickHouse

---

A comprehensive hands-on laboratory for large-scale Customer 360 analytics using ClickHouse, featuring 815 million records across 6 months of customer activity data.

🎯 Purpose

This lab provides practical experience with ClickHouse for Customer 360 analytics: - Large-scale customer data integration (815M+ records) - Multi-dimensional customer behavior analysis - Advanced analytics: RFM, cohort analysis, CLV prediction - Performance optimization with Materialized Views - Data governance: RBAC, data masking, GDPR compliance

Whether you're building a customer data platform or exploring analytical capabilities for customer insights, this lab demonstrates production-ready patterns with realistic data volumes.

📊 Dataset Scale

• Customers: 30M (30 million)
• Transactions: 500M (500 million)
• Events: 200M (200 million)
• Support Tickets: 5M (5 million)
• Campaign Responses: 100M (100 million)
• Product Reviews: 10M (10 million)
• Total Records: ~815M
• Time Period: 180 days (6 months)

📁 File Structure

customer360/
├── README.md                      # This file
├── 01-schema.sql                  # Database and table creation
├── 02-load.sql                    # Test data generation
├── 03-basic-queries.sql           # Basic analysis queries
├── 04-advanced-queries.sql        # Advanced analysis queries
├── 05-optimization.sql            # Materialized views and optimization
└── 06-management.sql              # Data management and security

🚀 Quick Start

Execute all scripts in sequence:

cd usecase/customer360

# Sequential execution
clickhouse-client --queries-file 01-schema.sql
clickhouse-client --queries-file 02-load.sql
clickhouse-client --queries-file 03-basic-queries.sql
clickhouse-client --queries-file 04-advanced-queries.sql
clickhouse-client --queries-file 05-optimization.sql
clickhouse-client --queries-file 06-management.sql

Or in a loop:

for file in 01-schema.sql 02-load.sql 03-basic-queries.sql 04-advanced-queries.sql 05-optimization.sql 06-management.sql; do
    echo "Executing $file..."
    clickhouse-client --queries-file "$file"
    echo ""
done

📖 Detailed Lab Steps

1. Schema Creation

clickhouse-client --queries-file 01-schema.sql

What it does: - Creates customer360 database - Creates 6 tables: - customers: Customer profile information - transactions: Transaction history (partitioned by month) - customer_events: Web/app activity events - support_tickets: Customer service tickets - campaign_responses: Marketing campaign responses - product_reviews: Product reviews and ratings

Expected time: ~1 second

---

2. Data Loading

clickhouse-client --queries-file 02-load.sql

What it does: - Generates 30M customer records - Generates 500M transaction records (2 batches) - Generates 200M event records (2 batches) - Generates 5M support tickets - Generates 100M campaign responses (2 batches) - Generates 10M product reviews

Expected time: 30-60 minutes (system dependent)

Warning: Large-scale data generation. Ensure sufficient disk space (minimum 100GB).

---

3. Basic Analytics

clickhouse-client --queries-file 03-basic-queries.sql

Query scenarios: - Customer 360 Unified View: 5-way JOIN for complete customer activity - Channel Analysis: Revenue and customer behavior by channel - Product Preferences by Segment: Category preferences by customer segment - Monthly Business Trends: 6-month revenue and growth trends - Channel Growth Trends: Monthly performance by channel - Conversion Funnel: Conversion rates from visit to purchase

Expected time: 1-10 seconds per query

---

4. Advanced Analytics

clickhouse-client --queries-file 04-advanced-queries.sql

What it analyzes: - RFM Analysis: Customer segmentation by Recency, Frequency, Monetary - Cohort Analysis: Retention rates by registration month - CLV Prediction: Customer lifetime value prediction metrics - LTV Analysis: 6-month customer lifetime value - Churn Risk Identification: Predict customer churn risk - Multi-touch Attribution: Campaign effectiveness analysis - Customer Journey Mapping: Customer interaction pattern analysis

Expected time: 5-30 seconds per query

---

5. Optimization

clickhouse-client --queries-file 05-optimization.sql

What it does: - Create Materialized View: Pre-aggregated customer KPIs - MV Query Examples: Queries using pre-aggregated data - Storage Analysis: Compression ratio and storage space - Query Execution Plan: EXPLAIN ESTIMATE for query cost analysis - Table Optimization: OPTIMIZE TABLE execution - Partition Management: Partition list and status - Performance Monitoring: Query performance metrics tracking

Expected time: 5-10 minutes for optimization tasks

---

6. Management & Security

clickhouse-client --queries-file 06-management.sql

What it covers: - Partition Management: Delete old partitions - TTL Settings: Automatic data deletion and aggregation - RBAC: Role-based access control setup - Data Masking: PII masking views - Row Level Security: Row-level security policies - GDPR Compliance: Personal data deletion and anonymization - Audit Log: Access history tracking - System Monitoring: Disk usage and query statistics

Expected time: Immediate execution for most commands

🔍 Key Learning Points

1. Schema Design

• MergeTree engine utilization
• Partition key configuration (PARTITION BY toYYYYMM)
• Sorting key optimization (ORDER BY)
• LowCardinality type utilization

2. Large-scale Data Processing

• Test data generation with numbers() function
• Parallel INSERT with max_insert_threads
• Batch INSERT strategy

3. Complex Analytical Queries

• Multi-table JOINs
• Window Functions
• CTE (Common Table Expressions)
• Aggregate function utilization

4. Performance Optimization

• Materialized Views
• AggregatingMergeTree
• Query execution plan analysis
• Partition pruning

5. Operations Management

• TTL settings
• Partition management
• RBAC
• Data masking
• Audit logging

🛠 Prerequisites

• CPU: Minimum 4 cores, recommended 8+ cores
• Memory: Minimum 16GB, recommended 32GB+
• Disk: Minimum 100GB free space
• ClickHouse Version: 23.x or higher

💡 Performance Tips

During Data Loading

• Adjust max_insert_threads setting
• Optimize batch size

During Query Execution

• Utilize partition keys in WHERE clause
• Use sampling (WHERE customer_id % 100 = 0)
• Use appropriate LIMIT

Monitoring

• Utilize system.query_log table
• Check partitions with system.parts
• Monitor running queries with system.processes

🔧 Troubleshooting

Out of Memory Error

SET max_memory_usage = 10000000000; -- 10GB

Query Timeout

SET max_execution_time = 300; -- 5 minutes

Insufficient Disk Space

• Delete old partitions
• Set TTL for automatic cleanup
• Drop unnecessary tables

🧹 Clean Up

Delete the test database after completion:

DROP DATABASE IF EXISTS customer360;

📚 Reference

• ClickHouse Official Documentation
• MergeTree Engine Guide
• Query Optimization Guide

📝 License

MIT — same as the rest of the repository.

👤 Author

Ken (ClickHouse Solution Architect) Created: 2025-12-06

From the notes site (migrated, not verified)

Moved on 2026-10-06 from the author's notes site (clickhouse.kr); not re-run here. English is an LLM-assisted translation. The figures below are as written in the note and may differ from the dataset scale above.

What is Customer 360?

Customer 360 is not just about collecting customer data; it enables a multi-dimensional understanding of the customer:

• Profile data: demographics, preferences, segments
• Transaction data: purchase history, payment information, per-channel transactions
• Behavioral data: web/app activity, session information, search patterns
• Interaction data: customer-service tickets, inquiry history
• Marketing data: campaign responses, conversion paths
• Feedback data: reviews, ratings, NPS scores

Fit-Gap analysis: test environment and storage efficiency

The note's test used a six-month dataset (2025-06-09 to 2025-12-06, 180 days).

Table	Compressed	Uncompressed	Ratio	Rows
transactions	3.55 GiB	31.05 GiB	8.73x	463M
customer_events	907.34 MiB	18.42 GiB	20.79x	200M
campaign_responses	450.69 MiB	5.68 GiB	12.90x	100M
customers	268.03 MiB	2.76 GiB	10.56x	30M
product_reviews	52.50 MiB	889.85 MiB	16.95x	10M
support_tickets	11.50 MiB	190.91 MiB	16.60x	5M
Total	5.68 GiB	58.85 GiB	10.36x	808M

The note points out that more than 59 GB of data compressed to 5.7 GB, which it says could cut storage cost in a cloud environment by about 90%.

Weak patterns: where to be careful

1. Complex self-joins and recursive queries

Complex self-joins on the same table (for example recommendation-network analysis) can degrade performance.

-- Be careful with this pattern
SELECT
    t1.product_category AS category_a,
    t2.product_category AS category_b
FROM transactions t1
JOIN transactions t2
    ON t1.customer_id = t2.customer_id
    AND t1.transaction_id < t2.transaction_id
    AND dateDiff('day', t1.transaction_date, t2.transaction_date) <= 7

Workarounds: use sampling (WHERE customer_id % 1000 = 0), pre-aggregate with a Materialized View, limit the analysis scope by time or segment.

2. Real-time updates and OLTP workloads

ClickHouse is optimized for OLAP, so frequent UPDATE/DELETE of individual records is inefficient. Workarounds: insert changes as new records and pick the latest version at query time, use the ReplacingMergeTree engine, batch updates (thousands of rows at a time).

-- Pattern for reading the latest customer record
SELECT *
FROM (
    SELECT *, row_number() OVER (PARTITION BY customer_id ORDER BY last_updated DESC) AS rn
    FROM customers
)
WHERE rn = 1

3. Transactions and strong consistency

ClickHouse does not support multi-table transactions. Workarounds: manage consistency at the application level, accept an eventual-consistency model, handle critical transactions in an OLTP database and replicate them to ClickHouse.

4. Join reordering

The note says that from ClickHouse 25.10 the join order is optimized automatically, so less manual tuning is needed, but earlier versions still require thinking about join order. In a LEFT JOIN the left table is scanned first and the right table is loaded into memory, so put the large table on the left.

-- Recommended: large table on the LEFT
SELECT ...
FROM transactions t  -- large table
LEFT JOIN customers c ON t.customer_id = c.customer_id

Join algorithms: hash (default, fits most cases), direct (small dimension tables, with a Dictionary), parallel_hash (very large tables).

Performance benchmark results (as recorded in the note)

Query type	Data scale	Run time	Throughput
Single-customer 360 view	1 customer, 5-way JOIN	< 0.1 s	-
RFM segmentation	30M customers	2-3 s	10M rows/sec
Cohort analysis	30M customers, 60 days	3-5 s	8M rows/sec
Revenue by channel	250M transactions	1-2 s	120M rows/sec
Funnel analysis	100M events, 7 days	2-4 s	25M rows/sec
Multi-touch attribution	50M campaign responses	3-5 s	10M rows/sec

Conclusion

Why ClickHouse fits Customer 360:

1. Large-scale processing: hundreds of millions of records can be queried and analyzed in seconds.
2. High compression: 11x or more compression cuts storage cost substantially.
3. Flexible schema: flexible data modelling for integrating many data sources.
4. SQL-friendly: existing analysts can use it with no learning curve.
5. Real-time analytics: Materialized Views and incremental processing enable near-real-time analysis.

Things to consider when adopting:

1. Workload shape: it is optimized for OLAP-centric workloads; if you need OLTP, consider a hybrid architecture.
2. Data modelling: partitioning and sort-key design matched to query patterns is the key to performance.
3. Security requirements: a thorough security policy for handling sensitive personal data is essential.
4. Cost optimization: in the cloud, monitor storage and compute resources to optimize cost.

Extensions: similar-customer discovery with vector search, BI integration for real-time monitoring, churn and CLV prediction models, and an A/B testing platform that analyzes experiment results in real time.

---

customer360/
├── README.md                      # 이 파일
├── 01-schema.sql                  # 데이터베이스 및 테이블 생성
├── 02-load.sql                    # 테스트 데이터 생성
├── 03-basic-queries.sql           # 기본 분석 쿼리
├── 04-advanced-queries.sql        # 고급 분석 쿼리
├── 05-optimization.sql            # Materialized View 및 최적화
└── 06-management.sql              # 데이터 관리 및 보안

cd usecase/customer360

# 순차 실행
clickhouse-client --queries-file 01-schema.sql
clickhouse-client --queries-file 02-load.sql
clickhouse-client --queries-file 03-basic-queries.sql
clickhouse-client --queries-file 04-advanced-queries.sql
clickhouse-client --queries-file 05-optimization.sql
clickhouse-client --queries-file 06-management.sql

for file in 01-schema.sql 02-load.sql 03-basic-queries.sql 04-advanced-queries.sql 05-optimization.sql 06-management.sql; do
    echo "Executing $file..."
    clickhouse-client --queries-file "$file"
    echo ""
done

clickhouse-client --queries-file 01-schema.sql

clickhouse-client --queries-file 02-load.sql

clickhouse-client --queries-file 03-basic-queries.sql

clickhouse-client --queries-file 04-advanced-queries.sql

clickhouse-client --queries-file 05-optimization.sql

clickhouse-client --queries-file 06-management.sql

SET max_memory_usage = 10000000000; -- 10GB

SET max_execution_time = 300; -- 5분

DROP DATABASE IF EXISTS customer360;

-- 이런 패턴은 주의 필요
SELECT
    t1.product_category AS category_a,
    t2.product_category AS category_b
FROM transactions t1
JOIN transactions t2
    ON t1.customer_id = t2.customer_id
    AND t1.transaction_id < t2.transaction_id
    AND dateDiff('day', t1.transaction_date, t2.transaction_date) <= 7

-- 최신 고객 정보 조회 패턴
SELECT *
FROM (
    SELECT *, row_number() OVER (PARTITION BY customer_id ORDER BY last_updated DESC) AS rn
    FROM customers
)
WHERE rn = 1

-- 권장 패턴: 큰 테이블을 LEFT에 배치
SELECT ...
FROM transactions t  -- 큰 테이블
LEFT JOIN customers c ON t.customer_id = c.customer_id