Why Shopify Built a SQL Modelling Layer on Top of dbt
In 2014, Shopify's data science team built Starscream, a PySpark-based data pipeline platform, to handle everything from ad hoc explorations to machine learning workloads. Six years later, the platform runs 76,000 jobs and writes 300 terabytes per day. But as the company grew, a large share of those jobs turned out to be straightforward reporting tasks that didn't need the full power of a distributed compute platform—and the overhead was hurting developer velocity.
Shopify's solution was Seamster: a production-grade SQL modelling workflow built on dbt (data build tool) and Google BigQuery. The system adds structure, testing, and validation tooling on top of dbt to make simple pipeline development fast and safe for dozens of data scientists working in a shared repository.
The Pain Points: Development Time and Cognitive Load
Interviews with Starscream users surfaced two dominant issues. The first was development time: prototyping a data model in PySpark meant writing boilerplate code and waiting through long runtimes, which made iteration on unfamiliar data painfully slow. The second was thinking—the way the programming language shapes how you approach the problem. Many data scientists preferred SQL because its structure enforces consistency in how business metrics are defined. In practice, they would prototype a query in SQL, then translate it into Python for the pipeline. That translation step was pure overhead.
An audit confirmed the scope of the problem: roughly 70 percent of the PySpark jobs running on Starscream were full batch queries that didn't require generalized computing. That was the opportunity. If Shopify could offer a SQL-native path for these common workloads, data scientists could spend their time on logic rather than plumbing.
Organizing Sources and Base Models for Safety
dbt's default configuration declares raw sources in a central sources.yml file. In a shared repository used by dozens of engineers, that file quickly becomes a bottleneck—every pull request that touches a source edits the same file, causing merge conflicts and friction.
Seamster solves this by leveraging dbt's flexibility: each raw source gets its own top-level directory with a source-formatted YAML file. Data scientists can parse only the documentation relevant to their work and contribute changes without stepping on each other's toes.
On top of that, Seamster implements a base layer of models following dbt's "staging" concept. These base models are one-to-one interfaces to raw sources: they don't change the grain of the data but do apply renaming, recasting, and other cleaning operations tied to the source system. This layer insulates downstream consumers from breaking changes in raw sources, which are outside Seamster's control. When an upstream source changes unexpectedly, the fix goes into a single base model rather than every dependent model.
Model Ownership and Layering
In dbt, a model is just a .sql file. Seamster extends that definition: a model is a directory containing four files:
model_name.sqlschema.ymlREADME.mdtest_model_name.py
Models can be grouped into directories that correspond to data science teams, such as Finance or Marketing. The warehouse is also organized into layers that signal data quality expectations:
- Base: one-to-one with raw data, cleaned, recast, and renamed.
- Application-ready: transformed and clean for consumption by another tool, such as training data for a machine learning model, but not dimensionally modelled.
- Presentation: shareable, reliable models that follow dimensional modelling best practices and can be used across domains.
This structure lets a data consumer quickly assess the expected quality of a model and find its owner when something goes wrong. The metadata is also passed upstream to other tools to support data discovery.
Unit Testing with Fixed Inputs
dbt ships native schema tests that run against production data to validate invariants like null presence or key uniqueness. Useful as that is, Seamster adds a second layer: unit tests that run against fixed input data rather than production data. This lets users cover edge cases that haven't appeared in production yet—important in a large organization where a single model may have many collaborators shipping frequent updates.
Seamster's Python-based unit testing framework lives in the test_model_name.py file within each model directory. The central concept is a "mock" data model backed by a Pandas dataframe. Users can construct mocks from a CSV-style string, a Pandas dataframe, or a list of dictionaries.
At execution time, the framework builds a query where each input mock becomes a common table expression (CTE), and references to production models (identified via dbt's ref macro) are swapped for references to the corresponding CTE. The output can be compared with an equality assertion, or checked against any expectation from the open-source Great Expectations library for more granular error messaging.
The framework's main cost is the roundtrip to the query engine for each test, even though each query processes only a handful of rows. Running the full suite on every local or CI invocation would be prohibitively slow. Seamster addresses this by using dbt's lineage tracking to identify which tests could potentially break given a set of changes—only those downstream models' tests are executed.
Validating the Whole DAG on Every Commit
The goal for Seamster's CI is simple: data scientists should be confident their changes won't break the next warehouse build. Rebuilding the entire warehouse on every commit isn't feasible from a time or cost perspective. Instead, Seamster materializes every model as a view in a temporary BigQuery dataset at the start of validation and drops that dataset when validation finishes. If a view can't be built—because an upstream model is missing a column, or the SQL is invalid—BigQuery fails with relevant error messaging.
This validation step currently takes about two minutes. As with unit testing, the actual work is reduced by building only the portion of the DAG affected by changed models. The same view-based strategy validates the contents of a model's schema.yml, catching invalid column names or data_type values that would otherwise go unnoticed until production.
Enforcing Warehouse Conformance Rules
Shopify's reporting warehouse must be accurate, and Seamster enforces consistency through a set of CI rules applied to the base, application-ready, and presentation layers. Examples include naming conventions for all columns and a rule that only base models may reference raw sources directly via the source macro. Certain rules apply only to specific layers: in the presentation layer, every column name must have a globally unique description to prevent divergence in metric definitions.
Since dbt configuration is YAML-based, most rule enforcement is straightforward parsing—simple to implement and inexpensive to run alongside the other CI checks.
Beta Results and Limitations
Seamster underwent a multiweek beta with Shopify data scientists testing it on real models. The feedback was broadly positive: the majority of users reported shipping models in days rather than weeks, and the model spec’s built-in documentation feature led to a noticeable increase in documentation coverage.
The beta also surfaced clear limitations. dbt’s current incremental support lacks safe and consistent mechanisms for late-arriving data, key resolution, and rebuilds. As a result, models requiring incremental semantics—such as type 2 dimensions and models handling over 1.5 billion events—were not feasible. These remain out of scope for the time being, with plans to address them in future work.
Adoption and Engineering Roadmap
The immediate focus is tailoring Seamster to Shopify data scientists. The primary obstacle for any new internal tool is adoption, so the team is working directly with individual groups to identify upcoming reporting work that Seamster can accelerate. User feedback is being folded into tutorials and documentation for onboarding.
Beyond batch workloads, the engineering outlook includes Apache Beam and Beam SQL as a path toward a single SQL-centric modelling tool that serves both batch and streaming use cases. Given Shopify’s commitment to open source, there is also interest in contributing the validation strategy and a unit testing framework to dbt’s community, depending on its needs.
