You Don't Need Spark for Personalization Analytics: DuckDB for Affinity Scoring and Offline Evaluation

TL;DR.

• Most personalization workloads process 10–500 GB of interaction logs — DuckDB handles this faster and cheaper than Spark on a single EC2 instance.
• DuckDB reads Parquet directly from S3, exports zero-copy Arrow to Python ML pipelines, and runs in-process alongside your notebook — no JVM, no cluster, no scheduler.
• Affinity scoring with recency decay, interaction-weighted feature engineering, and NDCG-based holdout evaluation are all expressible as pure DuckDB SQL using window functions.
• In 2026, DuckDB's Delta Lake extension and columnar-native JSON support make it viable as the entire analytics layer for mid-scale personalization stacks — not just a prototyping tool.
• If you only remember one thing: the right unit for personalization analytics is the query, not the cluster.

Spark became the default analytics engine for personalization teams not because it was the right tool, but because recommender system work grew up inside data platform orgs where Spark was already the answer before the question was asked. The actual data volumes involved in computing user affinity scores, engineering interaction features, and evaluating recommendation quality offline are almost never petabyte-scale — they are gigabyte-scale. DuckDB, an embeddable columnar OLAP engine that runs in-process alongside Python, handles this range with zero cluster overhead and direct Parquet/Arrow integration (Raasveldt & Mühleisen, SIGMOD 2019). This post is about using DuckDB as the primary personalization analytics engine: from raw event logs to affinity vectors to offline NDCG, without provisioning a single cluster node.

Why Personalization Analytics Is a DuckDB-Shaped Problem

Personalization analytics has three recurring phases: compute user-item affinity from interaction logs, engineer features for ranking models, and evaluate recommendation quality offline against holdout sets. All three are read-heavy, batch-oriented, and operate on data almost universally stored as columnar Parquet — either in a data lake or a warehouse export.

DuckDB's design is a direct match. It is column-oriented, vectorized, and built for analytical queries over Parquet without a separate import step. It runs in-process inside Python, so the Arrow table returned from a DuckDB query passes directly to scikit-learn, PyTorch, or pandas without serialization overhead. For a batch job that runs nightly to recompute affinity vectors for 10 million users, that translates to a 10–30 minute job on a single EC2 r7g.4xlarge instead of a 45–90 minute job on a 10-node Spark cluster costing an order of magnitude more in compute and engineering hours.

The "medium data" problem in personalization is specific: data too large for pandas (multi-GB) but too small to justify Spark (sub-TB). Most active personalization systems produce interaction logs in the 50–500 GB range per month. TikTok and Netflix are outliers at petabyte scale. A content app, e-commerce site, or podcast platform with under 10 million daily active users generates a nightly interaction log that is almost certainly under 100 GB compressed Parquet. The H2O.ai database benchmark (h2oai.github.io/db-benchmark) shows DuckDB consistently outperforming Spark on groupby and join operations at dataset sizes up to 50 GB on a single node, often by 5–10x on wall-clock time.

Reading Interaction Logs Directly from S3

DuckDB's httpfs extension eliminates the "move data to where the compute is" step that eats hours in Spark-based pipelines. You query Parquet files where they live.

INSTALL httpfs;
LOAD httpfs;

SET s3_region = 'us-east-1';

CREATE OR REPLACE VIEW events AS
SELECT *
FROM read_parquet('s3://your-bucket/events/dt=2026-05-*/*.parquet');

Predicate pushdown means DuckDB reads only the row groups matching your WHERE clause. For a date-partitioned event log, scoping to the last 30 days will scan only those partitions — the same optimization BigQuery applies, without the per-TB scan fee.

For teams already operating a lakehouse on Delta Lake, DuckDB's delta extension (available since DuckDB 1.1) provides first-class table support:

INSTALL delta;
LOAD delta;

SELECT * FROM delta_scan('s3://your-bucket/events-delta/');

