A card-present swipe in New York. Eight minutes later, the first of five card-absent charges from Velmora, in the Republic of Birana. The signal was in the transaction log the whole time — the question is who was reading it.
The fraud your dashboard already knew about
It’s Tuesday morning and a credit card ···4417 belonging to a customer in Queens — is swiped at a bodega on Roosevelt Avenue at 10:02 AM. Forty-five dollars, card present, chip read, authorized in under a second. Completely ordinary.
Eight minutes later the same card number is used to buy $320 of electronics from a merchant in Velmora. Card absent, no chip, no PIN, no device the issuer has seen before. Then another charge. Then three more, each larger than the last.
By 10:15 AM the card has taken five card-absent authorizations from a city 4,600 miles from where it was physically swiped thirteen minutes earlier. Total exposure: $1,995. While this is just one example, it’s an all too common story when it comes to credit card fraud or other financial crimes. And it could have been stopped.
Illustrative scenario: The card, cardholder, merchants, and transactions in this example are fictional, constructed to demonstrate the architecture. Any resemblance to a real incident is coincidental.
Here’s the uncomfortable part. Every one of those five authorizations was committed to ClearPay, the core payments database, the instant it happened. The row landed in pay.Transactions. The transaction log recorded it within milliseconds, and the CDC capture process surfaced it to the cdc change tables moments later. The data existed.
What didn’t exist was anything reading it. The fraud analytics warehouse was populated by a nightly batch export that had last run at 2:00 AM. At 10:15 AM, the moment the fifth charge cleared, the fraud team’s dashboard was showing a card with a clean eight-hour-old history and no foreign activity whatsoever.
The gap: The detection window for a velocity burst is measured in minutes. The refresh window for a batch-loaded fraud warehouse is measured in hours.
Why a faster batch still isn’t real-time
It’s tempting to treat this as a scheduling problem: run the export every fifteen minutes instead of every night. That instinct is wrong, and understanding why is the difference between a pipeline that reports fraud and one that prevents it.
Shrinking the interval fails for four independent reasons, and only the first is about speed.
- Volume: A mid-size issuer processes tens of millions of authorizations a month across thousands of merchants. A fifteen-minute full export of the transaction fact table is a repeated full scan of the largest table in the database, competing with the authorization path that has a sub-second SLA.
- Mutability: A payment is not an immutable event. It is a row whose txn_status walks a lifecycle — AUTHORIZED → SETTLED → CHARGEBACK or REVERSED — over hours or days. An append-only export captures the insert and silently misses every subsequent state transition. Your warehouse says AUTHORIZED; the truth is CHARGEBACK.
- Velocity of the pattern, not the data: Fraud signals are defined over short windows. “Three card-absent charges on one card inside five minutes” is meaningless if your smallest observable time slice is a nightly snapshot. The window itself has to be queryable in near-real time.
- Latency budget: Between the first anomalous charge and the point where intervention still helps — block the card, decline the next authorization — you have single-digit minutes. Any architecture whose floor latency exceeds that budget cannot participate in prevention, only in reporting.
The mutability point deserves emphasis because it is the one most often discovered late. Consider what the same transaction looks like in a batch-exported warehouse versus a CDC-fed one, three days after the fraud:
|
Field |
Batch warehouse |
CDC + ClickHouse |
Source of truth |
|
txn_id |
8841203 |
8841203 |
8841203 |
|
amount |
320.00 |
320.00 |
320.00 |
|
txn_status |
[Authorized] stale |
[Chargeback] |
CHARGEBACK |
|
updated_at |
2026-09-15 10:09 |
2026-09-18 14:33 |
2026-09-18 14:33 |
|
Chargeback linked |
[Missing] |
[Present] |
CB 5119, code 10.4 |
Both rows exist. Only one is true. A fraud model trained on the left-hand column learns that card-absent Velmora charges settle normally.
Consume the change feed, not the table
Striim does not poll the source tables on a schedule. It captures the changes directly from the SQL Server transaction log, and treats every committed change — insert, update, delete — as an event to be streamed. The authorization path is untouched; there is no repeated full scan of the fact table, no lock contention, no export window.
Those events land in ClickHouse, where the fraud queries live. The pairing matters: SQL Server is an excellent OLTP database but is not meant for analytical use cases, and ClickHouse is a modern analytical engine. CDC is the bridge that lets each do only what it is good at.
The pipeline is built as two applications — a one-time Initial Load that establishes the historical baseline, then a continuous CDC application that keeps it current.
All five tables in the pay schema are mapped the same way; Transactions stands in here as the representative fact table. Initial Load seeds history once and stops. Striim MSJet, the Striim CDC reader for SQL Server, then starts at the current log position: no gap, and no replay of rows the bulk load already wrote. Both write through the same ClickHouseWriter into the same ReplacingMergeTree tables.
Why two applications: Keeping the two applications separate makes the cutover explicit, and lets each use an upload policy suited to its shape — large batches for the bulk load, smaller ones for low-latency streaming.
Five responsibilities
The system divides into five discrete jobs. Each maps to one Striim component or one ClickHouse construct.
1. Historical baseline
SQL Server · ClearPay | Component: DatabaseReader
Bulk-reads the existing fact and dimension tables so ClickHouse starts with full history rather than only go-forward changes.
Responsibilities:
- Read all five tables in the pay schema
- Stop cleanly when the bounded read completes
2. Live change capture
SQL Server · Transaction Log | Component: MSJet
Reads the SQL Server transaction log directly and emits every insert, update, and delete as it commits — including the status transitions a batch export would miss.
Responsibilities:
- Stream INSERT on new authorizations
- Stream UPDATE on every txn_status change
- Stream DELETE for reversals and data removal
- Maintain LSN checkpoint for restart recovery
3. Mutable landing zone
ClickHouse · paymentscore | Component: ClickHouseWriter
Writes CDC events into ReplacingMergeTree tables, using a monotonic sequence number as the version column so the latest state of a row always wins.
Responsibilities:
- Map pay.% to paymentscore.%
- Version rows by __STRIIM_SEQNO
- Write deletes as __STRIIM_ISDELETED tombstones
- Apply source DDL changes automatically
4. Velocity detection
ClickHouse · Query layer | Component: Windowed aggregation
Groups card-absent authorizations by card over a rolling five-minute window and flags any card crossing the burst threshold.
Responsibilities:
- Count ECOM hits per card in the window
- Sum exposure and measure burst duration
- Join to cardholder identity for triage
5. Chargeback correlation
ClickHouse · Query layer | Component: FINAL join
Joins filed chargebacks to their parent transactions. Both the child insert and the parent status update travel the same stream, in source-transaction order, never requiring a reconciliation job to stitch them back together.
Responsibilities:
- Link Chargebacks to Transactions
- Surface reason codes and disputed amounts
- Compute chargeback rate per merchant
The CDC application in TQL
The continuous half of the pipeline is two components and a stream. The source reads the transaction log; the target writes ClickHouse.
CREATE APPLICATION PaymentsCDC; CREATE OR REPLACE SOURCE PaymentsCDC_MSJet USING MSJet ( ConnectionURL: ‘jdbc:sqlserver://<host>:1433; database=ClearPay;encrypt=true; trustServerCertificate=false; loginTimeout=30;’, DatabaseName: ‘ClearPay’, Username: ‘<reader-account>’, cdcRoleName: ‘STRIIM_READER’, Tables: ‘pay.%’, StartPosition: ‘NOW’, PollingInterval: 5, SendBeforeImage: true, FilterTransactionBoundaries: true, connectionRetryPolicy: ‘timeOut=30, retryInterval=30, maxRetries=3’ ) OUTPUT TO PaymentsCDC_Stream; CREATE OR REPLACE TARGET PaymentsCDC_ClickHouseWriter USING ClickHouseWriter ( connectionProfileName: ‘<clickhouse-connection-profile>’, Tables: ‘pay.%,paymentscore.%’, TableEngine: ‘ReplacingMergeTree’, Mode: ‘APPENDONLY’, CDDLAction: ‘Process’, NullMarker: ‘NULL’, UploadPolicy: ‘eventcount:5000,interval:10s’ ) INPUT FROM PaymentsCDC_Stream; END APPLICATION PaymentsCDC;
|
Two details in that configuration carry more weight than their line count suggests.
Tables: ‘pay.%’ on the source and ‘pay.%,paymentscore.%’ on the target is a wildcard mapping — every table in the pay schema is captured and written to the same-named table in paymentscore. Adding a sixth table to the source schema requires no pipeline change.
StartPosition: ‘NOW’ tells the reader to begin at the current log position rather than replaying available history. That is the correct setting when an Initial Load has already established the baseline — it is what makes the two-application handoff clean.
What you don’t build: Log position tracking and restart recovery. Type mapping between SQL Server and ClickHouse. Flush policy. DDL propagation when someone adds a column at the source. The handoff from bulk load to streaming without a gap or a double-load.
Each is a well-understood problem with a tedious, failure-prone implementation. In the configuration above, each is a property rather than a codebase.
Engine consistency: The Initial Load application must declare the same TableEngine as the CDC application. The Initial Load run creates the ClickHouse tables; if the CDC writer then expects a different engine, it will attempt to reconcile a table it did not create. Declare ReplacingMergeTree in both.
Why ReplacingMergeTree is the whole trick
ClickHouse is not a row-mutation store. There is no efficient UPDATE … WHERE txn_id = 8841203. So how does a status change replicate?
The answer is in the table definition the writer generates:
CREATE TABLE paymentscore.Transactions ( txn_id Int64, card_id Int32, txn_status String, amount Decimal(12,2), txn_ts DateTime64(3, ‘UTC’), updated_at DateTime64(3, ‘UTC’), — remaining source columns — __STRIIM_SEQNO UInt64, __STRIIM_ISDELETED UInt8 ) ENGINE = ReplacingMergeTree(__STRIIM_SEQNO) ORDER BY txn_id;
|
Two declarations carry the whole mechanism. ORDER BY txn_id tells ClickHouse what identifies a logical row — two rows sharing a txn_id are two versions of one transaction, not two transactions. The engine parameter ReplacingMergeTree(__STRIIM_SEQNO) tells it which version wins: the one with the highest sequence number.
So an update is written as a new row with the same sorting key and a higher version. The engine collapses duplicates during background merges, keeping only the greatest __STRIIM_SEQNO. Reads that need guaranteed-current state force that collapse with FINAL rather than waiting for a merge to happen.
|
Source event |
What ClickHouse stores |
What FINAL returns |
|
INSERT — new authorization |
Row v1, AUTHORIZED |
AUTHORIZED |
|
UPDATE — settlement |
Row v2, SETTLED alongside v1 |
SETTLED |
|
UPDATE — chargeback filed |
Row v3, CHARGEBACK alongside v1, v2 |
CHARGEBACK |
|
DELETE — reversal purge |
Row v4, __STRIIM_ISDELETED = 1 |
Filtered out by the query predicate |
Every query in this article therefore carries two obligations: read with FINAL, and filter __STRIIM_ISDELETED = 0. Omit either and you are querying history rather than state.
FINAL is not free — it forces merge work at query time, and on very large scans that cost is visible. Where latency matters more than strict currency, the alternatives are to accept eventually-merged reads or to pre-aggregate into a materialized view that collapses versions on write. For alert queries scoped to a five-minute window, FINAL over a handful of parts is cheap.
Three questions the data can now answer
What follows is ordinary SQL. That is the point, and it is worth saying before the code rather than after it.
The central insight: The hard part was making the data true, not writing the aggregation. Real-time fraud detection is not a question of faster SQL — it is a question of whether the analytical store holds current state by the time the query runs.
Each of the three queries below answers a different question about what that buys you.
Detection — can I catch the attack while it is happening?
SELECT t.card_id, concat(‘···’, c.card_last4) AS card, cu.full_name AS cardholder, count() AS hit_count, round(sum(t.amount), 2) AS exposure, dateDiff(‘second’, min(t.txn_ts), max(t.txn_ts)) AS burst_seconds FROM paymentscore.Transactions AS t FINAL JOIN paymentscore.Cards AS c FINAL ON t.card_id = c.card_id JOIN paymentscore.Customers AS cu FINAL ON c.customer_id = cu.customer_id WHERE t.__STRIIM_ISDELETED = 0 AND t.card_present = 0 AND t.txn_ts >= now() – INTERVAL 5 MINUTE GROUP BY t.card_id, c.card_last4, cu.full_name HAVING hit_count >= 3 ORDER BY hit_count DESC, exposure DESC; |
Run against the morning in Part 1, this returns card ···4417 at 10:13:27 — after the third charge, with $1,090 exposed and $905 still to come. That is inside the intervention window.
Three hits in five minutes is a deliberately loose trigger, tuned for a review queue rather than an automatic block. Tightening it — a higher count, a foreign-country predicate, a minimum amount — trades recall for precision, and where you set it depends on what the alert does next. The architectural point is unaffected by that choice: whatever threshold you pick is evaluated against current state, seconds after the transaction commits.
Correlation — can I combine attributes across time and geography?
The stronger signal combines geography with time. A card physically swiped in New York cannot legitimately be used card-absent in Velmora eleven minutes later.
SELECT ecom.card_id, concat(‘···’, c.card_last4) AS card, pos.last_city AS swiped_in, ecom.txn_city AS charged_in, ecom.amount, dateDiff(‘minute’, pos.last_swipe, ecom.txn_ts) AS gap_minutes FROM paymentscore.Transactions AS ecom FINAL JOIN paymentscore.Cards AS c FINAL ON ecom.card_id = c.card_id JOIN ( SELECT card_id, max(txn_ts) AS last_swipe, argMax(txn_city, txn_ts) AS last_city FROM paymentscore.Transactions FINAL WHERE __STRIIM_ISDELETED = 0 AND card_present = 1 GROUP BY card_id ) AS pos ON ecom.card_id = pos.card_id WHERE ecom.__STRIIM_ISDELETED = 0 AND ecom.card_present = 0 AND ecom.txn_country != ‘US’ AND ecom.txn_ts > pos.last_swipe AND dateDiff(‘minute’, pos.last_swipe, ecom.txn_ts) < 30 ORDER BY gap_minutes ASC; |
The subquery uses argMax to carry the city of the most recent card-present swipe alongside its timestamp — the ClickHouse idiom for “the value of X at the row where Y is maximal” without a window function or a self-join.
Current-state analytics — can I trust an aggregate built on mutable rows?
This is where Part 2’s mutability argument pays off. A merchant’s chargeback rate is an aggregate over a column that changes days after the row is written. Computed against a stale copy, it silently under-reports — every transaction that became a chargeback after the last export still counts as settled.
SELECT m.merchant_name, m.city, m.risk_tier, countIf(t.txn_status = ‘SETTLED’) AS settled, countIf(t.txn_status = ‘CHARGEBACK’) AS chargebacks, round(100.0 * countIf(t.txn_status = ‘CHARGEBACK’) / nullIf(count(), 0), 2) AS cb_rate_pct, round(sumIf(t.amount, t.txn_status = ‘CHARGEBACK’), 2) AS cb_exposure FROM paymentscore.Transactions AS t FINAL JOIN paymentscore.Merchants AS m FINAL ON t.merchant_id = m.merchant_id WHERE t.__STRIIM_ISDELETED = 0 GROUP BY m.merchant_id, m.merchant_name, m.city, m.risk_tier HAVING chargebacks > 0 ORDER BY cb_rate_pct DESC LIMIT 20; |
Card network thresholds: Visa’s VAMP and Mastercard’s ECP programs place merchants into monitoring when chargeback ratios cross defined thresholds. A merchant discovering this from a monthly network notice is already in remediation; a merchant watching cb_rate_pct climb in real time still has choices.
The same eight minutes, instrumented
Replaying the Part 1 timeline against a CDC-fed ClickHouse instance, the detection points move:
|
Moment |
Batch warehouse |
CDC + ClickHouse |
|
10:09 — first Velmora charge |
Not visible until 2:00 AM |
[Impossible travel flags] 7-min gap, NY → XB |
|
10:13 — third charge |
Not visible |
[Velocity burst fires] 3 hits, $1,090 |
|
10:15 — burst completes |
Not visible |
5 hits, $1,995, card queued for block |
|
+3 days — chargeback filed |
First visibility. Status still reads AUTHORIZED until the next full reload. |
[Status current] CHARGEBACK within seconds of filing |
The architecture did not make the fraud detectable. The fraud was always detectable — five card-absent charges from one foreign city on a card swiped in Queens minutes earlier is not a subtle pattern. What changed is that the pattern became queryable while it was still happening.
The general shape: This pattern generalizes past payments. Any domain where the source of truth is a mutable transactional table and the analysis needs current state over short windows — inventory positions, order fulfillment, claims adjudication, device telemetry — has the same structure. CDC into a columnar store with a merge-on-read engine, and every query reads state rather than history.
Building this yourself
Every component above is configuration, not code. The documentation for each:
- Source: MSJet: SQL Server CDC setup
- Source: Database Reader: Initial load configuration
- Target: ClickHouse Writer: Engine and mapping reference
- Concept: Table engines: Choosing a MergeTree family
Prerequisites on the source
|
Requirement |
Detail |
|
Deployment |
SQL Server on-premises, or on an Azure or EC2 virtual machine. MSJet reads the transaction log directly, so it needs an instance whose log files it can reach — a fully managed service such as Azure SQL Database does not qualify. |
|
Edition |
Enterprise, Standard, or Developer. Express does not support CDC. |
|
CDC enabled |
sys.sp_cdc_enable_db, then sys.sp_cdc_enable_table per table. |
|
Reader permissions |
Minimal — SELECT on the source schema and on the cdc schema. |
|
Log access |
The Striim host needs read access to the instance’s transaction log. On a VM deployment this usually means running the reader against the same machine or a share it can reach. |
Choosing the table engine
Striim supports six MergeTree variants. The choice follows from what the source does to its rows:
|
Engine |
Use when |
|
MergeTree |
Append-only source. No updates, no deletes. |
|
ReplacingMergeTree |
Rows are updated in place and you want latest-state reads. The default for CDC. |
|
CollapsingMergeTree |
Paired sign rows cancel; you need to net them out. |
|
CoalescingMergeTree |
Partial updates arrive per column. Requires ClickHouse 25.6+. |
|
SummingMergeTree |
Numeric columns should accumulate on merge. |
|
AggregatingMergeTree |
Pre-aggregated states are maintained incrementally. |
For a payments fact table whose rows are updated in place, ReplacingMergeTree is the natural fit: it represents successive versions of a row and resolves them to the latest state without application-side reconciliation. CollapsingMergeTree can model the same mutability, but it requires the pipeline to emit paired sign rows — more machinery for no gain when the source already supplies a clean version number.
Build the pipeline
The signal was in the log at 10:09. Whether anyone could act on it came down to one question: was anything reading it?
Stream SQL Server CDC into ClickHouse with Striim.
