bigtesty 0.3.0rc1

BigTesty is an integration testing framework for BigQuery, running the tests on a real and short-lived infrastructure.

• The real BigQuery engine, no emulator: the SQL behaves exactly as in production.
• Given / When / Then test files in YAML or JSON: load test data, run any SQL (queries, DML, MERGE, scripts, procedures), then assert on query results or table contents.
• Isolated and ephemeral: each scenario of each run gets its own datasets, created and destroyed by BigTesty. Several developers and CI pipelines can run the tests at the same time.
• Production SQL unchanged: the references to your datasets are rewritten to the isolated datasets.

Installation

Python 3.11+ is required.

pip install bigtesty
# or
uv add --dev bigtesty

BigTesty uses Application Default Credentials:

gcloud auth application-default login

Quick start

A BigTesty project is a bigtesty.yaml config file declaring the datasets and tables, and test files named *.bigtesty.yaml (or .yml / .json):

bigtesty.yaml
schema/monitoring/job_failure.json
ddl/monitoring/v_failures_count_by_feature.sql
tests/monitoring/
├── find_failures.bigtesty.yaml
├── merge_failures_summary.bigtesty.yaml
├── assertion_functions/failures_assertions.py
├── given/input_failures_feature_name.json
├── queries/find_failures_by_feature_name.sql
├── then/expected_failures_feature_name.json
└── when/merge_failures_summary.sql

bigtesty.yaml: the datasets and tables created for each scenario.

project: my-project        # or --project / GOOGLE_CLOUD_PROJECT
location: EU
tests: tests