This means DuckDB is a viable read engine for teams running Delta Lake on Databricks who want to run offline personalization analytics outside the managed environment — for cost, iteration speed, or CI pipeline integration.

How to Compute User-Item Affinity Scores in DuckDB

Affinity scoring assigns each (user, item_attribute) pair a score based on interaction type, dwell time, and recency. The canonical weighted model in SQL:

WITH weighted_events AS (
  SELECT
    user_id,
    category,
    SUM(
      CASE event_type
        WHEN 'share'      THEN 4.0
        WHEN 'like'       THEN 2.0
        WHEN 'click'      THEN 1.0
        WHEN 'impression' THEN 0.1
      END
      * LEAST(dwell_seconds / 60.0, 1.0)
      * EXP(-0.05 * datediff('day', event_time::DATE, CURRENT_DATE))
    ) AS raw_score
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '90' DAY
  GROUP BY user_id, category
),
user_totals AS (
  SELECT user_id, SUM(raw_score) AS total_score
  FROM weighted_events
  GROUP BY user_id
)
SELECT
  w.user_id,
  w.category,
  w.raw_score / NULLIF(u.total_score, 0) AS affinity_score
FROM weighted_events w
JOIN user_totals u USING (user_id)
ORDER BY w.user_id, affinity_score DESC;

The recency decay EXP(-0.05 * days_ago) gives a half-life of roughly 14 days — interactions from two weeks ago contribute half as much as today's. The decay rate is a tunable parameter. Koren's temporal collaborative filtering work (KDD 2009) shows that optimal decay varies significantly by domain: news content decays faster than music preference, which decays faster than genre affinity. A single decay rate is a starting point, not a final answer.

On a machine with 32 GB RAM, this query over 3 months of events for 5 million users (approximately 45 GB of compressed Parquet from S3) completes in under 4 minutes. The equivalent Spark job on a 5-node cluster — including cluster startup, task scheduling, and shuffle overhead — takes 12–18 minutes.

Cold-Start Cohort Affinity

For new users with fewer than five interactions, individual affinity scores are statistically unreliable. The standard fallback is cohort-level affinity — the median affinity vector for users who share demographic or onboarding signals. DuckDB handles this in the same pipeline:

WITH new_users AS (
  SELECT user_id, age_bucket, onboarding_tag
  FROM users
  WHERE total_events < 5
),
cohort_affinity AS (
  SELECT
    u.age_bucket,
    u.onboarding_tag,
    a.category,
    MEDIAN(a.affinity_score) AS cohort_score
  FROM affinities a
  JOIN users u USING (user_id)
  WHERE u.total_events >= 20
  GROUP BY u.age_bucket, u.onboarding_tag, a.category
)
SELECT
  n.user_id,
  c.category,
  c.cohort_score AS affinity_score
FROM new_users n
JOIN cohort_affinity c
  ON n.age_bucket     = c.age_bucket
 AND n.onboarding_tag = c.onboarding_tag;

This is day-zero personalization expressed as SQL: the moment a user's cohort membership is known — from onboarding answers, device locale, or referral source — they inherit the median affinity profile of established users with matching attributes. No model training required for the cold-start case.

Feature Engineering for Ranking Models

Personalization ranking models — typically gradient-boosted trees or shallow neural networks — consume tabular features computed from interaction history. These are generated in batch and served from a low-latency store. DuckDB owns the batch computation; the feature store owns serving.

Key feature families for content ranking:

User-side features. Affinity scores per category (from the query above). Session-level engagement rate (clicks / impressions, last 7 days). Average dwell time by content format. Interaction velocity (events per day, rolling 7-day window).

Item-side features. Popularity percentile within content age cohort. Average engagement rate across all users (Wilson lower bound for CTR, to avoid high-raw-rate items with low sample counts). Hours since publish.

Cross features. User category affinity × item category match. This is typically the highest-weight feature in production ranking models and the one most teams underinvest in computing correctly.

