<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Striim, Inc</title>
    <description>The latest articles on DEV Community by Striim, Inc (@striim_inc).</description>
    <link>https://dev.to/striim_inc</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4243461%2F087b735f-6701-4113-879b-ba9be658e1a4.png</url>
      <title>DEV Community: Striim, Inc</title>
      <link>https://dev.to/striim_inc</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/striim_inc"/>
    <language>en</language>
    <item>
      <title>Streaming Oracle CDC into ClickHouse with Striim</title>
      <dc:creator>Striim, Inc</dc:creator>
      <pubDate>Mon, 16 Nov 2026 23:18:17 +0000</pubDate>
      <link>https://dev.to/striim_inc/streaming-oracle-cdc-into-clickhouse-with-striim-9p7</link>
      <guid>https://dev.to/striim_inc/streaming-oracle-cdc-into-clickhouse-with-striim-9p7</guid>
      <description>&lt;p&gt;This post walks through a complete Oracle-to-ClickHouse CDC pipeline using Striim’s ClickHouseWriter. It covers the Oracle source prerequisites, how to pick the right MergeTree engine for your workload, what the writer does internally with each batch, and how to tune the pipeline for sustained production throughput.&lt;/p&gt;

&lt;p&gt;The running example is an Oracle &lt;code&gt;TPCH.ORDERS&lt;/code&gt; table being replicated into ClickHouse via real-time CDC.&lt;/p&gt;

&lt;p&gt;While this post focuses on Oracle, ClickHouseWriter receives CDC events from any Striim source — Oracle, SQL Server, Snowflake, Kafka, MongoDB, Salesforce, and more. Every source lands on the same write path: data streamed directly over JDBC into ClickHouse, with no intermediate object store.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F6zjui3mxu35tuxtafahq.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2F6zjui3mxu35tuxtafahq.png" alt=" " width="800" height="594"&gt;&lt;/a&gt;&lt;br&gt;
&lt;em&gt;Fig. 1 — Every supported source lands on one write path: stream directly over JDBC into ClickHouse, no intermediate object store.&lt;/em&gt;&lt;/p&gt;
&lt;h2&gt;
  
  
  1. Oracle Source Setup
&lt;/h2&gt;

&lt;p&gt;ClickHouseWriter receives CDC events from any Striim source, but Oracle has specific prerequisites that determine what data the writer can work with.&lt;/p&gt;
&lt;h3&gt;
  
  
  Supplemental Logging
&lt;/h3&gt;

&lt;p&gt;Oracle’s redo log only records changed columns by default. For CDC replication, the writer needs full row images. Enable supplemental logging at the table level:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;ALTER&lt;/span&gt; &lt;span class="k"&gt;DATABASE&lt;/span&gt; &lt;span class="k"&gt;ADD&lt;/span&gt; &lt;span class="no"&gt;SUPPLEMENTAL&lt;/span&gt; &lt;span class="no"&gt;LOG&lt;/span&gt; &lt;span class="no"&gt;DATA&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;ALTER&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="no"&gt;TPCH&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="no"&gt;ORDERS&lt;/span&gt; &lt;span class="k"&gt;ADD&lt;/span&gt; &lt;span class="no"&gt;SUPPLEMENTAL&lt;/span&gt; &lt;span class="no"&gt;LOG&lt;/span&gt; &lt;span class="no"&gt;DATA&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;ALL&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="no"&gt;COLUMNS&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;SUPPLEMENTAL LOG DATA (ALL) COLUMNS&lt;/code&gt; writes every column value to the redo log on every change, not just the modified columns. This is required for:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Before-images:&lt;/strong&gt; &lt;code&gt;optimizedMerge&lt;/code&gt; requires full row images to fill unchanged columns on &lt;code&gt;UPDATE&lt;/code&gt;. Without &lt;code&gt;ALL COLUMNS&lt;/code&gt; supplemental logging, before-images may be incomplete — only the modified columns and primary key are captured in the redo log.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Partition/sorting key pruning:&lt;/strong&gt; The writer collects distinct partitions and sort-key values from each batch to narrow &lt;code&gt;DELETE&lt;/code&gt; scope. If those column values are missing from the redo log, the optimization is silently disabled for that batch.&lt;/li&gt;
&lt;/ul&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;strong&gt;Gotcha:&lt;/strong&gt; If you enable supplemental logging at the database level (&lt;code&gt;ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS&lt;/code&gt;), it applies to every table. For large databases, prefer table-level logging on only the tables being replicated.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Note:&lt;/strong&gt; Striim does not mandate &lt;code&gt;SUPPLEMENTAL LOG DATA (ALL) COLUMNS&lt;/code&gt;. PK column or minimal supplemental logging is also fine. Please use the OptimizedMerge feature of the ClickHouseWriter. See &lt;a href="https://www.striim.com/docs/en/clickhouse-writer-programmer-s-reference.html" rel="noopener noreferrer"&gt;Striim documentation&lt;/a&gt;.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;h3&gt;
  
  
  OracleReader Configuration
&lt;/h3&gt;

