Near-Real-Time Fraud Rules with Snowflake Streams and Tasks

Last updated: October 2026

Real-time fraud detection is usually discussed as a streaming problem, with Kafka, a stream processor and a feature store. For many banks that is more machinery than the requirement calls for. The question that decides the design is how quickly the fraud team actually needs to know, and the answer is often measured in minutes.

This article walks through a design we delivered for a regional bank, described in our banking CDC case study. The bank's fraud engine had been running on a nightly batch extract, so suspicious transactions had a 24 to 48 hour head start. The figures and rules below come from that project. The SQL is a simplified illustration of the approach, written for this article.

Decide how fresh is fresh enough

We initially prototyped a true streaming approach on Kafka, but the bank's fraud operations team confirmed that anything under two minutes was acceptable, and that changed the design. A Snowflake task that runs every 60 seconds meets a two-minute target comfortably, and it was far simpler to maintain and cost a fraction of what a Kafka cluster would have.

The point generalises. If a decision has to be taken while a card payment is being authorised, the scoring belongs in the authorisation path, outside the warehouse. If the decision is whether to call a customer or freeze an account, a minute is usually fine, and the warehouse is a reasonable place to run the rules, next to the history they depend on.

Getting changes out of Oracle

The bank's core banking system ran on Oracle 19c. We used Talend's CDC connectors against the Oracle backend, which read every INSERT, UPDATE and DELETE from the database logs instead of waiting for the nightly batch. Transactions reached Snowflake's raw layer within 90 seconds of occurring. The CDC jobs ran on a dedicated integration server and wrote to Snowflake through the JDBC driver in batches of 5,000 rows per commit.

Log-based CDC from Oracle has two prerequisites that are worth confirming early. The database must run in ARCHIVELOG mode, so that filled redo logs are archived before they are reused, and supplemental logging must be enabled, because Oracle provides none by default and LogMiner cannot be used without it.

-- Is the database archiving redo logs, and is minimal
-- supplemental logging on? Expect ARCHIVELOG and YES (or IMPLICIT).
SELECT log_mode, supplemental_log_data_min FROM v$database;

-- Minimal supplemental logging, plus primary key columns so that
-- updates can be matched to rows downstream.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

The lesson that cost us the most attention was log retention. In the bank's environment, archived logs were kept for 24 hours, so if the CDC connector was down for more than a day, changes would be lost. We increased retention to 72 hours and added monitoring on archive log space so that the disk could not fill. This does not show up in a proof of concept, and it matters a great deal in production.

How retention is controlled depends on the platform. On a self-managed database, archived logs stay until they are deleted, either by the RMAN archive log deletion policy, by the database removing the oldest logs from the fast recovery area under space pressure, or by a manual deletion job. Amazon RDS for Oracle exposes an explicit setting instead:

BEGIN
  rdsadmin.rdsadmin_util.set_configuration(
    name  => 'archivelog retention hours',
    value => '72');
END;
/
COMMIT;

-- Confirm the setting
EXEC rdsadmin.rdsadmin_util.show_configuration;

Longer retention uses more storage, which is why the monitoring matters as much as the setting itself.

Tracking new rows with a stream

A stream on the raw transactions table records which rows are new since the last time the stream was consumed. For a raw table that only ever receives inserts, an append-only stream is the better choice, because Snowflake documents it as notably more efficient than a standard stream for insert-only data.

Two stream behaviours shape the task design. First, a stream advances its offset only when it is read inside a DML statement that commits. Querying it with a plain SELECT does not consume it. Second, a stream becomes stale if it is not consumed within the source table's retention period. Snowflake extends retention automatically up to MAX_DATA_EXTENSION_TIME_IN_DAYS, which defaults to 14 days, so a scoring task that is suspended for more than two weeks can leave the stream unusable.

CREATE OR REPLACE STREAM raw.transactions_new
  ON TABLE raw.transactions
  APPEND_ONLY = TRUE;

Writing the three rules in SQL

The bank used three rules:

