Snowflake + dbt for Personalization: Modeling Features the Warehouse Way

TL;DR.

• Snowflake + dbt for personalization is a cold-path workbench, not a serving layer. The warehouse is where features are born, versioned, and tested. It is not where you answer a ranking call in <50 ms.
• The dbt model layout that survives contact with production has three layers: staging (1:1 with source), intermediate (joins + window functions for cohort, RFM, lifetime aggregates), and marts/features (one row per entity, contract-tested).
• Materialization is the lever that determines cost. Use incremental with merge for append-mostly event tables, incremental with delete+insert for late-arriving updates, and dbt snapshots for slowly-changing user state.
• Cluster by (user_id, event_date) on feature tables. Snowflake's micropartition pruning is the difference between scanning 12 GB and scanning 80 MB for a single user's lookback window.
• The warehouse does not serve personalization. Hand off to an online feature store via reverse ETL — Hightouch advertises a Personalization API targeting one million requests per second at 30 ms response time, which is roughly the latency the warehouse cannot hit.

Most personalization stacks start with a dbt project. That is the right instinct. The data engineers know dbt, the warehouse is where the transactional truth already lives, and there is a real cost to standing up a parallel feature platform before you have features worth platforming. The trouble starts when teams try to run serving out of the same warehouse — Snowflake is many things, but it is not a <50 ms key-value store. This post walks the dbt model patterns that produce useful personalization features, the materialization strategies that keep credits sane, and the boundary where you stop and hand off to an online store.

The cold path: where Snowflake + dbt for personalization actually fits

Personalization stacks split into two paths. The hot path is what answers the ranking call: vector store, online feature store, low-latency graph cache. The cold path is what computes the features in the first place: aggregations over the last 90 days of behavior, cohort assignments, lifetime value scoring, churn-risk signals.

Snowflake + dbt for personalization is exclusively a cold-path workbench. That framing is the most useful thing you can hold in your head. The warehouse handles batch and micro-batch feature engineering — typically hourly or daily — and exports the results to the system that actually serves. Conflating the two is how teams end up with a 3-second time-to-first-recommendation and a Snowflake bill that doubles every quarter.

A reasonable mental model: dbt models are the recipe, Snowflake is the kitchen, and the online store is the serving counter. The recipe is versioned, tested, and reviewed. The kitchen produces a fresh batch on a schedule. The serving counter holds what was made and hands it to the customer in milliseconds. If you are confused about how the pieces talk to each other, our reference architecture for real-time personalization walks the hot path end-to-end. This post stays in the kitchen.

The dbt model layout for personalization features

The model layout matters more than the SQL inside any individual model. We use a three-layer convention that mirrors the standard dbt project structure but is opinionated about where personalization features live.

1. staging/ — one model per source table, 1:1 column mapping with renaming, type casting, and minimal cleaning. No joins. Materialized as view by default.
2. intermediate/ — joins, window functions, cohort assignments, RFM scoring. This is where most of the actual feature logic lives. Materialized as ephemeral for cheap reuse or incremental if the upstream is large.
3. marts/features/ — one row per entity (user, session, item). Each model represents a feature group that will be exported. Materialized as incremental and contract-tested.

A typical marts/features/feat_user_behavioral.sql model holds 30 to 80 columns: page-view counts at 1-day, 7-day, 30-day, 90-day windows; category-level affinity scores; recency-frequency-monetary buckets; session-depth percentiles; conversion-event counts. Each column is documented in schema.yml with a description and a tests block (not_null, accepted_range, unique on the entity key).

The discipline that pays off: one row per user, one model per feature group, dbt contracts on the marts layer. dbt contracts enforce column names, types, and constraints at compile time, which means a downstream model or reverse-ETL pipeline cannot silently break when somebody renames a column. This is the dbt feature engineering pattern that separates a real personalization stack from a SQL pile.

RFM, cohorts, and lifetime patterns in dbt

The three most useful dbt personalization features for most products:

• RFM scoring — Recency, Frequency, Monetary value bucketed into quintiles. Compute with NTILE(5) OVER (ORDER BY ...) window functions in an intermediate model, join back to user grain in the mart. Recompute daily.
• Cohort assignment — bucket users by acquisition month, acquisition channel, first-product-category. A dim_user_cohort mart joined to behavioral feature marts gives you the slice-and-dice layer for any downstream model or dashboard.
• Lifetime patterns — day-of-week, hour-of-day, session-duration histograms aggregated over the full user history. These are the features that beat a global model on day-90 retention.

The dbt Developer Blog's Snowflake feature store walkthrough shows the same window-aggregation pattern (rolling counts over 1, 7, and 30 days) for transaction features — extend the pattern to behavioral events and you have most of a useful feature set.

Incremental materialization is the cost lever

