Data Integrity Verification Methods

Dear #DataEngineers, No matter how confident you are in your SQL queries or ETL pipelines, never assume data correctness without validation. ETL is more than just moving data—it’s about ensuring accuracy, completeness, and reliability. That’s why validation should be a mandatory step, making it ETLV (Extract, Transform, Load & Validate). Here are 20 essential data validation checks every data engineer should implement (not all pipeline require all of these, but should follow a checklist like this): 1. Record Count Match – Ensure the number of records in the source and target are the same. 2. Duplicate Check – Identify and remove unintended duplicate records. 3. Null Value Check – Ensure key fields are not missing values, even if counts match. 4. Mandatory Field Validation – Confirm required columns have valid entries. 5. Data Type Consistency – Prevent type mismatches across different systems. 6. Transformation Accuracy – Validate that applied transformations produce expected results. 7. Business Rule Compliance – Ensure data meets predefined business logic and constraints. 8. Aggregate Verification – Validate sum, average, and other computed metrics. 9. Data Truncation & Rounding – Ensure no data is lost due to incorrect truncation or rounding. 10. Encoding Consistency – Prevent issues caused by different character encodings. 11. Schema Drift Detection – Identify unexpected changes in column structure or data types. 12. Referential Integrity Checks – Ensure foreign keys match primary keys across tables. 13. Threshold-Based Anomaly Detection – Flag unexpected spikes or drops in data volume or values. 14. Latency & Freshness Validation – Confirm that data is arriving on time and isn’t stale. 15. Audit Trail & Lineage Tracking – Maintain logs to track data transformations for traceability. 16. Outlier & Distribution Analysis – Identify values that deviate from expected statistical patterns. 17. Historical Trend Comparison – Compare new data against past trends to catch anomalies. 18. Metadata Validation – Ensure timestamps, IDs, and source tags are correct and complete. 19. Error Logging & Handling – Capture and analyze failed records instead of silently dropping them. 20. Performance Validation – Ensure queries and transformations are optimized to prevent bottlenecks. Data validation isn’t just a step—it’s what makes your data trustworthy. What other checks do you use? Drop them in the comments! #ETL #DataEngineering #SQL #DataValidation #BigData #DataQuality #DataGovernance

🛡️ Data Validation Checks Every Pipeline Should Have No matter how scalable or fancy your data pipeline is, if the data is wrong — nothing else matters. In my work across Nike, eBay, and healthcare platforms, I’ve learned that data validation is not optional — it's a first-class citizen in any pipeline. Here are some checks I always include: ✅ Schema consistency — making sure columns match expected formats ✅ Null thresholds — too many nulls = red flag ✅ Unique key enforcement — helps prevent silent duplications ✅ Data type mismatches — especially with JSON & XML inputs ✅ Volume spikes/drops — sudden shifts usually mean something’s broken ✅ Date range sanity — no future-dated transactions, please ✅ Reference integrity — missing lookups can skew metrics I usually build these into PySpark or Python utilities and wire them into Airflow DAGs — so pipelines fail fast instead of letting bad data leak downstream. Data quality isn’t just an afterthought — it’s step one. #DataEngineering #DataQuality #ETL #Airflow #PySpark #CloudData #BigData #DataPipelines #AWS #GCP #Azure #Monitoring

THE CRC32 FUNCTION IN DREMIO The CRC32 function in Dremio SQL is used to compute a binary string's cyclic redundancy check or CRC value. A CRC is a type of hash function that produces a checksum, detecting data storage or transmission errors. By comparing the CRC value of data at its source and its destination, you can tell whether the data has changed during transfer. This can be extremely useful in situations where ensuring data integrity is essential. For instance, you might use it when sending data over a network, writing data to disk, or storing data in a database. SCENARIO IN THE IMAGE Imagine you're a data analyst at a large corporation. You've recently begun migrating a massive amount of data from an old, outdated database system to a modern, cloud-based data lake for more efficient data management and analytics. Before the data was ingested into the data lake, your team wisely decided to create a CRC32 checksum for each row of data to ensure that you could verify the integrity of the data after the transfer. So now you have a table, let's call it OldDatabaseTable, with columns Data and OriginalCRC, where Data is the actual data from each row in the old database and OriginalCRC is the CRC32 checksum calculated before the transfer. Your team has now ingested this data into the data lake, and your job is to ensure nothing was corrupted during the migration. So, you decide to use Dremio and the CRC32 function to verify the data integrity using the query in the image below. QUERY EXPLANATION CRC32(Data) calculates the CRC32 checksum of the Data in the new data lake. OriginalCRC is the original CRC32 checksum calculated before the transfer. The CASE statement compares these two values. If they're the same, it returns 'Match', indicating that the data is intact. If they're not, it returns 'Mismatch', signaling that something has gone wrong during the transfer. Running this query gives you a list of all your data and a new IntegrityCheck column showing whether the original and new CRC32 values match for each row. Using the CRC32 function, you can quickly and efficiently confirm that all your data was transferred to the new data lake without corruption, ensuring your team can confidently proceed with analysis and decision-making. If you find any mismatches, you know precisely which data you need to recheck and possibly retransfer, making the debugging process much more manageable.

