SQL layer over user listening history for agentic access
- Kian Mehrabani works on personalization within Spotify, specifically on user behavior.
- Personalization products (favorite genres, daily listening, prompted playlists) are all driven by analysis of each user's listening history and tastes.
- Recommendations boil down to seeding candidates from listening history and filtering down to the best content for a user.
- At the core is a user behavior data platform: a distributed log of key behavior facts (streams, impressions, app interactions), keyed by user, serving petabytes of data.
More detail
Two traditional access patterns: low-latency access for recent data (recent streams), and a custom materialization engine on top that lets customers build materialized views for longer tails. Listening data is heavily skewed, so materialization is used to reduce tail latencies.
- New text-driven experiences let users ask any question about their listening history at any time, in plain language.
- Examples: 'what is my top artist in 2019?', 'what was my first stream?', 'what were my most played songs last summer?', 'how has my music taste changed this year?'.
- The existing platform had no way to serve these open-ended, dynamic queries without major architectural changes.
- The identified need: a flexible, dynamic query interface to user behavior that they did not already have.
More detail
The agent needs an interface to translate prompts into a useful intermediate, plus something that can crunch the actual listening history to produce the answer. Traditional recommendations remain just as important, so the solution had to sit on top of the existing system.
- The flexible, dynamic query interface pointed naturally to SQL.
- DuckDB was chosen because it could be built on top of what Spotify already had, rather than building a new system from the ground up.
- Goal was to add the capability with minimal disruption to the existing user behavior platform.
More detail
The 'Walks and Quacks like...' slide pairs the SQL logo with the DuckDB logo, framing DuckDB as the natural fit for an embeddable, in-process SQL engine.
- A stateless Kubernetes deployment sits on top of the user behavior store; disks are ephemeral and everything runs in memory.
- DuckDB runs in process inside Java gRPC services.
- Request flow: incoming user ID + SQL query, fetch listening behavior stored as Protobuf, convert to Apache Arrow vectors.
- Arrow data is piped into DuckDB via its Arrow integration, the SQL query runs, and results come back as Arrow, serialized over the wire.
- Clients turn results into JSON when needed, or keep using Arrow downstream.
More detail
The change to the existing platform was deliberately simple: a stateless, fully ephemeral service plopped on top of the existing user behavior store. Protobuf is the underlying data model.
- Most popular queries were play counts of content over different time windows.
- First and last plays of a particular track within a time window were also common.
- Queried time ranges varied enormously, from a few days up to a user's entire listening history.
- Response sizes are highly varied: median close to tens of megabytes, but tail queries approach ~1 GB each.
- Heavy data skew means even a modest time range can return large data for very active listeners.
More detail
Dashboards showed Proto Event Response Size at P50 (single-digit to ~32 MiB range) versus P99 (hundreds of MiB), illustrating the incredibly varied memory profile of the workload.
- Memory management was critical since everything runs in memory; a major early bottleneck was converting data to Arrow.
- They reduced allocations and reuse Arrow buffers between queries, keeping pre-allocated buffers per schema (impressions, interactions, streams).
- Joins turned out to be important (e.g. joining catalog metadata to see top artists); an existing distributed join / metadata service was hooked directly into DuckDB in process.
- Joins reintroduced memory challenges via the right-hand-side data and its own allocations, requiring further tuning.
- Built mainly for agents, it also democratized data access for data scientists doing lightweight experiments without weeks of onboarding or building a materialized view.
More detail
Buffer reuse has a downside: a small buffer can be resized by one giant query, leaving a large wasted memory block for subsequent small queries. Reusing the mature distributed join service was possible precisely because everything runs in memory and in process.
- The recent DuckDB Java version bundled jemalloc, which had a major impact on memory footprint.
- Average memory usage for the faucet-sql-service dropped by nearly 40%.
- The before/after per-pod memory chart shows noticeably lower and smoother usage after the switch.
- Kian thanked DuckDB contributors for the real, measurable production impact of their work.
More detail
The chart '(faucet-sql-service) Memory Usage - Avg per pod' shows peaks up to the mid-50 GiB range before, dropping to a lower, calmer band afterward. Kian noted 'it was a great day to be on call.'
Key Takeaways
- Spotify added agentic, natural-language access to user listening history by exposing a SQL layer with DuckDB on top of its existing user behavior platform.
- DuckDB was chosen to build on top of existing infrastructure rather than re-architect, running in-process inside Java gRPC services.
- The data path is Protobuf to Apache Arrow vectors into DuckDB and back to Arrow, with a stateless, fully in-memory Kubernetes deployment.
- Workloads are extremely varied: time ranges from days to a lifetime, response sizes from tens of MB (P50) to ~1 GB (tail), amplified by heavy per-user data skew.
- Memory management was the dominant engineering challenge; Arrow buffer reuse and per-schema pre-allocation were key, and joins reintroduced memory pressure.
- Joins (e.g. enriching streams with catalog metadata) proved essential and were enabled by hooking an existing distributed join service into DuckDB in process.
- Beyond agents, the SQL layer democratized data access for data scientists, and bundling jemalloc in DuckDB Java cut average memory footprint by nearly 40%.