Double Entry Transition Tables: How We Track State Changes At Shopify

Summary

Shopify needed a reliable way to count merchants using Shopify Balance over time, where usage requires both an active Shopify Balance account and an active Shopify account. The data team built accounts_transition_facts with double entry transition tables, a format that represents each state change as a -1 row for the previous state and a +1 row for the new state. Individual attribute tables use account_id, transition_at, and an index that breaks timestamp ties; the pipeline merges attributes, carries forward missing values, and generates net_change rows. Because net_change is additive, queries can sum it while filtering statuses, group results by date, or feed daily aggregates, while additional attributes can be added without rewriting existing SQL or PySpark. The initial build required substantial effort but produced a reporting structure intended to scale with Shopify Balance’s growing complexity.

Context

After Shopify Balance’s beta launch, the Shopify Data team needed to reliably count merchants using Balance, including historical counts. A merchant qualifies only when both the Shopify Balance account and the Shopify account are active, so the state changes of both accounts had to be tracked together over time.

Approach / What changed

The team created individual attribute tables for Shopify Balance and Shopify account status, keyed by account_id and ordered by transition_at plus an index for duplicate timestamps. A PySpark pipeline merges the attributes, fills missing values with the previous known state or defaults, and creates a double entry transition table with paired -1 and +1 rows in net_change. Queries and downstream jobs sum net_change to calculate current or daily account counts.

Takeaways

• Each state change is represented by a -1 row for the previous state and a +1 row for the new state, making net_change additive for status counts.
• An index ordered by transition_id resolves duplicate account_id and transition_at combinations, preserving a deterministic event order.
• Additional tracked attributes can be added to the transition table without rewriting existing SQL or PySpark that uses the additive net_change column.

Related reading