Query Optimization

The Analytics Warehouse runs on ClickHouse, a columnar database. Queries behave differently than in a traditional relational database — a few habits make them dramatically faster.

Key principles

1. Always filter by date

Data is physically organized by time. A date filter lets ClickHouse skip whole chunks of history without reading them — and since the warehouse now serves full history (not just 3 months), date filters matter more than ever. Each table has one primary time column to filter on:

Table	Filter on
`fact_treble_sessions`, `fact_treble_session_messages`, `fact_treble_session_variables`, `fact_ad_sessions`	`created_at`
`fact_treble_session_nodes`	`entered_at`
`fact_campaign_sends`	`scheduled_at`
`fact_campaign_daily`, `fact_agent_daily`	`day`
`fact_agent_conversations`, `fact_agent_conversation_messages`, `fact_agent_conversation_transfers`, `fact_agent_status_changes`, `fact_whatsapp_link_events`, `fact_target_events`	`created_at`
`fact_hsm_responses`	`responded_at`

-- Good: reads only the relevant slice of history
SELECT count() FROM fact_treble_sessions
WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01'

-- Bad: scans the full history of the table
SELECT count() FROM fact_treble_sessions
WHERE poll_name = 'Welcome flow'

2. You don’t need to filter by company_id

Your user has a row policy that filters by your company automatically, and the data is sorted by company first — every query benefits from this without you adding anything.

3. Select only the columns you need

ClickHouse reads only the columns you mention. This matters most on wide text columns (text, content, ai_automator_instructions): leaving them out of a SELECT can cut a query’s cost by an order of magnitude.

-- Good: 3 narrow columns
SELECT session_id, created_at, status
FROM fact_treble_sessions
WHERE created_at >= now() - INTERVAL 7 DAY

-- Avoid: SELECT * drags every wide column along
SELECT * FROM fact_treble_session_messages
WHERE created_at >= now() - INTERVAL 7 DAY

4. Aggregate in the database, not in your tool

Pull answers, not raw rows. A GROUP BY over millions of rows returns in well under a second; downloading those millions of rows into a BI tool doesn’t.

5. Use the daily rollups when they fit

fact_campaign_daily and fact_agent_daily pre-compute the most common dashboard metrics. A dashboard over a rollup reads thousands of rows instead of millions.

6. Use LIMIT when exploring

SELECT * FROM fact_treble_session_messages
WHERE created_at >= now() - INTERVAL 1 DAY
ORDER BY created_at DESC
LIMIT 100

JOIN tips

Join through ids, not names: session_id, agent_conversation_id, hsm_id — names are for display.
Put the small table on the right side of the JOIN — ClickHouse loads the right side into memory. Dimensions are always small; joining two big fact tables works best when both sides carry a date filter.
Include company_id in the join key when joining facts to dimensions (as the examples in this documentation do).

-- Sessions enriched with channel display name
SELECT s.poll_name, c.display_name, count() AS sessions
FROM fact_treble_sessions AS s
LEFT JOIN dim_channels AS c
    ON c.company_id = s.company_id AND c.channel_id = s.channel_id
WHERE s.created_at >= today() - 30
GROUP BY s.poll_name, c.display_name

System limits

Your user runs with protection limits that keep the platform stable for everyone:

Limit	Value
Maximum execution time	30 seconds
Maximum rows read	50 million
Maximum bytes read	5 GB
Maximum rows in result	500,000
Maximum memory per query	2 GB
Maximum columns read per query	50

If a query is cancelled by a limit:

Add or tighten the date filter (the fix in almost every case)
Drop unneeded columns — especially wide text columns
Aggregate with GROUP BY instead of pulling raw rows
Break very large extractions into month-sized chunks (see Incremental Sync for the pattern that avoids large extractions entirely)

Get Started

Meta

Build with Treble

Main Platform

Agent Platform

Integrations

Analytics Warehouse

Data Warehouse (Legacy)

Query Optimization

Query Optimization

Key principles

1. Always filter by date

2. You don’t need to filter by company_id

3. Select only the columns you need

4. Aggregate in the database, not in your tool

5. Use the daily rollups when they fit

6. Use LIMIT when exploring

JOIN tips

System limits

​Query Optimization

​Key principles

​1. Always filter by date

​2. You don’t need to filter by company_id

​3. Select only the columns you need

​4. Aggregate in the database, not in your tool

​5. Use the daily rollups when they fit

​6. Use LIMIT when exploring

​JOIN tips

​System limits

Query Optimization

Key principles

1. Always filter by date

2. You don’t need to filter by company_id

3. Select only the columns you need

4. Aggregate in the database, not in your tool

5. Use the daily rollups when they fit

6. Use LIMIT when exploring

JOIN tips

System limits