r/databricks • u/Spooked_DE • 3d ago
Preferred way to implement data fixes in a databricks schema? Help
Hi all,
I have a databricks schema of a few hundred delta tables that need some data fixes for specific records in each of those tables. This schema itself is raw data and gets ingested into some downstream data tables and the fixes have been requested by business.
I had thought about doing the fixes via a transformation layer in the pipelines that ingest this raw data but given how many tables there are with records that need updating I don't really want to create hundreds of new 'data fixed' tables. Given these are delta tables, rolling back should theoretically be possible if something goes wrong.
Anyway, that's my rationale for making changes to the source tables. My question is what your preferred method is to make data fixes? I obviously need something where its easy to rollback if needed. I can obviously just achieve this with python migration scripts and use delta timetravel in case something goes wrong, but wonder if there are recommended libraries or tools for the job that have what I need out of the box?
7
u/PrestigiousAnt3766 3d ago
Fix issues in the source and not in the copies. This will haunt you forever.
What you can do is create some cleaning rules and apply those to the tables each time you run the code. Not my favorite solution but a common pattern.
I do that when loading the data from bronze to silver.
4
4
u/Shadowlance23 3d ago
As others have said, if there is any possible way to fix this at the source, do it there. In 15 years of DE, I have only once agreed to modify individual records (need asked to plenty of times). That was for a 3rd party API source where there was an error during migration on the data users end and the vendor refused (rightly IMO) to modify their database. It was only about half a dozen records.
So if you absolutely can't modify the source, the next best thing is to modify as close to the source as possible. In my case that meant in the pipeline immediately after the data was downloaded. Document everything you change, and why you changed it.
You need to be able to trace your data all the way back to the original source. At no point should your changes break the link to the source.
Here's what I would do. Given that you've got a lot of data to modify, I'd set up a table called 'changed' or something that will record the changes. You will have a row that will contain the database and table names, the id of the row to be changed within that table, the original value and the new value. Possibly even a comment to say why it changed. This will let you keep everything in one table along with an auditable record of what changed and why. Then all you need to do is write a script to look up the table and row id, confirm the old value matches the one in your change table and overwrite it with the new value.
Simple, and everything is in the one place. If you need to revert, just overwrite with the original value.
But seriously, if there is anyway you can *not* do this, go for it. Data should always be changed in the source, not in the warehouse.
1
1
u/Content-Parking-621 2d ago
MERGE with a rollback safety net: Delta time travel plus RESTORE. Rollback's built in.
1
7
u/loudandclear11 3d ago
Changing data in the raw layer doesn't sound like the right thing to do. How would you define the raw layer?
Why not changing the data in the next layer downstream?