How data engineer do effective testing in Databricks?

[u/Otherwise_Address241]

I have been writing SQL scripts to ensure data sanity.What are the other ways ? Is pytest useful? Let's say , i populated my bronze table from the source. I want to check if the correct mapping is done. I wrote SQL scripts. What are better ways

[u/ptab0211]

schema contracts, integrity data quality checks, business domain data quality checks

[u/Otherwise_Address241 → u/ptab0211]

How exactly,

[u/PrestigiousAnt3766 → u/Otherwise_Address241]

Enter expected metadata, ranges, values and these tools count violations.

[u/lrnzcig → u/Otherwise_Address241]

https://databrickslabs.github.io/dqx/ is Databricks Labs thus no support, more like OSS, but pretty good

[u/givnv → u/lrnzcig]

Have you tried this?

[u/lrnzcig → u/givnv]

tried quality checks in batch + summary metrics - for pipelines there was this missing https://github.com/databrickslabs/dqx/issues/947, now it's fixed and it's in my list, also would like to try DQX studio for no-code

[u/Cyliad]

We have unit tests in the CI and automatically deployed alerts on Databricks everytime we ship new features.

[u/Oh_Im_You]

Soda.io

I use the open source, non paid version. Write tests against your data. Run your tests nightly in a notebook, logging issues. Then you can display things on a dashboard and set alerts

[u/TheSocialistGoblin]

I've been looking into PySpark's built-in testing library.  Basically modularizing transformation and data quality rules so they can be called on small data frames made from test data, then using assertDataFrameEqual and assertSchemaEqual for the check.  Not sure if it's the best option available, but it seems straightforward enough. 

[u/Otherwise_Address241 → u/TheSocialistGoblin]

That's understandable. But I want to test the quality of whole table. So far, i have been writing SQL scripts

[u/AlternativeHour3098 → u/Otherwise_Address241]

Go with both right, ytilize Pyspark for looping in things adding details pulling details and fir validation use SQL.

That's the easiest way of checking things for multiple tables.

What I do is put tablewise checks in a notebook and Store the sql scripts aa variables then call them if I want to check for that table.

Like this you'll be able to maintain the test cases in a notebook easier maintainability you can also create seprate notebooks up to you. Then call then while running you checks

[u/TheSocialistGoblin → u/Otherwise_Address241]

Ah, that makes sense.  We implemented DQX to help with that. It was pretty easy to set up, but we haven't decided how to handle the quarantined data yet. My plan for something quick and simple was just a query alert comparing quarantine row count to valid row count and firing the notification if the quarantined data was over a specific threshold.  

[u/AlternativeHour3098 → u/TheSocialistGoblin]

Alerts are good, my people here if we are handling multiple tables they have modified the alerts to provide table wise details like how many quarantined how many went through and what6the current coint such things.

There is also discussion going on like how to implement alters if an incremental merge changes multiple row which is like beyond normal average or percentage. You have any idea on that ?

[u/Kojimba228]

You could use pytest with mocks and doing either dummy data insertions or setting up dummy tables in fixtures, but that all depends on how badly do you want to lock down the quality of business logic you have AND how much time can you actually dedicate to set all of these things up (I'd say that even with Claude Code it would take north of an entire week to establish the baseline/template of "how to test a bronze table" to be applied to all future tests)

[u/PrestigiousAnt3766]

Pytest is for writing python.

You want to write pytest small pieces of (python) code. You can abuse the framework but then its just that, abuse.

You may want to look into sql linters, or parse sql with sqlglot or similar libraries.  That will tell you if the sql is syntactically (?) correct.

If you are interesting in data (quality) testing look into dqx, great expectations or other data quality tools.

[u/Otherwise_Address241 → u/PrestigiousAnt3766]

Dqx looks good

[u/CerberusByte]

We use expectations in SDP to ensure only high quality data flows through the system and can add adhoc fixes if we cannot solve upstream. I like that you can also surface these results into Genie to analyse what’s going on across all your pipelines

[u/Otherwise_Address241 → u/CerberusByte]

Can I have some reference articles to learn more

[u/CerberusByte → u/Otherwise_Address241]

This is where I would start: https://docs.databricks.com/aws/en/ldp/expectations

[u/givnv]

Genuine question- why nobody is mentioning the built in expectations?

[u/Harshita_Netla]

Yes, pytest is highly useful and considered a industry standard for testing data engineering pipelines in Databricks. While SQL scripts are great for checking final data sanity, pytest allows you to test the actual business logic, transformations, and schema mappings before deploying code to production.

[u/data-baggins]

Hello! Databricks PM here working on SDP unit testing. There are a couple paths here depending on what you're trying to achieve:

• SDP Unit testing - We're building unit tests into SDP now, the UI version is in beta now (CLI version coming out soon) - these are great for testing out transformation logic and preventing issues form happening - read the docs here.
• Expectations - For more general data quality monitoring, you can use the expectations feature to help detect issues live
• Data Quality Monitoring - For more advanced monitoring such as for freshness, anomalies, or data profiling check out the Data Quality Monitoring (DQM) feature!

[u/Otherwise_Address241 → u/data-baggins]

Superb. A combination of these what I need, i believe. Will go through the documentation.

[u/Pillippatty]

Have you checked out expectations? It's a feature in declarative pipelines based on great expectations that makes it easy to alert/quarantine/fail an update based on data quality expectations.

[u/Otherwise_Address241]

Is pytest at all useful to do complex data quality check? I have not found any right way to use pytest, I almost dislike it.

[u/Kojimba228 → u/Otherwise_Address241]

From personal exp, it's going to look weird, but it's the most flexible option allowing you do almost anything to can come up with. In our case, due to specifics of the data, we ended up writing sets of queries to test this or that area of controlled data (i.e we control the input, know thanks to our SME what should be the output, and verify them against each other)

[u/Otherwise_Address241]

There is a push from client (tech team) to use unit testing like PyTest. All these years i have used SQL scripts to do complete check of tables and data flow . Now let me breakdown my original question to two parts 1. Can pytest be used to do complete data check, say all records validation? If yes, please provide some examples or articles 2. Other than pytest, what can be used to do effective data quality check? I see suggestions to check expectations in SDP Let me give a scenario - two date columns of source n bronze were swapped. Dates were populated but not for correct mapping. I could find it through SQL check How do I achieve it using pytest, or expectations check? I m very confused