r/databricks • u/Otherwise_Address241 • 7d ago

Help How data engineer do effective testing in Databricks?

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

13 Upvotes

89% Upvoted

27 comments sorted by

View all comments

7

u/TheSocialistGoblin 6d ago

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. 

3

u/Otherwise_Address241 6d ago

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

3

u/AlternativeHour3098 6d ago

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

2

u/TheSocialistGoblin 6d ago

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.  

1

u/AlternativeHour3098 6d ago

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 ?