ClickHouse Sales Analytics POC

This POC simulates large sales data in ClickHouse and exposes .NET 9 analytics APIs plus a React dashboard for revenue, quantity, profit, margin, product performance, region performance, trends, and price-change simulations.

Structure

• docker-compose.yml - local ClickHouse server.
• sql/init/001_schema.sql - MergeTree tables for dimensions, facts, scenarios, and results.
• src/SalesAnalytics.Api - .NET 9 Web API using ClickHouse HTTP queries.
• src/SalesData.Worker - .NET 9 background worker that generates and batch-inserts fake sales data.
• dashboard - React/Vite dashboard starter.

Run

For detailed setup instructions, see docs/quickstart.md.

1. Start ClickHouse:

docker compose up -d

The local credentials are default / clickhouse. If you previously started this project with different ClickHouse credentials and now get Code: 516 Authentication failed, recreate the local ClickHouse volume:

docker compose down -v
docker compose up -d

2. Generate data. The default target is 10 million fact rows, inserted in 50,000-row JSONEachRow batches:

dotnet run --project src/SalesData.Worker

For a quick smoke test, override the target:

dotnet run --project src/SalesData.Worker --Simulation:TargetSalesRecords=100000 --Simulation:BatchSize=10000

For dashboard sample data, use:

dotnet run --project src\SalesData.Worker --Simulation:TargetSalesRecords=250000 --Simulation:BatchSize=25000 --Simulation:Products=250 --Simulation:Customers=10000 --Simulation:TruncateBeforeLoad=true

3. Start the API:

dotnet run --project src/SalesAnalytics.Api --urls http://localhost:5088

The API listens on http://localhost:5088.

4. Start the dashboard:

cd dashboard
npm install
npm run dev

Open http://localhost:5173.

API Examples

For API details, see docs/api.md. For ClickHouse notes, see docs/clickhouse.md.

GET /api/analytics/overview?dateFrom=2025-01-01&dateTo=2026-05-23
GET /api/analytics/products?page=1&pageSize=20
GET /api/analytics/categories
GET /api/analytics/regions
GET /api/analytics/trend?grain=month
GET /api/analytics/top-products?pageSize=10
GET /api/analytics/slow-products?pageSize=10

Price simulation:

POST /api/price-simulations
Content-Type: application/json

{
  "productId": 1,
  "currentPrice": 100,
  "newPrice": 110,
  "elasticityPercent": -15,
  "dateFrom": "2025-01-01",
  "dateTo": "2026-05-23",
  "region": null
}

The API response wraps data with queryExecutionMs so the dashboard can show ClickHouse query timing.

Example ClickHouse Queries

Total revenue and profit:

SELECT
    sum(revenue) AS revenue,
    sum(quantity) AS quantity,
    sum(profit) AS profit,
    (profit / revenue) * 100 AS margin
FROM sales_analytics.sales_fact
WHERE sales_date >= '2025-01-01'
  AND sales_date < '2026-01-01';

Monthly trend:

SELECT
    toStartOfMonth(sales_date) AS month,
    sum(revenue) AS revenue,
    sum(quantity) AS quantity,
    sum(profit) AS profit
FROM sales_analytics.sales_fact
GROUP BY month
ORDER BY month;

Best products by revenue:

SELECT
    p.product_name,
    sum(sf.revenue) AS revenue,
    sum(sf.quantity) AS quantity
FROM sales_analytics.sales_fact sf
INNER JOIN sales_analytics.products p ON p.product_id = sf.product_id
GROUP BY p.product_name
ORDER BY revenue DESC
LIMIT 20;

Notes

• sales_fact is partitioned by month and ordered by (sales_date, product_id, region, channel).
• The worker treats each sales fact row as an order item record.
• Price simulation applies the requested elasticity directly to historical quantity. Example: a 10% price increase with elasticityPercent = -15 reduces expected quantity by 15%.
• The implementation uses ClickHouse HTTP with FORMAT JSON for queries and FORMAT JSONEachRow for ingestion.