&lt;p&gt;The source side of the pipeline:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;OR&lt;/span&gt; &lt;span class="k"&gt;REPLACE&lt;/span&gt; &lt;span class="no"&gt;SOURCE&lt;/span&gt; &lt;span class="nc"&gt;OracleSource&lt;/span&gt; &lt;span class="k"&gt;USING&lt;/span&gt; &lt;span class="nc"&gt;Global&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;OracleReader&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="nc"&gt;Tables&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;                &lt;span class="s1"&gt;'"TPCH"."ORDERS"'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;connectionProfileName&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s1"&gt;'admin.Oracle_CP'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;SendBeforeImage&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;       &lt;span class="kc"&gt;true&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;DictionaryMode&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;        &lt;span class="s1"&gt;'OnlineCatalog'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;FetchSize&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;             &lt;span class="mi"&gt;1000&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="no"&gt;OUTPUT&lt;/span&gt; &lt;span class="k"&gt;TO&lt;/span&gt; &lt;span class="nc"&gt;CDCStream&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The properties that matter for ClickHouse:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Property&lt;/th&gt;
&lt;th&gt;Value&lt;/th&gt;
&lt;th&gt;Why it matters for ClickHouse&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;SendBeforeImage&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;true&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Required for MergeTree + &lt;code&gt;optimizedMerge&lt;/code&gt; (fills unchanged columns from before-image) and partition/sorting key pruning (uses before-image values for &lt;code&gt;UPDATE&lt;/code&gt;/&lt;code&gt;DELETE&lt;/code&gt; events).&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;DictionaryMode&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;OnlineCatalog&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Reads the Oracle data dictionary from the online catalog.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;FetchSize&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;1000&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Rows per initial-load fetch.&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;OracleReader emits a Striim-specific event (&lt;code&gt;WAEvent&lt;/code&gt;) that tracks the data and metadata of each DML operation. Each event carries the table name, operation type (&lt;code&gt;INSERT&lt;/code&gt;/&lt;code&gt;UPDATE&lt;/code&gt;/&lt;code&gt;DELETE&lt;/code&gt;/&lt;code&gt;PKUPDATE&lt;/code&gt;), table metadata via a &lt;code&gt;typeUUID&lt;/code&gt;, and the row data as &lt;code&gt;Object[]&lt;/code&gt; arrays (after-image and, when &lt;code&gt;SendBeforeImage&lt;/code&gt; is enabled, before-image).&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Choosing Your Engine
&lt;/h2&gt;

&lt;p&gt;The &lt;code&gt;TableEngine&lt;/code&gt; property on ClickHouseWriter determines how &lt;code&gt;INSERT&lt;/code&gt;, &lt;code&gt;UPDATE&lt;/code&gt;, and &lt;code&gt;DELETE&lt;/code&gt; operations physically land in ClickHouse. This is the highest-leverage decision in the pipeline.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;MergeTree (MERGE mode):&lt;/strong&gt; Merging the CDC operation is handled by Striim’s internal algorithm. Every update is handled as delete followed by insert. Supports &lt;code&gt;optimizedMerge&lt;/code&gt; for source partial row images. &lt;em&gt;(Replay of DML Events)&lt;/em&gt;&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;ReplacingMergeTree:&lt;/strong&gt; Every CDC operation becomes an &lt;code&gt;INSERT&lt;/code&gt; with a monotonic &lt;code&gt;__STRIIM_VERSION&lt;/code&gt; and an &lt;code&gt;__STRIIM_ISDELETED&lt;/code&gt; flag. ClickHouse’s background merge keeps only the highest-version row per &lt;code&gt;ORDER BY&lt;/code&gt; key. Deletes are tombstones (&lt;code&gt;ISDELETED=1&lt;/code&gt;). &lt;em&gt;(Replay of DML Events; Versioned Rows)&lt;/em&gt;&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;CollapsingMergeTree:&lt;/strong&gt; Every CDC operation becomes an &lt;code&gt;INSERT&lt;/code&gt;. Signed rows: &lt;code&gt;__STRIIM_SIGN = +1&lt;/code&gt; (state) or &lt;code&gt;-1&lt;/code&gt; (cancellation). ClickHouse cancels matched pairs during background merge. Requires before-images from the source. Order-sensitive. &lt;em&gt;(Replay of DML Events; Signed Rows)&lt;/em&gt;&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;CoalescingMergeTree:&lt;/strong&gt; ClickHouse 25.6+. Partial-image updates: &lt;code&gt;NULL&lt;/code&gt; means “column unchanged.” The writer inserts only changed columns; ClickHouse coalesces during background merges. No &lt;code&gt;DELETE&lt;/code&gt;/cancellation overhead. &lt;em&gt;(Replay of DML Events)&lt;/em&gt;&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;SummingMergeTree:&lt;/strong&gt; Numeric columns are summed on merge. Append-only: no &lt;code&gt;UPDATE&lt;/code&gt;/&lt;code&gt;DELETE&lt;/code&gt; semantics. Suited for pre-aggregated rollup targets. &lt;em&gt;(Append-only)&lt;/em&gt;&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;AggregatingMergeTree:&lt;/strong&gt; Pre-aggregated analytical targets using &lt;code&gt;SimpleAggregateFunction&lt;/code&gt; columns. Append-only, no CDC mutation support. &lt;em&gt;(Append-only)&lt;/em&gt;&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Which engine for Oracle CDC?
&lt;/h3&gt;

