DEV Community

Cover image for Don't mock BigQuery, test on BigQuery: the rewrite of BigTesty
Google Developer Experts profile image Mazlum Tosun
Mazlum Tosun for Google Developer Experts

Posted on Originally published at Medium

Don't mock BigQuery, test on BigQuery: the rewrite of BigTesty

#bigquery #googlecloud #dataengineering #testing

This article was originally published on Medium.

Most of the BigQuery projects I work on have a lot of SQL and very few tests on it.

It's not that teams don't care. It's that testing BigQuery SQL is awkward. When I ask how they do it, I usually get one of three answers:

  • Mocks or emulators. Fast, but they don't run BigQuery SQL. MERGE, scripting, QUALIFY, UNNEST, the way timestamps are compared: the moment it matters, the test and production disagree.
  • A shared test dataset. Real BigQuery, but everyone writes into the same tables. Two CI pipelines at the same time and the tests fail for reasons nobody can reproduce.
  • Nothing. The SQL is reviewed, deployed, and the first real test is the dashboard on Monday morning.

I built BigTesty in 2023 to get a fourth answer: run the tests on the real BigQuery engine, but give each test its own datasets, created before it and deleted after it. No emulator, nothing shared.

The first version proved the idea, but every test run went through Pulumi and a state bucket. This year I rewrote it from scratch. BigTesty 1.x is a Python CLI and a pytest plugin that only need the BigQuery client. This article is about what it does now, and what I changed along the way.

A test, from the start

A BigTesty project has a config file that declares the datasets and tables of your SQL:

# bigtesty.yaml
location: EU

datasets:
  - id: monitoring
    tables:
      - id: job_failure
        schema_file: schema/monitoring/job_failure.json
      - id: job_failure_summary
        schema_file: schema/monitoring/job_failure_summary.json
Enter fullscreen mode Exit fullscreen mode

And test files written as Given / When / Then:

# tests/monitoring/merge_failures_summary.bigtesty.yaml
scenarios:
  - name: merge_updates_existing_and_inserts_new_features
    given:
      - table: monitoring.job_failure
        rows:
          - { featureName: featurePSG, jobName: job1, dwhCreationDate: "2026-01-10 10:00:00" }
          - { featureName: featurePSG, jobName: job2, dwhCreationDate: "2026-01-11 10:00:00" }
          - { featureName: featureOM, jobName: job3, dwhCreationDate: "2026-01-12 10:00:00" }
      - table: monitoring.job_failure_summary
        rows:
          - { featureName: featurePSG, failuresCount: 5, lastFailureDate: "2025-12-31 23:00:00" }
    when:
      sql_file: when/merge_failures_summary.sql
    then:
      - name: summary_is_upserted
        table: monitoring.job_failure_summary
        expected:
          - { featureName: featurePSG, failuresCount: 7, lastFailureDate: "2026-01-11 10:00:00" }
          - { featureName: featureOM, failuresCount: 1, lastFailureDate: "2026-01-12 10:00:00" }
Enter fullscreen mode Exit fullscreen mode

Then you run it:

uv add --dev bigtesty
bigtesty test --project my-test-project
Enter fullscreen mode Exit fullscreen mode

For each scenario, BigTesty creates a dataset like monitoring_boamiv_1, with the tables of the config. It loads the given rows with load jobs, which are free, and runs the when SQL with its references to monitoring. rewritten to that dataset. Then it checks the then blocks and deletes the dataset.

The when is executed as a BigQuery script, so it can be anything: a MERGE, a DELETE then an INSERT, a CALL of a procedure, a BEGIN...END block. Views, functions and procedures can be created in each dataset with DDL files.

Because every scenario has its own datasets, scenarios run in parallel, and two pipelines, or two developers, never see each other's data. If a run is killed in the middle, the tables expire after a few hours and bigtesty gc deletes the datasets left behind.

Why a rewrite

