← Back to all articlesDATA ENGINEERING

Snowplow on Snowflake: A Medallion Architecture for Behavioural Data

What Snowplow actually does, and how to lay it out on Snowflake from raw events to a real-time lane and a Gold star schema.

Nitin Jain
·November 19, 2026·10 min read
Snowplow on Snowflake: A Medallion Architecture for Behavioural Data

Most teams meet behavioural data through a vendor dashboard. You get page views, sessions and a bounce rate, and you take the numbers on trust. The day you want to join those clicks to subscriptions, content metadata or ad revenue, you find out the raw data is hard to get, sampled, or shaped by someone else’s definitions.

Snowplow takes the opposite approach. It collects every event, validates it against a schema you own, enriches it and lands it in your own warehouse. What you do with it from there is up to you. That freedom is the point, and it is also the risk: without a clear layout, a Snowplow warehouse turns into one huge table that every team queries in its own way.

This is the first post in a four-part series based on a recent project for a large digital publisher with several news brands, web and mobile apps. Here I cover what Snowplow does end to end, and the Snowflake layout we use for it: a medallion design with a real-time lane. All numbers below are illustrative sample data.

What Snowplow does, end to end

Snowplow is a pipeline, not a dashboard. It has five moving parts, and it helps to know what each one is responsible for before you design anything downstream.

ComponentWhat it doesWhat to watch
TrackersSDKs for web (JavaScript), iOS, Android and server-side languages. They build events and send them over HTTP.Tracker config decides what you can ever analyse. Auto-tracking can record things you did not mean to track.
CollectorA lightweight HTTP endpoint that accepts events and writes them to a stream (Kinesis, Pub/Sub or Kafka) as fast as possible.It does no validation. It just has to stay up.
EnrichValidates each event against its schema, then runs enrichments: IP to geo lookup, user-agent parsing, campaign and referrer parsing, IAB bot detection and more.Events that fail validation go to a separate failed events stream, not into your table. Monitor it.
IgluThe schema registry. Every custom event and entity has a versioned JSON Schema, for example iglu:com.acme/article/jsonschema/1-0-0.Schemas are your data contract. Version them like code.
LoaderWrites enriched events into the warehouse. On Snowflake, the Snowflake Streaming Loader uses Snowpipe Streaming, so events arrive within seconds to a few minutes.New schemas create new columns automatically. Plan for wide tables.

The raw events table, entities and self-describing events

Everything lands in one wide table, usually atomic.events. Each row is one event: a page view, a page ping, a screen view, a link click, a custom event. The table has well over a hundred standard columns that every event shares, such as event_id, app_id, platform, event_name, collector_tstamp, derived_tstamp, domain_userid, domain_sessionid, user_id, page_url and the geo and user-agent fields filled by enrichment.

Two concepts carry everything else:

  • Self-describing events are your own event types, such as article_shared or paywall_shown. Each has an Iglu schema. On Snowflake they load into an OBJECT column named after the schema, for example unstruct_event_com_acme_paywall_shown_1.
  • Entities (also called contexts) are extra objects attached to an event to describe the world around it: the article, the user, the device, the web page. One event can carry many entities. They load into ARRAY columns such as contexts_com_acme_article_1, one column per schema major version.

Because the values sit inside VARIANT, every field must be cast when you read it, for example contexts_com_acme_article_1[0]:articleId::varchar. That is fine for one analyst. It is not fine for fifty dashboards, each with its own casting, null handling and dedupe logic. This is the main reason the raw table should never be the thing people query.

Snowplow gives you clean, typed, schema-checked events. It does not give you a data model. Designing the layers on top is the real work.

Why medallion, and why a real-time lane

A medallion layout (Bronze, Silver, Gold) is not a fashion choice here. Behavioural data has three needs that pull against each other:

  1. Replay. You will change your modelling logic. When you do, you need the raw events to rebuild from. That is Bronze.
  2. Freshness. Some users care about the last five minutes, not last week. A newsroom watching a breaking story, a live ops team watching an app release. They need clean data fast, without waiting for a batch model.
  3. Consistency. Everyone else needs one definition of a session, a view and an engaged minute, joined to the business. That is Silver and Gold.

A plain batch medallion serves needs one and three but fails freshness. A pure streaming design serves freshness but makes sessions and history painful. The answer we use is a medallion with one extra, thin layer near the bottom: a real-time base silver that cleans events within minutes and can be read directly, while the heavier Silver and Gold layers run incrementally on their own schedule.