datasets:
  - id: monitoring
    tables:
      - id: job_failure
        schema_file: schema/monitoring/job_failure.json   # BigQuery JSON schema
        partitioning: { type: DAY, field: dwhCreationDate }
        clustering: [featureName, jobName]
      - id: job_failure_summary
        schema:                                            # or inline
          - { name: featureName, type: STRING }
          - { name: failuresCount, type: INT64 }
          - { name: lastFailureDate, type: TIMESTAMP }
    ddl_files:                                             # views, functions, procedures...
      - ddl/monitoring/*.sql

tests/monitoring/merge_failures_summary.bigtesty.yaml: one or several scenarios.

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:                                   # or rows_file, a JSON or YAML file
          - { featureName: featurePSG, failuresCount: 5, lastFailureDate: "2025-12-31 23:00:00" }
    when:
      sql_file: when/merge_failures_summary.sql
    then:
      - 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" }
      - query: SELECT featureName FROM monitoring.job_failure_summary GROUP BY featureName HAVING COUNT(*) > 1
        row_count: 0

Run the tests:

bigtesty test

PASSED  monitoring/find_failures.bigtesty.yaml::find_failures_by_feature_name  (4.7s)
PASSED  monitoring/find_failures.bigtesty.yaml::find_failures_by_feature_name_with_functions  (4.1s)
PASSED  monitoring/find_failures.bigtesty.yaml::find_failures_by_job_name  (3.8s)
FAILED  monitoring/merge_failures_summary.bigtesty.yaml::merge_updates_existing_and_inserts_new_features  (8.2s)

________ monitoring/merge_failures_summary.bigtesty.yaml::merge_updates_existing_and_inserts_new_features ________
then[0] / rows:
    expected 2 rows, got 2, diff from expected to actual:
    Value of root[0]['failuresCount'] changed from 8 to 7.

3 passed, 1 failed (run zivarf)

All the files used in this documentation are in the examples folder.

Test files

A scenario has 3 blocks:

• given: the rows loaded into the tables, inline (rows) or from a JSON / YAML file (rows_file). The rows are loaded with load jobs: they are immediately available for DML statements.
• when (optional): the SQL under test (sql or sql_file), executed as a BigQuery script: queries, DML, MERGE, CALL of a procedure, multi-statements...
• then: one or several assertions on a query, a query_file or a whole table:
  • expected / expected_file: the expected rows,
  • row_count: the expected number of rows,
  • functions: custom Python assertion functions, called with (actual_rows, expected_rows).

Options of the then blocks:

The actual and expected values are compared with the types of the query result: a TIMESTAMP written "2026-01-11 10:00:00", "2026-01-11T10:00:00Z" or "2026-01-11T12:00:00+02:00" is the same value, a NUMERIC can be written 1.5 or "1.50", a DATE "2026-01-11"...

In YAML, yes / no / on / off and the dates stay strings: only true / false are booleans.

IDE validation

JSON Schemas are published for the config and the test files. With the VS Code YAML extension or JetBrains IDEs:

# yaml-language-server: $schema=https://raw.githubusercontent.com/tosun-si/bigtesty/main/schemas/bigtesty-test.schema.json

Isolation and SQL rewrite

For each run, BigTesty creates one dataset per configured dataset and per scenario, e.g. monitoring_hlnyks_0, labelled with bigtesty_run_id. The references to the configured datasets in the SQL (DDL files, when and then) are rewritten to these datasets, the formatting of the SQL is kept:

• monitoring.job_failure, `monitoring.job_failure`, `monitoring`.`job_failure`
• my-project.monitoring.job_failure, when my-project is the test project or listed in project_aliases (e.g. the production project hardcoded in the SQL).

The comments, string literals and references to other datasets or projects are not touched. Don't alias a table with the name of a configured dataset (FROM x AS monitoring), it would be rewritten.

The datasets are destroyed at the end of the run (cleanup: always by default, or on_success / never to analyse the result in BigQuery). As a safety net, their tables expire after table_expiration_hours (6 by default), and bigtesty gc deletes the datasets left by killed runs.

CLI

bigtesty test                      # run all the scenarios
bigtesty test -k merge             # only the scenarios whose ID contains "merge"
bigtesty test --cleanup never -v   # keep the datasets, show the executed SQL
bigtesty validate                  # validate the config and test files, no call to BigQuery
bigtesty gc --older-than-hours 6   # delete the datasets left by killed runs
bigtesty schema -o schemas         # write the JSON Schemas

The exit code is 0 when all the scenarios pass, 1 when a scenario fails and 2 for an invalid config.

Run with Docker

docker run -it \
   -e GOOGLE_CLOUD_PROJECT=$PROJECT_ID \
   -v $(pwd)/examples:/usr/local/src/app/project \
   -v $HOME/.config/gcloud:/usr/local/src/app/.config/gcloud \
   groupbees/bigtesty test --config project/bigtesty.yaml

Mounting the local gcloud configuration authenticates the container with your Application Default Credentials, without a long-lived service account key.

Run in CI

Examples for GitHub Actions, Cloud Build and GitLab CI.

Migrating from 0.2.x to 0.3.0

The version 0.3.0 introduces a new format for the config and test files:

• The tables.json file and the --root-test-folder, --root-tables-folder, --tables-config-file, --region and --keep-infra options are replaced by the bigtesty.yaml config file and --config, --location and --cleanup.
• The definition_*.json files are replaced by *.bigtesty.yaml / *.bigtesty.json test files:
  • destination_dataset + destination_table → table: dataset.table, input / input_file_path → rows / rows_file,
  • actual / actual_file_path → query / query_file, expected_file_path → expected_file,
  • assertion_type is removed: the expected rows, row_count and functions can be combined,
  • expected_functions → functions,
  • fields_to_ignore (regexes) → ignore_columns (column names), often not needed anymore thanks to the typed comparison.
• The paths are relative to the file that declares them.
• The custom assertion functions receive the rows normalized with the query result schema.

Migrating from 0.1.x to 0.2.0

The version 0.2.0 removes Pulumi: the --iac-backend-url option is removed, Python 3.11+ is required, the working and home directory of the Docker image is /usr/local/src/app.

Documentation

The full documentation is on the BigTesty website.