The first version of BigTesty used Pulumi to create the datasets and tables. It worked, but it came with things that have nothing to do with testing SQL: a GCS bucket for the Pulumi state, an IaC engine in the loop, and three CLI options to find the files.

Ephemeral test datasets don't need a state. They need a name, a label, and a way to find them again. So BigTesty 1.x creates them with the BigQuery client, labels them with the run ID, and deletes them by label. That's it.

Removing Pulumi was the starting point, but once I was in there, I changed most of what I didn't like:

  • One bigtesty.yaml config instead of a tables.json and three folder options.
  • Paths relative to the file that declares them, like in any other tool.
  • A when step, so the test can run DML and scripts, not only check a SELECT.
  • Typed comparison: "2026-01-11 10:00:00" and "2026-01-11T10:00:00Z" are the same TIMESTAMP, 1.5 and "1.50" the same NUMERIC. No more regexes to ignore the dates.
  • uv and Python 3.11+, PyPI with Trusted Publishing, and a Docker image for the CI tools that don't run Python.

There is a migration guide in the docs for the 0.x projects. The concepts are the same, so it's mostly a mapping of fields.

Testing the SQL of production, as is

This is the part I care the most about.

Copying a query into a test folder to test it is a trap: the copy is tested, the original drifts. So in BigTesty, sql_file can point anywhere in the repository, including the SQL your production job runs:

when:
  sql_file: ../../src/monitoring/sql/build_daily_failures.sql
Enter fullscreen mode Exit fullscreen mode

That SQL references my-prod-project.monitoring.job_failure, not the test dataset. When my-prod-project is listed in the project_aliases of the config, BigTesty rewrites those references too.

But in real life, production SQL is rarely executable as is. It has query parameters, and it's often a template. Query parameters are the easy part: BigTesty passes them as real BigQuery query parameters, not replaced in the text, exactly like your job does.

when:
  sql_file: ../../src/monitoring/sql/build_daily_failures.sql
  parameters:
    feature_names: [featurePSG, featureOM]   # ARRAY<STRING>
    min_failures: 2                          # INT64
Enter fullscreen mode Exit fullscreen mode

Templates are harder, because every team has its own engine. Jinja, Airflow macros, a function someone wrote three years ago that replaces ${env}. I didn't want BigTesty to reimplement any of them, because a test that renders the SQL differently from production is a test of something else.

So BigTesty doesn't render templates. It calls your rendering code:

# bigtesty.yaml
project_aliases: [my-prod-project]
sql_renderer: { module: tests/render.py, function: render }
vars: { env: test }
Enter fullscreen mode Exit fullscreen mode 
# tests/render.py
from monitoring.templating import render_sql   # the code your production job uses


def render(sql: str, vars: dict) -> str:
    return render_sql(sql, **vars)
Enter fullscreen mode Exit fullscreen mode

The SQL is rendered with the same macros and the same environment as in production. If someone breaks a macro or forgets a variable, the tests fail too, and bigtesty validate catches it without calling BigQuery:

when (src/monitoring/sql/build_daily_failures.sql): the SQL renderer failed: UndefinedError: 'ds' is undefined
Enter fullscreen mode Exit fullscreen mode

The full example, with a Jinja template, a shared macro and the production job, is the 06-production-sql example of the repository.

Failures you can read

A test framework is mostly used when tests fail. In the first version, a failed assertion was a raw DeepDiff output, and finding the wrong row took some reading.

Now a failure looks like this:

FAILED  monitoring/failures.bigtesty.yaml::wrong_value_and_missing_row  (4.8s, 20.0 MiB billed)

________ monitoring/failures.bigtesty.yaml::wrong_value_and_missing_row ________
failures_per_feature / rows:
    expected 3 rows, got 3: 1 changed, 1 missing, 1 unexpected
      changed     {featureName: "f1"}  n: 2 → 1
      missing     {featureName: "f4", n: 1}
      unexpected  {featureName: "f3", n: 1}