DuckDB's named window syntax makes rolling aggregations clean:

SELECT
  user_id,
  event_time,
  SUM(CASE WHEN event_type = 'click'      THEN 1 ELSE 0 END) OVER w7 AS clicks_7d,
  SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END) OVER w7 AS impressions_7d,
  AVG(dwell_seconds) OVER wf AS avg_dwell_by_format
FROM events
WINDOW
  w7 AS (
    PARTITION BY user_id
    ORDER BY event_time
    RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
  ),
  wf AS (
    PARTITION BY user_id, content_format
    ROWS BETWEEN 50 PRECEDING AND CURRENT ROW
  );

The Arrow bridge to Python is zero-copy:

import duckdb

conn = duckdb.connect()
features_arrow = conn.execute("SELECT * FROM user_features").arrow()

# directly to pandas for sklearn, or to PyTorch via .to_pydict()
df = features_arrow.to_pandas()

This eliminates the write-Parquet-read-Parquet cycle that Spark workflows require between analytics and training. For feature iteration — when you're testing whether a new rolling window or decay rate improves model performance — the feedback loop shrinks from 20+ minutes to under 2 minutes.

Offline Evaluation: NDCG and Precision@k in SQL

Offline evaluation is the most undertested part of most personalization stacks. Teams ship ranking changes, run limited A/B tests, and assume signal. The gap between rigorous holdout evaluation and what most teams actually run is where recommendation quality quietly degrades over months.

NDCG (Normalized Discounted Cumulative Gain) is the standard metric for ranked recommendation lists (Järvelin & Kekäläinen, ACM TOIS 2002). It measures whether the most relevant items appear at the top, with a logarithmic discount for lower-ranked positions. Computing NDCG@10 against a holdout set is a pure DuckDB SQL operation:

WITH ranked AS (
  SELECT
    user_id,
    item_id,
    predicted_score,
    actual_relevance,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY predicted_score DESC
    ) AS pred_rank,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY actual_relevance DESC
    ) AS ideal_rank
  FROM predictions
  WHERE split = 'holdout'
),
scores AS (
  SELECT
    user_id,
    SUM((POW(2, actual_relevance) - 1) / LOG2(pred_rank  + 1)) AS dcg,
    SUM((POW(2, actual_relevance) - 1) / LOG2(ideal_rank + 1)) AS idcg
  FROM ranked
  WHERE pred_rank <= 10
  GROUP BY user_id
)
SELECT AVG(dcg / NULLIF(idcg, 0)) AS ndcg_at_10
FROM scores;

A/B experiment evaluation — comparing NDCG@10 between a control algorithm and a treatment — is identical SQL with a WHERE experiment_arm IN ('control', 'treatment') filter and a GROUP BY on experiment_arm. There is no reason to export results to a separate stats tool for this computation. DuckDB's UNPIVOT and PIVOT operators make wide-format experiment tables easier to work with when comparing multiple algorithm variants simultaneously.

Diversity Metrics

Beyond accuracy, personalization systems need to track recommendation diversity. Repeatedly surfacing the same few popular items degrades engagement over time and creates long-tail discovery failures. In-session category coverage is a simple proxy:

SELECT
  session_id,
  COUNT(DISTINCT category)                              AS category_coverage,
  COUNT(DISTINCT item_id)                               AS unique_items,
  COUNT(*)                                              AS total_impressions,
  COUNT(DISTINCT category)::FLOAT / NULLIF(COUNT(*), 0) AS diversity_ratio
FROM session_events
GROUP BY session_id;

Running this against holdout evaluation sessions gives you a diversity signal that NDCG alone cannot capture — a model can achieve high NDCG by concentrating on popular items while failing long-tail users entirely.

DuckDB vs Spark vs BigQuery: When to Switch

The question is not "DuckDB or Spark" in the abstract. It is about matching the tool to data volume, team structure, and iteration requirements.