&lt;p&gt;For a general Oracle OLTP table with mixed &lt;code&gt;INSERT&lt;/code&gt;/&lt;code&gt;UPDATE&lt;/code&gt;/&lt;code&gt;DELETE&lt;/code&gt; traffic:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;MergeTree + MERGE:&lt;/strong&gt; Use when you need the target table to always reflect current state without &lt;code&gt;FINAL&lt;/code&gt;, or when downstream consumers (BI tools, dashboards) cannot filter on &lt;code&gt;ISDELETED&lt;/code&gt;. Higher write cost due to per-batch &lt;code&gt;DELETE&lt;/code&gt; mutations. Enable &lt;code&gt;optimizedMerge&lt;/code&gt; when the Oracle source sends partial row images to fill unchanged columns from the before-image before compaction.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;ReplacingMergeTree:&lt;/strong&gt; Start here. All operations become &lt;code&gt;INSERT&lt;/code&gt;s, so write throughput is high and there are no mutation-based &lt;code&gt;DELETE&lt;/code&gt;s. Query with &lt;code&gt;SELECT ... FINAL WHERE __STRIIM_ISDELETED = 0&lt;/code&gt; to get current state. The trade-off: queries without &lt;code&gt;FINAL&lt;/code&gt; see stale/duplicate rows until background merge runs.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;CoalescingMergeTree:&lt;/strong&gt; Use for partial-image workloads (ClickHouse 25.6+) where most &lt;code&gt;UPDATE&lt;/code&gt;s touch only a few columns. The writer inserts only changed columns; &lt;code&gt;NULL&lt;/code&gt; means “column unchanged,” and ClickHouse coalesces during background merges, avoiding full-row writes.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;NULL caveat:&lt;/strong&gt; If your Oracle source has legitimate &lt;code&gt;NULL&lt;/code&gt; values — for example, a nullable &lt;code&gt;SHIP_DATE&lt;/code&gt; set to &lt;code&gt;NULL&lt;/code&gt; on cancellation — those &lt;code&gt;NULL&lt;/code&gt;s are silently dropped during coalescing. Use MergeTree + &lt;code&gt;MERGE&lt;/code&gt; with &lt;code&gt;optimizedMerge&lt;/code&gt; instead for tables where &lt;code&gt;NULL&lt;/code&gt; is a meaningful data value.&lt;/p&gt;

&lt;p&gt;CoalescingMergeTree has no built-in compaction pass for &lt;code&gt;DELETE&lt;/code&gt;s, but the writer handles them via a configurable delete strategy: &lt;code&gt;LightweightDelete&lt;/code&gt; (default, fast soft-delete; physical removal deferred to background merge) or &lt;code&gt;MutationDelete&lt;/code&gt; (immediate physical removal, higher write I/O).&lt;/p&gt;

&lt;h3&gt;
  
  
  Oracle to ClickHouse Data Type Mapping
&lt;/h3&gt;

&lt;p&gt;The writer handles type conversion automatically. Key mappings for Oracle types:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Oracle Type&lt;/th&gt;
&lt;th&gt;ClickHouse Type&lt;/th&gt;
&lt;th&gt;Notes&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;code&gt;NUMBER(p,0)&lt;/code&gt; where p ≤ 18&lt;/td&gt;
&lt;td&gt;&lt;code&gt;Int64&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Integer-range numbers. &lt;code&gt;NUMBER(p,0)&lt;/code&gt; with p ≤ 9 maps to &lt;code&gt;Int32&lt;/code&gt;.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;code&gt;NUMBER(p,s)&lt;/code&gt; where s &amp;gt; 0&lt;/td&gt;
&lt;td&gt;&lt;code&gt;Decimal(p,s)&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Fixed-point. ClickHouse Decimal supports up to &lt;code&gt;Decimal128(38,s)&lt;/code&gt;.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;code&gt;FLOAT&lt;/code&gt;, &lt;code&gt;BINARY_DOUBLE&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;&lt;code&gt;Float64&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;code&gt;VARCHAR2(n)&lt;/code&gt;, &lt;code&gt;CHAR(n)&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;&lt;code&gt;String&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;ClickHouse String is unbounded; no length constraint carried over.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;code&gt;CLOB&lt;/code&gt;, &lt;code&gt;NCLOB&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;&lt;code&gt;String&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Large text. Ensure &lt;code&gt;UploadPolicy&lt;/code&gt; batch sizes account for payload size.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;DATE&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;DateTime&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Oracle DATE includes time; ClickHouse DateTime is second-precision.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;TIMESTAMP&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;code&gt;DateTime64(6)&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Microsecond precision. Oracle &lt;code&gt;TIMESTAMP(9)&lt;/code&gt; is truncated to 6.&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;code&gt;RAW&lt;/code&gt;, &lt;code&gt;BLOB&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;&lt;code&gt;String&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Hex-encoded. Binary columns are not natively supported in ClickHouse ingest.&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  3. The Write Path
&lt;/h2&gt;

&lt;p&gt;ClickHouseWriter streams data directly to ClickHouse over the JDBC v2 client. There is no intermediate object store (no S3, GCS, or ADLS staging). Batches are serialized in memory and sent over the JDBC connection.&lt;/p&gt;

