clickhouse integration stitch data — Close the Destination Gap in 2026

Stitch will extract Salesforce, HubSpot, Postgres, and a long list of SaaS sources on a schedule. ClickHouse^® 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):

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:

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:

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.

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.

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:

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

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