• Velocity: more than 5 transactions in 10 minutes from the same account.
• Geographic anomaly: transactions in two different countries within an hour.
• Amount threshold: a single transaction above three times the account's 90-day average.

The new rows in the stream are not enough to evaluate these rules. A velocity check on a new transaction needs the account's transactions from the previous ten minutes, most of which were processed in earlier runs. The query therefore uses the stream to find which accounts have new activity, reads the last hour of those accounts' transactions from the base table, computes the rules over that window, and keeps only the results for the new rows.

Snowflake supports RANGE window frames with INTERVAL offsets on timestamp columns, which suits the velocity rule. The geographic rule can be written with LAG: if two transactions in different countries fall within an hour, then some pair of consecutive transactions between them must also differ in country and be less than an hour apart, so comparing each transaction with the one before it is sufficient. The 90-day average comes from a small baseline table refreshed once a day, so the scoring query does not scan 90 days of history every minute.

INSERT INTO fraud.alerts (txn_id, account_id, rule_name, txn_ts, detected_at)
WITH new_txn AS (
    SELECT txn_id, account_id, txn_ts
    FROM raw.transactions_new
),
recent AS (
    -- The new rows plus the last hour of history for the same accounts.
    SELECT t.txn_id, t.account_id, t.txn_ts, t.amount, t.country
    FROM raw.transactions t
    WHERE t.account_id IN (SELECT account_id FROM new_txn)
      AND t.txn_ts >= (SELECT DATEADD(hour, -1, MIN(txn_ts)) FROM new_txn)
),
scored AS (
    SELECT r.*,
           COUNT(r.txn_id) OVER (
               PARTITION BY r.account_id ORDER BY r.txn_ts
               RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW
           ) AS txns_10m,
           LAG(r.country) OVER (PARTITION BY r.account_id ORDER BY r.txn_ts) AS prev_country,
           LAG(r.txn_ts)  OVER (PARTITION BY r.account_id ORDER BY r.txn_ts) AS prev_ts,
           b.avg_amount_90d
    FROM recent r
    LEFT JOIN fraud.account_baseline b ON b.account_id = r.account_id
)
SELECT txn_id, account_id, rule_name, txn_ts, CURRENT_TIMESTAMP()
FROM (
    SELECT s.*, 'VELOCITY' AS rule_name FROM scored s WHERE txns_10m > 5
    UNION ALL
    SELECT s.*, 'GEO_ANOMALY' FROM scored s
     WHERE country <> prev_country AND txn_ts <= DATEADD(hour, 1, prev_ts)
    UNION ALL
    SELECT s.*, 'AMOUNT_3X_AVG' FROM scored s WHERE amount > 3 * avg_amount_90d
)
WHERE txn_id IN (SELECT txn_id FROM new_txn);

Keeping all three rules in a single INSERT matters. The stream is consumed when the statement commits, so a second statement reading the same stream afterwards would see nothing. If the rules have to be separate statements, run them inside one explicit transaction, where the stream returns the same rows until the transaction commits.

Scheduling the task, and what it costs

The bank's task ran every 60 seconds with a WHEN condition, so a run is skipped when the stream is empty. Checking the condition does not start a warehouse. Snowflake records a nominal cloud services charge for each skipped run, which is covered by the daily cloud services allowance in most accounts.

CREATE OR REPLACE TASK fraud.score_transactions
  WAREHOUSE = fraud_wh
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw.transactions_new')
AS
  INSERT INTO fraud.alerts (txn_id, account_id, rule_name, txn_ts, detected_at)
  -- ... the scoring query shown above ...
  ;

-- Tasks are created suspended.
ALTER TASK fraud.score_transactions RESUME;

If a run is still going when the next scheduled time arrives, Snowflake skips that scheduled time, so runs never pile up behind each other. Overlap for task graphs is now controlled by OVERLAP_POLICY, which replaced the deprecated ALLOW_OVERLAPPING_EXECUTION parameter in March 2026, with NO_OVERLAP as the default.