&lt;h3&gt;
  
  
  Batch Lifecycle
&lt;/h3&gt;

&lt;p&gt;Events accumulate in a buffer until the &lt;code&gt;UploadPolicy&lt;/code&gt; threshold is reached (event count or time interval, whichever fires first). At rollover, the writer processes the batch.&lt;/p&gt;

&lt;p&gt;For MergeTree, when &lt;code&gt;Mode&lt;/code&gt; is set to &lt;code&gt;MERGE&lt;/code&gt;, multiple change records with the same &lt;code&gt;ORDER BY&lt;/code&gt; value are compacted and the latest snapshot of a record is applied to the target table. This applies for &lt;code&gt;optimizedMerge&lt;/code&gt; too. By default, with &lt;code&gt;APPEND_ONLY&lt;/code&gt; mode all events are inserted, so an audit log of all changes is preserved.&lt;/p&gt;

&lt;p&gt;For ReplacingMergeTree, every CDC operation is transformed into an &lt;code&gt;INSERT&lt;/code&gt; with a monotonically increasing &lt;code&gt;__STRIIM_VERSION&lt;/code&gt; and sent directly.&lt;/p&gt;

&lt;p&gt;The engine-specific transformation per operation:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;CDC Operation&lt;/th&gt;
&lt;th&gt;MergeTree (&lt;code&gt;MERGE&lt;/code&gt;)&lt;/th&gt;
&lt;th&gt;ReplacingMergeTree&lt;/th&gt;
&lt;th&gt;CoalescingMergeTree&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;INSERT&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Staged, compacted, then &lt;code&gt;INSERT&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;1 &lt;code&gt;INSERT&lt;/code&gt; (&lt;code&gt;ISDELETED=0&lt;/code&gt;)&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;INSERT&lt;/code&gt; of all columns (no &lt;code&gt;NULL&lt;/code&gt; coalescing needed)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;UPDATE&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Staged, compacted, then &lt;code&gt;DELETE&lt;/code&gt; old + &lt;code&gt;INSERT&lt;/code&gt; new&lt;/td&gt;
&lt;td&gt;1 &lt;code&gt;INSERT&lt;/code&gt; (new values, higher version deduplicates old)&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;INSERT&lt;/code&gt; of changed columns only; &lt;code&gt;NULL&lt;/code&gt; = unchanged; ClickHouse coalesces at merge&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;DELETE&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Staged, &lt;code&gt;DELETE&lt;/code&gt; from target&lt;/td&gt;
&lt;td&gt;1 &lt;code&gt;INSERT&lt;/code&gt; (tombstone, &lt;code&gt;ISDELETED=1&lt;/code&gt;)&lt;/td&gt;
&lt;td&gt;Executed via configurable delete strategy: &lt;code&gt;LightweightDelete&lt;/code&gt; (default) or &lt;code&gt;MutationDelete&lt;/code&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;PK UPDATE&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;DELETE&lt;/code&gt; old key + &lt;code&gt;INSERT&lt;/code&gt; new key (special path)&lt;/td&gt;
&lt;td&gt;2 &lt;code&gt;INSERT&lt;/code&gt;s (old-key tombstone + new-key insert)&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;DELETE&lt;/code&gt; old key + &lt;code&gt;INSERT&lt;/code&gt; new key with changed columns&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h3&gt;
  
  
  Partition and Sorting Key Pruning
&lt;/h3&gt;

&lt;p&gt;In the MergeTree engine with &lt;code&gt;MERGE&lt;/code&gt; mode, the &lt;code&gt;DELETE&lt;/code&gt; query that removes superseded keys from the target needs to scan for matching rows. Without optimization, this scans the entire table. The Striim ClickHouseWriter automatically narrows the scan.&lt;/p&gt;

&lt;p&gt;Per batch, the writer collects every distinct value of each partition key column and each sorting key column from the event data. It appends these as &lt;code&gt;IN&lt;/code&gt;-list filters to the &lt;code&gt;DELETE&lt;/code&gt; statement. The &lt;code&gt;IN (...)&lt;/code&gt; clause lets the sparse index skip granules within the partition. Together, these reduce the mutation from a full-table scan to a narrow range.&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;&lt;strong&gt;Before-image correctness:&lt;/strong&gt; For &lt;code&gt;UPDATE&lt;/code&gt; and &lt;code&gt;PKUPDATE&lt;/code&gt; events, the pruning filter uses the before-image column values (the old row), because the &lt;code&gt;DELETE&lt;/code&gt; targets the row that currently exists in the target. For &lt;code&gt;INSERT&lt;/code&gt; and &lt;code&gt;DELETE&lt;/code&gt; events, it uses the after-image. If any partition/sorting key column is missing from the event (supplemental logging not enabled for that column), pruning is disabled for the entire batch.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;h3&gt;
  
  
  Delete Strategies
&lt;/h3&gt;

