Best and efficient way for data validation in cleansing data ?

Hi VoltesDev ,
Thanks for posting your question in Microsoft Fabric Community forum.
When adopting the Medallion architecture and moving data from Bronze to Silver, data validation is a critical step to ensure data quality and reliability. Below is a step-by-step approach to efficiently handle validation, including the use of column profiling.

Use Column Profiling for Initial Analysis

Column profiling in Microsoft Fabric Data Flows is a helpful tool to get a clear picture of your data and spot major issues before setting up validation rules.

For example:

• You can check how many values are missing (null counts), see how data is distributed, find duplicates, or catch unusual values  in key fields like Vendor ID, Product ID, and Currency.
    
  

  In the above images we can see Valid, Error and Empty.
  
  

  Then you can use the methods like Flagging and Logging Invalid records to apply Validation in Bronze to Silver Transformation.
  Flagging records which are valid and invalid is a solid approach. It allows

     Transparency: Invalid fields are marked but can remain accessible for further analysis.
     Traceability: Enables to identify issues without needing to re-run validations.

     Flexibility: Keeps the data pipeline moving without halting the entire process.

  Enhancements to This Approach:

  • Column-Level Flags: Instead of just flagging the entire record as "valid" or "invalid," you can add separate flags for each key column (e.g., valid_vendor_id, valid_product_id). This makes it easier to pinpoint issues and take necessary action.

  • Valid State Metadata: You can create a structured metadata table to track the validation status of individual records or columns. This helps avoid repeatedly checking the same data . It’s like having a clear to-do list for your data!

  Logging records:
  

  Instead of relying on someone to manually check the log table and take action, you can automate the process:

  • Automated Reprocessing: When missing or corrected data (like a Vendor ID) becomes available, set up an automatic system to revalidate flagged records and mark them as "valid."

  • Hold or Skip Invalid Rows: Instead of stopping the entire import process, you can temporarily flag and skip invalid rows, letting the valid data continue. The flagged rows can then be reprocessed once the issues are resolved.

  • Automated Notifications: Set up automatic alerts to notify the right teams when there’s invalid data that needs attention, so they can fix it quickly and keep things moving smoothly.

   

  For more insights into enhancing your data validation practices, refer to Semantic Link - Data Validation Using Great Expectations.
  If younneed any further assistance please reach out.
  If this post helps please accept as solution to help find others easily and a kudos would be appreciated.
  
  Thank you.