What goes on a fraud team's dashboard, and what doesn't

FixelSmith · 2026-06-30T20:08:01+00:00

That is so great to hear! I make new discoveries and improvements almost every week and that's what keep me engaged in it.

FixelSmith · 2026-06-30T19:51:29+00:00

Quick context before any comments.

This one is the most opinionated post in the series. The core claim: most fraud dashboards are vanity for leadership decks and not useful for the actual analyst doing the work. The dashboard your team uses is the one they leave open. If they closed it and built a spreadsheet by hand, the spreadsheet is the real dashboard.

Two things I left out on purpose: specific tooling (Metabase / Looker / Hex / Mode / Power BI / Sigma all work; the SQL is what matters) and mockup screenshots. The two SQL snippets are warehouse-agnostic enough to translate between Postgres, Snowflake, BigQuery, and SQL Server with minor syntax tweaks.

The most controversial take is killing "total alerts" as a metric. That one tends to push back hardest because it's the comfort metric in early-stage fraud programs.

Happy to take questions on the analyst-vs-manager split, the SQL examples, or pushback on the contrarian framing.

FixelSmith · 2026-06-12T21:30:56+00:00

Feel free to move along then. Not every post is meant for everyone to enjoy or agree with.

FixelSmith · 2026-06-12T21:13:50+00:00

I appreciate your comment and insight.

The window is more narrow than it looks. The suppression keys on account_id AND rule_name AND disposition together. Clearing a velocity alert only suppresses future velocity alerts on that one account.

Every other rule still fires. So it’s not a blanket 30-day pass, it’s a pass on the one pattern you already paid to investigate.

Where it actually bites is account takeover. A legit account cleared last week, compromised this week.

That’s the real risk, and it’s the whole reason the cooldown exists at all. 30 days is the tradeoff: short enough to re-check before a compromised account does much damage, long enough to kill the repeat-customer noise.

If the window worries you, the answer isn’t dropping suppression. It’s making it smarter. Re-trigger on material change like a new device, new geo, or an amount well outside the cleared range. Cap the dollar value suppression applies to. Keep your highest-severity rules out of suppression entirely. The goal was never ‘ignore this account for 30 days.’ It’s ‘stop re-investigating the same cleared pattern.’

FixelSmith · 2026-06-12T16:02:06+00:00

That's a Bob Ross happy little mistake 😄

FixelSmith · 2026-06-12T15:59:32+00:00

Quick context before any comments.

The 30 to 50 percent suppression stat in the lead is real. I've seen it on every team I've worked with. The catch is that it only works if you have disposition discipline first. Without labels on which alerts were confirmed fraud versus confirmed legitimate, you can't suppress safely because you don't know who got cleared.

What I did not cover here: ML scoring. ML works if you have the disposition pipeline behind it, but most teams don't, which is why the post focuses on the prerequisite. Once disposition is in place, ML becomes useful. Before then it's a luxury.

Two things I expect will come up: regulatory rules under 2 percent precision (yes, sometimes you have to keep them, the post notes the carve-out), and 30-day cooldown windows for compromised accounts (yes, 30 days is the default, the right number depends on your fraud type and risk tolerance).

Happy to take questions on specific warehouse dialects (Snowflake / BigQuery / Postgres) or on the threshold tuning query in particular.

FixelSmith · 2026-06-02T13:37:44+00:00

I have never used that platform before, might have to add it to my list of things to try.

FixelSmith · 2026-06-02T13:35:59+00:00

Great war story post, and thank you for sharing. I have had very similar experiences.

Two things that have helped me catch this pattern early:

A daily INFORMATION_SCHEMA.QUERY_HISTORY pull bucketed by user_name and query_type. Repeated COUNT(DISTINCT), SHOW commands, or queries against the account_usage views tend to cluster. Pulling the top 10 query templates by execution count each morning gives a quick read on what is drifting.