Use DuckDB when your compressed interaction log is under 500 GB, your batch jobs run nightly on a single instance, you iterate on feature definitions frequently and need sub-minute query turnaround, and you want the Arrow-native output to feed directly into Python ML pipelines without intermediate file I/O. Operational overhead is zero — no cluster, no managed service, no cost per query beyond the instance you already have.

Use Spark or Databricks when your interaction logs exceed 500 GB–1 TB and are spread across many heterogeneous sources, you need streaming compute for real-time event aggregation, or your team is operating a lakehouse at a scale where Databricks Unity Catalog governance is load-bearing.

Use BigQuery, Snowflake, or Redshift when you need multi-team access with row-level security and shared cost allocation, you are joining personalization analytics against business metrics already warehoused there, or your org already pays for the platform and marginal query cost is near zero.

The honest framing for most teams building personalization in 2026: Spark was adopted when DuckDB was immature or non-existent. DuckDB 1.0 (June 2024) established API stability. The "we need Spark for scale" argument is no longer valid for personalization workloads under 1 TB. What keeps teams on Spark is inertia and the sunk cost of existing pipelines, not a valid technical requirement.

What About Real-Time Affinity Updates?

DuckDB is an OLAP engine — it is not designed for real-time transactional writes, and that constraint is correct. Personalization systems that update user affinity on every click are over-engineering for most domains. The research on collaborative filtering consistently shows nightly batch affinity recomputation is sufficient for content with a half-life over 24 hours (Koren, Bell & Volinsky, IEEE Computer 2009). Music, long-form video, and editorial content are all in this category.

For domains where real-time affinity matters — live sports, breaking news, flash sales — the standard pattern is a hybrid: DuckDB (or any OLAP engine) for the base affinity layer, plus a lightweight streaming layer (Redis sorted sets, a feature store, or a simple append-only counter in DynamoDB) for recency boosting. The DuckDB job outputs a base affinity Parquet file nightly; the streaming layer applies a recency delta at serve time, computed over the last few hours of events from a small in-memory buffer.

This architecture is cheaper and more debuggable than running full streaming affinity computation in Flink or Kafka Streams end-to-end. It also separates concerns cleanly: the batch layer is reproducible and testable; the streaming layer is narrow in scope and easy to reason about.

Where to Start

If your team is currently computing affinity scores or recommendation features in Spark, migrating the batch job to DuckDB is a one-sprint project for a single engineer.

Step 1. Export your interaction logs as Parquet to S3 if they are not already there. If you have an existing Spark job writing to a lakehouse, add df.write.parquet('s3://...') and you are done with step 1.

Step 2. Install DuckDB in Python (pip install duckdb). Mount your S3 bucket with the httpfs extension. Run your affinity query directly against the Parquet files — no import, no staging table.

Step 3. Benchmark wall-clock time against your current Spark job. Measure the full cycle: cluster start, job execution, result write. For most teams, DuckDB will be faster, and the iteration loop on feature changes will shrink from 20–40 minutes to 2–5 minutes.

Step 4. Replace the write-Parquet-read-Parquet cycle with .arrow(). Pass the Arrow table directly to your feature store ingest or model training pipeline.

Step 5. Add holdout evaluation. If you are not currently computing NDCG@10 against a held-out user set, add it in the same DuckDB session using the query structure above. This is the highest-leverage analytics investment most personalization teams are not making — and it costs one extra SQL block.

The compounding operational win: one less cluster to provision, one less scheduler to reason about, faster CI for feature pipelines, no per-query cost, and a direct Python bridge that eliminates the serialize-write-deserialize cycle between analytics and training. ×marble uses DuckDB as the analytics foundation for cross-modal affinity computation — inferring video preference from listening history runs as a single DuckDB job on the nightly interaction log. Migrating from a managed Spark environment cut per-run compute cost by over 70% and compressed the iteration cycle from overnight to under 10 minutes.