𝗧𝗵𝗲 𝗙𝗗𝗔 𝗶𝘀 𝗶𝗻𝗰𝗿𝗲𝗮𝘀𝗶𝗻𝗴 𝘀𝗰𝗿𝘂𝘁𝗶𝗻𝘆 𝗮𝗿𝗼𝘂𝗻𝗱 𝗗𝗮𝘁𝗮 𝗜𝗻𝘁𝗲𝗴𝗿𝗶𝘁𝘆 𝘄𝗶𝘁𝗵 𝘁𝗵𝗲𝗶𝗿 𝗹𝗮𝘁𝗲𝘀𝘁 𝗳𝗼𝗿𝗺𝗮𝗹 𝘄𝗮𝗿𝗻𝗶𝗻𝗴 𝗹𝗲𝘁𝘁𝗲𝗿 — 𝗗𝗼𝗲𝘀 𝘆𝗼𝘂𝗿 𝘁𝗲𝗮𝗺 𝘂𝗻𝗱𝗲𝗿𝘀𝘁𝗮𝗻𝗱 𝘆𝗼𝘂𝗿 𝘀𝘂𝗯𝗺𝗶𝘀𝘀𝗶𝗼𝗻 𝗱𝗮𝘁𝗮 𝗼𝗿 𝗻𝗲𝗲𝗱 𝘁𝗼 𝘃𝗲𝗿𝗶𝗳𝘆 𝘁𝗲𝘀𝘁𝗶𝗻𝗴 𝗮𝗿𝗼𝘂𝗻𝗱 𝘆𝗼𝘂𝗿 𝗔𝗜/𝗠𝗟 𝗺𝗲𝗱𝗶𝗰𝗮𝗹 𝗱𝗲𝘃𝗶𝗰𝗲? At Innolitics, our team works closely with FDA reviewers, and guidance like the "Cybersecurity in Medical Devices: Quality System Considerations and Content of Premarket Submissions" which provides recommendations for ensuring data integrity including: • ✍️ 𝗖𝗿𝘆𝗽𝘁𝗼𝗴𝗿𝗮𝗽𝗵𝗶𝗰 𝗮𝘂𝘁𝗵𝗲𝗻𝘁𝗶𝗰𝗮𝘁𝗶𝗼𝗻: Using digital signatures or message authentication codes (MACs) to verify data authenticity and integrity. • 📑 𝗖𝗵𝗲𝗰𝗸𝘀𝘂𝗺𝘀 𝗮𝗻𝗱 𝗵𝗮𝘀𝗵 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀: Employing algorithms to detect unintended data changes. • ✅ 𝗗𝗮𝘁𝗮 𝘃𝗮𝗹𝗶𝗱𝗮𝘁𝗶𝗼𝗻: Checking data for completeness, accuracy, and consistency with expected values. 𝖳𝗈 𝖺𝖽𝖽𝗋𝖾𝗌𝗌 𝗍𝗁𝗂𝗌 𝗍𝗒𝗉𝖾 𝗈𝖿 𝗈𝖻𝗃𝖾𝖼𝗍𝗂𝗈𝗇, 𝖼𝗈𝗇𝗌𝗂𝖽𝖾𝗋: • 𝗗𝗲𝘀𝗰𝗿𝗶𝗯𝗶𝗻𝗴 𝗶𝗻𝘁𝗲𝗴𝗿𝗶𝘁𝘆 𝗰𝗼𝗻𝘁𝗿𝗼𝗹 𝗺𝗲𝗰𝗵𝗮𝗻𝗶𝘀𝗺𝘀: Specify the methods used to protect data integrity during transmission and storage. • 𝗝𝘂𝘀𝘁𝗶𝗳𝘆𝗶𝗻𝗴 𝗰𝗼𝗻𝘁𝗿𝗼𝗹 𝗰𝗵𝗼𝗶𝗰𝗲𝘀: Explain why your chosen methods provide adequate protection for the data and the intended use of the device. • 𝗣𝗿𝗼𝘃𝗶𝗱𝗶𝗻𝗴 𝘁𝗲𝘀𝘁𝗶𝗻𝗴 𝗱𝗼𝗰𝘂𝗺𝗲𝗻𝘁𝗮𝘁𝗶𝗼𝗻: Demonstrate that you've tested your integrity controls and that they're effective in detecting and preventing data corruption.Audit trails for every annotation and immutable Version Control AI developers now need more than great models — they need infrastructure that can defend their evidence from scrutiny: • 🔐 𝖠𝗎𝖽𝗂𝗍 𝗍𝗋𝖺𝗂𝗅𝗌 𝖿𝗈𝗋 𝖾𝗏𝖾𝗋𝗒 𝖺𝗇𝗇𝗈𝗍𝖺𝗍𝗂𝗈𝗇 𝖺𝗇𝖽 𝗂𝗆𝗆𝗎𝗍𝖺𝖻𝗅𝖾 𝖵𝖾𝗋𝗌𝗂𝗈𝗇 𝖢𝗈𝗇𝗍𝗋𝗈𝗅 • 👜Proof of Data Sequestration • ✅FDA-aligned GMLP compliance by design Ad-hoc reader studies and opaque validation are no longer acceptable. Regulators are now expecting traceability, reliability, and full lifecycle control. In other words, regulatory-grade AI needs a regulatory-grade development team! How will you ensure that your internal processes and any third-party lab are GMLP compliant to defend your submission data? Visit our article on documenting AI/ML algorithms, or reach out to us here! #GMLP #FDA #DataIntegrity #AIValidation #MedicalAI #RegulatoryTech #AIinHealthcare