The Problem: Counting Merchants on Shopify Balance Over Time

After the beta launch of Shopify Balance, Shopify's data team needed a reliable answer to a deceptively simple question: how many merchants are using the product, and how has that number changed historically? A merchant counts as a Balance user only if they hold both an active Shopify Balance account and an active Shopify account. Tracking that composite state over time requires monitoring changes to two separate account statuses simultaneously.

To solve this, the team built a data pipeline around double entry transition tables, a presentation format that records every attribute change as two rows instead of one. The approach trades upfront complexity for long-term simplicity: it keeps all timing information intact, exposes which underlying attribute triggered each composite state change, and—thanks to an additive net_change column—keeps query code short even as the tracked product grows more complex.

How Double Entry Transition Tables Work

A standard transition table records one row per state change, with columns for the previous and new states. A double entry transition table instead emits two rows for each change, distinguished by a signed net_change value:

  • net_change = -1: the row representing the previous state.
  • net_change = +1: the row representing the new state.

That additive column is the core benefit. To count how many entities are in a given state right now, you sum net_change filtered to that state. Beyond simple counts, the format provides several other advantages:

  • Change attribution: when an overall status depends on multiple underlying flags, you can look at a row and identify exactly which attribute flipped.
  • Full temporal fidelity: all transition timestamps are preserved, with a secondary index used to correctly order events that share an identical timestamp—useful for computing state durations.
  • Extensibility: because all downstream queries rely on the additive net_change, adding new tracked attributes later requires no rewrites of existing SQL or PySpark.

Building the Attribute Inputs

The pipeline starts with per-attribute input tables. For Shopify Balance, the team created two: one tracking the status of the Shopify Balance account, the other tracking the Shopify account status. Each attribute table must contain three kinds of columns:

  • A common partition key—here, account_id.
  • A sort key, typically a transition_at timestamp plus an index.
  • The attribute value being tracked.

The index column is critical. Generated via a row-number window function ordered by transition_id, it breaks ties when a single account_id has multiple transitions with the same timestamp. This guarantees every row has a unique account_id, transition_at, index triple, ensuring events are always processed in the correct order later in the merge step.

Our Shopify Balance status attribute table showing a merchant that joined and left Shopify Balance.

Merging Attributes Into a Single Stream

Once the individual attribute tables are ready, they are fed into a build process the team refers to as "build merge state transitions." This job performs two steps.

First, it generates a combined set of unique rows keyed by account_id and sorted by transition_at and index. It creates one column per attribute, populating each from its source table in the sort-key order. Where an attribute has no event at a given row, the pipeline forward-fills from the most recent known value for that attribute.

Two example attributes merged into a single output table.

Second, a downstream process converts this merged stream into double entry format. It assigns net_change = +1 to every existing row—the "current" state—and inserts a duplicate row with net_change = -1 for each transition's prior state. The output is the accounts_transition_facts table, which records both the Shopify account status and the Balance account status over time in a single composite view.

A merchant that joined and left Shopify Balance in our accounts_transition_facts double entry transition table.

For example, the table might show a merchant's shopify_status flipping from inactive to active in 2018, then the balance_status moving from not_on_balance to active on March 14, 2021, and later from active to inactive on April 23, 2021.

Querying With the Additive Column

The payoff comes at query time. To count all merchants currently active on both Shopify and Balance, you filter on the two status columns and sum net_change. Grouping the sum by date yields the net change in qualified accounts per day. Because the column is additive, this same pattern extends to any future attributes added to the table without modifying existing queries.

Other jobs can also consume the table directly. The team uses the daily net-change output to update an aggregate snapshot table for Shopify Balance metrics, all via concise PySpark that leverages the same net_change-summing pattern.

The initial construction of accounts_transition_facts took considerable effort, but it has since made the central question trivial to answer. The team plans to extend the table with additional statuses that change over time as Shopify Balance evolves. The key takeaway is generalizable: when you need robust historical counts of entities whose state depends on multiple changing attributes, double entry transition tables are a dependable, scalable pattern.