The default materialized='view' is fine for staging. Past staging, it stops scaling. A view re-runs the underlying SQL every time it is queried, which means a downstream mart with five upstream views pays five full scans per query.

Switch to materialized='incremental' in the marts layer and the model only processes new or changed rows on each run. The dbt incremental models docs describe the pattern: first run builds the full table, subsequent runs use is_incremental() to filter source rows.

Three strategies, three use cases:

• merge (default on Snowflake) — works for append-mostly event streams where occasional late-arriving rows need to update existing keys. Use this for feat_user_behavioral and similar.
• delete+insert — use when a window of recent data needs to be fully recomputed (for example, the last 7 days of session-level features because session boundaries can shift). Cheaper than merge for moderate window deletes.
• append — only when rows are immutable and deduplication happens downstream. Rare in personalization, useful for raw event ingest.

A concrete example for a daily-refresh user-behavioral mart:

{{ config(
    materialized='incremental',
    unique_key='user_id',
    incremental_strategy='merge',
    cluster_by=['user_id', 'feature_date']
) }}

select
  user_id,
  current_date as feature_date,
  count(*) as pageviews_1d,
  count(distinct session_id) as sessions_1d,
  ...
from {{ ref('int_user_events_recent') }}
{% if is_incremental() %}
  where event_ts > (select max(feature_date) from {{ this }}) - interval '1 day'
{% endif %}
group by user_id

This pattern — incremental merge + cluster-by + a guarded is_incremental() predicate — is the dbt personalization features baseline. Once it is in place, your marginal compute cost is roughly linear in new-event volume rather than user-count squared.

Snapshots for slowly-changing user state

Behavioral features change every hour. User state changes occasionally — subscription tier, marketing-consent flags, primary device, lifecycle stage. You want both: the current state for serving, and the full history for backtesting models and explaining decisions.

dbt snapshots solve this with Type-2 SCD semantics. The dbt snapshots documentation describes two strategies — timestamp (recommended, uses an updated_at column) and check (compares listed columns). Snapshots add four metadata columns: dbt_valid_from, dbt_valid_to, dbt_scd_id, and dbt_updated_at.

A practical setup for personalization:

• snapshots/snap_user_profile.sql — captures changes to subscription tier, marketing consent, locale, primary device, churn risk bucket. Strategy = timestamp, key = user_id, updated_at = profile_updated_at.
• A mart dim_user_current selects where dbt_valid_to is null for the serving view.
• Backtesting models join behavioral events to snap_user_profile on event_ts between dbt_valid_from and coalesce(dbt_valid_to, current_timestamp) to get the state-as-of-event.

That last pattern — state-as-of-event joins — is what makes explainability tractable later. When a recommendation looks weird, you want to know what the user's profile looked like at the moment the recommendation was made, not what it looks like now. We unpack this in explainable recommendations and the path of a recommendation.

The cost reality: cluster-by, micropartition pruning, and what credits buy

Snowflake stores data in immutable micropartitions of roughly 50-500 MB. Every query scans some number of those micropartitions. The job of clustering is to keep the partitions that matter for a given query physically close, so the optimizer can prune the rest.

For personalization feature tables, the right clustering key is almost always (user_id, event_date) or (user_id, feature_date). The two reasons:

1. Most downstream queries filter by user (online store backfills) or by date range (training set generation).
2. The user-id cardinality is high enough that clustering provides real selectivity.

Without clustering, a query for one user's 90-day feature history might scan every micropartition in the table. With clustering, it scans the dozen that hold that user's data. This is the difference between a query that costs nothing and a query that doubles the monthly bill.

Practical guidance:

• Set cluster_by in the dbt config() block on every feature mart larger than ~10 GB.
• Use system$clustering_information() in a dbt test to monitor cluster-depth ratios. A ratio above 5 means it is time to re-cluster.
• Use Snowflake's WAREHOUSE_METERING_HISTORY to attribute credits to dbt models. Tag your dbt-Cloud jobs with query_tag so the meter shows which mart is expensive.
• Consider a separate WH_DBT_PERSONALIZATION warehouse so personalization workloads don't contend with BI workloads. Cost attribution becomes trivial.

A useful rule of thumb: a well-modeled personalization dbt project for a 5 to 10 million user product should cost low four-figure dollars per month in Snowflake credits at daily refresh cadence. If your number is much higher, something is wrong — usually a missing cluster-by, a full-table-rebuild incremental, or a view that should be a table.

Reverse ETL: the handoff to the online feature store

The warehouse does not serve. The dbt models you just built feed an online store via reverse ETL. This is the boundary where Snowflake stops being the answer.

Hightouch is the most common reverse-ETL choice in the dbt ecosystem. Their Personalization API blog describes the pattern: dbt models are cached in a distributed low-latency database that updates after each dbt run, and queried over HTTP. They cite a target of one million requests per second at 30 ms response time — which is roughly the operating envelope you need for a personalization serving call.

