Problem
Yelp's Clickstream Analytics team owns the Sessions Data Mart. Its central sessions_table holds one row per session and has numerous downstream dependencies. When the team changes the model code, the underlying tables are backfilled and the new data must be checked before users hear about it. The team did this with manual SQL queries. They were hard to track, tedious to rerun in Redshift and in S3 + Athena, and hard to share with product and analyst colleagues.
Example from the article: adding a product_name column to sessions_table, a model built from 16 left joins, so users can separate business-product sessions from consumer-product sessions.
Solution in brief
Turn the ad-hoc validation queries into dbt generic tests and tag them by the stage at which they run: development, production backfill, or the daily batch. Each stage gates the next one, and failed rows are stored for debugging and sharing.
Questions this case answers
- How to validate a large backfilled event-derived table in stages before exposing it to downstream consumers
- How to keep data-quality queries versioned, parameterized and runnable across several data stores
- How to validate a backfilled sessions table before announcing it to users?
- How to keep validation queries versioned and rerunnable across Redshift and S3 + Athena?
Tools and methods
- dbt generic tests — reusable, parameterized test macros written in Jinja and attached to a model in its config file.
- dbt tags — group tests into validation stages and run each group on its own.
- store_failures — persists the rows of failed tests in the data store where the test ran.
- dbt-expectations — package of ready-made generic tests for common checks.
Google or a one-time email code. No password.