BigQuery in the Right Lane: Five Checkpoints for AI Data Pipelines
A five-checkpoint workflow for keeping BigQuery in the right lane: land and shape data once, tune partitions and history windows, and federate instead of copying.
EtsyIsobel ScottExplainer
Etsy is experimenting with large language models (LLMs) to create anonymous buyer profiles from browsing and purchasing behavior, capturing interests such as styles, categories, and shopping missions. The pipeline combines recent searches, item views, purchases, favorites, session data, and listing information from internal feature-store and BigQuery sources, then prompts an LLM to generate structured profiles with explanations and confidence information. To scale processing, Etsy moved listing data to clustered and partitioned BigQuery tables, shortened input history from two years to nine months, increased batch sizes and compute, added managed concurrency, and used Airflow to stagger tasks. For 10 million users, these changes reduced generation time from 21 days to three and cut estimated cost per million users by 94%; experiments apply profiles to query rewriting and refinement pills.
Etsy is seeking more personalized search and discovery for nearly 90 million buyers and over 100 million listings because traditional search and recommendation systems may not capture nuanced interests, styles, aesthetics, categories, or shopping missions. The work remains experimental and is being developed with buyer privacy in mind.
The system retrieves recent searches, item views, purchases, favorites, session data, and listing information from internal feature-store and BigQuery sources, then prompts an LLM to generate structured buyer profiles. Scaling changes include clustered and partitioned BigQuery tables, a nine-month session window, larger batches, managed concurrency, increased compute, cost-oriented prompt adjustments, and Airflow scheduling with staggered user_id batching. Profiles are applied to query rewriting and refinement pills.
When behavioral data is insufficient to infer categorical interests, the relevant fields and confidence hashmap can remain empty; the profile still includes an explanation and observed interests.
Buyer profiles can enrich queries such as “cool posters” with predicted interests and expose high-confidence interests as clickable refinement pills that reformulate searches.
Cold-start experiments use user-based collaborative filtering to let new users inherit aspects of profiles from similar users based on limited signals such as brief sessions or initial searches.
Summaries are generated from the original by a language model. Check the original before relying on it.
See the idea
A five-checkpoint workflow for keeping BigQuery in the right lane: land and shape data once, tune partitions and history windows, and federate instead of copying.
Keep reading
The Good Stuff
Paid picks we use ourselves. Links carry a referral code — a subscription through one earns us a commission at no extra cost to you.
One useful email. Unsubscribe anytime.