Hi @SantiNath_Dey,
Great question. This is something I have dealt with firsthand across multiple large-scale data warehouse migrations, and I actually wrote a blog post recently that covers exactly this. I would highly recommend checking it out: Speed Up Data Warehouse Migration Validation.
But let me give you the short version here, mapped directly to the three areas you mentioned.
For schema and volumetrics, you don't need a separate tool. Databricks SQL scripting now lets you build a reusable stored procedure that handles all of this in one parameterised call. In the blog, I walk through a validate_migration procedure that takes a source table, a target table, key columns, and check columns as parameters, then automatically runs row-count comparisons and column-level aggregate checks, and writes every result to a central migration_validation.results table with timestamps. The key win here is consistency. Every team member runs the same procedure, every result lands in the same place, and you stop comparing screenshots in meetings.
Your question about aggregations and hashes is covered in Tip 2 of the blog. For column-level aggregates (SUM, AVG on numeric/decimal columns), the stored procedure handles that through a loop over your check columns. For hash-based comparison, I recommend generating an MD5 hash per row using MD5(CONCAT_WS('|', ...)) across the columns you care about, then doing a FULL OUTER JOIN on the primary key to find mismatches. Something like:
SELECT s.policy_id, s.row_hash AS source_hash, t.row_hash AS target_hash
FROM source_hashed s
FULL OUTER JOIN target_hashed t ON s.policy_id = t.policy_id
WHERE s.row_hash != t.row_hash
OR s.policy_id IS NULL
OR t.policy_id IS NULL;
This gives you three things that a simple EXCEPT doesn't. You can identify which rows differ (not just that they differ), you can join back to the source to see what changed, and you can store hashes for incremental comparison on subsequent runs.
The hash approach above helps with row-level integrity. For billion-row tables, computing a hash per row and comparing on the primary key is far more efficient than a full EXCEPT. And because you are joining on the PK, you get a clean mismatch report that tells you whether a row is missing, extra, or changed...which is exactly what your team needs for root-cause analysis.
You mentioned you are currently using custom Python/PySpark scripts. The blog makes the case that you can replace all of that with a DBSQL-native validation architecture that's simpler to maintain and more powerful:
- SQL Scripting for reusable, parameterized validation procedures
- EXCEPT + MD5 hashes for row-level comparison at scale
- Unity Catalog for automatic lineage tracking and tagging tables with validation status (so you can answer "who validated what and when?" without digging through Slack)
- AI/BI Dashboards that query your validation results table directly... stakeholders see pass/fail status in real time instead of waiting for someone to email a spreadsheet
- Databricks Jobs to schedule validation runs on a cadence (daily during migration, hourly during cutover) with alerts when a check flips from pass to fail
The blog has full code examples for each of these, including the stored procedure, the hash comparison views, the Unity Catalog tagging queries, and the dashboard dataset SQL. I would start there and adapt the patterns to your specific tables.
Hope this helps.
If this answer resolves your question, could you mark it as โAccept as Solutionโ? That helps other users quickly find the correct fix.
Regards,
Ashwin | Delivery Solution Architect @ Databricks
Helping you build and scale the Data Intelligence Platform.
***Opinions are my own***
