--- title: "Land clickhouse integration segment events the right way" excerpt: "Ship Segment events into ClickHouse® via warehouse, Functions, or Tinybird. Keep identity off the fact table. clickhouse integration segment." authors: "Tinybird" categories: "AI Resources" createdOn: "2026-08-04 00:00:00" publishedOn: "2026-08-04 00:00:00" updatedOn: "2026-08-04 00:00:00" status: "published" --- Segment is a **customer data pipeline**: sources collect `track` / `identify` / `page` calls, then destinations fan that data out. ClickHouse{% sup %}®{% /sup %} is where you want those events when the question is "how many X did Y do in the last hour?" at interactive speed. A **clickhouse integration segment** project goes wrong when teams treat Segment as a database, or ClickHouse® as an identity graph. Split those jobs. Segment collects and routes. ClickHouse® aggregates and serves. Tinybird sits on the ClickHouse® side when you need HTTP without a custom API tier. ## **What you are integrating (be precise)** Segment moves **event streams and traits**, not warehouse models. | Call | Typical ClickHouse® use | Freshness usually needed | | --- | --- | --- | | `track` | Fact table for product analytics | seconds to minutes | | `page` / `screen` | Funnel and content metrics | seconds to minutes | | `identify` | Dimension table / enrichment join | minutes is often fine | | `group` | Account-level attributes for B2B | minutes is often fine | | `alias` | Identity stitching metadata | rarely needed in CH facts | If your Segment workspace is mostly dumping into Snowflake for Reverse ETL, ClickHouse® is an **additional analytical sink**, not a replacement for every destination. Keep ad networks, email tools, and Reverse ETL where they are. ### **Tracking plans are part of the integration** A ClickHouse® schema cannot save a chaotic tracking plan. Before you build sinks: - Name events in a stable vocabulary (`Signed Up` not `signed_up` / `signUp` / `SIGNUP`) - Require `messageId` uniqueness and consistent `timestamp` semantics - Decide which properties are "contracted" vs free-form - Block obviously broken events in Segment Protocols / plan enforcement if you have it Garbage in still becomes very fast garbage in ClickHouse®. ## **Path 1: Warehouse destination, then load ClickHouse®** Common and boring. Also reliable. ```text App → Segment → Snowflake/BigQuery/Redshift → scheduled export → ClickHouse® ``` Use when: - Segment already lands in a warehouse destination you cannot remove - Freshness can be minutes to hours - Governance wants one "official" Segment landing zone - Security prefers not to open a public HTTP insert path Load with explicit columns. Segment schemas grow whenever marketers add properties. ```sql CREATE TABLE segment.tracks ( message_id String, anonymous_id String, user_id String, event LowCardinality(String), ts DateTime64(3), properties String, context String, loaded_at DateTime ) ENGINE = ReplacingMergeTree(loaded_at) PARTITION BY toYYYYMM(ts) ORDER BY (event, ts, message_id); ``` Keep `properties` as JSON text (or ClickHouse® JSON type if you standardized on it). Promote only the keys you filter on (`plan`, `country`, `revenue`). ### **Export job sketch** ```sql -- warehouse → stage, then ClickHouse® pulls or a loader inserts SELECT id AS message_id, anonymous_id, user_id, event, timestamp AS ts, properties, context, CURRENT_TIMESTAMP() AS loaded_at FROM segment.tracks WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY); ``` Use a 1–3 day lookback so late-arriving Segment deliveries still land. `ReplacingMergeTree` on `message_id` keeps reprocessing safe. ## **Path 2: Segment Functions / webhooks into HTTP inserts** When you need fresher data than warehouse sync: ```text App → Segment → Function / webhook destination → ClickHouse® HTTP INSERT ↘ Tinybird Events API ``` A Function can POST NDJSON batches to ClickHouse® or Tinybird. You own retries, auth headers, and mapping from Segment's envelope to your columns. Sketch of the mapping you want in the Function: ```javascript // conceptual: one Segment track → one analytics row function toRow(event) { return { message_id: event.messageId, anonymous_id: event.anonymousId || "", user_id: event.userId || "", event: event.event, ts: event.timestamp, properties: JSON.stringify(event.properties || {}), context: JSON.stringify(event.context || {}), }; } ``` Batching pattern that will not melt inserts: ```text Buffer rows for 1–2 seconds OR until 500–2000 events POST NDJSON once On 429/5xx: exponential backoff, then dead-letter to S3/Kafka Never block the Segment Function on a single-row chatty loop ``` Example ClickHouse® insert target: ```bash curl -sS -X POST \ "https://your-clickhouse:8443/?query=INSERT%20INTO%20segment.tracks%20FORMAT%20JSONEachRow" \ -H "X-ClickHouse-User: segment_writer" \ -H "X-ClickHouse-Key: $PASSWORD" \ -H "Content-Type: application/x-ndjson" \ --data-binary @batch.ndjson ``` ## **Path 3: Object storage as a buffer** Some teams mirror Segment warehouse sync files (or Function flushes) into S3, then: ```sql INSERT INTO segment.tracks SELECT * FROM s3( 'https://bucket.s3.amazonaws.com/segment/tracks/**.parquet', 'AWS_KEY', 'AWS_SECRET', 'Parquet' ); ``` Useful when: - Security wants ClickHouse® to pull, not receive pushes - You already standardize on lake landing zones - Reprocessing a day means "rerun the INSERT," not "replay Segment" ## **Identity vs analytics (do not collapse them)** Segment's identity resolution wants a graph: anonymous ids merging into users, device stitching, profile traits. ClickHouse® wants append-friendly facts. **Recommended split:** 1. **Events fact table** keyed by `message_id`, sorted by `(event, ts)` 2. **Traits / identifies table** with `ReplacingMergeTree` on `user_id` + `received_at` 3. Joins in SQL for "events by plan", not profile mutation inside the fact table ```sql CREATE TABLE segment.identifies ( user_id String, email String, plan LowCardinality(String), traits String, received_at DateTime64(3) ) ENGINE = ReplacingMergeTree(received_at) ORDER BY user_id; ``` Enrichment query pattern: ```sql SELECT t.event, count() AS events, uniq(t.user_id) AS users FROM segment.tracks t LEFT JOIN segment.identifies i FINAL ON t.user_id = i.user_id WHERE t.ts >= now() - INTERVAL 7 DAY AND i.plan = 'pro' GROUP BY t.event ORDER BY events DESC; ``` If you overwrite fact rows every time identity merges, your event counts become fiction. Identity changes should update the dimension side, not rewrite history. ### **Anonymous traffic** Many product events arrive with only `anonymous_id`. Keep it on the fact table. Do not drop events because `user_id` is empty. Report both `uniq(user_id)` and `uniq(anonymous_id)` when measuring top-of-funnel. ## **Property chaos is the maintenance tax** Marketing will add `experiment_bucket_v17`. Engineering will not want 200 LowCardinality columns. Rules that age well: - Raw landing table accepts new keys inside `properties` - A thin "modeled" table or Tinybird Pipe projects stable fields - Alert when a property exceeds a cardinality budget before you promote it to a column - Never `SELECT *` from Segment-shaped tables into public APIs - Document promoted columns in the same place as the tracking plan Promoting a property: ```sql ALTER TABLE segment.tracks ADD COLUMN IF NOT EXISTS plan LowCardinality(String) DEFAULT ''; -- backfill from JSON for recent partitions only ALTER TABLE segment.tracks UPDATE plan = JSONExtractString(properties, 'plan') WHERE ts >= today() - 30 AND plan = ''; ``` Do this as a migration, not inside every insert Function. ## **Context fields worth promoting** Segment `context` often has more analytical value than random properties: | Context path | Why promote | | --- | --- | | `context.page.path` | content analytics | | `context.campaign.*` | attribution | | `context.userAgent` / device | client mix (carefully) | | `context.ip` | usually **do not** store raw in analytics | Default to hashing or dropping raw IPs unless you have a compliance reason and retention policy. ## **Billing and volume surprises** Segment charges on MTUs / events depending on contract. ClickHouse® storage is usually the cheap part. The expensive mistake is **dual-writing every destination plus an unfiltered ClickHouse® firehose**. Filter in Segment (tracking plans, destination filters) before the analytical sink: - Drop noisy `page` spam if funnels do not need it - Sample high-volume interaction events if product analytics only needs aggregates - Exclude internal users / QA sources in the Function or with a `context` filter Your MergeTree and your invoice will thank you. ## **Query patterns that should be fast** Design `ORDER BY` and promotions for these: ```sql -- activation funnel counts SELECT event, count(), uniq(user_id) FROM segment.tracks WHERE event IN ('Signed Up', 'Activated', 'Subscribed') AND ts >= now() - INTERVAL 14 DAY GROUP BY event; -- daily active users SELECT toDate(ts) AS day, uniq(user_id) FROM segment.tracks WHERE event = 'Application Opened' GROUP BY day ORDER BY day; ``` If every dashboard starts with `JSONExtract` on cold columns, promote those fields or precompute in a Pipe / materialized view. ## **Tinybird as the Segment analytics destination** When the reason you want ClickHouse® is product dashboards and APIs: 1. Stream Segment → Tinybird (Events API from a Function, or warehouse → S3 → Tinybird) 2. Model Pipes for funnels and activation metrics 3. Leave Reverse ETL and ad destinations in Segment as they are ```sql NODE signup_funnel SQL > SELECT event, count() AS events, uniq(user_id) AS users FROM tracks WHERE event IN ('Signed Up', 'Activated', 'Subscribed') AND ts >= {{ DateTime(start_time, '2026-08-01 00:00:00') }} GROUP BY event TYPE endpoint ``` Segment remains the collection and activation fabric. Tinybird is the fast analytical consumer for [user-facing analytics](https://www.tinybird.co/blog/user-facing-analytics). This is also where [real-time data ingestion](https://www.tinybird.co/blog/real-time-data-ingestion) matters: product features rarely want to wait for the next warehouse sync. ## **Security and privacy** - Separate Segment write keys per environment - Scope ClickHouse® / Tinybird tokens to insert-only for landing, read-only for apps - Decide PII policy before the first `identify` lands (email, phone, address) - Prefer trait hashing for sensitive fields when analytics only needs joins - Retain raw Segment payloads only as long as product + legal require ## **Validation before you cut traffic** 1. Compare `message_id` counts Segment debugger / delivery vs ClickHouse® for a 1-hour window 2. Confirm `identify` upserts change traits without duplicating `track` counts 3. Replay a day of events; prove `ReplacingMergeTree` / ids keep metrics stable 4. Load-test the Function/webhook path at 2× peak 5. Verify late events inside your lookback window still appear 6. Confirm internal-user filters actually drop QA traffic ## **Operational runbook** | Symptom | Check first | | --- | --- | | Missing events | Destination filters, Function errors, auth 401s | | Duplicate counts | Replay without `message_id` dedupe | | Trait join empty | `user_id` casing / empty ids on tracks | | Slow dashboards | `JSONExtract` overuse, wrong `ORDER BY` | | Cost spike | Unfiltered `page` volume, dual sinks | ## **Frequently Asked Questions (FAQ)** ### **Does Segment have a native ClickHouse® destination?** Not as a universal first-party warehouse-style destination in every catalog. Most teams use a warehouse hop, Functions/webhooks, or Tinybird. ### **Should anonymous_id or user_id be the sort key?** For event facts, prefer `(event, ts, message_id)`. Keep user keys as columns for filtering. Identity merges should not rewrite history in the fact table. ### **How do I handle nested properties?** Store JSON; promote hot keys. Nested object explosion in DDL is how Segment integrations rot. ### **Can ClickHouse® replace Segment?** No. Segment collects and routes. ClickHouse® analyzes. Different layers. ### **How fresh can Segment → ClickHouse® be?** Functions/webhooks: seconds if you batch lightly. Warehouse hop: whatever the Segment sync + export schedule is. Pick the path that matches the product SLA. ### **Should I land `page` calls at all?** Only if funnels or content analytics need them. High-volume `page` streams are a common way to burn Segment MTUs and ClickHouse® storage for dashboards nobody opens.