Event Paths in DuckDB

A table of events tells you what happened. Understanding how users reached a purchase, how orders moved through fulfillment, or what preceded a device failure takes more work: reconstruct the paths, count them, and keep the ones that stopped along the way.

In SQL, that quickly becomes a chain of joins and window functions. Find the starts and ends, collect the events between them, handle retries, collapse repeats, and group identical sequences. Missing end events and overlapping attempts make even the counting rules awkward.

At scale, those joins can be expensive. An event may belong to many candidate journeys, so a range join copies it into the intermediate result for each one. Sorting and aggregation then process that expanded dataset. Returning only the first nine steps of each path does little to help if the query has already materialized every step.

We built JOURNEYS to make path discovery a native DuckDB operation. It returns observed paths, counts, and durations, including incomplete journeys.

It sits next to MATCH_RECOGNIZE for explicit patterns and SESSIONIZE for inactivity gaps.

Suppose an events table has user_id, ts, event_id, and event. Four users produce these sequences, with all events inside one day:

User 1: Homepage → Signup → Docs → Checkout → Purchase
User 2: Signup → Docs → Checkout → Purchase
User 3: Pricing → Signup → Create project → Purchase
User 4: Docs → Signup → Docs → Docs
Copy

Let's say we want the paths from Signup to Purchase. Three users completed that journey through two routes; the fourth signed up and stopped at the documentation.

That path exploration is a single JOURNEYS clause:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
    path,
    converted,
    truncated,
    count AS journey_count,
    avg_duration
FROM events
JOURNEYS (
    PARTITION BY user_id
    ORDER BY ts, event_id
    STEP event
    PATHS BETWEEN (event = 'Signup') AND (event = 'Purchase')
    MEASURED AS UNIQUES
    WITHIN INTERVAL '1' DAY
)
ORDER BY journey_count DESC, path;

JOURNEYS returns all three paths:

path                                  converted  truncated  journey_count
-------------------------------------------------------------------------
[Signup, Docs, Checkout, Purchase]    true       false      2
[Signup, Create project, Purchase]    true       false      1
[Signup, Docs]                        false      false      1
Copy

The query orders events within each user, starts at their first signup, and follows the sequence to the earliest purchase within one day. Events before signup are excluded. Users who never reach a purchase in that window remain in the result. Consecutive documentation visits collapse into one step, so repeated clicks do not obscure the route.

With joins, the same event may be copied into many candidate journeys before the final paths are known. JOURNEYS avoids that large intermediate result. It sorts events by identity and time, then scans each identity's events to find matching paths. The operator reuses the spill-capable sorting pipeline behind MATCH_RECOGNIZE.

The numbers below were measured on an Apple M4 Pro with 14 CPU cores, using DuckDB v1.5.5. Each workload contains 10 million input rows, with tables loaded in memory before query timing. Both columns are medians from timed runs after a discarded warmup. The SQL join baseline uses the same in-memory tables: find start and end events, range-join every event in each span, then aggregate truncated paths.

The first row starts a path at every A — 1.26 million of them — so later events are copied into many overlapping spans. That join grew to 591 million rows and exhausted more than 85 GB of spill without returning a result. The start-to-end row hits the same wall on every A→B span: 348 million joined rows, then out of memory. The last row is a hash join of 10 million one-event users; JOURNEYS is about 5× faster.

JOURNEYS runs in the DuckDB execution core behind Altertable. The JOURNEYS reference covers path modes, counting, expansion, and windows. You can prepare events from lakehouse tables and run the clause through SQL Explorer or the query API.

We plan to publish the extension through the DuckDB community extension distribution. To discuss an event-flow workload in Altertable, email [email protected].