&lt;p&gt;MergeTree &lt;code&gt;MERGE&lt;/code&gt; mode supports two delete mechanisms, controlled by the &lt;code&gt;DeleteStrategy&lt;/code&gt; property:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;DELETE&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;target_table&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;pk_columns&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="k"&gt;DISTINCT&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;pk_columns&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;stage&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;ALTER&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;target_table&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;DELETE&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;pk_columns&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="k"&gt;DISTINCT&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;pk_columns&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;stage&lt;/span&gt;&lt;span class="o"&gt;&amp;gt;&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;LightweightDelete&lt;/code&gt; marks rows as deleted; physical removal happens during ClickHouse’s background merge. Writes are fast, but deleted rows are briefly visible to concurrent readers (filtered by a hidden mask column). &lt;code&gt;MutationDelete&lt;/code&gt; rewrites the affected data parts immediately. Higher write I/O, but deleted rows are physically gone after the statement completes. Use &lt;code&gt;MutationDelete&lt;/code&gt; when compliance requirements, such as GDPR right-to-erasure, demand immediate physical removal.&lt;/p&gt;

&lt;h3&gt;
  
  
  Primary Key Updates
&lt;/h3&gt;

&lt;p&gt;ClickHouse has no native &lt;code&gt;UPDATE&lt;/code&gt; on &lt;code&gt;ORDER BY&lt;/code&gt; key columns. The writer handles an update to the &lt;code&gt;ORDER BY&lt;/code&gt; keys as a delete-then-insert pair, coalescing unchanged column data also.&lt;/p&gt;

&lt;h3&gt;
  
  
  Retry Handling
&lt;/h3&gt;

&lt;p&gt;When a batch write fails due to a transient error — authentication failures, SSL/TLS issues, or connection drops — ClickHouseWriter automatically retries with exponential backoff (2s → 32s, with jitter) before halting the application.&lt;/p&gt;

&lt;p&gt;On authentication failures, the writer fetches fresh credentials before reconnecting, making it resilient to credential rotation. The writer also cleans up any partial state before each retry, so a failed batch does not leave inconsistent data in the target.&lt;/p&gt;

&lt;p&gt;Connection-level retries for individual SQL statements are governed separately by the &lt;code&gt;ConnectionRetryPolicy&lt;/code&gt; property, which controls the number of attempts, backoff delays, and total timeout before the application halts.&lt;/p&gt;

&lt;p&gt;When all retries are exhausted, the writer surfaces a clear halt message with remediation guidance — for example, authentication failures prompt a credentials check, and SSL errors prompt certificate verification.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. The Complete Pipeline
&lt;/h2&gt;

&lt;p&gt;Putting it together: an end-to-end Oracle CDC pipeline targeting ReplacingMergeTree in ClickHouse.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;OR&lt;/span&gt; &lt;span class="k"&gt;REPLACE&lt;/span&gt; &lt;span class="no"&gt;APPLICATION&lt;/span&gt; &lt;span class="nc"&gt;Oracle_To_ClickHouse&lt;/span&gt;
  &lt;span class="no"&gt;RECOVERY&lt;/span&gt; &lt;span class="mi"&gt;30&lt;/span&gt; &lt;span class="no"&gt;SECOND&lt;/span&gt; &lt;span class="kt"&gt;INTERVAL&lt;/span&gt;
  &lt;span class="no"&gt;USE&lt;/span&gt; &lt;span class="no"&gt;EXCEPTIONSTORE&lt;/span&gt; &lt;span class="no"&gt;TTL&lt;/span&gt; &lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s1"&gt;'7d'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="no"&gt;FLOW&lt;/span&gt; &lt;span class="nc"&gt;SourceFlow&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;OR&lt;/span&gt; &lt;span class="k"&gt;REPLACE&lt;/span&gt; &lt;span class="no"&gt;SOURCE&lt;/span&gt; &lt;span class="nc"&gt;OracleSource&lt;/span&gt; &lt;span class="k"&gt;USING&lt;/span&gt; &lt;span class="nc"&gt;Global&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;OracleReader&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="nc"&gt;Tables&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;                &lt;span class="s1"&gt;'"TPCH"."ORDERS"'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;connectionProfileName&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s1"&gt;'admin.Oracle_CP'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;SendBeforeImage&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;       &lt;span class="kc"&gt;true&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;DictionaryMode&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;        &lt;span class="s1"&gt;'OnlineCatalog'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;FetchSize&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;             &lt;span class="mi"&gt;1000&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;CDDLAction&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;            &lt;span class="s1"&gt;'Process'&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="no"&gt;OUTPUT&lt;/span&gt; &lt;span class="k"&gt;TO&lt;/span&gt; &lt;span class="nc"&gt;CDCStream&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;END&lt;/span&gt; &lt;span class="no"&gt;FLOW&lt;/span&gt; &lt;span class="nc"&gt;SourceFlow&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;OR&lt;/span&gt; &lt;span class="k"&gt;REPLACE&lt;/span&gt; &lt;span class="no"&gt;TARGET&lt;/span&gt; &lt;span class="nc"&gt;ClickHouseTarget&lt;/span&gt; &lt;span class="k"&gt;USING&lt;/span&gt; &lt;span class="nc"&gt;Global&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;ClickHouseWriter&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="n"&gt;connectionProfileName&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s1"&gt;'admin.ClickHouse_CP'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;Tables&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;                &lt;span class="s1"&gt;'"TPCH"."ORDERS",tgt.orders_rmt'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;TableEngine&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;           &lt;span class="s1"&gt;'ReplacingMergeTree'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;UploadPolicy&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;          &lt;span class="s1"&gt;'eventcount:10000,interval:30s'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;DeleteStrategy&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;        &lt;span class="s1"&gt;'LightweightDelete'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;CDDLAction&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;            &lt;span class="s1"&gt;'Process'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nc"&gt;TargetTableDefinition&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s1"&gt;'{"tgt.orders_rmt": {"Order By": "(order_id)"}}'&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="no"&gt;INPUT&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="nc"&gt;CDCStream&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;END&lt;/span&gt; &lt;span class="no"&gt;APPLICATION&lt;/span&gt; &lt;span class="nc"&gt;Oracle_To_ClickHouse&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Key points in the TQL above:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Tables mapping:&lt;/strong&gt; &lt;code&gt;"TPCH"."ORDERS",tgt.orders_rmt&lt;/code&gt; maps the source Oracle table to the target ClickHouse table. Source uses Oracle’s quoted-identifier syntax; target uses ClickHouse’s &lt;code&gt;database.table&lt;/code&gt; format.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Recovery:&lt;/strong&gt; &lt;code&gt;RECOVERY 30 SECOND INTERVAL&lt;/code&gt; checkpoints every 30 seconds. Checkpoints advance only after ClickHouse acknowledges the batch, so recovery replays from the last acknowledged position. With ReplacingMergeTree, replayed events are idempotent — the version-based deduplication discards duplicates.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;CDDLAction:&lt;/strong&gt; Controls how Oracle DDL events (table creation, column additions, drops, or modifications) are handled at the target. &lt;code&gt;Process&lt;/code&gt; auto-translates them to ClickHouse &lt;code&gt;ALTER TABLE&lt;/code&gt; or &lt;code&gt;CREATE TABLE&lt;/code&gt; statements. &lt;code&gt;Ignore&lt;/code&gt; skips DDL and continues processing DML. &lt;code&gt;Halt&lt;/code&gt; flushes pending events and stops the application.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Target Table Definition
