ClickHouse Database Schema

Overview

This document defines the ClickHouse database schema for the analytics dashboard implementation. All tables are optimized for fast analytical queries across date ranges and restaurant dimensions.

Quick Reference

Analytics Tables (syn database):

• analytics_revenue - Hourly revenue by restaurant
• analytics_bookings_covers - Hourly bookings and covers count
• analytics_capacity - Hourly capacity utilization

Key Optimization:

• Partitioned by month for efficient date range queries
• Primary keys optimized for (restaurant_id, date) filtering
• ReplacingMergeTree engine for update handling

Table Definitions

Table 1: analytics_revenue

Purpose: Store hourly revenue aggregations by restaurant

CREATE TABLE syn.analytics_revenue (
    restaurant_id UInt32,
    date Date,
    hour String,
    revenue Float64,
    _version UInt64
) ENGINE = ReplacingMergeTree(_version)
PARTITION BY toYYYYMM(date)
PRIMARY KEY (restaurant_id, date, hour)
ORDER BY (restaurant_id, date, hour)
SETTINGS index_granularity = 8192, compress_marks = true;

Column Descriptions:

Optimization:

• PARTITION BY toYYYYMM(date) - Monthly partitions reduce query scan time
• PRIMARY KEY (restaurant_id, date, hour) - Optimizes for restaurant + date range queries
• ReplacingMergeTree(_version) - Handles updates by version number

Query Example:

SELECT date, hour, revenue
FROM syn.analytics_revenue
WHERE restaurant_id = 1597
  AND date >= '2025-12-19'
  AND date <= '2025-12-25'
ORDER BY date, hour;

Table 2: analytics_bookings_covers

Purpose: Store hourly booking counts and cover counts with averages

CREATE TABLE syn.analytics_bookings_covers (
    restaurant_id UInt32,
    date Date,
    hour String,
    bookings_count Int32,
    confirmed_covers Int32,
    avg_party_size Float64,
    _version UInt64
) ENGINE = ReplacingMergeTree(_version)
PARTITION BY toYYYYMM(date)
PRIMARY KEY (restaurant_id, date, hour)
ORDER BY (restaurant_id, date, hour)
SETTINGS index_granularity = 8192, compress_marks = true;

Column Descriptions:

Calculation Notes:

• avg_party_size = confirmed_covers / bookings_count
• Only includes confirmed bookings (not no-shows)
• Excludes cancelled reservations

Query Example:

SELECT date, hour, bookings_count, confirmed_covers, avg_party_size
FROM syn.analytics_bookings_covers
WHERE restaurant_id = 1597
  AND date >= '2025-12-19'
  AND date <= '2025-12-25'
ORDER BY date, hour;

Table 3: analytics_capacity

Purpose: Track seat utilization and peak hour patterns

CREATE TABLE syn.analytics_capacity (
    restaurant_id UInt32,
    date Date,
    hour String,
    reserved_covers Int32,
    utilization_percent Float64,
    is_peak_hour UInt8,
    _version UInt64
) ENGINE = ReplacingMergeTree(_version)
PARTITION BY toYYYYMM(date)
PRIMARY KEY (restaurant_id, date, hour)
ORDER BY (restaurant_id, date, hour)
SETTINGS index_granularity = 8192, compress_marks = true;

Column Descriptions:

Calculation Notes:

• utilization_percent = (reserved_covers / restaurant_total_capacity) * 100
• Peak hours defined as 12:00-14:00 (lunch) and 19:00-21:00 (dinner)
• Values > 100% indicate over-booking

Query Example:

SELECT date, hour, reserved_covers, utilization_percent, is_peak_hour
FROM syn.analytics_capacity
WHERE restaurant_id = 1597
  AND date >= '2025-12-19'
  AND date <= '2025-12-25'
ORDER BY date, hour;

Indexing Strategy

Primary Key Design

All tables use a three-part primary key for optimal query performance:

PRIMARY KEY (restaurant_id, date, hour)

Why this order:

1. restaurant_id first - Filter by restaurant (most selective)
2. date second - Filter by date range (time-series queries)
3. hour third - Additional granularity for hourly data

Query Pattern Match:

-- FAST: Uses primary key (matches first 2 parts)
WHERE restaurant_id = ? AND date BETWEEN ? AND ?

-- MODERATELY FAST: Uses primary key (matches all 3 parts)
WHERE restaurant_id = ? AND date = ? AND hour = ?

-- SLOWER: Doesn't use primary key
WHERE revenue > 1000  -- Revenue not in index

Partitioning Strategy

PARTITION BY toYYYYMM(date)

Benefits:

• Queries on date ranges only scan relevant partitions
• January data in separate partition from February
• Reduces I/O significantly for historical queries
• Example: Querying Dec 2025 only reads Dec 2025 partition

Performance Characteristics

Query Performance Expectations

Storage Size Estimates

Note: ClickHouse compression typically achieves 10:1 ratio, making storage very efficient.

Data Population Methods

Option 1: Materialized Views (Recommended)

Automatic aggregation from source tables (reservations) into analytics tables.

-- Example MV for revenue
CREATE MATERIALIZED VIEW syn.mv_analytics_revenue AS
SELECT
    restaurant_id,
    toDate(created_at) AS date,
    formatDateTime(created_at, '%H:%i') AS hour,
    SUM(amount) AS revenue,
    max(created_at) AS _version
FROM booking_production.reservations
GROUP BY restaurant_id, date, hour;

Advantages:

• Automatic population
• Real-time updates
• No manual backfill needed

Option 2: Manual Batch Inserts

For initial data load from historical records.

INSERT INTO syn.analytics_revenue
SELECT
    restaurant_id,
    toDate(created_at) AS date,
    formatDateTime(created_at, '%H:%i') AS hour,
    SUM(amount) AS revenue,
    max(created_at) AS _version
FROM booking_production.reservations
WHERE created_at >= '2024-01-01'
GROUP BY restaurant_id, date, hour;

Maintenance Operations

Optimize Tables (Remove old versions)

-- Run daily to clean up ReplacingMergeTree versions
OPTIMIZE TABLE syn.analytics_revenue FINAL;
OPTIMIZE TABLE syn.analytics_bookings_covers FINAL;
OPTIMIZE TABLE syn.analytics_capacity FINAL;

Data Verification

-- Verify row counts per restaurant
SELECT restaurant_id, COUNT(*) as row_count
FROM syn.analytics_revenue
WHERE date >= '2025-01-01'
GROUP BY restaurant_id
ORDER BY row_count DESC;

-- Check for NULL values
SELECT COUNT(*) as null_count
FROM syn.analytics_revenue
WHERE revenue IS NULL OR date IS NULL OR hour IS NULL;

-- Verify hour format
SELECT DISTINCT hour
FROM syn.analytics_revenue
ORDER BY hour;

Backup Strategy

-- Export table structure
ALTER TABLE syn.analytics_revenue AS JSON > analytics_revenue_schema.json;

-- Export sample data
SELECT *
FROM syn.analytics_revenue
WHERE restaurant_id IN (SELECT restaurant_id FROM syn.analytics_revenue LIMIT 10)
LIMIT 1000
FORMAT TabSeparatedWithNames > analytics_revenue_sample.tsv;

Migration Path

Phase 1: Create Tables (1 day)

• Execute all CREATE TABLE statements
• Verify tables exist: SHOW TABLES IN syn;

Phase 2: Initial Data Load (2-3 days)

• Backfill historical data from reservations table
• Verify data completeness: Check row counts match expectations

Phase 3: Setup Materialized Views (1 day)

• Create MVs for automatic updates
• Test with new reservations

Phase 4: Validation (1 day)

• Run verification queries
• Compare MV results with manual calculations
• Performance test with typical queries

Related Documentation

• 01-system-architecture.md - Overall architecture
• 03-implementation-guide.md - Step-by-step setup