The architecture, end-to-end:

1. dbt builds marts/features/feat_user_* on Snowflake on a daily or hourly schedule.
2. A reverse-ETL job (Hightouch, Census, or a custom one) reads the changed rows and syncs them to an online store — Redis, DynamoDB, a vector DB, or a managed feature store like Snowflake Feature Store + an external cache.
3. The application calls the online store at request time. Latency budget: low tens of milliseconds.
4. Decisions are logged back to the warehouse for the next training cycle.

If you skip step 2 and have your application query Snowflake directly, you have a <2 s time-to-first-result and a furious head of engineering. We covered the broader architectural picture in the marketing engineer's personalization stack, and the trade-offs of warehouse-first versus graph-first stores in knowledge graphs vs vector embeddings.

What does not belong in dbt

For completeness, the features that should not live in your dbt project:

• Real-time event scoring — anything that needs to update on a per-event basis (last-page-viewed, current-session-cart-value). Compute these in a stream processor (Flink, Materialize, Tinybird) and write to the online store directly.
• Embeddings — vectorization is a model concern, not a SQL concern. Generate embeddings in Python, write them to a vector store, and reference them by entity ID from dbt-managed features.
• Graph traversals — neighbor-of-neighbor lookups against a knowledge graph are not what Snowflake is for. Use a graph database (or our engine) for the relationship structure and let dbt own the tabular aggregates.

The simple test: if a feature requires sub-second freshness, it does not belong in a dbt mart. dbt is for the things that change every hour or every day, not every keystroke.

How ×marble fits in

We build ×marble for teams that have done the dbt + Snowflake work and hit the wall where tabular features stop being enough. The dbt models we just described give you a clean cold-path workbench — once you need relationship reasoning across users, items, and contexts, you need a graph layer over the top. ×marble plugs into the online side of the architecture: feature marts come out of dbt, an event stream feeds the knowledge graph, and the graph serves the ranking call at the latency your application requires. If you are running Vivo for daily AI video briefings or Video for personalized YouTube discovery, the dbt patterns in this post are roughly what powers the cold path under the hood — except the graph layer replaces the more expensive bits of the joins.

FAQ

How do I use Snowflake and dbt together for personalization?

Treat Snowflake + dbt as the cold-path feature workbench: dbt models compute behavioral aggregates, RFM scores, cohort assignments, and lifetime patterns on a schedule, materialized as incremental tables clustered by (user_id, date). The warehouse does not serve personalization at request time. Use reverse ETL (Hightouch, Census) to sync the dbt feature marts to an online store, and call the online store from your application.

What is the best dbt materialization for feature engineering?

For personalization feature marts, use materialized='incremental' with incremental_strategy='merge' and a unique_key on the entity (usually user_id). For slowly-changing user state — subscription tier, lifecycle stage, consent flags — use dbt snapshots with strategy='timestamp'. Use view only at the staging layer, and ephemeral for intermediate models that are referenced exactly once.

How do I keep Snowflake costs under control for dbt personalization features?

Cluster every feature mart by (user_id, event_date) so Snowflake's micropartition pruning can avoid full scans, monitor system$clustering_information() to catch drift, run dbt on a dedicated warehouse so credits are attributable, and avoid full-table-rebuild incrementals by guarding is_incremental() predicates carefully. A well-tuned dbt personalization project for a 5 to 10 million user product is typically low four-figure dollars per month at daily refresh.

Can dbt power real-time personalization?

No. dbt is a batch and micro-batch transformation tool — the fastest useful refresh cadence is hourly. Real-time personalization (sub-second feature updates) requires a stream processor writing directly to an online feature store. Use dbt for the cold path (daily aggregates, cohorts, lifetime features) and a streaming layer for the hot path (current session, last event, real-time intent). The two paths share an entity ID and meet in the online store.

When should I use dbt snapshots versus incremental models for user data?

Use incremental models for high-cardinality, frequently-changing data where you only need the current value — page views, sessions, recent purchases. Use snapshots for low-cardinality, slowly-changing user state where the history matters for backtesting and explainability — subscription tier, lifecycle stage, profile attributes. Snapshots add dbt_valid_from and dbt_valid_to columns that make state-as-of-event joins tractable.

Further reading

• Reference architecture for real-time personalization — the hot path that consumes the dbt feature marts described here.
• The marketing engineer's personalization stack — where dbt sits in the broader tooling chain.
• Hyper-personalization explained for engineers — what the dbt features actually power downstream.
• Recommendation engine vs personalization layer — why the feature warehouse is not the recommendation system.
• dbt Developer Blog: Snowflake feature store and dbt — the official dbt walkthrough for the feature-store integration.
• dbt incremental models documentation — strategy reference for merge, delete+insert, and append.
• Hightouch: power personalization with dbt models — the canonical reverse-ETL handoff pattern.