Using a standard transition table, we can convert it to an attribute with a simple PySpark job: a sort key, generally a transition_at timestamp and an index.a partition key that’s common across attribute, which in our case is an account_id.An attribute input table must have a specific set of columns: In our case, we created individual attribute tables for both the Shopify Balance account status and the Shopify account status. We need at least one attribute, but it can scale to any number of attributes as the product we’re tracking grows. But how do we do this? Building Our Double Entry Transition Pipelinesįirst, we need to prepare individual attribute tables to be used as inputs for our double entry transition data infrastructure. It also gives us a clean way to query the status of each entity over time.
The bonus is that you don’t have to rewrite any existing SQL or PySpark, all thanks to the additive nature of the net_change column.įor our purpose of identifying how many merchants are using Shopify Balance, double entry transition tables allow us to track state changes for both the Shopify Balance account and the Shopify account in a single table.
Easily scaled with additional attributes: if the downstream dependencies are written correctly, you can add additional attributes to your table as the product you’re tracking grows in complexity. This is helpful for situations where you need to know something like the duration of a given status. Preserving all timing information: the output preserves all timing information, and even correctly orders transitions that have identical timestamps. Identifying cause of change: for situations where you care about an overall status (one that depends on several underlying statuses), you can go into the table and see which of the individual attributes caused the change. This allows you to quickly get the number of entities that are in a certain state by summing up net_change while filtering for the state you care about. The net_change column is additive: this is the true benefit of using this type of table. net_change = +1: this row is the new stateĭouble entry transition tables have many advantages including:. net_change = -1: this row is the previous state. It took me a long time to wrap my head around this net_change column, but it essentially works like this: if you want to track the status of something over time, every time the status changes from one state to another or vice versa, there will be two entries: They can also combine many individual tracked attributes into a single output. In comparison to a standard transition table that has from and to columns, double entry transition tables output two rows for each state change, along with a new net_change column. At Shopify, one of our first use cases of a double entry transition table was used to track the state of merchants using the platform, allowing us to report on how many merchants have active accounts. What Are Double Entry Transition Tables?ĭouble entry transition tables are essentially a data presentation format that tracks changes in attributes of entities over time. In this post, we’ll explore how we built a data pipeline using double entry transition tables to answer our question: how many Shopify merchants are using Shopify Balance? We’ll go over how we designed something that scales as our product grows in complexity, the benefits of using double entry transition tables-from ease of use to future proofing our reporting-and some sample queries using our new table. It does all of this while simplifying our queries and reducing long term maintenance on our reporting. While very much an “invest up front and save a ton of time in the long run” strategy, double entry transition tables give us the flexibility to see the individual inputs that cause a given change. 
This means we needed to build something to track the state changes of both accounts simultaneously, and make that tracking robust and reliable over time. To be considered as using Shopify Balance, a merchant has to have both an active Shopify Balance account and an active Shopify account. It’s also more complicated than it seems to answer. While this sounds like a simple question, it’s foundationally critical to knowing if our product is a success and if merchants are actually using it. After the beta launch of Shopify Balance, the Shopify Data team was brought in to answer the question: how do we reliably count the number of merchants using Balance? In particular, how do we count this historically? Recently we launched Shopify Balance, a money management account and card that gives Shopify merchants quick access to their funds with no fees.
0 kommentar(er)
