The Complex Data Models Behind Shopify's Tax Insights Feature

Summary

Shopify Tax’s Tax Insights feature provides merchants with state-level information about sales-tax liability and approaching liability, amid U.S. rules that vary by jurisdiction, product, threshold, and evaluation period. The implementation modified existing data models, added four new ones, and represented changing tax rules with lookup data containing valid_from and valid_to dates, including thresholds, date ranges, inclusions, exclusions, and inclusive or exclusive comparisons. Teams prototyped models in BigQuery SQL, implemented them in PySpark and Spark jobs, and combined merchant monthly taxable-sales aggregates with the rule model to evaluate economic nexus; the architecture moved source data through GCS and a warehouse into a final model. Each run publishes merchant-keyed insights to Google Cloud Bigtable, where the core application retrieves them for the frontend, while warehouse data supports analytics and other processes.

Context

U.S. sales-tax laws vary by state, city, product, threshold, order type, and evaluation period. Merchants need to know when they become liable or approach liability, while tax rules and their applicable dates can change over time.

Approach / What changed

The team consulted tax and cross-functional experts, planned intermediate data models, prototyped them in BigQuery SQL, and implemented them with PySpark and Spark jobs. A dated rule lookup and dimensional model captured evaluation periods, thresholds, order inclusion rules, and comparison semantics. Merchant sales aggregates were evaluated against those rules, and the resulting insights were published by merchant identifier to Google Cloud Bigtable for retrieval by the core application.

Takeaways

• Tax rules were modeled with valid_from and valid_to dates so changing thresholds, evaluation periods, and other requirements could be applied at the relevant date.
• The architecture used intermediate data models and separated merchant sales aggregation from rule evaluation, supporting debugging, testing, analytics, and reuse of warehouse data.
• A job publishes each merchant’s latest state-level liability insights to Google Cloud Bigtable, enabling the backend to fetch them for display in the store’s tax settings page.

Related reading