rerun: bigtesty test -k "monitoring/failures.bigtesty.yaml::wrong_value_and_missing_row"
Enter fullscreen mode Exit fullscreen mode

The changed rows are matched with the expected ones on their equal columns, so you see n: 2 → 1 on the right row instead of one missing and one unexpected row.

When the SQL itself fails, you get the step, the file, the BigQuery job, and the SQL as it was executed, with a caret where BigQuery points:

when (tests/monitoring/when/broken.sql): BadRequest: Unrecognized name: unknown_column at [3:3]
job: 2007a27c-0e0e-4ad8-b8f9-194a77adfeec

  1 | DELETE FROM `my-project.monitoring_boamiv_1.job_failure`
  2 | WHERE
  3 |   unknown_column = 1;
    |   ^
Enter fullscreen mode Exit fullscreen mode

And every scenario shows the bytes it billed. With --junit-xml, GitLab and GitHub show the failed scenarios directly in the merge request.

Not only YAML: a real BigQuery infra in your tests

YAML is great for SQL. But a lot of the code that writes to BigQuery isn't SQL files: it's a Python job, a service, a dbt model, a Dataflow pipeline. For those, what you want is the infra, not the YAML.

So the ephemeral infra is also an API. In pytest, it's a fixture with a setup and a teardown, like Testcontainers but on BigQuery:

@pytest.fixture(scope="class")
def infra():
    with BigTestyInfra.from_config(CONFIG) as infra:       # setup: datasets and tables created
        infra.load("monitoring.job_failure", FAILURES)      # given
        build_failures_summary(infra.client, infra.dataset_id("monitoring"))   # when
        yield infra
                                                            # teardown: datasets deleted


class TestFailuresSummary:
    def test_one_row_per_feature(self, infra):
        rows = infra.query("SELECT featureName, failuresCount FROM monitoring.job_failure_summary ORDER BY 1")

        assert rows == [
            {"featureName": "featureOM", "failuresCount": 1},
            {"featureName": "featurePSG", "failuresCount": 2},
        ]
Enter fullscreen mode Exit fullscreen mode

You choose the lifecycle: one infra per test, per class, per module or for the whole session. And with infra.reset(), parametrized scenarios can share one infra and still start from empty tables.

For the other languages, bigtesty infra create --output json and bigtesty infra destroy give the same infra to any test framework. I wrote a complete Java example with a JUnit 5 extension, the BigTesty CLI or its Docker image run with Testcontainers: bigtesty-java-example.

For the GCP organizations

Most of the projects I work on are in organizations with policies, and a test tool that ignores them doesn't get past the first security review. So BigTesty 1.x supports:

  • CMEK: the test datasets and tables can be encrypted with a Cloud KMS key, per config or per dataset.
  • Labels on the datasets and the jobs, for the FinOps reports.
  • max_bytes_billed on each query job, and the bytes billed of each scenario in the report.
  • Workload Identity Federation in the CI examples, with no service account key.

The GCP account only needs roles/bigquery.user on the test project: it creates datasets, owns them, and deletes them.

And for the coding agents

More and more of the tests I see are written with a coding agent. So BigTesty ships Agent Skills: setup, writing tests, Python tests and troubleshooting. bigtesty skills install copies them into your project, and the agent writes BigTesty tests the way the docs describe, instead of guessing a format.

What's next

The next things on my list are key columns to match the rows of a diff, schema assertions, and a guide to test dbt models with their compiled SQL.

If you test BigQuery SQL, I'd really like to know how you do it today, and what would make you try BigTesty. The issues of the repository are open for that.

⭐ If you find BigTesty useful, a star on GitHub helps a lot. It's how a small open-source project gets found by the teams who need it.

Don't mock BigQuery. Test on BigQuery.

If you enjoyed this article, follow me for more content on Google Cloud, BigQuery, data engineering, testing and open source:

Top comments (0)