A walkthrough of Bruin's payments and fraud monitoring demo: PostgreSQL change capture, one-minute ClickHouse rollups, late-arriving restatements, sealed daily KPIs, and a dashboard you can run locally.
Arsalan Noorafkan
Developer Advocate
Quick answer: Bruin's demo-payments-clickhouse template is a small reference architecture for a problem that looks simple until payment statuses start changing. PostgreSQL holds the operational record. An updated_at cursor captures new and restated rows into an append-only ClickHouse change log. Bruin folds that log into additive minute, hour, and day grains, then serves today's live minute data beside sealed daily history. A Dashboard-as-Code dashboard reads the result on a one-minute schedule, roughly one minute behind the source.
The demo is useful because it makes the uncomfortable parts visible. A payment can be approved, then refunded, then charged back. A unique card count cannot be added across groups. A P95 cannot be averaged or safely rolled up by taking the largest group P95. And ClickHouse is happiest when you model those facts explicitly instead of asking one table to do everything.
This walkthrough follows the template from source to dashboard. The code lives in the Bruin repository's demo-payments-clickhouse template.
The fastest path is a clone and one command:
git clone https://github.com/bruin-data/bruin.git
cd bruin
bash templates/demo-payments-clickhouse/demo.sh
The template expects docker, bruin, python3, and dac on your PATH. The script starts the PostgreSQL and ClickHouse containers, waits for both health checks, replays 10 minutes of traffic from the previous UTC day, replays the current day minute by minute, and serves the dashboard. It takes about three minutes with the default 30-minute live block, most of that time spent replaying windows.
If you want the executable form after bruin init has copied the template, use:
./demo-payments-clickhouse/demo.sh up 60
The number is the length of the live-day replay. The helper also has smaller commands for inspecting the pieces:
./demo-payments-clickhouse/demo.sh status
./demo-payments-clickhouse/demo.sh serve
./demo-payments-clickhouse/demo.sh down
The local dashboard normally opens at http://localhost:8321. DAC chooses another free port if that one is busy and prints the URL it uses.
The Docker stack is only the local boundary. PostgreSQL remains the system of record, ClickHouse is the analytical destination, and the dashboard is reading the same kind of objects a scheduled environment would read. There is no cloud account or production credential involved.
The pipeline is intentionally a cascade. Each layer has one job:
PostgreSQL payments.transactions
|
| ingestr cursor on updated_at
v
ClickHouse raw_transaction_changes
|
| append-only MergeTree, one captured version per row
v
stg_transaction_changes
|
| argMax latest version, bucket by created_at
v
rollup_txn_1m
| \
| \
v v
rollup_txn_1h kpi_txn_daily
\ /
v v
serving_realtime_risk
|
v
DAC semantic model + dashboard
Bruin is the control plane for this design. ClickHouse stores and queries the data, while Bruin describes the assets, orders the work, applies the right time windows, and checks whether the result is safe to show.
The pipeline definition connects the ingestion asset to the staging view, rollups, daily KPIs, serving view, checks, semantic model, and dashboard. The dependency graph gives each step a clear input and output. That matters when a status restatement arrives: Bruin can rerun the affected downstream assets in dependency order instead of turning the whole project into one large SQL script.
The schedule is ordinary cron, * * * * *, but the materialization strategy carries the important part of the contract. The minute rollup declares a time_interval materialization with a -15m interval modifier. The hourly rollup and daily KPI table use their own windows, including a three-hour correction window for daily recomputation. When you run the demo with --apply-interval-modifiers, Bruin expands the requested interval to those correction windows before it executes the SQL.
That is a useful Bruin feature for this use case: the correction policy lives beside the asset that needs it. A reader can see that the live minute table corrects 15 minutes of history and that the daily table corrects three hours. It is much easier to reason about than a scheduler with a hidden retry rule.
The local config and variables make the same project runnable in different environments. docker/bruin-local.yml points the named postgres-default and clickhouse-default connections at the local containers. --var txns_per_minute=200 changes the traffic volume without editing SQL. In a managed deployment, the connections can point elsewhere while the pipeline and its logic stay in git.
Bruin also keeps the operational context with the asset: owners, domains, tags, grain, event-time column, freshness target, money units, correction window, and materialization strategy are declared in the @bruin metadata blocks. The checks are part of the same project as the transformations. Run them with:
bruin run demo-payments-clickhouse/pipeline.yml \
--config-file demo-payments-clickhouse/docker/bruin-local.yml \
--only checks
For payments, that combination is the difference between a query that returns a number and a pipeline that can explain where the number came from, how fresh it is, which source versions it includes, and whether the grains reconcile.
ClickHouse supplies the storage and SQL primitives that make the cascade practical.
The raw table uses an append-friendly MergeTree. Every captured PostgreSQL version can be written without trying to update an existing row in place. The rollups use ReplacingMergeTree materializations with explicit keys, so a rerun of a minute or day can replace the affected result. The pipeline still collapses source versions in SQL first; the table engine is not being asked to understand payment state transitions.
The version collapse uses argMax:
SELECT
transaction_id,
argMax(status, updated_at) AS status,
argMax(amount_cents, updated_at) AS amount_cents
FROM bruin_payments.stg_transaction_changes
GROUP BY transaction_id
argMax(value, updated_at) returns the value associated with the greatest updated_at for each transaction. The demo applies it to every mutable payment field, not only status, so a refund or chargeback is treated as one coherent latest version.
The rest of the minute query is deliberately plain ClickHouse SQL. toStartOfMinute assigns the row to its authorization minute. countIf produces status, fraud, and decline-reason counts. sumIf produces approved, refunded, and chargeback volume. DateTime64 keeps sub-second timestamps in UTC, and toLowCardinality stores repeated dimensions such as card network, country, and merchant category efficiently.
The daily KPI query uses the functions that cannot be reconstructed from additive minute rows. uniqExact(card_id) computes a distinct card count, and quantileExact(0.95)(auth_latency_ms) computes the P95 from the collapsed transaction population. toDecimal64 converts integer cents into a display amount without introducing floating-point sums into the rollup base.
The staging view is a small but important ClickHouse boundary. Ingestion infers nullable columns, while the sorting keys on the rollup tables need non-null values. The view uses assumeNotNull, ifNull, and toLowCardinality once, then gives every downstream asset the same types. It is a logical view, so the consumers can still push their created_at filters down to the raw table.
Bruin and ClickHouse fit this problem because their responsibilities are different. Bruin handles scheduling, dependency order, correction windows, configuration, metadata, and checks. ClickHouse handles append-heavy history, fast scans over the bounded source window, and the SQL functions needed to collapse and aggregate it. Together they give the risk dashboard a short freshness target without throwing away the versions needed to correct a payment later.
The result is a useful middle ground for payments monitoring: the source remains PostgreSQL, the analytical workload stays in ClickHouse, and the rules for freshness and correctness remain visible in a versioned Bruin project.
The seed asset writes to payments.transactions in PostgreSQL. Each row represents a payment authorization with fields such as the transaction ID, amount, merchant category, card network, country, authorization latency, status, and fraud label.
The seed also performs real updates. Some authorizations move through a lifecycle like:
approved -> refunded -> chargeback
Those updates are the point of the demo. If the warehouse only saw inserts, almost any aggregate would look correct. The interesting case is when a status changes after the original authorization was already counted.
The generated traffic is deterministic. A row is a function of its minute window and row index, so replaying the same window produces the same transaction instead of creating a new one. The defaults are 40 authorizations per minute, a 6% restatement rate, and restatements that reach three windows back. You can tune them with --var:
bruin run demo-payments-clickhouse/pipeline.yml \
--config-file demo-payments-clickhouse/docker/bruin-local.yml \
--apply-interval-modifiers \
--var txns_per_minute=200 \
--start-date "2026-08-24 11:00:00" \
--end-date "2026-08-24 11:00:59.999999"
The demo uses integer minor units, amount_cents, rather than a floating-point amount. That gives every volume sum an exact representation and makes the currency policy obvious: this template is USD only. A multi-currency system needs an explicit FX model before it adds amounts together.
The name raw_transaction_changes is deliberate. It is an append-only history of the versions captured from PostgreSQL, not a table that pretends each transaction_id appears once.
The demo uses an updated_at cursor. A new authorization moves into the source. A refund or chargeback updates the same source row, bumps updated_at, and is picked up by a later run. The ingestr asset appends that new version to a ClickHouse MergeTree.
This is worth spelling out because it differs from log-based CDC. The toolchain version used for the template does not support managed PostgreSQL CDC into a ClickHouse destination, and its ClickHouse merge path was not safe for this use case. The template chooses a boring, inspectable fallback: append versions and collapse them when reading.
That choice has boundaries. A hard delete is not captured because the deleted source row no longer moves its cursor. Multiple updates inside one capture interval resolve to the last version seen in that interval. For this payment ledger, rows are not hard-deleted and the current status is what the dashboard needs. If you need a complete transition audit trail, use a log-based CDC path that preserves every event.
There is one rule downstream users have to follow:
SELECT
transaction_id,
argMax(status, updated_at) AS status
FROM bruin_payments.stg_transaction_changes
WHERE created_at >= now('UTC') - INTERVAL 1 HOUR
GROUP BY transaction_id
Collapse by transaction_id before aggregating. Also filter by created_at, not updated_at. Every version of a transaction keeps the same authorization time, so a created_at boundary keeps the full version history for the payments in scope. An updated_at boundary can cut that history in half.
The staging view also fixes nullability and encodes repeated strings with LowCardinality. ClickHouse will reject a nullable sorting key, and putting the conformance in one view keeps every rollup from repeating the same ifNull expressions.
rollup_txn_1m is the most important table in the project. It buckets transactions by created_at into UTC minutes and stores measures that survive addition:
The table uses a time_interval strategy with a 15-minute lookback. Each scheduled run reprocesses the previous 15 minutes, which gives a late restatement a chance to update the minute where the authorization happened.
That lookback is not magic. It is the correctness budget. A chargeback arriving 20 minutes late falls outside a 15-minute lookback and will not rewrite the old minute. Widening the window buys more correction time and costs more recompute on every run.
The pipeline also buckets on created_at, never on the time the change arrived. If a payment was authorized at 11:08 and charged back at 11:10, the chargeback belongs to the 11:08 authorization minute. Otherwise a dashboard would show the event in the minute it happened to reach the warehouse, which is a different question.
You can watch the correction happen by finding a transaction with all three states, then joining its authorization minute to rollup_txn_1m. The template's README includes the full query. In the example output, the 11:08 minute first counted the payment as approved. Two runs later, the lookback reprocessed that minute and changed it to a chargeback.
This is the useful part of a near-real-time pipeline: fresh data and a bounded correction window, with the trade-off written down.
Once the minute table contains only additive measures, the coarser rollups are cheap. rollup_txn_1h sums the minute rows into hours. The additive portion of kpi_txn_daily sums the same minute rows into days.
minute counts and volumes
|
+--> hourly sums
|
+--> daily sums
There is no reason to rescan the raw change log for every count and amount. The minute table is the reusable base, and every higher grain can be rebuilt from it with ordinary sum() expressions. That is the practical benefit of the cascade: the expensive version-collapse and grouping work stays close to the source grain, while historical reporting reads much smaller tables.
The cascade does not make every metric additive. It makes the boundary visible.
Unique cards and P95 authorization latency are different from counts and amounts.
If you sum unique_cards across merchant categories, a card used in two categories appears twice. If you take the maximum of each group's P95, you get the slowest small group, not the 95th percentile of all authorizations. Both numbers can look reasonable in a dashboard while answering the wrong question.
The daily KPI asset therefore re-reads the collapsed change log for those measures. In one verified run from the template, a naive sum of group unique-card counts returned 447 against a true distinct count of 427. A naive maximum of group P95 values returned 355 ms against a true P95 of 131 ms.
The alternative is to store aggregate states such as uniqState and quantileState in an AggregatingMergeTree. That can make some non-additive calculations composable, but it introduces its own complexity under restatements. The demo keeps the trade-off plain: additive metrics cascade; unique and percentile metrics pay the recomputation cost where they are needed.
serving_realtime_risk is a view, not another copy of the entire warehouse. It unions two paths:
today -> rollup_txn_1m, live and roughly one minute behind
earlier -> kpi_txn_daily, sealed and safe for distinct and P95 KPIs
This split solves two problems at once. The current day stays fresh because the dashboard reads the minute rollup. Completed days stay cheap to query because they use the daily KPI table. The serving view adds derived rates such as approval rate, decline rate, fraud rate, chargeback rate, and average approved ticket from their additive bases.
The live side returns NULL for unique cards and P95 latency by design. A blank is more honest than a plausible number calculated from an invalid rollup. The dashboard can still show live authorizations, approval rate, fraud rate, volume, and the per-minute throughput chart while the sealed side supplies the measures that need a complete day.
The template keeps the metric definitions in semantic/payments_risk.yml. The dashboard refers to names such as approval_rate, fraud_rate, and volume; it does not repeat the SQL for each widget.
That separation matters when a metric changes. If the definition of approval rate changes, the semantic model is the place to review it, and every dashboard widget that uses the metric gets the same definition. The model also carries dimensions for date, merchant category, card network, country, and whether a row belongs to the live day.
The payments risk dashboard definition uses that model for headline metrics and breakdowns. It reads the per-minute rollup directly for the authorizations chart because the serving view is at day grain. Other widgets cover decline reasons, approved volume by card network, P95 latency, and a full risk breakdown.
The fraud labels come from the source and are monitored, not modelled. This is a monitoring reference architecture, not a machine-learning fraud scoring system. It gives an operations or risk team a consistent view of what the source says happened and how those measures change when a payment is restated.
The template includes blocking checks for the things that commonly make a payments dashboard quietly wrong:
[0, 1] and monetary measures that are non-negativeRun the checks with:
bruin run demo-payments-clickhouse/pipeline.yml \
--config-file demo-payments-clickhouse/docker/bruin-local.yml \
--only checks
Then verify the row counts reconcile across the grains:
SELECT 'changelog' AS grain,
uniqExact(transaction_id) AS n
FROM bruin_payments.stg_transaction_changes
UNION ALL
SELECT 'minute', sum(txns)
FROM bruin_payments.rollup_txn_1m
UNION ALL
SELECT 'hour', sum(txns)
FROM bruin_payments.rollup_txn_1h
UNION ALL
SELECT 'day', sum(txns)
FROM bruin_payments.kpi_txn_daily
UNION ALL
SELECT 'serving', sum(txns)
FROM bruin_payments.serving_realtime_risk;
All five authorization totals should agree. If they do not, the dashboard is not ready for a confident answer, regardless of how polished the chart looks.
The demo has a clear set of defaults:
There are also clear limits. Cursor capture depends on a reliable updated_at. Hard deletes disappear from the destination. A restatement that changes a transaction's reporting dimensions needs a wider delete-and-reinsert strategy for the affected hour or day. And the current live view cannot honestly provide exact distinct cards or P95 latency without reading more detail.
Those limits are not footnotes to hide. They are the design. Near-real-time payments analytics is a series of explicit trade-offs: how far back to correct, which measures to pre-aggregate, which history to seal, and which numbers to leave blank until they are trustworthy.
If you want to inspect the implementation, start with the template README, then trace pipeline.yml, the assets under assets/, and the dashboard and semantic files. The whole project is small enough to run locally, query by hand, and change while you can still see exactly what each layer is doing.
A practical 2026 guide to running data pipelines in CI/CD with Bruin. A working GitHub Actions workflow, the GitLab and Azure equivalents, what to validate on a pull request versus deploy on merge, how to test only changed assets, and how to enforce data contracts before a schema change ships.
How to build a data platform on a small budget in 2026. Where the money actually goes, which pricing models bite at scale, the cheapest reference stack for a lean team (Bruin plus a pay-per-query warehouse), and how to cut Snowflake and BigQuery costs without cutting scope.
A practical Fivetran migration guide for moving PostgreSQL to BigQuery with Bruin and ingestr. Validate parity, reduce MAR-based spend, and run a governed open-source data ingestion pipeline.
Practical updates on open-source data pipelines, AI analysts, governance, and what we are shipping at Bruin.
The signup form is hosted by Brevo. Allow marketing cookies to load it.