---
title: "How to Build a Production Grade Workflow with SQL Modelling"
description: "Shopify’s Starscream data platform handled 76,000 jobs and wrote 300 terabytes daily, but its PySpark workflow was slow and cumbersome for commonplace reporting. An audit found that about 70 percent of PySpark jobs were full batch queries that did not require generalized computing, while users often wrote SQL before translating it into Python. The team built Seamster with dbt and Google BigQuery, adding source and model structures, ownership metadata, Python-based unit tests with fixed mock inputs, CI validation, and warehouse rules. A base layer isolates raw-source changes, while models are organized into base, application-ready, and presentation layers and defined through SQL, schema, README, and test files. After a multiweek beta, most users reported shipping models in days rather than weeks, although dbt’s incremental support could not safely handle some late-arriving-data, key-resolution, rebuild, and very large-model cases."
---

# How to Build a Production Grade Workflow with SQL Modelling

[Shopify](https://yomu.fyi/company/shopify) · 2023-10-18 · Nov 19, 2020

**Type:** Problem & solution

## Summary

Shopify’s Starscream data platform handled 76,000 jobs and wrote 300 terabytes daily, but its PySpark workflow was slow and cumbersome for commonplace reporting. An audit found that about 70 percent of PySpark jobs were full batch queries that did not require generalized computing, while users often wrote SQL before translating it into Python. The team built Seamster with dbt and Google BigQuery, adding source and model structures, ownership metadata, Python-based unit tests with fixed mock inputs, CI validation, and warehouse rules. A base layer isolates raw-source changes, while models are organized into base, application-ready, and presentation layers and defined through SQL, schema, README, and test files. After a multiweek beta, most users reported shipping models in days rather than weeks, although dbt’s incremental support could not safely handle some late-arriving-data, key-resolution, rebuild, and very large-model cases.

## Context

Starscream’s PySpark platform was computationally powerful but poorly suited to routine reporting. Users faced long development cycles, boilerplate code, and translation from SQL prototypes into Python; an audit found that about 70 percent of its PySpark jobs were full batch queries that did not require generalized computing.

## Approach / What changed

Shopify created Seamster as a SQL reporting pipeline using dbt and Google BigQuery. It added decentralized source files, a base model layer, team and warehouse-layer organization, model ownership metadata, Python-based unit tests with fixed mock inputs, CI validation, and rules for documentation and dimensional modelling practices.

## Takeaways

- Seamster’s model specification uses four files—model\_name.sql, schema.yml, README.md, and test\_model\_name.py—supporting implementation, documentation, and testing alongside ownership metadata.
- Its unit-testing framework builds mock input and expected models from CSV-style strings, Pandas dataframes, or lists of dictionaries, then compares BigQuery-generated results using equality or Great Expectations assertions.
- Most beta users reported shipping models in a couple of days instead of a couple of weeks, but some type 2 dimensions and models exceeding 1.5 billion events remained unsupported because of dbt’s incremental-data limitations.

**Tags:** [CI/CD](https://yomu.fyi/topic/ci-cd), [dbt](https://yomu.fyi/topic/dbt), [Google Cloud](https://yomu.fyi/topic/gcp), [Python](https://yomu.fyi/topic/python), [Testing](https://yomu.fyi/topic/testing)

- Source: [Shopify](https://shopify.engineering/build-production-grade-workflow-sql-modelling)
- Source URL: https://shopify.engineering/build-production-grade-workflow-sql-modelling
- Ingested by Yomu: 2026-08-31T01:11:10.098Z

[Read original post](https://shopify.engineering/build-production-grade-workflow-sql-modelling)