Engineering Overview

This page provides a technical summary of the engineering decisions, quality standards, and design trade-offs that underpin duckdb-behavioral. It is intended for technical evaluators, contributors, and anyone seeking to understand the depth and rigor of this project.

Project Scope

duckdb-behavioral is a loadable extension for DuckDB that implements the complete set of behavioral analytics functions found in ClickHouse. The extension is written entirely in Rust, compiles to a shared library (.so / .dylib) that is stamped with DuckDB's metadata footer as a .duckdb_extension file, and integrates with DuckDB via the stable C extension API (C API v1.2.0, loadable into DuckDB 1.3.2 and later).

The project spans several distinct engineering disciplines:

Architecture

Separation of Concerns

The codebase enforces a strict separation between business logic and FFI integration:

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#ffffff', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#f5f5f5', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a', 'mainBkg': '#ffffff', 'clusterBkg': '#f5f5f5', 'clusterBorder': '#333333'}}}%%
graph TB
    subgraph "DuckDB Process"
        DDB[DuckDB Engine]
        SEG[Segment Tree<br/>Windowing]
    end

    subgraph "Extension Entry Point"
        EP[entry_point_v2! macro<br/>src/lib.rs]
    end

    subgraph "FFI Bridge — src/ffi/"
        REG[register_all]
        FS[sessionize.rs]
        FR[retention.rs]
        FW[window_funnel.rs]
        FWE[window_funnel_events.rs]
        FQ[sequence.rs]
        FE[sequence_match_events.rs]
        FN[sequence_next_node.rs]
        FV[version.rs]
    end

    subgraph "Business Logic — Pure Safe Rust"
        SS[sessionize.rs]
        SR[retention.rs]
        SW[window_funnel.rs]
        SQ[sequence.rs]
        SN[sequence_next_node.rs]
        subgraph "Pattern Engine"
            PP[parser.rs]
            PE[executor.rs]
        end
        subgraph "Common"
            EV[event.rs — 16-byte Copy]
            TS[timestamp.rs]
        end
    end

    DDB -->|LOAD extension| EP
    EP --> REG
    REG --> FS & FR & FW & FWE & FQ & FE & FN & FV
    SEG -->|init/update/combine/finalize| FS & FR & FW & FWE & FQ & FE & FN
    FS --> SS
    FR --> SR
    FW --> SW
    FWE --> SW
    FQ --> SQ
    FE --> SQ
    FN --> SN
    SQ --> PP & PE
    SW --> EV
    SQ --> EV
    SN --> EV

    style EP fill:#d9d9d9,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style DDB fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SEG fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style REG fill:#f0f0f0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FS fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FR fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FW fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FWE fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FQ fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FE fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FN fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FV fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SS fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SR fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SW fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SQ fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SN fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style PP fill:#ffffff,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style PE fill:#ffffff,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style EV fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style TS fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a

