Run clickhouse integration hevo data with a load contract

Hevo Data is a managed ELT / pipeline product: connect sources, replicate on a schedule or via CDC, land in a destination. ClickHouse^® is a strong destination when those pipelines feed interactive analytics instead of another slow warehouse copy.

A durable clickhouse integration hevo data setup starts with a load contract: what Hevo is allowed to create, how updates show up, and what you query after merges. Skip that contract and you will debug "why is this order still active?" with three different answers from three dashboards.

Confirm the destination capabilities you are buying

Before modeling tables, answer these in the Hevo UI / docs for your plan:

1. Is ClickHouse® a native destination, or are you landing in S3/Snowflake first?
2. Does Hevo create tables automatically, or do you bring DDL?
3. Are loads append, upsert, or full refresh per pipeline?
4. How are deletes represented (hard delete, flag, ignore)?
5. Who owns schema drift when a source adds a column?
6. What are Hevo's egress IPs for allowlisting?
7. Can you isolate DEV and PROD destinations cleanly?

If ClickHouse® is not native on your plan, use the same object-storage bridge pattern as other ELT tools: Hevo → S3 → ClickHouse® / Tinybird.

When Hevo is the right extractor

Hevo fits well when:

• You need many SaaS + DB sources without building connectors
• CDC from operational databases matters
• Your team wants a UI-operated pipeline, not Airflow-for-everything
• ClickHouse® / Tinybird is the serving layer, not the system of record

Hevo is a weaker fit when you only have one Kafka topic and a single consumer. Use a Kafka-native path instead.

Write the load contract first

Paste this into the pipeline runbook and make Hevo match it.

Source:           Postgres public.orders (CDC)
Destination:      analytics.orders_hevo
Load mode:        upsert on order_id
Version column:   _hevo_sourced_ts (or source updated_at)
Deletes:          soft flag _hevo_is_deleted = 1
Query contract:   FINAL + deleted = 0 for "current rows"
Freshness SLA:    < 15 minutes end-to-end
Owner:            data-platform@
On-call notes:    replay is safe; do not truncate prod

Without that paragraph, every on-call invents a different truth.

Write one contract per pipeline, not one vague wiki page for "Hevo."

Table design Hevo will not invent for you

Even when Hevo can CREATE TABLE, production teams should own engine settings.

CREATE TABLE analytics.orders_hevo (
    order_id          String,
    customer_id       String,
    status            LowCardinality(String),
    amount            Float64,
    currency          LowCardinality(String),
    source_updated_at DateTime64(3),
    _hevo_sourced_ts  DateTime64(3),
    _hevo_is_deleted  UInt8
)
ENGINE = ReplacingMergeTree(_hevo_sourced_ts)
PARTITION BY toYYYYMM(source_updated_at)
ORDER BY order_id;

Consumer view:

CREATE VIEW analytics.v_orders_current AS
SELECT
    order_id,
    customer_id,
    status,
    amount,
    currency,
    source_updated_at
FROM analytics.orders_hevo FINAL
WHERE _hevo_is_deleted = 0;

If Hevo only appends duplicates on retry, ReplacingMergeTree is what makes reruns safe. If Hevo full-refreshes a small dimension nightly, a replace-by-partition strategy may be cleaner than upsert semantics. Match engine to mode.

Append-only event tables

Not everything is CDC. For immutable events from a source Hevo pulls as append:

CREATE TABLE analytics.stripe_events_hevo (
    event_id    String,
    event_type  LowCardinality(String),
    event_time  DateTime64(3),
    payload     String,
    _hevo_ingested_at DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time, event_id);

Do not force ReplacingMergeTree on pure append streams. It adds FINAL tax for no benefit.

Pipeline modes and what they imply

Do not run CDC into an append-only table and hope UNIQUE exists. It does not.

Historical backfill tactics

1. Create the production table with final ORDER BY
2. Run historical load into a _backfill table or directly with larger Hevo batches
3. Switch to incremental/CDC only after row counts match the source within tolerance
4. Keep the backfill job config archived so you can rebuild after a bad migration

For large sources, backfill by date partitions if the source allows. A single multi-day firehose into ClickHouse® will work, but it makes failure recovery painful.

Transform in Hevo or after land?

Hevo offers transformation features. Use them for:

• Dropping columns you must never store (secrets, raw PAN, etc.)
• Light type cleanup
• Filtering rows that should never reach analytics (test accounts)
• Hashing emails when policy requires it

Push to ClickHouse® / Tinybird SQL:

• Rollups and funnels reused by many apps
• Join-heavy dimensional models
• API-facing metrics
• Slowly changing business definitions ("active subscriber")

If every Hevo transform is a one-off cleanup script, you will rebuild it when you add Tinybird or dbt later. Prefer landing cleansed facts, modeling metrics once in SQL.

Example post-land model

CREATE TABLE analytics.orders_daily_stats (
    day          Date,
    status       LowCardinality(String),
    orders       UInt64,
    gmv          Float64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, status);

CREATE MATERIALIZED VIEW analytics.orders_daily_stats_mv
TO analytics.orders_daily_stats AS
SELECT
    toDate(source_updated_at) AS day,
    status,
    count() AS orders,
    sum(amount) AS gmv
FROM analytics.orders_hevo
WHERE _hevo_is_deleted = 0
GROUP BY day, status;

Note: with ReplacingMergeTree sources, materialized views that aggregate raw parts can over-count before merges. Prefer aggregating from a deduped view / Pipe, or accept approximate rollups and document it.

