--- title: "clickhouse integration stitch data — Close the Destination Gap in 2026" excerpt: "Stitch has no native ClickHouse® destination. Land via S3 or a warehouse hop, then load. Practical clickhouse integration stitch data patterns." authors: "Tinybird" categories: "AI Resources" createdOn: "2026-07-27 00:00:00" publishedOn: "2026-07-27 00:00:00" updatedOn: "2026-07-27 00:00:00" status: "published" --- Stitch will extract Salesforce, HubSpot, Postgres, and a long list of SaaS sources on a schedule. ClickHouse{% sup %}®{% /sup %} will scan that data in milliseconds. The awkward part is the middle: **Stitch does not offer a first-party ClickHouse® destination**. A **clickhouse integration stitch data** setup is therefore not "pick ClickHouse® in the Stitch UI." It is a deliberate landing strategy. Object storage, an existing warehouse, then a load into ClickHouse® (or Tinybird). ## **What Stitch is actually good at** Stitch (Talend Stitch) is managed **ELT extraction**. It owns: - Source connectors and auth refresh for SaaS APIs - Incremental replication and historical catch-up - Soft system columns (`_sdc_batched_at`, `_sdc_sequence`, and friends) - Delivery into a **supported destination catalog** (Snowflake, BigQuery, Redshift, Postgres, S3-style landings depending on plan) It does **not** own ClickHouse® table engines, sort keys, or API serving. That is your job after the data lands. If your goal is "stop writing custom HubSpot extractors," Stitch still earns its keep. If your goal is "interactive product analytics," you need a second hop into ClickHouse®. ## **The destination gap (and why it matters)** Teams often discover the gap late: 1. Stitch is already wired to Snowflake or BigQuery 2. Dashboard queries get expensive or slow 3. Someone asks for ClickHouse® 4. There is no toggle in Stitch to retarget ClickHouse® Plan for that from day one. Treat Stitch as the **extractor**, not the analytical platform. ## **Strategy A: S3/GCS as the Stitch landing zone** This is the cleanest path when you can change or add a Stitch destination. **Flow:** sources → Stitch → S3/GCS prefix → ClickHouse® `s3()` / `gcs()` load (scheduled). **Why it works:** object storage is a neutral format Stitch understands. ClickHouse® reads Parquet/JSON/CSV from buckets efficiently. You control MergeTree design on the ClickHouse® side. **Stitch-side setup (conceptual):** ```text Destination: Amazon S3 (or GCS) Bucket: company-stitch-landing Prefix: stitch/{source}/{table}/ Format: JSON or CSV per Stitch export options ``` **ClickHouse®-side table built for SaaS upserts:** ```sql CREATE TABLE stitch.hubspot_contacts ( id String, email String, lifecycle_stage LowCardinality(String), owner_id String, updated_at DateTime, _sdc_batched_at DateTime, _sdc_sequence UInt64 ) ENGINE = ReplacingMergeTree(_sdc_batched_at) PARTITION BY toYYYYMM(updated_at) ORDER BY (lifecycle_stage, id); ``` **Incremental pull from the landing bucket:** ```sql INSERT INTO stitch.hubspot_contacts SELECT id, email, lifecycle_stage, owner_id, parseDateTimeBestEffort(updated_at) AS updated_at, parseDateTimeBestEffort(_sdc_batched_at) AS _sdc_batched_at, _sdc_sequence FROM s3( 'https://company-stitch-landing.s3.amazonaws.com/stitch/hubspot/contacts/**.json.gz', 'AWS_ACCESS_KEY_ID', 'AWS_SECRET_ACCESS_KEY', 'JSONEachRow' ) WHERE toDate(_sdc_batched_at) >= today() - INTERVAL 3 DAY; ``` Schedule that statement with Airflow, cron, or ClickHouse® Cloud scheduled queries. Scope the `WHERE` to a watermark so each run is cheap and rerunnable. ## **Strategy B: Keep the warehouse, add ClickHouse® as a speed layer** If Stitch must keep writing to Snowflake/BigQuery/Postgres for other teams, do not rip that out. Add ClickHouse® as a **read-optimized copy**. **Flow:** Stitch → warehouse (unchanged) → export changed partitions → ClickHouse®. Useful when: - Finance/BI still lives in the warehouse - ClickHouse® is only for product analytics or expensive rollups - You need a gradual cutover Cost: two stores, two schemas to keep honest. Mitigate with a single export job keyed on `_sdc_batched_at` and explicit column lists (never `SELECT *` into production APIs). ## **Working with Stitch system columns** Stitch metadata is useful if you treat it as **load control**, not business dimensions. | Column | Use in ClickHouse® | | --- | --- | | `_sdc_batched_at` | Watermark for incremental loads; good `ReplacingMergeTree` version | | `_sdc_sequence` | Tie-break when timestamps collide | | `_sdc_table_version` | Detect hard resyncs / table rebuilds | | Source `updated_at` | Prefer for analytics filters when trustworthy | **Do not** put `_sdc_*` fields in dashboard group-bys. **Do** keep them on the raw table and strip them in a view or Tinybird Pipe for consumers. ```sql CREATE VIEW stitch.v_hubspot_contacts AS SELECT id, email, lifecycle_stage, owner_id, updated_at FROM stitch.hubspot_contacts FINAL; ``` ## **SaaS schema drift is the real maintenance cost** HubSpot and Salesforce add properties constantly. Stitch will start shipping new fields. ClickHouse® will not magically widen your table unless you `ALTER TABLE`. Operational rules that prevent weekend pages: 1. Load jobs use **named columns**, not `SELECT *` 2. New fields land in a `properties_json String` catch-all until modeled 3. Alert when Stitch sync logs show schema changes 4. Promote important fields to real columns in a migration, not mid-load ## **Freshness expectations (be honest)** End-to-end lag is: ```text Stitch sync interval + object storage / warehouse settle time + ClickHouse® load schedule ``` That is usually **tens of minutes to hours**, not seconds. For product features that need second-level freshness, keep Stitch for SaaS dimensions and use a separate event path (Kafka or Tinybird Events API) for hot facts. Join them in ClickHouse®. ## **When Tinybird is the better ClickHouse® layer** Choose Tinybird when the reason you want ClickHouse® is **APIs and product metrics**, not another warehouse clone. Pattern: 1. Stitch lands files in S3 2. Tinybird S3 connector ingests on a schedule 3. SQL Pipes publish authenticated HTTP endpoints ```sql NODE contacts_by_stage SQL > SELECT lifecycle_stage, count() AS contacts FROM hubspot_contacts GROUP BY lifecycle_stage ORDER BY contacts DESC TYPE endpoint ``` You keep Stitch's connector coverage. You skip ClickHouse® cluster ops. Product clients call HTTP instead of warehouse SQL. That is the usual end state for [user-facing analytics](https://www.tinybird.co/blog/user-facing-analytics) on CRM data. ## **Implementation checklist** - [ ] Confirm Stitch destination can be S3/GCS, or accept a warehouse hop - [ ] Create MergeTree / `ReplacingMergeTree` tables before first load - [ ] Map `_sdc_batched_at` into your watermark logic - [ ] Ban `SELECT *` in load SQL and APIs - [ ] Define freshness SLA as Stitch cadence + load lag - [ ] Decide who owns schema promotions when SaaS fields appear - [ ] If APIs matter, evaluate Tinybird before building a custom serving tier ## **FAQ** ### **Does Stitch support ClickHouse® natively?** No. Use S3/GCS landing plus ClickHouse® table functions, a warehouse export, or Tinybird's object-storage connector. ### **Can I point Stitch Postgres destination at ClickHouse®?** No. ClickHouse® is not a Postgres wire-compatible warehouse for Stitch's destination expectations. Land somewhere Stitch supports, then load ClickHouse® on purpose. ### **How do I dedupe Stitch re-deliveries?** `ReplacingMergeTree` versioned on `_sdc_batched_at` (or source `updated_at`), then `FINAL` / `argMax` views for consumers. ### **What breaks first in production?** Schema drift and watermark mistakes. Explicit columns and a 2–3 day lookback on incremental loads fix most incidents.