--- title: "Run clickhouse integration hevo data with a load contract" excerpt: "Configure Hevo into ClickHouse® with clear load modes and CDC-friendly tables. Drift, retries, and APIs for clickhouse integration hevo data." 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" --- Hevo Data is a managed **ELT / pipeline** product: connect sources, replicate on a schedule or via CDC, land in a destination. ClickHouse{% sup %}®{% /sup %} 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. ```text 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. ```sql 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: ```sql 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: ```sql 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** | Hevo mode (conceptual) | ClickHouse® implication | Typical sources | | --- | --- | --- | | Incremental append | `MergeTree`, tolerate duplicates or dedupe in views | event tables, logs | | Upsert / CDC | `ReplacingMergeTree` + version + delete flag | Postgres/MySQL OLTP | | Full load | Truncate/replace partition, or swap tables | small dimensions | | Historical + incremental | Backfill once with larger batches; then CDC | most DB sources | 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** ```sql 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** ```text 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: ```sql 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: ```text 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 ```sql 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](https://www.tinybird.co/blog/user-facing-analytics) without a second application database project. It also keeps the [real-time data ingestion](https://www.tinybird.co/blog/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: | Convention | Example | | --- | --- | | Database / prefix per source system | `salesforce.*`, `pg.*`, `stripe.*` | | Shared calendar dimensions | one `dim_date` | | Shared customer key strategy | map early in Pipes | | Raw vs modeled layers | `raw_*` vs `mart_*` or views | 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.