Query Optimization

The Data Warehouse is built on ClickHouse, a columnar database. This means queries behave differently than in a traditional relational database. Here’s how to leverage the structure to get fast results.

Key Principles

1. Always filter by date

All fact tables are partitioned by month (toYYYYMM(created_at)). Filtering by date allows ClickHouse to skip entire partitions without reading them.

-- Good: ClickHouse reads only 1 partition
SELECT * FROM fact_conversations
WHERE created_at >= '2026-04-01' AND created_at < '2026-05-01'

-- Bad: ClickHouse reads all partitions
SELECT * FROM fact_conversations
WHERE agent_name = 'Juan'

2. You don’t need to filter by company_id

Your user has a row policy that automatically filters by your company. You don’t need to add WHERE company_id = ... — it’s applied transparently.

3. Select only the columns you need

ClickHouse is columnar: it only reads the columns you mention in your SELECT. Fewer columns = less data read = faster.

-- Good: reads only 3 columns
SELECT conversation_id, created_at, status
FROM fact_conversations
WHERE created_at >= now() - INTERVAL 7 DAY

-- Avoid: reads all columns
SELECT *
FROM fact_conversations
WHERE created_at >= now() - INTERVAL 7 DAY

4. Use LIMIT to explore

When exploring data, use LIMIT to avoid pulling millions of rows:

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

Internal Table Structure

Each table has a sort key that determines how the data is organized on disk. Queries that filter by the first columns of the sort key are much more efficient.

Sort Keys by Table

Table	Sort Key	Filters efficiently by
`fact_conversations`	`(company_id, created_at, conversation_id)`	date, conversation_id
`fact_agent_messages`	`(company_id, created_at, message_id)`	date, message_id
`fact_redirections`	`(company_id, created_at, redirection_id)`	date
`fact_agent_status_changes`	`(company_id, agent_id, created_at)`	agent_id + date
`fact_agent_daily`	`(company_id, day, agent_id)`	day, agent_id
`fact_sessions`	`(company_id, created_at, session_id)`	date, session_id
`fact_deployment_status`	`(company_id, timestamps_eta, deployment_id)`	date
`fact_deployment_daily`	`(company_id, day, poll_id)`	day, poll_id
`fact_inbound_messages`	`(company_id, created_at, session_id)`	date
`fact_whatsapp_links`	`(company_id, created_at, event_id)`	date
`fact_hsm_responses`	`(company_id, response_date, interaction_answer_id)`	date

company_id is always the first column of the sort key. Since your user has an automatic filter by company, all your queries take advantage of this optimization without you having to do anything.

Secondary Indexes

Some tables have additional indexes that help filter by columns that aren’t in the sort key:

Table	Index	Column	Type	Useful for
`fact_agent_messages`	`idx_conversation_id`	`conversation_id`	minmax	Looking up messages from a specific conversation
`fact_agent_messages`	`idx_sender`	`sender`	bloom_filter	Filtering by `sender = 'AGENT'` or `sender = 'USER'`
`fact_sessions`	`idx_poll_id`	`poll_id`	minmax	Filtering by campaign
`fact_sessions`	`idx_inbound_outbound`	`inbound_outbound`	bloom_filter	Filtering by INBOUND/OUTBOUND type
`fact_deployment_status`	`idx_poll_id`	`poll_id`	minmax	Filtering by campaign
`fact_deployment_status`	`idx_status`	`status`	bloom_filter	Filtering by delivery status
`fact_inbound_messages`	`idx_poll_id`	`poll_id`	minmax	Filtering by campaign
`fact_hsm_responses`	`idx_hsm_id`	`hsm_id`	minmax	Filtering by HSM template
`fact_hsm_responses`	`idx_poll_id`	`poll_id`	minmax	Filtering by campaign

System Limits

Your user has the following limits to protect system stability:

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	2 GB

If your query exceeds any of these limits, it will be cancelled automatically. To avoid it:

Add tighter date filters
Select fewer columns
Use LIMIT
Pre-aggregate with GROUP BY instead of pulling individual rows

Common Patterns

JOIN between fact tables

You can cross tables using conversation_id or survey_user_id:

-- Messages from a conversation along with conversation data
SELECT
    fc.conversation_id,
    fc.agent_name,
    fm.created_at AS message_date,
    fm.sender,
    fm.content
FROM client_analytics.fact_conversations fc
INNER JOIN client_analytics.fact_agent_messages fm
    ON fm.conversation_id = fc.conversation_id
WHERE fc.created_at >= now() - INTERVAL 7 DAY
ORDER BY fm.created_at
LIMIT 1000

JOIN with dimensions

-- Productivity by team (team_name comes from the dimension)
SELECT
    at.team_name,
    sum(ad.chats_handled) AS chats,
    round(avg(ad.avg_first_response_sec), 0) AS avg_response_sec
FROM client_analytics.fact_agent_daily ad
INNER JOIN client_analytics.dim_agent_tags at ON at.agent_id = ad.agent_id
WHERE ad.day >= today() - 30
GROUP BY at.team_name
ORDER BY chats DESC

Custom service level

-- Define your own SLA threshold
SELECT
    toDate(created_at) AS day,
    count() AS conversations,
    countIf(first_response_sec <= 60) AS within_60s,
    countIf(first_response_sec <= 120) AS within_120s,
    countIf(first_response_sec <= 300) AS within_5min,
    round(countIf(first_response_sec <= 120) * 100.0 / count(), 1) AS sla_pct
FROM client_analytics.fact_conversations
WHERE created_at >= now() - INTERVAL 30 DAY
  AND first_response_sec IS NOT NULL
GROUP BY day
ORDER BY day DESC

Get Started

Meta

Build with Treble

Main Platform

Agent Platform

Integrations

Data Warehouse

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. Use LIMIT to explore

Internal Table Structure

Sort Keys by Table

Secondary Indexes

System Limits

Common Patterns

JOIN between fact tables

JOIN with dimensions

Custom service level

​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. Use LIMIT to explore

​Internal Table Structure

​Sort Keys by Table

​Secondary Indexes

​System Limits

​Common Patterns

​JOIN between fact tables

​JOIN with dimensions

​Custom service level

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. Use LIMIT to explore

Internal Table Structure

Sort Keys by Table

Secondary Indexes

System Limits

Common Patterns

JOIN between fact tables

JOIN with dimensions

Custom service level