How do I run SQL unit tests in CI using Bitbucket Pipelines?
Add a job to bitbucket-pipelines.yml that installs the Bruin CLI and runs bruin unit-test ./pipeline. It runs each query against mock input rows and compares to expected output, with no production data involved. Store warehouse credentials as Bitbucket Pipelines repository variables, and use a deployment concurrency so two deploys can never write the same tables at once. Keep validation and deployment as separate jobs: validation should be fast and credential-free, deployment serialised and gated.
Command
bruin unit-test ./pipelineDefined in
YAML + CI
Works with
Bitbucket Pipelines + Bruin CLI
What you get
How to do it
- 1
Create or open bitbucket-pipelines.yml in your repository.
- 2
Install the Bruin CLI as a step: curl -LsSf https://getbruin.com/install/cli | sh.
- 3
Add bruin unit-test ./pipeline as the job's command.
- 4
Store warehouse credentials as Bitbucket Pipelines repository variables, never in the repository.
- 5
Add a deployment concurrency so concurrent runs cannot write the same tables.
- 6
Make the job required for merge, so a failure actually blocks.
How it works in code
# bitbucket-pipelines.yml
- run: curl -LsSf https://getbruin.com/install/cli | sh
- run: bruin unit-test ./pipelineRun bruin unit-test ./pipeline and Bitbucket Pipelines fails the build when the pipeline does, before the change reaches production.
Worth knowing
A job that reports failures without failing the build gets ignored within weeks. Make it required. And give CI its own warehouse connection pointed at a scratch schema, because a pull request can come from anywhere and should never hold production credentials.
Other ways to do this
Bruin is not always the right answer. Here is where the alternatives are stronger.
| Option | When it is the better choice |
|---|---|
| Bruin | Wire bruin unit-test ./pipeline into Bitbucket Pipelines so pipeline changes get checked the same way application code does. |
| dbt in CI | The same pattern with dbt build and a state-based selector. Well documented, and the right choice if dbt is already your transformation layer. |
| A managed orchestrator | Tools like Dagster or Prefect Cloud handle scheduling and retries more richly than a CI runner, at the cost of another system to run. |
| Soda in CI | Soda's data-contract checks are the strongest documented option if formal contracts between teams are the main goal. |
Common questions
How do I run data pipelines in Bitbucket Pipelines?
Install the Bruin CLI in a job and run bruin unit-test ./pipeline. Validation needs no warehouse credentials; deployment reads them from Bitbucket Pipelines repository variables.
How do I stop two Bitbucket Pipelines deploys running at once?
Use a deployment concurrency. Two pipeline runs writing the same tables concurrently is the hardest data bug to diagnose, and it is entirely preventable.
Should validation and deployment be separate Bitbucket Pipelines jobs?
Yes. Validation should run on every pull request, take seconds, and need no credentials. Deployment should run on merge, be serialised, and be gated. Combining them makes validation slow and deployment unsafe.
Related use cases
Run SQL unit tests in CI with GitHub Actions
How do I run SQL unit tests in CI using GitHub Actions?
Unit tests in CIRun SQL unit tests in CI with GitLab CI
How do I run SQL unit tests in CI using GitLab CI?
Unit tests in CIRun SQL unit tests in CI with Azure Pipelines
How do I run SQL unit tests in CI using Azure Pipelines?
Deploy data pipelines like software
Open source. Validate on every pull request, deploy on merge, roll back with git.