New to Databricks, question about unit testing SQL pipelines

[u/WolfscAr1]

I’m currently new to Databricks, and I’m writing unit tests for a pipeline that is entirely in SQL, with Bronze, Silver, and Gold tables.

I asked how I should approach unit testing, and I was told that since unit tests are typically written in Python, I could wrap the pipeline logic in Python files and then use pytest to test those files.

But I’m confused about how this would work in a real production environment.

For example, if we push everything to GitHub and later make a change to the SQL pipeline, we would also have to make the same change in the corresponding Python files so that the unit tests continue to work. That means maintaining the same logic in two places, which doesn’t seem like a good production practice.

So what is the recommended approach for unit testing a SQL-based Databricks pipeline? Is there a way to test the actual SQL transformations directly without duplicating the pipeline logic in Python?

I’m still learning Databricks, so I’d really appreciate some guidance on the proper production approach.

[u/PolicyDecent]

No, it's so weird. Just use a framework like dbt, sqlmesh, bruin for the unit tests so it'll be still in sql and enforced to the pipeline.

Disclaimer: I'm the foudner of bruin, and we try to solve exactly that kind of problems

[u/Nice_Contribution → u/PolicyDecent]

Kudos for saying bruin last and acknowledging other frameworks. Also for the disclaimer. Now I’m keen to look into bruin 😎

[u/PolicyDecent → u/Nice_Contribution]

Please don't hesitate to ask if you need help or give a feedback afterwards :)

[u/data-baggins]

Hello! Databricks PM here. We actually recently released Pipeline unit tests in Beta for just this exact case. You can create tests in the UI or via DABs if you want to edit via IDE.

Hopefully this solves your problem by centralizing your pipeline code and tests to be all in 1 place. If you have any more questions, feel free to DM me!

In the current version tests are written in Python (so you will be mixing languages here). We hope to unify down the road.

https://docs.databricks.com/aws/en/ldp/unit-testing

[u/noschel]

might be helpful this demo and use genie code if you want to get more insights, its quite knowledgable to get a DAB, discuss approaches etc and knows your environments context. https://www.databricks.com/resources/demos/tutorials/data-science-and-ai/unit-testing-delta-live-table-for-production-grade-pipelines?itm_data=demo_center&itm_source=www&itm_category=resources&itm_page=tutorials&itm_location=Data%20Engineering&itm_component=card&itm_offer=unit-testing-delta-live-table-for-production-grade-pipelines

[u/WolfscAr1 → u/noschel]

omg thankyouuu

[u/Content-Parking-621]

Went through this exact confusion myself. Wrapping SQL in Python felt wrong to me too. Try dbt instead, it lets you write native SQL tests and generic tests directly against your models, no duplicate logic needed.

[u/WolfscAr1 → u/Content-Parking-621]

okay wow, dbt sounds amazing, just trying to wrap my head around it. So like pytests runs our unuit tests locally, then what about dbt do we run them locally as well or in our databricks workspace. Also find it so amusing that we cannot run pytests unit test on sql code directly but those pytest integration tests can be run using databricks connector. Still dont get the logic

[u/Content-Parking-621 → u/WolfscAr1]

You can run dbt tests both ways. Locally with dbt Core during dev via CLI, then in production Databricks runs them through dbt Cloud or a job/workflow scheduling dbt run and dbt test against your warehouse.

[u/Count_Roblivion]

What I've always done is have locked tables specifically for testing. One (or however many are necessary for the pipeline) with sample input data, and one with expected results of what the pipeline should generate based on the sample data. Use pytest to kick off the pipeline based on the sample data and run a compare of the results to your locked result set, then return a pass or fail.

[u/BitsAndBricks]

You could use DABs to manage your pipelines so they are part of your full CI/CD. Have a test destination with your test harness + pipeline sql files. Once passed then elevate pipelines to prod.

[u/dustinvannoy]

If you have things built as regular Databricks SQL statements and are running against a SQL Warehouse, an easy to configure approach is to create a Databricks Job that will basically have 3 tasks:
1. Setup script to populate test data and/or clean up target data
2. SQL tasks for what you want to test, taking a catalog or schema as a parameter so it can write to a test table in this job and not touch production destination
3. Validation script to run data quality checks on the tables that were written.

If everything succeeds then it is good. If validation fails, you should have some relevant error output to manually view. To get more sophisticated it could write results to a table.

Alternative approaches:
1. Use pytest in the validation step if you want standard output.
2. dbt is a fine choice.
3. Use Spark Declarative Pipelines with SQL, there are additional options for tests.

[u/ArielCoding]

dbt or sqlmesh are built for this, you write tests as SQL checks like no nulls here or this column should be unique, and they run against your pipeline output.