LayerWhat lives thereRefreshWho uses it
BronzeRaw atomic.events exactly as loaded, duplicates and bots includedContinuous (loader)Data engineers, for replay and audit
Base silverSame columns, deduplicated by event_id, bots removed, key entity fields flattened1 to 5 minutesReal-time dashboards, live ops, the Silver models
SilverSnowplow dbt package output: snowplow_unified_views, snowplow_unified_sessions, snowplow_unified_usersIncremental, e.g. hourlyAnalytics engineers
GoldStar schema: fct_page_view, fct_session, dim_content, dim_device, dim_geo, dim_applicationIncremental, after SilverAnalysts, data scientists
ServingAggregates and semantic views with business namesAfter GoldBusiness users, BI tools, AI assistants

Bronze: keep it raw, keep it short

Bronze is the loader’s target table and nothing else. Do not add columns, do not delete rows, do not let dashboards point at it. Its only jobs are to receive events and to make replay possible.

Two decisions matter. First, retention. Raw events are the bulkiest data you own. Once Base silver and Silver are trusted, many teams keep only a short window of Bronze in the hot table (for example 7 to 14 days) and archive older raw data to cheaper storage, because rebuilds normally start from Base silver. Choose this on purpose, with your rebuild plan written down. Second, clustering. Most reads filter on time, so cluster or partition on collector_tstamp or load_tstamp, the same column your downstream models use for incremental windows.

Base silver: the real-time lane

Snowplow delivers events at least once, so duplicates of the same event_id do happen. Bots and crawlers also generate traffic that the IAB enrichment can flag. Base silver removes both, a minute or so after the event lands, and nothing more. Keep the logic small: the less it does, the cheaper and more reliable a 1-minute refresh is.

sql
-- Base silver sketch on Snowflake (illustrative names) create or replace dynamic table analytics.base_silver.events target_lag = '1 minute' warehouse = transform_rt_wh as select e.*, e.contexts_com_acme_article_1[0]:articleId::varchar as article_id from analytics.bronze.events e where coalesce( e.contexts_com_iab_snowplow_spiders_and_robots_1[0]:spiderOrRobot::boolean, false ) = false qualify row_number() over ( partition by e.event_id order by e.collector_tstamp ) = 1;

Keeping the earliest collector_tstamp per event_id matches what the Snowplow dbt packages do themselves, so the real-time numbers and the Silver numbers agree. A newsroom or live ops dashboard can read this table directly, filtered to the last hour. Whether you build it as a dynamic table or with streams and tasks, and what a 1-minute versus 5-minute lag costs, is the subject of part four of this series.

Silver: let the Snowplow dbt packages do the hard part

Sessionisation, engaged time from page pings, user stitching and safe incremental processing are hard to write well. Snowplow publishes open-source dbt packages that do this, and we use them rather than rebuild them. The unified package covers web and mobile in one set of tables: snowplow_unified_views, snowplow_unified_sessions, snowplow_unified_users and snowplow_unified_user_mapping. Separate packages cover media playback, ecommerce and attribution.

The packages keep an incremental manifest, so each run only processes new events plus a lookback window. Point the package at Base silver rather than Bronze, so bot traffic never enters a session.

yaml
# dbt_project.yml (illustrative) vars: snowplow_unified: snowplow__atomic_schema: base_silver snowplow__events_table: events snowplow__start_date: '2026-01-01' snowplow__enable_web: true snowplow__enable_mobile: true # Leave empty to process every app_id. # Filter test and dev apps downstream instead. snowplow__app_id: []

That last variable deserves a warning. The package only processes the app IDs in snowplow__app_id. On one project, a short allow-list left over from an early pilot meant whole websites and Android apps were silently missing from Silver and everything downstream, while Bronze had them all. Our rule now: keep the list empty, flag test apps in dim_application, and reconcile event counts per app_id between Base silver and Silver every day.

Also know what the package does not model. The unified package builds views and sessions from page views, page pings, screen views and screen ends. Link clicks, app installs and your own custom events mostly survive only as counts on the session. If the business needs them as rows, you model them yourself. Part two goes through the packages in detail.

Gold: a star schema the business can read

Silver tables are wide and Snowplow-shaped. Gold reshapes them into facts and dimensions that match how the business talks: fct_page_view and fct_session at the centre, with dim_content, dim_page, dim_device, dim_geo, dim_application, dim_traffic_source and dim_person around them. Facts hold integer keys, IDs, timestamps and measures. Descriptive columns live in dimensions.

