| TEST | STEP | COMPONENT | DESCRIPTION |
| Check source connection | Source extraction | Verify that if you have wrong credentials your job stops (try changing the username to a fake one) | |
| Verify data is loaded into the STG | Bucket to Staging | Query the "log_files" table and check how many rows have been loaded and rejects into the STG | |
| Verify data is loaded into the ODS | Staging to ODS | Query the "log_tabes" table and check how many rows have been loaded and rejects into the ODS | |
| Verify the all STG rows are inserted in the ODS | Staging to ODS | Verify that for a same meta_run_id the number of rows inserted in the STG is equal to the one of ODS. You can verify that in the log_tables . | |
| Verify surrogate key is unique | Build Data Mart | DIM SCD Type1, DIM SCD Type2 | Verify that the surrogate is unique |
| Check the soft deletes | Build Data Mart | DIM SCD Type1, DIM SCD Type2 | Verify that soft deletes work as expected (deleted_flag='Yes'). You can remove a line from the ODS and rerun the SCD job. You should then generate a delete_flag |
| Verify updates on a business key | Build Data Mart | DIM SCD Type2 | To perform this test you may need to manually modify data in ODS to simulate changes. You can modify an ODS record to show that the end date and current_flag is set to false for the existing dimension record. A new line is created with a new dim_key, end_date = '9999-12-31' and current_flag = YES |
| Dimension does not change if no change in ODS | Build Data Mart | DIM SCD Type1, DIM SCD Type2 | Run twice the SCD job (i.e. reprocessing the same file), no change should occur in the dimension table. |
| Compare dimensions and ODS rows | Build Data Mart | Dimensions and facts | If ODS is a full extraction then the dimension table should have the same number of rows than a snapshot of ODS. If you apply filters to build dimension the number will not be equal, you can justify the diffrence |
| Verify joins | Build Data Mart | If building your dims or fact involves LEFT JOIN you should have that the number of output rows is equal to the one of input. | |
| Verify surrogate keys in fact tables | Build Data Mart | FACT | If your fact tables contains surrogate keys of some dimension tables you must verify that all surrogate keys exists in the dimension table (if they are missing deep dive , it could be that you do not add them in the dimension yet) |
| Verify log tables | Logs | Verify that the log tables are correctly filled. For instance given a meta_run_id you should be able | |
| Verify columns are correctly mapped | |||
| Check on GCS that files is moved to the right bucket/folder... |
Overview
Content Tools
Tasks