> ## Documentation Index
> Fetch the complete documentation index at: https://help.treble.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Query Optimization

> How to write efficient queries against the Data Warehouse.

# 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.

```sql theme={null}
-- 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.

```sql theme={null}
-- 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:

```sql theme={null}
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                   |

<Note>
  `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.
</Note>

### 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`:

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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
```