Drift, retries, and the Monday morning pipeline

Failure modes specific to managed ELT into ClickHouse®:

1. Source column added → Hevo ships it → insert fails on strict schema. Mitigation: allow nullable add via controlled ALTER, or park extras in a JSON column.
2. Pipeline replay → duplicate versions. Mitigation: versioned ReplacingMergeTree.
3. Delete not modeled → "current" queries show zombies. Mitigation: require a delete flag in the contract.
4. Tiny batches on a huge table → part spam. Mitigation: raise Hevo load batching; schedule OPTIMIZE only on hot partitions if needed.
5. Timezone-naive timestamps → partitions skew. Mitigation: normalize to UTC in Hevo or on first ClickHouse® view.
6. Type widening (Int → String in source). Mitigation: stage in String-friendly landing columns when sources are messy SaaS APIs.
7. Silent success with zero rows. Mitigation: watermark alerts on max(_hevo_sourced_ts).

Schema drift playbook

1. Hevo alerts on schema change OR insert errors
2. Platform adds Nullable column / JSON catch-all in staging
3. Backfill if needed for last N days
4. Promote to modeled views/Pipes
5. Only then expose to BI / product APIs

Do not let auto-ALTER from an ELT tool be the only change management you have in production.

Connectivity and credentials

• Dedicated ClickHouse® user: hevo_writer with insert/select on target DB only
• TLS on 8443 for Cloud
• IP allowlisting for Hevo's egress ranges
• Secrets in Hevo's credential store, not shared Slack messages
• Separate destinations for DEV and PROD (accidental prod writes from a sandbox pipeline are a rite of passage you can skip)
• Network tests from Hevo before you schedule historical loads

Grant sketch:

CREATE USER hevo_writer IDENTIFIED BY '/* vault */';
GRANT INSERT, SELECT, CREATE TABLE ON analytics.* TO hevo_writer;
-- Prefer CREATE TABLE only if Hevo must auto-create; revoke later if you own DDL

Freshness and SLAs

Define freshness as:

source commit time
  → Hevo capture lag
  → Hevo load lag
  → ClickHouse® visibility (incl. FINAL if used)

A 5-minute Hevo schedule cannot meet a 30-second product SLA. For hot paths, keep Hevo for durable CDC into analytics tables, and use Events API / Kafka for sub-minute product events.

When to put Tinybird in front of the same pipelines

Hevo is for getting source data moving. Product features still need HTTP.

Pattern:

1. Hevo lands cleaned CDC tables in ClickHouse®-backed Tinybird (native or via S3)
2. Pipes encode the query contract (_hevo_is_deleted = 0, business filters)
3. Apps call endpoints instead of running BI SQL against raw Hevo tables

NODE orders_by_status
SQL >
    SELECT
        status,
        count() AS orders,
        sum(amount) AS gmv
    FROM orders_hevo
    WHERE _hevo_is_deleted = 0
      AND source_updated_at >= {{ DateTime(start_time, '2026-08-01 00:00:00') }}
    GROUP BY status

TYPE endpoint

That is how ELT teams get to user-facing analytics without a second application database project. It also keeps the real-time data ingestion story honest: Hevo for operational DB sync, Tinybird for serving.

Multi-source warehouses in ClickHouse®

Hevo users often land Salesforce + Postgres + Stripe into one analytics DB. Conventions that prevent chaos:

Do not join raw Hevo tables with conflicting customer identifiers in five ad hoc BI workbooks. Model once.

Acceptance tests for a new Hevo → ClickHouse® pipeline

1. Insert/update/delete a row in the source; prove the view matches within SLA
2. Replay the pipeline window; metrics stay flat
3. Add a benign source column in staging; pipeline still green
4. Revoke network access briefly; alerts fire; catch-up does not duplicate business counts
5. Compare source counts vs v_orders_current for a static historical day
6. Confirm PROD credentials cannot write to DEV tables and vice versa

Cost control

• Exclude unused source tables in Hevo (wide Postgres schemas are a trap)
• Drop bulky columns you never query
• Partition by time so TTL / drops are easy
• Prefer rollups for dashboards that only need daily grains
• Do not keep full-refresh pipelines on huge facts "because it is simpler"

Frequently Asked Questions (FAQ)

Does Hevo support ClickHouse® as a destination?

Support varies by Hevo plan and connector maturity. Confirm in your workspace. If native ClickHouse® is missing, land in S3 or a warehouse Hevo supports, then load ClickHouse® / Tinybird.

Should Hevo auto-create MergeTree tables?

Fine for spikes. For production, check in DDL yourself so ORDER BY and partitioning match query patterns.

How do I model source deletes?

Prefer a soft-delete flag and filter in views/Pipes. Hard ALTER DELETE per CDC event does not scale.

Hevo vs Fivetran vs Stitch for ClickHouse®?

Similar ELT job, different catalogs and pricing. The ClickHouse®-side contract (engine, version, deletes, drift) matters more than the logo on the extractor.

Why is FINAL slow on my Hevo tables?

Too many unmerged parts or an ORDER BY that does not match access patterns. Improve batching, run controlled OPTIMIZE on hot partitions, or query an aggregated mart instead of raw CDC tables.

Can I use Hevo for real-time product APIs?

Hevo can feed the tables those APIs read, but it is still an ELT cadence tool. For sub-second ingest of app events, pair Hevo (DB/SaaS sync) with Tinybird Events API or Kafka for the hot path.