Gold is also where Snowplow data meets everything else: the CMS for article metadata, the subscription system for paying users, the enterprise date table you already own. A few principles we hold to:

  • Reuse the enterprise date dimension. Do not build a second calendar just for clickstream.
  • No hardcoded reference lists. Brand per app_id or region per country belongs in a reference table or a dbt seed owned by the right team, not in a case statement. Better still, have the tracker send the brand on every event as an entity.
  • Plan for late-arriving content. A view often arrives before the CMS knows about the article. Insert a placeholder dimension row from the event, then update it in place when the CMS row lands. The key never changes.
  • Unknown is a value. Use reserved keys such as -1 Unknown and -2 Not applicable so facts never carry null keys.

Part three covers surrogate keys, slowly changing dimensions and the clean-URL page dimension in depth.

Aggregates and serving views

The top layer exists so most people never write a join. Build a few aggregates that match real questions (daily views and engaged minutes by brand, content and platform; weekly active users by application) and expose them through semantic views with business names and descriptions. This is the layer BI tools and AI assistants should read. It is small, fast and cheap to query, and it carries one agreed definition of each metric.

sql
-- Serving view sketch (illustrative) create or replace view analytics.serving.daily_content_engagement as select d.calendar_date, a.brand_name, a.platform, c.section_name, count(*) as page_views, count(distinct f.session_key) as sessions, round(sum(f.engaged_time_seconds) / 60) as engaged_minutes from analytics.gold.fct_page_view f join analytics.gold.dim_date d on f.date_key = d.date_key join analytics.gold.dim_application a on f.application_key = a.application_key join analytics.gold.dim_content c on f.content_key = c.content_key where a.is_test_app = false group by 1, 2, 3, 4;

What this looks like in practice

With sample numbers: say a mid-sized publisher collects around 50 million events a day. Bronze grows by that much, plus a small share of duplicates. Base silver refreshes every minute and processes about a minute of data in a few seconds on a small warehouse. Silver runs hourly and turns those events into a few million views and sessions. Gold and the aggregates run after Silver. The live dashboard reads Base silver; the weekly editorial report reads a serving view; a data scientist building a churn model reads Gold. All three see the same deduplicated, bot-free events.

Before you commit to a refresh cadence, benchmark it. Build the Base silver table in parallel, run it for a few weeks at the lag you want, and look at credits per day against the value of fresher numbers. It is common to find that 5 minutes costs a fraction of 1 minute and nobody can tell the difference.

Checklist before you build

  • Agree the tracking plan and Iglu schemas first. The warehouse cannot model what the tracker never sent.
  • Turn on IAB bot detection and user-agent enrichment in Enrich.
  • Monitor failed events. A broken schema version silently drops data from your table.
  • Point dashboards at Base silver or serving views, never at Bronze.
  • Leave snowplow__app_id empty unless you have a strong reason, and reconcile counts per app between layers.
  • Write down who owns each layer, its refresh target and its retention.

Planning a Snowplow build on Snowflake?

We help teams design behavioural data platforms that are fast where it matters and consistent everywhere else: tracking plans, real-time layers, dbt package setup and Gold star schemas. Talk to us before the first event lands.

Frequently Asked Questions

Does Snowplow replace my web analytics tool?

It replaces the collection layer and gives you the raw, event-level data in your own warehouse. Dashboards, metric definitions and reports are yours to build on top, usually from Gold and serving views.

Why not let dashboards read the raw events table directly?

The raw table contains duplicates and bot traffic, and custom fields sit inside VARIANT columns that must be cast. Every dashboard would reimplement dedupe, bot filtering and casting differently, and the numbers would disagree.

Do I need the real-time base silver layer if I already run the dbt packages?

Only if someone needs numbers fresher than your Silver schedule. The packages run incrementally in batches. If a live dashboard needs the last few minutes, a thin deduplicated and bot-filtered layer is cheaper and simpler than running the full packages every minute.

How long should I keep Bronze data?

Long enough to rebuild from it safely. Many teams keep a short hot window in the raw table and archive older raw events to cheaper storage once downstream layers are trusted. Decide it together with your rebuild plan.

Which Snowplow dbt package should I start with?

For web and mobile apps, the unified package. It produces views, sessions, users and user mapping in one set of tables. Add the media player, ecommerce or attribution packages only if your trackers send those events.

Need help building your data platform?

At CData Consulting, we design, build, and operate modern data infrastructure for companies across North America. Whether you are planning a migration, optimizing costs, or building from scratch — let's talk.

Get data engineering insights delivered to your inbox

Join our newsletter for weekly insights on Snowflake, data architecture, and modern analytics.