SaaS analytics is not a BI report your ops team runs once a week. It is product infrastructure: usage charts inside your app, billing breakdowns per organization, feature adoption metrics that update as users click. Your customers expect these views to load quickly, reflect recent data, and show only their tenant's rows. ClickHouse® is an OLAP columnar database designed for high-throughput ingestion and analytical queries over large event datasets, per ClickHouse documentation.

This post covers the schema patterns, multi-tenant isolation, ingestion paths, and pre-aggregation strategies that SaaS teams use with ClickHouse for embedded analytics.

What SaaS analytics requires

Product analytics inside a SaaS application has different constraints than internal BI:

• Multi-tenant isolation. Customer A must never see Customer B's data, enforced at the query layer, not just the application layer.
• High concurrency. Hundreds or thousands of tenants query dashboards simultaneously during business hours.
• Continuous ingestion. Every API call, login, feature toggle, and billing event streams into the analytics layer without batch delays.
• Predictable query latency. Charts and filters need fast aggregation over large event volumes per tenant.

Row-oriented OLTP databases degrade on large analytical scans. ClickHouse addresses volume through columnar storage, vectorized execution, and sort keys aligned to tenant-scoped query patterns, as described in ClickHouse MergeTree documentation.

Event schema for SaaS products

The foundation is an append-only events table with tenant ID in the sort key:

CREATE TABLE saas_events
(
    event_time      DateTime64(3),
    tenant_id       LowCardinality(String),
    user_id         String,
    event_name      LowCardinality(String),
    feature         LowCardinality(String),
    plan_tier       LowCardinality(String),
    quantity        Float64,
    metadata        String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_time, event_name);

The sort key puts tenant_id first so queries filtered by tenant read contiguous granules. LowCardinality is a ClickHouse type that stores a dictionary of unique values and replaces columns with integer keys, reducing memory use when cardinality is low relative to row count, per ClickHouse LowCardinality documentation.

For usage-based billing, a separate table tracks billable units with the same tenant-first sort key:

CREATE TABLE usage_records
(
    recorded_at     DateTime64(3),
    tenant_id       LowCardinality(String),
    metric_name     LowCardinality(String),
    units           Float64
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(recorded_at)
ORDER BY (tenant_id, metric_name, recorded_at);

SummingMergeTree pre-aggregates units on merge, reducing storage for billing rollups that only need totals, not individual records.

Multi-tenant query patterns

Every SaaS analytics query includes a tenant filter. The pattern is consistent across dashboards:

SELECT
    toStartOfHour(event_time)   AS hour,
    event_name,
    count()                     AS event_count,
    uniq(user_id)               AS active_users
FROM saas_events
WHERE tenant_id = {tenant_id:String}
  AND event_time >= now() - INTERVAL 7 DAY
GROUP BY hour, event_name
ORDER BY hour;

For multi-tenant SaaS options, the tenant filter must be enforced server-side, not passed as an optional parameter the client can omit. Tinybird JWTs embed tenant_id as a fixed claim so the SQL Pipe always filters on the authenticated tenant.

Materialized views pre-aggregate per-tenant metrics on write:

CREATE MATERIALIZED VIEW saas_hourly_usage
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, hour, event_name)
AS SELECT
    tenant_id,
    toStartOfHour(event_time)   AS hour,
    event_name,
    count()                     AS event_count,
    uniqState(user_id)          AS active_users
FROM saas_events
GROUP BY tenant_id, hour, event_name;

Dashboard queries against the materialized view scan pre-aggregated rows instead of raw events. ClickHouse materialized views documentation describes how views populate target tables on insert.

Ingestion from SaaS applications

SaaS apps generate events from application servers, background workers, and webhooks. ClickHouse accepts them through multiple paths:

HTTP ingestion. The Events API accepts JSON batches from application backends. For SaaS products already posting events to an analytics endpoint, this is the lowest-friction path.

Kafka engine. For SaaS platforms using Kafka, ClickHouse can consume topics directly via the Kafka table engine:

CREATE TABLE saas_events_kafka
(
    event_time      DateTime64(3),
    tenant_id       String,
    user_id         String,
    event_name      String,
    feature         String,
    plan_tier       String,
    quantity        Float64,
    metadata        String
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'broker:9092',
    kafka_topic_list = 'saas-events',
    kafka_group_name = 'clickhouse-consumer',
    kafka_format = 'JSONEachRow';

CREATE MATERIALIZED VIEW saas_events_mv TO saas_events AS
SELECT * FROM saas_events_kafka;

Events flow from application to Kafka to ClickHouse without a custom consumer application.

For building an Insights page for your SaaS, the ingestion path from application events to queryable API typically takes a few hours with Tinybird rather than weeks building a warehouse pipeline.

Common SaaS analytics queries

Usage timeline. Token consumption, API calls, or seat activity over time:

SELECT
    toStartOfDay(event_time)    AS day,
    sum(quantity)               AS total_units
FROM saas_events
WHERE tenant_id = {tenant_id:String}
  AND event_name = 'api_call'
  AND event_time >= now() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day;

Feature adoption. Which features each tenant uses and how often:

SELECT
    feature,
    count()                     AS usage_count,
    uniq(user_id)               AS unique_users,
    count() / uniq(user_id)     AS avg_uses_per_user
FROM saas_events
WHERE tenant_id = {tenant_id:String}
  AND event_time >= now() - INTERVAL 7 DAY
GROUP BY feature
ORDER BY usage_count DESC;

Plan tier breakdown. For SaaS products with tiered pricing, aggregate usage by plan to identify upgrade candidates:

SELECT
    plan_tier,
    uniq(tenant_id)             AS tenant_count,
    sum(quantity)               AS total_usage
FROM saas_events
WHERE event_time >= now() - INTERVAL 30 DAY
GROUP BY plan_tier;

For user-facing analytics, these queries power the charts customers see inside your product, not internal ops dashboards.

Billing and usage-based pricing analytics

SaaS products on usage-based pricing need real-time visibility into consumption against plan limits. ClickHouse handles the aggregation; your billing system handles the charges.

SELECT
    tenant_id,
    metric_name,
    sum(units)                  AS total_units,
    sum(units) / 1000000        AS millions_of_units
FROM usage_records
WHERE recorded_at >= toStartOfMonth(now())
GROUP BY tenant_id, metric_name
HAVING total_units > 0
ORDER BY total_units DESC
LIMIT 100;

For usage-based pricing: should you build or buy, the analytics layer that tracks consumption is separate from the billing engine. ClickHouse stores and aggregates usage events. Stripe, Chargebee, or your billing provider handles invoicing.

Alerting on usage thresholds uses the same query pattern with a scheduled job or Tinybird Pipe called by your alerting system when total_units crosses a plan limit.

Pre-aggregation for dashboard performance

Raw event scans work for ad-hoc exploration. Production dashboards need pre-aggregated rollups:

TTL rules manage data lifecycle per tier:

ALTER TABLE saas_events
    MODIFY TTL event_time + INTERVAL 90 DAY;

Free-tier tenants might retain 30 days. Enterprise tenants retain 365 days. TTL runs automatically without manual partition drops.

For ClickHouse adtech platforms, the same multi-tenant event schema and rollup patterns apply to ad serving analytics, campaign reporting, and billing reconciliation across advertiser accounts.

Tinybird for SaaS analytics

Building SaaS analytics on raw ClickHouse requires managing cluster infrastructure, Kafka consumers, materialized view backfills, JWT authentication, and API generation. Tinybird is managed ClickHouse with these layers built in.

The workflow for SaaS teams:

1. Define event datasources with tenant ID in the sort key.
2. Stream events via the Events API or Kafka connector.
3. Write SQL Pipes with {tenant_id} parameters locked by JWT claims.
4. Publish Pipes as HTTP endpoints your frontend calls directly.

For real-time dashboard step by step, the path from mock data to production endpoints follows the same pattern: datasource, pipes, JWT-secured endpoints, frontend charts.

Resend, processing 100TB per month on Tinybird, measured 62ms p90 query latency in production without caching. Multi-tenant SaaS dashboards run inside that latency envelope. Tinybird is SOC 2 Type II certified.

Branch environments let SaaS teams iterate on schema changes and new chart queries without affecting production datasources. SQL Pipes version alongside your application code, so analytics features ship through the same CI/CD pipeline as product features.

When SaaS analytics becomes product infrastructure

Early-stage SaaS teams query Postgres for usage metrics. It works until aggregate queries start timing out, dashboards show stale data from nightly ETL jobs, or customers ask for real-time usage visibility that your architecture cannot deliver.

ClickHouse shifts analytics from a batch reporting problem to a real-time product feature. Events ingest continuously. Dashboards query fresh data. Tenant isolation is enforced at the API layer. The same engine handles 10 tenants and 10,000 tenants with the same query patterns.

For SaaS products where usage visibility drives retention, upgrades, and trust, the analytics layer is as critical as the application database. ClickHouse, via Tinybird, is the path most teams take once embedded analytics becomes a product requirement rather than a nice-to-have.