Standardize data for delivery_dt column in f_order table

Introduction: The Data Standardization is the part of transition process where the engineered data is moved from one stage to other for next level of execution. This process mainly includes the conversion of datatype of final dataset into the datatype required in target table. For example - If the datatype of delivery_dt is timestamp (2024-09-10) is source system but target accepts only decimal (38,0) type (20240910).

 

Requirement: Create a SQL logic to check if delivery_dt from f_order table is 100% Decimal (38,0) and should be in the format of yyyymmdd. Output should have order_nbr, delivery_dt and validation_status (PASS or FAIL)

 

Unity Catalog Information: purgo_playground.f_order