Zendesk Support to ClickHouse: How to Move Your Data
Move Zendesk Support into ClickHouse with Airbyte. Why repeated tickets are a movement history worth keeping, and how to sort for state or journeys.
Moving Zendesk Support into ClickHouse gives you fast aggregation over years of tickets. Zendesk reports adequately on recent volume and becomes slow once somebody asks how resolution times have moved across three years and forty agents.
This guide covers the managed path with Airbyte. Two things shape the build: tickets arrive repeatedly as they change, which is more useful here than it sounds, and the sorting key you choose decides which questions are cheap.
Zendesk Support to ClickHouse at a glance:
Why move data from Zendesk Support to ClickHouse?
Two situations account for most of these pipelines.
The first is interactive analysis at volume. Resolution times by queue across several years, contact rates by product area and how a process change affected backlog are heavy aggregations, and speed decides whether anybody investigates a pattern or assumes one.
The second is powering a dashboard support leadership watches daily. If you need governed access to comment text and joins to contracts and revenue, Zendesk Support to BigQuery serves that better than a column store will.
What do you need before you start?
Four things, and the last one is a design decision rather than a credential:
Your subdomain and a chosen authentication method. The subdomain is the part before zendesk.com in your account URL. The Zendesk Support source documentation covers the available methods and the streams.
A sense of which streams carry your volume. Tickets, comments and metric events grow with every conversation; groups, brands and custom field definitions are configuration numbering in the dozens.
A ClickHouse database and credentials. Records land in typed columns over the native protocol, and the types settled at table creation are the ones you keep.
A decision about current state versus history. Both are available from the same data, and which one your queries favour determines how you sort the table.
If your database restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.
How do you build a Zendesk Support to ClickHouse pipeline in Airbyte?
Step 1: Decide whether you want state or movement
Work out whether your questions are mostly about how things stand or about how they changed, because both are answerable and they want different table designs. Backlog by queue today is a state question. How long tickets spend in each status, or how often priority gets escalated, is a movement question, and answering it depends on keeping something most pipelines throw away.
Step 2: Configure the Zendesk Support source
Click Sources in the left navigation, then New Source, and select Zendesk Support, following adding a source. Supply the subdomain, authentication and a start date. Select the large conversational streams deliberately and take the configuration streams too, since they are small and make your dimensions readable.
Step 3: Configure the ClickHouse destination
Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply the host, port, database and credentials. Inspect the column types after the first sync, paying attention to status, priority and channel, which take a handful of values across millions of rows.
Step 4: Create the connection and sort deliberately
Click Connections, then New connection, select your streams and a sync mode. Then set the sorting key from the answer you gave in step one, because that is what decides whether ClickHouse skips data or scans it.
Expect the comment and metric event streams to dominate your sync time, since those grow with every exchange.
Why are repeated tickets worth keeping?
Because they are a record of movement rather than a mistake. A ticket changes many times between opening and closing, and each sync catches it as it stood, so the same ticket identifier appears repeatedly with different statuses, assignees and priorities. Most destinations treat that as something to collapse.
Here it is an opportunity, because scanning a lot of rows cheaply is precisely what this destination does. Sorted by ticket and by when each version was captured, those rows show how a ticket travelled: which queue it landed in, when it was escalated, how long it sat before somebody replied. That is a question Zendesk answers awkwardly and your table answers directly.
So keep both readings available. Use FINAL in views reporting current state, since counting the raw table inflates open tickets by however many times each was captured. And query the underlying rows when you want transitions, treating the sequence as the history it is. One pipeline, two genuinely different datasets, which is unusual enough to be worth designing for deliberately.
How should these tables be sorted?
According to which of those two readings dominates, because a sorting key that suits one makes the other slower. A table sorted by date first serves questions bounded by a period, which covers most reporting: tickets created last quarter, resolution times by month, volume by week.
A table sorted by ticket first serves questions about individual journeys, because every version of one ticket sits together. If your analysis is mostly about how tickets move through statuses, that ordering does considerably less work per query than a date-sorted table filtered down to one identifier.
Most teams want date first with the ticket identifier close behind, which serves reporting well and keeps journey queries reasonable. Type the repeating columns for low cardinality while you are there, since status, priority, channel and queue take a small set of values across millions of rows and that is exactly the case the optimisation exists for.
Frequently asked questions
Why does the same ticket appear many times?
Tickets change throughout their life and each sync captures the current version. Those rows are a movement history rather than an error.
My open ticket count is too high.
You are counting every captured version. Report current state through a view applying FINAL and keep the raw table for transition analysis.
What should I sort by?
Usually a date first with the ticket identifier close behind, which serves period reporting well without making individual ticket journeys expensive.
Which streams should I expect to be slow?
Comments and metric events, since they grow with every exchange rather than with every ticket. Configuration streams are trivial by comparison.
Can I do this without writing code?
The pipeline, yes. The sorting keys and the views separating current state from transitions are SQL, and they are what makes this fast and correct.
Get your Zendesk Support data into ClickHouse
Decide early whether your questions are about state or movement, because both are answerable from the same rows and they want different sorting. Treat repeated tickets as the change history they are rather than as duplicates to eliminate. Then report current state through views applying FINAL, sort by date with the ticket identifier close behind, and type status and channel columns for low cardinality.
Airbyte's connector catalog includes 600+ pre-built connectors, so support history can be analysed at whatever speed the questions demand. For storefront data into the same destination, see Shopify to ClickHouse, and for search performance into the same destination, Google Search Console to ClickHouse.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.