• Business logic (src/*.rs, src/common/, src/pattern/): Pure safe Rust. No FFI types, no unsafe blocks. All algorithmic work -- pattern parsing, pattern matching, funnel search, retention bitmask logic -- lives here. Its unit tests exercise Rust structs directly, without a DuckDB connection.

• FFI bridge (src/ffi/, 9 files): Contains all unsafe code. Each aggregate has an FFI module wiring the DuckDB aggregate callbacks (state_size, init, update, combine, finalize, destroy); version.rs registers the behavioral_version() scalar. The update, combine, and finalize callbacks are generated with quack-rs's aggregate_{update,combine,finalize}_callback! macros, which catch a panic and report it as a SQL error (the release profile uses panic = "unwind" so this guard works). Every callback's unsafe block has a // SAFETY: comment documenting the invariants it relies on.

• Entry point (src/lib.rs): Uses the quack_rs::entry_point_v2! macro with the Connection/Registrar trait pattern. Functions are registered via con.register_aggregate_set(builder) (variadic aggregates) and con.register_aggregate(builder) (sessionize). This replaces ~80 lines of hand-rolled unsafe code and eliminates struct layout fragility across DuckDB versions.

Why This Matters

This architecture enables:

• Independent unit testing: The 547 unit tests exercise Rust structs directly and run in about two seconds, without loading the extension.
• Safe evolution: Updating the DuckDB version only requires updating libduckdb-sys in Cargo.toml and re-running E2E tests. Business logic is decoupled from the database.
• Auditable unsafe scope: The unsafe boundary is confined to src/ffi/ (9 files: mod.rs, 7 aggregate modules, version.rs). Reviewers can audit the safety-critical code without reading the entire codebase.

Testing Philosophy

The Test Pyramid

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#ffffff', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#f5f5f5', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a', 'clusterBkg': '#f5f5f5', 'clusterBorder': '#333333'}}}%%
graph TB
    subgraph "Complementary Test Levels"
        L3["Mutation Testing<br/>93.9% detected (570/607, 0.10.0 audit)<br/>cargo-mutants"]
        L2["Integration + E2E<br/>28 in-process LOAD tests<br/>8 sqllogictest files on the DuckDB CLI"]
        L1["Unit Tests (547)<br/>State lifecycle, edge cases, combine correctness<br/>Property-based (33 proptest), mutation-guided (67)"]
    end

    style L1 fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style L2 fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style L3 fill:#d9d9d9,stroke:#333333,stroke-width:2px,color:#1a1a1a

This project implements a rigorous multi-level testing strategy:

Level 1: Unit Tests (547 tests)

Organized by category within each module:

• State lifecycle tests -- empty state, single update, multiple updates, finalize
• Edge cases -- threshold boundaries, NULL handling, empty inputs, overflow at type boundaries (u32::MAX, i64::MIN)
• Combine correctness -- empty-into-empty, empty-into-populated, populated-into-empty, associativity verification, configuration propagation
• Property-based tests (33 proptest) -- algebraic properties required by DuckDB's segment tree: combine associativity, commutativity, identity element, idempotency, monotonicity; plus two differential tests that check the sequence matcher against the original backtracking search on random patterns and event sets
• Mutation-testing-guided tests (67) -- tests written specifically to kill mutants that survived earlier test suites (51 from the v0.4.x run, 16 from the 0.10.0 run)

Level 2: E2E Tests (8 sqllogictest files: 44 query + 34 statement directives)

SQL logic tests in test/sql/*.test, run against a real DuckDB CLI instance, that validate the complete chain: extension loading, function registration, SQL execution, and result correctness. These tests caught three critical bugs that the entire unit suite missed:

1. A segmentation fault on extension load (incorrect pointer arithmetic)
2. Six of the seven functions that existed then silently failing to register (missing API call)
3. window_funnel returning incorrect results (combine not propagating configuration)

This same load → register → execute chain also runs in-process inside cargo test (tests/extension_load.rs, 28 tests; run with DUCKDB_DOWNLOAD_LIB=1 cargo test). It builds the real release cdylib, appends the DuckDB metadata footer, and LOADs it via quack_rs::testing::InMemoryDb::open_unsigned() — so the entire class of FFI-registration bugs above is now caught by the regular test suite, not only by the CLI-based E2E job in CI.

Level 3: Mutation Testing (93.9% detected, 0.10.0 audit)

cargo-mutants systematically replaces operators, removes branches, and changes return values across the codebase. The 0.10.0 audit of the 9 non-FFI modules detected 570 of 607 viable mutants (547 caught, 23 timed out); v0.4.x had caught 130 of 147. Tests were then written for the surviving mutants that change behaviour, and window_funnel.rs, sequence_next_node.rs and pattern/parser.rs were re-run: 320 of 342 viable detected. The 22 remaining survivors were reviewed by hand as equivalent (e.g. OR vs XOR on non-overlapping bit fields, the hash function of a cache, a function never called for one path type).

Key Insight

Unit tests validate algorithmic correctness in isolation. E2E tests validate the integration boundary. Neither alone is sufficient. The three bugs found during E2E validation were entirely in the FFI layer -- a domain that unit tests by definition cannot exercise.

Performance Engineering

Methodology

Every performance claim in this project is a Criterion microbenchmark of the Rust aggregate state (not an end-to-end SQL query), backed by:

• Criterion.rs benchmarks with 95% confidence intervals
• Multiple runs (minimum 3) to establish baselines
• Throughput reporting (elements per second) alongside wall clock time
• Negative results documented honestly -- five attempted optimizations were measured, found to be regressions, reverted, and documented

Scale

Optimization History

Optimization sessions recorded in PERF.md (Sessions 1 through 18, with before/after measurements), each following a documented protocol:

1. Establish baseline with 3 Criterion runs
2. Implement one optimization per commit
3. Measure before/after with confidence intervals
4. Accept only when confidence intervals do not overlap
5. Revert and document if the improvement is not statistically significant

Selected highlights:

Five attempted optimizations were negative results:

• LSD radix sort: 4.3x slower than pdqsort for 16-byte embedded-key structs
• Branchless sessionize: 5--10% regression; branch predictor handles 90/10 patterns efficiently
• String pool with index vectors: 10--55% slower than Arc<str> at most scales
• Compiled pattern preservation: Caching parsed patterns did not improve performance
• First-condition pre-check: Adding an early-exit branch for non-matching first conditions increased overhead

Full optimization history with confidence intervals: Performance

Domain Significance

Why Behavioral Analytics Matters

Behavioral analytics answers a class of questions that traditional SQL aggregations cannot express efficiently:

• Sessionization: Given a stream of timestamped events, partition them into logical sessions based on inactivity gaps. This is fundamental to web analytics, mobile app analysis, and IoT telemetry.

• Retention cohorts: Given users who first appeared at time T, what fraction returned at T+1, T+2, T+N? This is the core metric for SaaS businesses, subscription services, and any product measuring long-term engagement.

• Conversion funnels: Given a multi-step process (browse, add to cart, checkout, purchase), how far does each user progress within a time window? This directly drives revenue optimization in e-commerce.

• Event sequence patterns: Given a temporal sequence of heterogeneous events, does a specific pattern occur? How many times? What happened next? This generalizes to fraud detection, clinical event monitoring, user journey analysis, and process mining.

Who Needs This

Scale Context

These problems are inherently large-scale. A mid-sized SaaS application generates millions of events per day. An e-commerce platform during a sale event can produce hundreds of millions of events per hour. The ability to update a sessionize state with one billion events in 1.20 seconds on a single core (a Criterion microbenchmark of the Rust state, not a SQL query) suggests these analyses can run as interactive queries rather than batch jobs.

Implementation Challenges

DuckDB Aggregate Function Registration

DuckDB's Rust crate does not provide high-level aggregate function registration. This project uses the quack-rs SDK (v0.19.0) which wraps the raw C API with safe builders (including returns_logical(LogicalType) for LIST(T) return types), state management, vector I/O, LIST output helpers, and the aggregate_{update,combine,finalize}_callback! macros that turn a panic into a SQL error. All 8 aggregate functions use quack-rs builders for registration: the seven variadic functions via AggregateFunctionSetBuilder, and sessionize via AggregateFunctionBuilder (a single fixed signature — DuckDB windows any aggregate, so no window-specific registration exists). Each aggregate implements five callback functions:

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#f5f5f5', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#e8e8e8', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a', 'noteBkgColor': '#e8e8e8', 'noteTextColor': '#1a1a1a', 'noteBorderColor': '#333333', 'labelTextColor': '#1a1a1a', 'transitionColor': '#333333'}}}%%
stateDiagram-v2
    [*] --> state_size: DuckDB queries state byte size

    state_size --> init: Allocate state buffer
    init --> update: Process input rows

    state "update" as update {
        [*] --> ProcessRow
        ProcessRow --> AccumulateState
        AccumulateState --> ProcessRow: Next row
    }

    update --> combine: Segment tree merge
    note right of combine
        Called O(n log n) times.
        Target state is zero-initialized.
        Must propagate ALL config fields.
    end note

    combine --> finalize: Produce result
    finalize --> destroy: Free resources
    destroy --> [*]

state_size  -- Returns the byte size of the aggregate state
init        -- Initializes a new state (called by DuckDB's segment tree)
update      -- Processes one row of input into the state
combine     -- Merges two states (called O(n log n) times by segment tree)
finalize    -- Produces the final result from the accumulated state

Because duckdb_aggregate_function_set_varargs does not exist, each function that accepts a variable number of boolean conditions registers one overload per arity via a function set:

sessionize and the behavioral_version() scalar have a single signature.

Pattern Engine

The sequence functions use a custom pattern engine:

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#ffffff', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#f5f5f5', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a'}}}%%
flowchart LR
    SQL["Pattern String<br/>'(?1).*(?t<=3600)(?2)'"] --> PARSE["Recursive Descent<br/>Parser"]
    PARSE --> STEPS["CompiledPattern<br/>Condition(1), AnyEvents,<br/>TimeConstraint(<=, 3600),<br/>Condition(2)"]
    STEPS --> CLASS{Classify}
    CLASS -->|"All adjacent"| FAST1["O(n) Sliding<br/>Window"]
    CLASS -->|"Wildcard-separated"| FAST2["O(n) Linear<br/>Scan"]
    CLASS -->|"Time constraints<br/>or mixed"| NFA["Feasibility pass +<br/>greedy walk"]

    style SQL fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style PARSE fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style STEPS fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style CLASS fill:#ffffff,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FAST1 fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FAST2 fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style NFA fill:#d9d9d9,stroke:#333333,stroke-width:2px,color:#1a1a1a

• A recursive descent parser that compiles pattern strings (e.g., (?1).*(?t<=3600)(?2)) into an intermediate representation of typed steps
• A general matcher for every other shape: one backward pass per event-consuming step marks the positions from which the rest of the pattern can still complete, then a forward walk takes the earliest such position at each step. It returns exactly the match a lazy (.* matches as little as possible) backtracking search finds first, in O(s · n log n) for n events and s pattern steps. The extension used that backtracking search through v0.9.1; it was replaced for being quadratic per group on patterns with .* or skipping time constraints. It is kept as a test-only oracle, and differential property tests check that the two agree.
• Fast-path classifiers that detect common pattern shapes (adjacent conditions, wildcard-separated conditions) and dispatch to specialized O(n) linear scans

Combine Semantics in DuckDB's Segment Tree

DuckDB's windowing and grouping machinery uses a segment tree that creates fresh zero-initialized states and combines source states into them. This creates a subtle correctness requirement: combine must propagate all configuration fields (window size, mode flags, pattern string, direction/base parameters), not just the accumulated data. Missing this caused silent incorrect results that passed all unit tests but failed E2E validation.

Quality Standards

Technology Stack