&lt;/h3&gt;

&lt;p&gt;When ClickHouseWriter creates the target table, you control the physical layout via the &lt;code&gt;TargetTableDefinition&lt;/code&gt; property. At minimum, specify the &lt;code&gt;ORDER BY&lt;/code&gt; clause — this is the deduplication key for ReplacingMergeTree and the sort key for MergeTree:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight json"&gt;&lt;code&gt;&lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="nl"&gt;"tgt.orders_rmt"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="nl"&gt;"Order By"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"(order_id)"&lt;/span&gt;
  &lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Additional keys like &lt;code&gt;Partition BY&lt;/code&gt;, &lt;code&gt;Primary Key&lt;/code&gt;, and &lt;code&gt;Settings&lt;/code&gt; can be added to control partitioning, sparse index granularity, and other engine-level options.&lt;/p&gt;

&lt;h3&gt;
  
  
  Querying the Target
&lt;/h3&gt;

&lt;p&gt;With ReplacingMergeTree, the target contains multiple versions of each row until background merge runs. Two query patterns:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Option 1: FINAL keyword (forces merge at query time, slower)&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;tgt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;orders_rmt&lt;/span&gt; &lt;span class="no"&gt;FINAL&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;__STRIIM_ISDELETED&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- Option 2: Subquery with max version (faster for large tables)&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;tgt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;orders_rmt&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;__STRIIM_VERSION&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="nb"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;__STRIIM_VERSION&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;tgt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;orders_rmt&lt;/span&gt; &lt;span class="n"&gt;o2&lt;/span&gt;
  &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;o2&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;order_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;tgt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;orders_rmt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;order_id&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;__STRIIM_ISDELETED&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h3&gt;
  
  
  Monitoring the Application Progress
&lt;/h3&gt;

&lt;p&gt;Striim provides two ways to monitor a running pipeline: the web UI flow view and the &lt;code&gt;mon&lt;/code&gt; command in the Striim console. The UI shows live throughput rates and event counts at a glance; the console gives a full breakdown of per-table batch statistics.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fsakphynjgzj3c131rg8t.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fsakphynjgzj3c131rg8t.png" alt="Striim web UI showing ClickHouse pipeline monitoring and throughput information." width="799" height="463"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;For detailed per-target metrics, use &lt;code&gt;mon -v &amp;lt;AppName&amp;gt;&lt;/code&gt; in the Striim console. The &lt;code&gt;Table Write Information&lt;/code&gt; field reports last-batch and cumulative event counts, upload and merge timing, and batch size — useful for diagnosing throughput bottlenecks:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;-- Elapsed time: 81 ms
-- Processing - mon -v ClickhouseTarget

