The first transfer I inspected was for 17.35 US dollars. The sender and recipient were the same account. Its source label was zero.
There was nothing cinematic about it. That was the useful part.
I was looking at a table with 179,702,229 transfers. If I started with a dramatic network of red arrows, I could make almost anything look suspicious. I wanted to start with something quieter. A transfer, a question, and a way to establish exactly what the screen knew.
That became the brief for Follow the money.
I built it around a sequence I could explain. Find the period. Narrow the payment channel. Locate the bank route. Open the transfers. Inspect an account's immediate connections. At every step, show the SQL and the time it took to return.
Before I followed the money, I followed the import
The IBM AML dataset is synthetic. I use the Kaggle distribution. The labels describe the generator's ground truth. They do not turn this dashboard into a detector of real financial crime.
My MotherDuck catalogue contained three tables. Transactions held 179,702,229 rows. Accounts held 2,126,855. Patterns held 49,400.
The third number nearly sent the project in the wrong direction.
The Patterns table had one column, named BEGIN LAUNDERING ATTEMPT - STACK. Its nonempty rows were begin and end markers. There were no transaction records in that import. I counted 16,466 begin markers, 16,467 end markers, and 16,467 null rows.
I could have joined on guesses and produced a convincing investigation view. Instead, I gave those markers their own catalogue. They describe the imported pattern vocabulary. They do not establish which transfers belong to an attempt.
The odd column name is consistent with a marker being consumed as a header. That is an import hypothesis. I do not reconstruct a missing attempt from it.
This changed the dashboard's story. I could trace observed transfers between accounts, but I could not claim that a path was a recovered laundering attempt. The distinction lives in the model and the interface.
I gave each filter a job
I started with a date range and one payment currency. I kept them in a compact scope bar with the payment channel and source label. The charts became the remaining controls.
The first chart separates all transfers and labelled transfers into aligned time plots. They share dates and keep their own scales. I can see whether a rise in ordinary activity coincides with a rise in the labelled signal without comparing two overlapping axes.
The next chart compares each channel's share of all transfers with its share of labelled transfers. In the first week of August 2022, within US Dollar payments, ACH carried 12.08 percent of transfers and 75.29 percent of labelled transfers. Two bars made that concentration visible before I wrote a sentence about it.
I then follow the bank flows. Their widths encode labelled transfer counts across the twelve largest directed routes. Selecting a ribbon filters the exact sender and recipient pair. In the unlabelled scope, the diagram ranks and sizes routes by unlabelled counts instead.
Each chart keeps its own choices visible. A selected day stays inside its surrounding timeline. Selecting ACH leaves the other channels available for comparison. Selecting a route leaves neighboring routes in view. Every other chart and the ledger receive that selection through SQL. I can remove it from the scope bar, or select it again to clear it.
I can also brush a period, move it, or resize either edge. I keep that interval separate from the outer date range. The timeline retains its context while the summary and channel comparison answer the narrower question. The native source spans 1 August 2022 through 12 January 2023. Choosing an earlier start extends the visible axis without inventing earlier transfers.
I kept the account selection local. Opening an account changes the investigation detail, while the shared cohort still constrains its connections. An account is identified by bank and account number together. Account numbers alone are not a join key I am willing to trust.
The pattern catalogue has a different scope. It describes the whole imported marker file. Applying a transaction date filter to it would imply a relationship the data does not contain, so I say exactly which scope it uses.
I also made one decision early that removed a surprising amount of visual noise. There is no explanatory subtext inside the charts. Titles, legends, controls, and values do the work. Definitions belong in the article and query disclosures. The chart panels remain places to look.
The browser does not need 179 million rows
My tennis dashboard already uses DuckDB WASM to query Parquet in the browser. This dataset demanded a different division of work.
MotherDuck executes the large scans against the native tables in vikram-de-portfolio-fast. Its WASM client gives the browser a SQL connection authenticated with my access token. I load one complete, scoped overview into an in-memory browser table. I do not download the raw transaction table and hope virtualization will make it manageable.
I let Mosaic 0.33.0 coordinate the summary, timeline and channel queries over that local table. A day, channel or source-label selection changes a Mosaic predicate. The query runs in browser DuckDB WASM, or Mosaic reuses an identical result from its bounded cache. I have no application SQL planner deciding whether one query can impersonate another.
The scope boundary matters. Changing the outer dates, currency, bank or directed route creates a fresh overview from MotherDuck. The snapshot keeps every channel and source label inside that scope so linked charts can retain their comparison context. The bank-flow comparison, ledger, account detail and SQL console still query MotherDuck directly.
| Function | Work and execution location |
|---|---|
overviewLoadSql() | Builds the scoped native-source aggregation that creates a local WASM table. |
createAmlOverview() and overviewQueries() | Own the snapshot lifetime and declare Mosaic clients, selections and local SQL. Mosaic owns result caching, consolidation and compatible preaggregation. |
amlQueries(), accountQueries() and gridPageSql() | Build cloud SQL for routes, account investigation and bounded transfer pages. |
queryMotherDuck() | Runs read-only SQL through the shared MotherDuck connection. Local Arrow streams and cloud reads keep exclusive connection ownership until their results have been consumed. |
amlTimelineOption(), amlChannelsOption() and amlRoutesOption() | Map results into ECharts options. They do not aggregate transfers. |
I cap the overview at 50,000 rows and reject a larger cohort instead of enabling a partial cache. Mosaic retains at most 32 MiB of result IPC. That byte budget does not cap total browser memory or the local database. Replacing a snapshot drops its table and generated preaggregation schema. Disconnecting terminates the worker and discards the session.
For the ledger, a block contains 200 rows plus one lookahead row. The extra row tells me whether another block exists without running an exact filtered count for every scroll event. I retain at most six blocks.
Sorting, searching, and filtering generate SQL. A new interaction invalidates the old datasource and cancels superseded MotherDuck queries. A generation check prevents late results from replacing the current selection even when a cancellation arrives too late to stop the database work.
That last detail matters. Cancelling a promise and cancelling a query are different operations.
SELECT *
FROM (
SELECT rowid AS source_rowid,
"Timestamp",
"From Bank",
"Account",
"Amount Paid"
FROM "vikram-de-portfolio-fast".main.HI_Large_Trans
WHERE "Timestamp" >= DATE '2022-08-01'
AND "Timestamp" < DATE '2022-08-02'
AND "Payment Currency" = 'US Dollar'
) AS cohort
ORDER BY "Amount Paid" DESC, source_rowid
LIMIT 201
Virtualization bounds rendering. SQL pushdown bounds what crosses the connection. Neither makes an arbitrary sort over the entire dataset free. The selected cohort and physical model still determine the work the database must do.
I measured the table before committing to it
I built a small development POC for AG Grid Community, Glide Data Grid, and TanStack Table with TanStack Virtual. Each candidate receives the same retained rows and columns. The harness records time to paint and frame intervals during scrolling.
I keep the harness and decision record with the implementation so the experiment can be rerun. Its fixture measures browser rendering. It does not establish a MotherDuck latency number.
AG Grid Community gives me an infinite row model, keyboard navigation, column resizing, and filter controls in one maintained component. I use that grid in both the ledger and SQL console. Glide's canvas approach is interesting, but its published React peer range stops at React 18 while this application uses React 19. TanStack gives me more control and also leaves more interaction behaviour for me to own.
I save the grid's sort, search, column filters, visibility, and widths against the dataset version and query. The global cohort also lives in the URL. A refresh should return me to the question I was asking.
I followed the wait back to storage
My first local cache returned only 14,063 overview rows. It still made me wait around thirty seconds. The small result concealed the work underneath it. MotherDuck was scanning 179,702,229 source transfers in DuckLake before the browser could begin.
I profiled the same grouping against a native MotherDuck copy. One recorded DuckLake run took 29.17 seconds. The native run took 4.37 seconds and processed the same number of input rows and groups. Those are observed query runs, not a controlled benchmark or a promise about total dashboard startup.
I moved the application to the native database and reconciled every overview group. Counts matched exactly across all 14,063 groups. Paid amounts matched within the declared floating point tolerance. I then stopped asking each chart to rebuild a bank-pair aggregate it did not need.
The browser overview keeps day, payment channel and source label. Its outer SQL already fixes the date range, currency and any bank or route selection. Counts and paid amounts are additive within that grain. Bank-route comparisons and account connections retain their own source queries because their extra dimensions cannot be recovered from this small local table.
I removed the unpublished cloud-rollup proposal and daily-model detection path. The first optimization was a better storage source and a smaller scoped load. The second was reusing that work locally through Mosaic.
I do not add a distinct account count to the overview and then sum it across days. The same account can appear repeatedly. That question needs a query that preserves its meaning.
Currency needs the same discipline. I do not add dollars, yen, and bitcoin into one impressive total. The cohort selects a payment currency, and the ledger keeps paid and received amounts with their respective currency fields. The source money columns are DOUBLE, so aggregate displays inherit floating point limits. Casting their output to text does not restore precision the import never stored.
Every chart carries its receipt
The small number at each chart's top left is the elapsed query time in milliseconds. It updates while the query is running and settles when the materialized result is available.
For a local chart's first result, I include loading its selected cohort from MotherDuck. Later timings measure Mosaic coordination, cache lookup, local execution when needed and result conversion. Cloud charts measure submission through materialized result conversion. Both exclude connection initialization, chart rendering and animation. I keep the execution engine in View SQL so the number has a boundary I can inspect.
The tennis map exposed a related mistake I wanted to avoid. Its annual animation reuses one query containing all years. Advancing the year does not run that map query again. Its timing therefore stays attached to the query that actually produced the frames.
I also keep charts mounted during updates. Removing a canvas for every filter change destroys continuity. Stable series identities let values move into their next positions. Reduced motion still takes priority, and playback stops when the page is hidden.
Brushing exposed a different ownership problem. React could redraw the chart while ECharts was moving a selection, resetting a gesture halfway through it. I gave the timeline its own controller using ECharts' documented brush events. ECharts owns the active drag. I commit its inclusive UTC interval on release, then let Mosaic update the linked views. Programmatic restoration stays silent. Keyboard date inputs follow the same path.
I also aligned restored brush edges with the daily sample coordinates. A day is a calendar interval, but a plotted mark is a point. Mixing midnight boundaries with midday marks made a moved selection grow by a day. Giving both the same coordinates made moving and resizing predictable.
Long SQL taught me a layout lesson
An expanded query once stretched beyond its panel and across the neighbouring chart. The overflow was a sizing failure in the component hierarchy.
I changed the disclosure layout so each grid track, panel, disclosure, and code viewport can shrink to its allotted width. The query scrolls inside that viewport. I can still read the complete SQL, and the surrounding page keeps its structure.
That is the same contract I want from the data layer. A component owns its bounds. A query owns its scope. Neither should make its neighbour absorb an assumption it never agreed to.
The token is part of the architecture
I followed the connection pattern in MotherDuck's own WASM example. I enter my access token, and the browser connects directly to my MotherDuck account. I do not need to issue another credential or add Read Scaling just to establish that connection.
I keep the token in the current tab's memory. I clear the input after submission and terminate the MotherDuck worker when I disconnect. Refreshing requires me to connect again, while my filter and grid state remain saved. Changing accounts resets the shared MotherDuck worker so the next connection cannot silently retain the previous identity.
This makes the access model explicit. Someone opening the dashboard needs a token with access to the tables. My application does not hand my token to every portfolio visitor. A client-side SQL check improves the experience, but it does not remove the underlying permissions of the credential I supply. A public dashboard without a login would need a separately restricted reader or a server query service.
Direct browser queries also have a cost consequence. A token holder can submit reads within that account's permissions. A page-size limit is not a spending limit. I keep that distinction alongside the performance measurements.
Where the trail ends
The dashboard can show me a labelled transfer, the bank route around it, and an account's bounded neighbourhood within my chosen cohort. I can inspect the rows, open the SQL, and see how long each answer took.
It cannot turn missing pattern records into evidence. It cannot make mixed currencies comparable without a conversion model. It cannot make every query instant by putting WASM in the architecture diagram.
Those boundaries gave the project its shape.
I started with a small transfer in a very large table. I ended with an interface that lets me keep asking the next question without losing the meaning of the previous answer.