Tax Insights: Making Dynamic Tax Laws Computable
US sales tax law is notoriously fragmented. Following the 2018 Supreme Court ruling that removed the physical-presence requirement, most states now obligate out-of-state sellers to collect tax once they cross certain economic thresholds. Those thresholds vary not just by dollar amount, but also by order count, product type, and evaluation window—some states use a calendar year, others a rolling 12-month period, and New York looks back four complete tax quarters.
Shopify Tax surfaces personalized, state-level liability guidance directly on a merchant's admin page. Behind that uncomplicated interface sits a substantial data engineering effort: the team had to modify several existing data models, create four new ones, and design the whole pipeline to handle tax laws that shift over time. The data work is what makes it possible to answer a question like "Am I approaching nexus in a specific state?" on an ongoing, automated basis.
The Shape of the System
The core application databases are copied into the data warehouse in Google Cloud Storage (GCS). From there, Spark jobs process the raw tables into a series of intermediate and final data models. The final model—the one containing the per-merchant liability snapshots—is loaded into Google Cloud Bigtable, where the core application can query it and render results in the UI.
That same warehouse data is available to the analytics platform and to other business processes. Keeping the data in GCS after modeling means the insights aren't trapped behind an API; they can power machine-learning projects, reporting, or additional features without a second pipeline.
Requirements Gathering: More Than Tax Law
Before writing code, the team had to become fluent in US tax code—or at least fluent enough to model it. That meant consulting Shopify's internal tax experts, alongside stakeholders from product, engineering, UX, and marketing. The list of questions to settle went well beyond tax specifics:
- How does this feature reduce friction for merchants?
- Where in Shopify admin do merchants interact with tax settings, and how often?
- Which kinds of orders count toward liability thresholds, and which are excluded?
- What does the adoption funnel look like, and how will the feature be announcedand tracked?
- What metrics define success, and what reporting needs to be built?
Most of these questions apply to any product launch, not just tax. Treating them as a prerequisite—rather than jumping into schema design—kept the engineering effort aligned with a clearly-defined merchant outcome.
Prototype in SQL Before Committing to Spark
With multiple complex models on the drawing board, the team's first move was to enumerate every dataset they'd likely need and set a rough timeline. Breaking the work into intermediate datasets pays off in a few ways: it's easier to reuse the pieces separately for analytics, easier to debug when something goes wrong, and it keeps the final code modular rather than one inscrutable mega-job.
The team prototyped the models in SQL directly against BigQuery, since all the upstream tables already lived in GCS. That avoided the overhead of tracking down raw Parquet file paths and assembling ad-hoc Spark dataframes for what was fundamentally an exploratory exercise.
Modeling Rules That Change
State-specific rules extend far beyond a tax rate. The economic nexus threshold in one state might be $250,000 in sales, excluding digital orders and taxable marketplace sales; another may require both $100,000 in sales and 200 orders. And the evaluation window itself can differ sharply—previous calendar year, current and previous calendar year, a rolling 12-month period ending with the last complete month, or four preceding quarters.
The team first built a lookup table of all the rules, with valid_from and valid_to columns so that law changes could be represented accurately over time. From that table, they derived a dimensional model that answers, for any given date and US state (plus DC and Puerto Rico):
- The start and end date of the sales evaluation period(s)
- The dollar and order thresholds, and whether they're inclusive
- Which product or channel types are included or excluded
Thanks to the valid-date columns, this model handles changing laws automatically. When a state tweaks its rules—say, at the turn of the calendar year—calculations for each merchant continue to be correct against the applicable historical ruleset.
The Nexus Evaluation Model
A separate model aggregates each merchant's net taxable sales per state per month. Joining that with the rules model yields the core output: an actual evaluation of each merchant's liability over time.
At the merchant-state-month grain, each row contains more than just current totals:
- The applicable timeframe(s) and order types that went into the evaluation
- Sales and order figures versus the thresholds
- Whether the state even has an economic nexus law
- When the merchant first crossed the threshold
- Whether the merchant is already tax-compliant in that state (and therefore should not be chided)
- How fresh or current the data is
Having liability history built in makes it trivial to take the latest snapshot and present it to merchants. That same dataset answers internal business questions about feature adoption and merchant outcomes.
Shipping the Models
Shipment of the SQL prototypes proceeded via Starscream, Shopify's internal PySpark pipeline platform that handles tens of thousands of daily jobs, and Seamster, a newer framework leveraging dbt. Seamster was a natural fit for this work, since it allows SQL prototypes to be used almost as-is for production loads, skipping a fairly mechanical translation step into PySpark.
Model-by-model, PR-by-PR, the team loaded the outputs into the data warehouse, checked them in the BI tool, and confirmed the numbers looked as expected.
Serving Data to the Merchant UI
The final model lands in the warehouse, but Amazon isn't there to fetch a Spark table on every page load. So the liability-calculation job doesn't just write results to GCS—when it finishes, it triggers a companion job that loads the freshest snapshots into Bigtable with the merchant's unique identifier as the key and the state-level tax-liability details as the value.
Shopify's backend reads that key-value store quickly and passes the insights up to the frontend. Merchants can now act on their US tax obligations straight from their store's tax settings page, and the historic data sits durable in the warehouse, ready to measure whatever comes next.
Lessons From Building a Tax-Facing Product
Shipping a customer-facing feature built on top of complex tax data came with its own set of challenges. Here are the main takeaways from the process.
Plan for the Data Work to Take Longer Than Expected
The team frequently found itself behind schedule when handing off the final dataset to backend engineers, leading to crunch time. Building data models for internal use is one thing; adding a customer-facing layer raises the complexity significantly. Extra effort goes into:
- Understanding the nuances of the data
- Identifying and handling every edge case
- Ensuring data quality is correct and reliable
- Optimizing jobs so they scale as data grows
Building buffer into timelines from the start helps keep expectations realistic and deadlines achievable.
Lean on Domain Experts Early and Often
The U.S. tax code is highly nuanced, and for someone without a tax background, the data presented many edge cases. Consulting with colleagues who had deep knowledge of the intricacies of the U.S. tax system prevented the team from getting stuck and kept the work moving.
Design for Regions Beyond the Initial Launch
Although the initial feature was U.S.-only, the team accounted for other countries and their tax laws before building the product. That meant generalizing much of the code and infrastructure so the functionality could be extended easily. That foresight paid off when the same feature was launched for Canadian merchants required to collect GST — the second rollout went much more smoothly than the first.
