How to Track State with Type 2 Dimensional Models - Shopify

Tracking State in Analytical Data Models

Application databases are built for speed, not history. A typical users table stores the current state of each user's settings, updating fields in place whenever a change occurs. This is a Type 1 dimension—simple and performant, but it leaves analysts blind to the past.

As analysts, we often need to answer questions about historical state. How many users adopted a feature 90 days ago? What's the retention rate for a newly introduced setting? How often do users toggle a preference? To answer these, we need a Type 2 dimensional model that preserves every version of a record over time, with columns like valid_from, valid_to, and is_current.

Here's how to build such models using modern ETL tooling, based on our experience at Shopify.

Why Application Databases Fall Short

In an ideal world, the application's core data model would track full history itself, appending new records instead of updating in place. This gives you a fully trusted source of truth. However, this approach usually hits two walls:

1. Engineers resist analytical use cases. Application performance depends on compact, indexable tables. Keeping full history in the operational database works against that.
2. Features are often built on existing tables. Retrofitting history into a mature schema requires risky, expensive migrations.

For instance, when we added a language setting to the pre-existing users model to support Shopify admin translations, redesigning that table for history was out of the question.

Three Ways to Capture Change

Stitch Database Snapshots

Most companies already extract periodic snapshots of application tables into the warehouse. These snapshots can serve as inputs for building Type 2 dimensions, and dbt even provides a built-in solution for this approach. The caveat: updates that happen between extraction intervals are lost.

Add a Database Event Log

The most granular approach is to log every insert and update as an event, typically streaming it through something like Kafka. At Shopify, which is built on Ruby on Rails, we leveraged the after_commit Active Record callback to push a record to Kafka once it was successfully written to the application database. Streams like Kafka typically deliver "at-least-once", so you'll need to handle duplicates downstream.

This option isn't perfect—it demands engineering cooperation and introduces an extra moving part—but it's often the fastest path to reliable, fine-grained history.

Modify the Core Application Model

As discussed, this is the gold standard but rarely attainable after launch. You might advocate for it when building brand-new features, but expect resistance due to performance and migration costs.

Modelling the Event Stream

Whichever source you pick, the transformation to a Type 2 dimension follows the same pattern. Let's illustrate with a user_update event log, assuming you've instrumented it from day one. The log captures every creation and change to a users record:

From this stream, we want to produce a table where each row represents the period a user had a particular language active:

Notice that current records have a NULL valid_to, which you might fill with the job's last run timestamp in practice. Filter with WHERE is_current to get the latest state.

Recipe 1: PySpark

At Shopify, we use PySpark for data models that must scale to massive datasets. To experiment locally without installing Spark, pull a pre-built Docker image and run the code in a Jupyter notebook.

The transformation involves three steps:

• Filter to relevant events. Exclude updates to columns you don't track and eliminate duplicates from Kafka's at-least-once delivery.
• Derive the time boundaries. For each user, the start of a valid period is the event's timestamp; the end is the next relevant event's timestamp.
• Write the historical rows. One row per period per language, marking the latest as current.

The intermediate result after filtering will look like this:

Applying the final windowing logic produces the Type 2 dimension shown earlier.

Recipe 2: dbt

dbt solves a common pain—translating SQL logic into a PySpark API. It's a tool we're exploring at Shopify as a complement to PySpark. With dbt, the same transformation is expressed directly in SQL, making the logic more readable and testable.

Using dbt materializations and window functions, you can replicate the PySpark pipeline exactly and produce identical output dimensions. This approach is particularly attractive when your transformation logic is well understood and doesn't require Spark's distributed computing power.

Key Takeaways

Type 2 dimensional models are essential for answering time-based analytical questions. Start by adding event logging to your application database—it's the most flexible source. Then, transform that stream into a Type 2 dimension using either PySpark or dbt, depending on your batch size and tooling preferences. Both approaches yield the same structured, queryable history.

Pitfalls and Practical Lessons

Applying these patterns across several data models surfaced a few recurring issues worth planning for before you start.

Log After Commit, Not Before

Early implementations logged record changes before the transaction had committed to the database, which produced mismatches in downstream Type 2 models. The reliable pattern is to always log events from an after_commit callback. If logging happens anywhere else in the request lifecycle, the event can reference state that never actually persisted.

Application-Level Logging Is Fragile in Two Ways

First, event logging embedded in application code is vulnerable to future refactors. An engineer may remove the after_commit call while changing unrelated code, silently breaking the event stream. A CODEOWNERS file can at least notify you when those files are touched.

Second, you can miss updates that never go through the Rails model at all. If an external process modifies records directly in the database, no application code runs, and no event is emitted.

Kafka Is Not an Absolute Guarantee

It is possible to lose events in the Kafka pipeline. A Shopify server running the Ruby code could fail before emitting to Kafka, or Kafka itself could be down. These are rare, but the design should tolerate it. Two mitigations help:

• Run continuous data quality checks that compare the Type 2 model against the current state and flag discrepancies.
• When discrepancies appear, backfill the event log from a snapshot of current state.

Deletes Need an Explicit Strategy

Hard deletes are indistinguishable from create or update records in this logging layout, so they must be handled deliberately. Two viable options:

• Switch the table design to soft deletes so rows are never physically removed.
• Extend the Kafka schema with an event type field (create, update, or delete) and respond accordingly in the Type 2 logic.

A Better Source of Truth Ahead

Building Type 2 dimensional models for Shopify’s admin languages was iterative, requiring coordination between data and engineering teams. The analytical payoff justified the effort, but the approach still leans on application code and Kafka for an audit trail. A data engineering team at Shopify is working on a more robust alternative: storing MySQL binary logs (binlogs) in the data warehouse.

Binlogs are directly tied to the source database, so they are less susceptible to data loss than Kafka events. They also eliminate the need to instrument every model individually, since all table changes are tracked automatically regardless of which process made them. With binlogs as the extraction source, the hope is to produce Type 2 dimensions out of the box for all future models — no per-model event logging required.