╒══════════════════════════════════════════════════════════════════════════════════════════════════════════╕
│                                        TARGET admin.ClickhouseTarget                                        │
├───────────────────────────────┬───────────────────────────────────────────────────────────────────────────┤
│ Property                      │ Value                                                                       │
├───────────────────────────────┼───────────────────────────────────────────────────────────────────────────┤
│ Accepted                      │ 29                                                                          │
│ No.of events accepted per     │ 0                                                                           │
│ interval                      │                                                                             │
│ Accepted Rate                 │ 0                                                                           │
│ CPU                           │ 0.00067                                                                     │
│ CPU Rate Per Node             │ 0.005%                                                                      │
│ CPU Rate                      │ 0.067%                                                                      │
│ Discarded Event Count         │ 0                                                                           │
│ Number of events seen per     │ 0                                                                           │
│ monitor snapshot interval     │                                                                             │
│ Ignored Tables List           │                                                                             │
│ Input                         │ 29                                                                          │
│ Input Rate                    │ 0                                                                           │
│ Latest Activity               │ 2026-09-21 10:54:57                                                         │
│ Num Servers                   │ 1                                                                           │
│ Output                        │ 29                                                                          │
│ Rate                          │ 0                                                                           │
│ Table Write Information       │ [ {                                                                         │
│                               │   "cdc_ch.src_CH_AddAdditionalColumns11" : {                               │
│                               │     "Mapped Source Table" : "waction.src_CH_AddAdditionalColumns11",       │
│                               │     "Last batch info" : {                                                   │
│                               │       "No of inserts" : 1,                                                  │
│                               │       "No of updates" : 0,                                                  │
│                               │       "No of deletes" : 1,                                                  │
│                               │       "No of pkupdates" : 1,                                                │
│                               │       "No of DDLs" : 0,                                                     │
│                               │       "Total events merged" : 3,                                            │
│                               │       "Batch Sequence Number" : 6,                                          │
│                               │       "Batch Event Count" : 3,                                              │
│                               │       "Batch Size in bytes" : 564,                                          │
│                               │       "Max Record Size in batch" : 282,                                     │
│                               │       "Batch Accumulation Time in ms" : 30001,                              │
│                               │       "Micro Batch Count" : 0,                                              │
│                               │       "Integration Task Time" : {                                           │
│                               │         "Upload Time in ms" : 6,                                            │
│                               │         "Compaction Time in ms" : 0,                                        │
│                               │         "Merge Time in ms" : 0,                                             │
│                               │         "In-Memory Compaction Time in ms" : 0,                              │
│                               │         "Stage Resources Management Time in ms" : 0,                        │
│                               │         "pk Update Time in ms" : 0,                                         │
│                               │         "DDL Execution Time in ms" : 0,                                     │
│                               │         "Total Integration Time in ms" : 6                                  │
│                               │       }                                                                     │
│                               │     },                                                                      │
│                               │     "Last successful merge time" : "Mon Sep 21 10:55:16 PDT 2026",          │
│                               │     "Total Batches Created" : 7,                                           │
│                               │     "Total Batches Uploaded" : 7,                                          │
│                               │     "Total Batches Ignored" : 0,                                            │
│                               │     "Total Batches Queued" : 0,                                             │
│                               │     "Total event info" : {                                                  │
│                               │       "No of inserts" : 3,                                                  │
│                               │       "No of updates" : 0,                                                  │
│                               │       "No of deletes" : 9,                                                  │
│                               │       "No of pkupdates" : 17,                                               │
│                               │       "No of DDLs" : 0,                                                     │
│                               │       "Total events merged" : 29                                            │
│                               │     },                                                                      │
│                               │     "Avg Upload Time in ms" : 9,                                            │
│                               │     "Avg Compaction Time in ms" : 0,                                        │
│                               │     "Avg In-Mem Compaction Time in ms" : 0,                                 │
│                               │     "Avg Merge Time in ms" : 0,                                             │
│                               │     "Avg Waiting Time in Queue in ms" : 3,                                  │
│                               │     "Avg Event Count Per Batch" : 4,                                        │
│                               │     "Avg Batch Size in bytes" : 934,                                        │
│                               │     "Avg Stage Resources Management Time in ms" : 0,                        │
│                               │     "Avg Integration Time in ms" : 10,                                      │
│                               │     "Avg Micro Batch Count Per Batch" : 0,                                  │
│                               │     "Min Integration Time in ms" : 2,                                       │
│                               │     "Max Integration Time in ms" : 23                                       │
│                               │   }                                                                         │
│                               │ } ]                                                                         │
│ Target Acked                  │ 29                                                                          │
│ Target Freshness              │ 55s:071ms                                                                   │
│ Target Output                 │ 29                                                                          │
│ Target Rate                   │ 0                                                                           │
│ Timestamp                     │ 2026-09-21 10:56:11                                                         │
│ Write Timestamp               │ 2026-09-21 10:55:16                                                         │
└───────────────────────────────┴───────────────────────────────────────────────────────────────────────────┘
-- -&amp;gt; SUCCESS
-- Elapsed time: 496 ms
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h3&gt;
  
  
  Recovery Semantics
&lt;/h3&gt;