The cost to watch is the warehouse. A user-managed warehouse is billed per second with a 60-second minimum each time it resumes, so a task that finds new data every minute will keep the warehouse running almost continuously. An X-Small Gen1 warehouse that never suspends uses 24 credits a day, and Gen2 warehouses, now the default in most regions, consume more per hour. Serverless tasks are an alternative: you omit the WAREHOUSE parameter and Snowflake bills the compute the task actually uses.

Triggered tasks, generally available since July 2024 and in serverless form since January 2025, remove the schedule altogether. The task runs when the stream has new data, at most every 30 seconds by default (the USER_TASK_MINIMUM_TRIGGER_INTERVAL_IN_SECONDS parameter can lower that to 10), and uses no compute while there is nothing to process. For a new build, we would evaluate a serverless triggered task first.

CREATE OR REPLACE TASK fraud.score_transactions
  TARGET_COMPLETION_INTERVAL = '2 MINUTES'
  WHEN SYSTEM$STREAM_HAS_DATA('raw.transactions_new')
AS
  INSERT INTO fraud.alerts (txn_id, account_id, rule_name, txn_ts, detected_at)
  -- ... the scoring query ...
  ;

Sending alerts to PagerDuty and Slack

When the scoring query writes to the alerts table, the fraud operations team needs to be paged. Snowflake's webhook notification integrations can post to Slack, Microsoft Teams and PagerDuty, and the current documentation adds Jira and ServiceNow. The webhook URL or routing key is stored in a secret, and messages are sent with SYSTEM$SEND_SNOWFLAKE_NOTIFICATION.

CREATE OR REPLACE SECRET ops_db.fraud.pagerduty_routing_key
  TYPE = GENERIC_STRING
  SECRET_STRING = '<pagerduty-integration-key>';

CREATE OR REPLACE NOTIFICATION INTEGRATION fraud_pagerduty_int
  TYPE = WEBHOOK
  ENABLED = TRUE
  WEBHOOK_URL = 'https://events.pagerduty.com/v2/enqueue'
  WEBHOOK_SECRET = ops_db.fraud.pagerduty_routing_key
  WEBHOOK_BODY_TEMPLATE = '{
    "routing_key": "SNOWFLAKE_WEBHOOK_SECRET",
    "event_action": "trigger",
    "payload": {
      "summary": "SNOWFLAKE_WEBHOOK_MESSAGE",
      "source": "snowflake-fraud-scoring",
      "severity": "critical"
    }
  }'
  WEBHOOK_HEADERS = ('Content-Type' = 'application/json');

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_PLAIN(
    SNOWFLAKE.NOTIFICATION.SANITIZE_WEBHOOK_CONTENT('3 new fraud alerts')
  ),
  SNOWFLAKE.NOTIFICATION.INTEGRATION('fraud_pagerduty_int')
);

In practice the send call sits in a child task that runs after the scoring task, reads alerts that have not yet been sent, sends one message summarising them and marks them as sent. Sending one page per batch instead of one per alert keeps the on-call engineer from being paged twenty times for one incident. The NOTIFICATION_HISTORY table function shows what was sent and whether delivery succeeded.

Reconciliation runs on the same platform

The bank had a second problem that the same platform solved. It ran three separate ledgers, the core banking ledger, a loan servicing platform and an investment accounting system, and two analysts spent eight working days every month-end reconciling them by hand. The previous SOX audit had produced 23 data discrepancy findings.

We built a dbt project that joins all three ledger sources every hour on an X-Small warehouse, since the work is mostly joins and aggregations. Discrepancies are posted to a Slack channel through a notification integration. About 15 to 20 genuine discrepancies surface each month, and the review now takes about four hours, most of it spent investigating causes instead of finding the differences in the first place. The next SOX audit produced no findings.

Over the first six months, fraud losses fell by 67%. Before the change, most fraud flags were raised after the funds had already moved; with alerts arriving within about 90 seconds, the team can act while the money can still be stopped.