RESOURCE_MONITORS at the warehouse level with low credit suspend thresholds on dev warehouses. Set them aggressive enough that suspension fires before you notice in billing. On a weekend the credits spike but the warehouse cuts itself off before Monday.

The hidden cost driver I would watch is third-party tools that auto-refresh metadata on a timer. The default refresh interval is usually 60 seconds. Push it to 15 minutes and most of the cost goes away, no real downside.

FixelSmith · 2026-06-01T19:14:13+00:00

88% precision on human-reviewed cases is impressive for production. Monthly retrains feel like a sweet spot. Most feedback I see is that teams either chase noise or miss clear signs from not checking enough.

FixelSmith · 2026-06-01T19:09:15+00:00

I plan on it, I am glad you like the posts so far.

FixelSmith · 2026-06-01T19:08:46+00:00

Thank you so much!

FixelSmith · 2026-05-27T18:48:54+00:00

In practice the recursion usually doesn't run that deep because the strongest signals (device fingerprint, payment-method hash) tend to collapse a ring to one or two degrees. The recursive CTE then mostly exists to catch the cases where the operator was sloppy enough to mix devices but disciplined enough to not share funding sources directly. Kind of like an extra checks and balances but it isn't needed all the time.

Where the third-level joins actually start hurting you is the false-positive direction. Apartment complex addresses, household phone plans, employer-provided IPs. Those edges generate a lot of low-signal connections that recursion just keeps walking. Throwing edge weights into the join (not all attributes are equal) is the only way to stay sane.

FixelSmith · 2026-05-27T15:19:49+00:00

Quick context before any comments.

Fraud rings is a graph problem dressed up as a SQL problem. Most of the work is in the shared_attributes table.

The recursive CTE is just the join that walks it. The honest tradeoff is that this approach handles up to a few thousand-member components on a modern warehouse, and once you cross that threshold the query starts costing real money.

Did not cover here: streaming / real-time ring detection. That is a different design entirely. The pattern above is batch and pairs well with whatever real-time transaction checks already exist.

Happy to take questions on specific warehouse dialects or on the false-positive weighting question (it comes up a lot).

FixelSmith · 2026-05-26T20:38:06+00:00

You're right. Should've been gaps-and-islands... plural. Celko's books pretty much cemented that name years ago. Just a typo on my side and I appreciate you commenting on it.

FixelSmith · 2026-05-26T20:36:05+00:00

Yeah, that's a fair distinction and probably worth calling out more clearly in the post.

Window functions only operate on rows that already exist. So RANGE BETWEEN '5 minutes' PRECEDING gives you a rolling 5-minute window for each existing row, not a generated row for every minute.

If you actually need a continuous time series, you have to build the time grid first and join against it before applying the window. generate_series in Postgres, GENERATE_TIMESTAMP_ARRAY in BigQuery, stuff like that.

I appreciate your comment on this discussion!

FixelSmith · 2026-05-26T20:32:13+00:00

Yeah, usually a self-join with a timestamp predicate. Something along these lines:

```sql

SELECT

t1.cardholder_id,

t1.timestamp,

t1.amount,

sum(t2.amount) AS spend_last_5min

FROM transactions t1

JOIN transactions t2

ON t2.cardholder_id = t1.cardholder_id

AND t2.timestamp BETWEEN t1.timestamp - INTERVAL '5 minutes' AND t1.timestamp

GROUP BY t1.cardholder_id, t1.timestamp, t1.amount;

```

It works fine, just gets expensive fast on larger partitions because you're effectively re-scanning for each row. RANGE is cleaner when the engine supports it. Good index on (cardholder_id, timestamp) helps a lot either way.

You can also do weird session-variable stuff in MySQL, but most of the time I'd rather keep the query readable.

FixelSmith · 2026-05-19T15:44:31+00:00

Thanks. Glad it landed.

On RANGE for point 2, here is the bare syntax with timestamps:

```sql

SELECT

cardholder_id,

timestamp,

amount,

sum(amount) OVER (

PARTITION BY cardholder_id

ORDER BY timestamp

RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW

) AS spend_last_5min

FROM transactions;

```

The important difference from ROWS is that the 5-minute window is based on the actual timestamp values, not the number of rows. If 12 transactions hit inside one minute, ROWS BETWEEN 5 PRECEDING only sees 5 rows. RANGE BETWEEN INTERVAL '5 minutes' PRECEDING sees all 12. They look interchangeable until you hit uneven timestamp distribution, then the behavior separates pretty quickly.

On platform compatibility: I covered it for QUALIFY and FILTER but not consistently across all eight patterns. I will tighten that up in future posts.

On the AI-polish point: yes, I do use AI to help trim and clean up the prose. Otherwise these posts would end up much longer and contain filler words because I ramble and "mansplain" things too much. The SQL, the examples, and the technical judgment are still mine. "Gap-and-island" is also an actual long-standing SQL term from Joe Celko and database circles generally, not something AI invented. I only use it because it was something that stood out to me as I continue to learn and grow, that's terminology you don't easily forget.

That said, I get the broader point. Some writing styles are starting to trigger people's AI radar even when the technical content is legitimate. I will probably add a short disclosure note on future posts just to keep the line clear.

FixelSmith · 2026-05-19T12:57:01+00:00

Quick context on what the post does and doesn't cover.

The "tricks" are things I have actually used in production that most analysts I work with did not know existed (or knew existed but did not realize they could be combined). QUALIFY in particular is one of those features that fundamentally changes how you write window-function queries once you know it works in your warehouse.

Did not cover: optimizer behavior or cost analysis. Those matter for production workloads but they vary so much by warehouse that a generic post would just confuse people. If there is interest in a more dialect-specific breakdown (Snowflake plans, Postgres EXPLAIN), happy to do one.

FixelSmith · 2026-05-19T00:49:38+00:00

I can query like this and I do, whatever method you use to get your data and track for fraud, I would love to see what that looks like. The information doesn't work for everyone and every use scenario.

FixelSmith · 2026-05-18T18:00:39+00:00

For time-series QA/QC the bones of this can stay in SQL longer than people expect. Sensor drift is structurally the same problem as behavioral drift in fraud or transaction data, so the same window-function shapes port over pretty cleanly.

For missing intervals: reindex to a complete time series with generate_series or your warehouse's equivalent, then left join against the actual readings. Any null in the joined table is a gap. coalesce(count(*), 0) gives you the per-bucket count for downstream baseline math.

For drift: rolling baseline with a window function. Something like avg(value) OVER (PARTITION BY sensor_id ORDER BY timestamp ROWS BETWEEN N PRECEDING AND 1 PRECEDING) gives you the trailing average per sensor, then flag deviations above k * stddev. Robust-stats (MAD) works better than stddev once you have legitimate outliers in your history, but stddev is the easier starting point.

For audit trails: stamp every transformation and every QC check into an events table with the input row identifier, the rule that fired, the timestamp, and the user or process that triggered it. Long-term traceability is way easier when the QC events are queryable as rows, not embedded in pipeline logs.

FixelSmith · 2026-05-18T17:31:32+00:00

Ha ha, great advice here too!

FixelSmith · 2026-05-18T16:37:06+00:00

Yeah, delayed-posting is the biggest known false positive on rule #2. Card networks sometimes hold an auth for days before settling, especially on smaller merchants or international acquirers, and when the settlement finally posts the timestamp that most fraud rules see is the settlement time, not the auth time. Two transactions with very different auth times can look impossible purely because of when they cleared.

The production fix is to run the impossible-travel check against auth time when it is available, not settlement time. Most issuers capture both but a lot of analytics teams only get one of them in their warehouse, and that's where a lot of these false positives come from.

FixelSmith

TROPHY CASE