&lt;p&gt;Striim checkpoints are transactional with the batch write. A checkpoint advances only after ClickHouse acknowledges the &lt;code&gt;INSERT&lt;/code&gt; (for insert-only engines) or the full &lt;code&gt;DELETE&lt;/code&gt; + &lt;code&gt;INSERT&lt;/code&gt; cycle (for MergeTree &lt;code&gt;MERGE&lt;/code&gt;). On failure:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;The application restarts from the last acknowledged checkpoint position.&lt;/li&gt;
&lt;li&gt;Events between the last checkpoint and the failure are replayed.&lt;/li&gt;
&lt;li&gt;For ReplacingMergeTree, replayed events have the same or lower version — background merge deduplicates them.&lt;/li&gt;
&lt;li&gt;For MergeTree &lt;code&gt;MERGE&lt;/code&gt;, replayed events are re-compacted and re-applied. The &lt;code&gt;DELETE&lt;/code&gt; + &lt;code&gt;INSERT&lt;/code&gt; cycle is idempotent as long as the staging tables are cleaned up (they are dropped at the end of each batch).&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;This gives you at-least-once delivery with effective exactly-once semantics when using version-based engines (ReplacingMergeTree) or idempotent compaction (MergeTree &lt;code&gt;MERGE&lt;/code&gt;).&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Production Tuning
&lt;/h2&gt;

&lt;p&gt;A pipeline that works in dev can fail under production load. These are the properties that matter for sustained throughput.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;UploadPolicy — &lt;code&gt;eventcount:10000, interval:30s&lt;/code&gt;:&lt;/strong&gt; Flush on whichever threshold hits first. Too-frequent flushes create many small parts; ClickHouse will throw &lt;code&gt;TOO_MANY_PARTS&lt;/code&gt; (default limit: 300 active parts per partition). Raise &lt;code&gt;eventcount&lt;/code&gt; before adjusting ClickHouse’s &lt;code&gt;parts_to_throw_insert&lt;/code&gt; setting.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;ParallelThreads — &lt;code&gt;default: 1&lt;/code&gt;:&lt;/strong&gt; Spins up multiple writer instances, sharded by target table name. Each table is always handled by the same instance, preserving event order. A single table never splits across instances — for single-table throughput, use a &lt;code&gt;ROUTER&lt;/code&gt; to hash-partition events by sorting key across N writer instances.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;ConnectionRetryPolicy — exponential backoff → HALT:&lt;/strong&gt; &lt;code&gt;'initialRetryDelay=10s, retryDelayMultiplier=2, maxRetryDelay=1m, maxAttempts=10, totalTimeout=10m'&lt;/code&gt;. Exponential backoff on transient connection loss. After &lt;code&gt;totalTimeout&lt;/code&gt;, the application halts.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;CDDLAction — &lt;code&gt;Process | Ignore | Halt&lt;/code&gt;:&lt;/strong&gt; &lt;code&gt;Process&lt;/code&gt;: auto-translate Oracle DDL (&lt;code&gt;ADD&lt;/code&gt;/&lt;code&gt;DROP&lt;/code&gt;/&lt;code&gt;MODIFY COLUMN&lt;/code&gt;) to ClickHouse &lt;code&gt;ALTER TABLE&lt;/code&gt;. &lt;code&gt;Ignore&lt;/code&gt;: skip DDL, keep processing DML. &lt;code&gt;Halt&lt;/code&gt;: flush pending events and stop the application. Use &lt;code&gt;Halt&lt;/code&gt; when schemas are managed by external tools such as dbt or Terraform.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;IgnorableExceptionCode — &lt;code&gt;'TABLE_NOT_FOUND'&lt;/code&gt;:&lt;/strong&gt; Discards events for an unmapped target table instead of halting. The only accepted value. Use when the source captures tables that don’t exist on the ClickHouse side.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Key things to watch in production:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Too Many Parts:&lt;/strong&gt; ClickHouse’s &lt;code&gt;system.parts&lt;/code&gt; table shows active parts per partition. If writes outpace merges, the part count climbs toward the 300-part limit. First response: increase &lt;code&gt;UploadPolicy&lt;/code&gt; event count to produce fewer, larger batches.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Mutation lag:&lt;/strong&gt; For MergeTree mode, &lt;code&gt;system.mutations&lt;/code&gt; shows queued and running mutations. A growing queue means &lt;code&gt;DELETE&lt;/code&gt; mutations are not completed before the next batch arrives. Consider switching to ReplacingMergeTree if mutation lag is chronic.&lt;/p&gt;&lt;/li&gt;
&lt;li&gt;&lt;p&gt;&lt;strong&gt;Replication lag:&lt;/strong&gt; Striim’s built-in lag monitoring shows the time delta between the source commit timestamp and the target write acknowledgment. A sustained increase in lag indicates the writer cannot keep up with source throughput — either increase batch size, add &lt;code&gt;ParallelThreads&lt;/code&gt; for multi-table pipelines, or deploy a &lt;code&gt;ROUTER&lt;/code&gt;-based fan-out for a single hot table.&lt;/p&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;TQL and property behavior based on Striim 5.4.2 ClickHouseWriter specification. ClickHouse engine behavior based on ClickHouse 24.x/25.x documentation.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;Ready to stream Oracle CDC into ClickHouse? Try &lt;a href="https://go2.striim.com/trial-clickhouse" rel="noopener noreferrer"&gt;Striim for ClickHouse&lt;/a&gt;.&lt;/p&gt;

</description>
      <category>dataengineering</category>
      <category>ai</category>
      <category>database</category>
      <category>clickhouse</category>
    </item>
  </channel>
</rss>
