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

This post introduces and details the implementation of double-entry transition tables as a data modeling technique for tracking state changes of entities over time. It explains the concept, its advantages (additive net_change column, identifying cause of change, preserving timing, scalability), and provides a step-by-step guide on building the pipelines using PySpark. The example focuses on tracking Shopify account and Shopify Balance account statuses, demonstrating how to query active accounts and net changes over time. This technique is applied to reliably count merchants using Shopify Balance historically.