---
title: "Double Entry Transition Tables: How We Track State Changes At Shopify"
description: "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."
---

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

[Shopify](https://yomu.fyi/company/shopify) · 2023-10-18 · May 18, 2022

**Type:** Explainer

## 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.

**Tags:** [Architecture](https://yomu.fyi/topic/architecture), [Data Pipelines](https://yomu.fyi/topic/data-pipelines), [Python](https://yomu.fyi/topic/python), [Scalability](https://yomu.fyi/topic/scalability)

- Source: [Shopify](https://shopify.engineering/double-entry-transition-tables-shopify)
- Source URL: https://shopify.engineering/double-entry-transition-tables-shopify
- Ingested by Yomu: 2026-08-30T15:26:25.012Z

[Read original post](https://shopify.engineering/double-entry-transition-tables-shopify)