Architecture:
Below is the high level architecture for the Data Quality KPIs monitoring tool.
Data Flow
| Sources Definition | Identify different data sources and how to ingest either raw data, or dq metrics depending on the source |
| Identified Sources | SAP BW, SuccessFactors, Big Query (Data Ocean) |
| ETL and Preparation | Talend used to load and ingest tables in BigQuery, Leveraging automation and orchestration capabilities to automate the recurring jobs. |
| Source For DQ KPIs Monitoring Project | Big Query Data Ocean is the sole source for Quality checks after tables are loaded from their original sources. |
| Data Residing in Big Query | Data residing on Big Query brought in prj-data-dq-selfservice-dev project using the necessary views to be Scanned and checked by DataPlex, the generated metrics naturally reside in Big Query (Dataplex_quality) |
| Data Quality Metrics Ingestion | Populating the BigQuery fact table with DQ metrics obtained from the previously mentioned sources By Mapping the resulting table to the developed DM (Using a stored procedure) |
| Big Query Views | Creating the views necessary to answer Business requirements, providing them with degree of flexibility in a way that they can have more control over the data the need to query according to business changing requirements, to approach selfservice. |
| Visualization | Uses QlikSense that connects to bigQuery views to ingest data, Use Qlik Sense to Visualize, present, add alerting capabilities for different business domains Different KPIs and Different Business Rules. and Failed records |
| Google Drive | Used to store failed records Data, then URL for the sheet is inserted in FACT_failed_records associated with quality_rule_key. |
Data Model
Data Mapping:
The out put of Dataplex quality checks is Dataplex_quality table, It is mapped to the Data Model "in the previous section", on Big query to later be used as the sole data source for QlikSense visualization.
Data Mapping is detailed in this document.
Procedures
Dataplex to Data Model Mapping Stored Procedure:
Procedure Name:
Scheduling:
- Scheduled Profile Scans: Runs on Dataplex and scheduled while created.
- Scheduled Data Quality Rules Scan: Runs on Dataplex and scheduled while created.
- Scheduled Stored Procedure Run: Routine is triggered using scheduled query on weekly Basis.
- Scheduled QlikSense Refresh: set by the visualization engineer on qliksense.
Time of Runs and Duration Window:
| Dataplex | 4:00 - 5:00 CET |
| BigQuery Routine | 5:00 - 6:00 CET |
| Talend | 6:00 - 9:00 CET |
| QlikSense | 9:00 CET |
Monitoring
GCP Monitoring tools:
- Dataplex Logs
- Big Query Logs
- Cloud Monitoring Dashboard
Error Handling
- Failure alert are set in rule creation to alert stakeholders/users when a rule fails.
- Stored procedure scheduling failure alert is sent in case the scheduled Routine, doesn't run as intended.
Known Bugs
No Identified Bugs.