This document provides an overview of the two main Google Cloud Platform (GCP) services leveraged in this project: BigQuery and Dataplex. These services work together to enable efficient data storage, processing, and quality monitoring.
- BigQuery is GCP's fully managed, serverless data warehouse designed for fast SQL-based analysis on large datasets. It is used to store, organize, and process data ingested from various source systems. BigQuery serves as the central data repository for this project.
- Dataplex is GCP's intelligent data fabric solution, which allows for unified data management, governance, and quality control across distributed data. In this project, Dataplex is responsible for the execution of data quality rules, ensuring the consistency and reliability of data processed and stored in BigQuery.
Project Hierarchy
The project structure within GCP has been organized to ensure clear separation between domains and environments, while supporting an efficient data ingestion and data quality validation process.
Domain-specific Projects
For each domain within the Data Quality Monitoring Tool (DQMT), a dedicated GCP project exists for every environment:
Development (dev)
Testing (test)
Pre-Production (ppd)
Production (prod)
Each project serves as the location where:
- The data is ingested from source systems.
- The initial views used for data processing and quality checks are created.
Domain Projects List
Domain\Environment | Development | Testing | Pre-Production | Production |
|---|---|---|---|---|
| Human Resources | prj-data-dm-hr-dev | prj-data-dm-hr-test | prj-data-dm-hr-ppd | prj-data-dm-hr-prod |
| Structure & Shared | prj-data-dm-structure-dev | prj-data-dm-structure-test | prj-data-dm-structure-ppd | prj-data-dm-structure-prod |
| Finance | prj-data-dm-finance-dev | prj-data-dm-finance-test | prj-data-dm-finance-ppd | prj-data-dm-finance-prod |
| Marketing | prj-data-dm-marketing-dev | prj-data-dm-marketing-test | prj-data-dm-marketing-ppd | prj-data-dm-marketing-prod |
| Procurement | prj-data-dm-procurement-dev | prj-data-dm-procurement-test | prj-data-dm-procurement-ppd | prj-data-dm-procurement-prod |
Data Quality & Final Views Projects
In addition to the domain-specific projects, a separate set of projects is used to:
Import the final views generated by the domain projects.
Define and execute data quality rules through Dataplex.
Environment | Project |
|---|---|
| Development | prj-data-dq-selfservice-dev |
| Testing | prj-data-dq-selfservice-test |
| Pre-Production | prj-data-dq-selfservice-ppd |
| Production | prj-data-dq-selfservice-prod |
Project Support Teams
The successful implementation and maintenance of the DQMT solution is supported by a dedicated team, each contributing with specialized skills across different areas of the project:
Name | Role | Scope |
|---|---|---|
| Ahmed Elsayed | Data Architect | Architecture and design of data pipelines and models |
| Maria João Pimenta | Data Engineer | Data ingestion, transformation, and automation |
| Ram Atirajyam | Data Engineer | Data ingestion, transformation, and automation |
| Ibrahim Mansey | Visualization Engineer | Data visualization and dashboard development |
| Mohamed Hazem | Visualization Engineer | Data visualization and dashboard development |
| Rawan Shehab | Functional Analyst | Business analysis and functional requirements |
Project Access and Service Accounts
Google Groups
The following Google Groups have access to the DQMT GCP projects, organized by role:
Group | Purpose | |
|---|---|---|
| Data Architects Group | Access for Data Architects | gcp-da-prj-data-dq-selfservice-nonprod@solvay.com |
| Data Engineers Group | Access for Data Engineers | gcp-de-prj-data-dq-selfservice-nonprod@solvay.com |
| Data Analysts / Business Analysts | No specific group | — |
| Data Developers Group | Access for Data Developers | gcp-dv-prj-data-dq-selfservice@solvay.com |
Note: No new Google Groups were specifically created for this project.
Service Accounts
The following Service Accounts are used within the DQMT project for process automation and integration:
Service Account | Description |
|---|---|
| sbs-is-appli-qlikview.support@solvay.com | QlikView integration and support |
| sa-talend@prj-data-dq-selfservice-dev.iam.gserviceaccount.com | Talend jobs execution |
| sa-cloudfunction@prj-data-dq-selfservice-dev.iam.gserviceaccount.com | Cloud Functions automation |
BigQuery
Projects, Datasets, Tables and Views
Domain Projects
Projects | View | Source Tables/Views |
| prj-data-dm-hr-[env] | DS_prj_dqkpi.V_BusinessUnit | ODS.ODS_SFC_0000_F001_F_W_FOBusinessUnit |
| DS_prj_dqkpi.V_CcHrFin | ODS.ODS_SFC_0000_F001_F_W_FOCostCenter ODS.ODS_BWH_0000_F001_F_W_BW_QRY_C_COSTCTR_0001 | |
| DS_prj_dqkpi.V_Company | ODS.ODS_SFC_0000_F001_F_W_FOCompany | |
| DS_prj_dqkpi.V_CostCenter | ODS.ODS_SFC_0000_F001_F_W_FOCostCenter | |
| DS_prj_dqkpi.V_EmpBusiness | DS_prj_dqkpi.V_EmpJob DS_prj_dqkpi.V_BusinessUnit | |
| DS_prj_dqkpi.V_EmpCompensation | ODS.ODS_SFC_0000_F001_F_W_EmpCompensation DS_prj_dqkpi.V_PickListValueV2 | |
| DS_prj_dqkpi.V_EmpCompensationJob | DS_prj_dqkpi.V_EmpCompensation DS_prj_dqkpi.V_EmpJob | |
| DS_prj_dqkpi.V_EmpJob | DS_prj_dqkpi.V_PickListValueV2 DS_prj_dqkpi.V_FOLocation DS_prj_dqkpi.V_EmpJobCoded | |
| DS_prj_dqkpi.V_EmpJobCoded | ODS.ODS_SFC_0000_F001_F_D_EmpJob | |
| DS_prj_dqkpi.V_EmpJob_CC | DS_prj_dqkpi.V_EmpJob DS_prj_dqkpi.V_CostCenter | |
| DS_prj_dqkpi.V_EmpJob_CompensationGroupSumCalc | ODS.ODS_SFC_0000_F001_F_W_empCompensationGroupSumCalculatedNav DS_prj_dqkpi.V_EmpJob | |
| DS_prj_dqkpi.V_EmpJob_LocGroup | DS_prj_dqkpi.V_EmpJob DS_prj_dqkpi.V_LocationGroup | |
| DS_prj_dqkpi.V_EmpJob_Location | DS_prj_dqkpi.V_EmpJob DS_prj_dqkpi.V_FOLocation | |
| DS_prj_dqkpi.V_FOLocation | ODS.ODS_SFC_0000_F001_F_W_FOLocation | |
| DS_prj_dqkpi.V_LocationGroup | ODS.ODS_SFC_0000_F001_F_W_FOLocationGroup | |
| DS_prj_dqkpi.V_PickListValueV2 | ODS.ODS_SFC_0000_F001_F_W_PickListValueV2 | |
| DS_prj_dqkpi.V_Position | ODS.ODS_SFC_0000_F001_F_W_Position | |
| DS_prj_dqkpi.V_PositionJobInfo | DS_prj_dqkpi.V_EmpJob | |
| prj-data-dm-structure-[env] | DS_prj_sls_dataquality_kpi.V_PF1 | ODS.ODS_SPF_0000_F001_F_W_T001W |
| DS_prj_sls_dataquality_kpi.V_PF1_MVKE_T001W_TVKOT_T001K | ODS.ODS_SPF_0000_F001_F_W_MVKE ODS.ODS_SPF_0000_F001_F_W_T001W ODS.ODS_SPF_0000_F001_F_W_TVKOT | |
| DS_prj_sls_dataquality_kpi.V_PF1_MVKE_T001W_TVKOT_T001K_U | DS_prj_sls_dataquality_kpi.V_PF1_MVKE_T001W_TVKOT_T001K | |
| DS_prj_sls_dataquality_kpi.V_WP1 | ODS.ODS_SPW_0000_F001_F_W_T001W ODS.ODS_SPW_0000_F001_F_W_T001K | |
| DS_prj_sls_dataquality_kpi.V_WP1_MVKE_T001W_TVKOT_T001K | ODS.ODS_SPW_0000_F001_F_W_MVKE ODS.ODS_SPW_0000_F001_F_W_T001W ODS.ODS_SPW_0000_F001_F_W_TVKOT | |
| DS_prj_sls_dataquality_kpi.V_WP1_MVKE_T001W_TVKOT_T001K_U | DS_prj_sls_dataquality_kpi.V_WP1_MVKE_T001W_TVKOT_T001K | |
| prj-data-dm-finance-[env] | DS_prj_sls_dataquality_kpi.V_COSTCTR_0001 | ODS.ODS_BWH_0000_F001_F_W_BW_QRY_C_COSTCTR_0001 |
| prj-data-dm-marketing-[env] | DS_prj_sls_dataquality_kpi.V_PF1_KNA1 | ODS.ODS_SPF_0000_F001_F_W_KNA1 |
| DS_prj_sls_dataquality_kpi.V_WP1_KNA1 | ODS.ODS_SPW_0000_F001_F_W_KNA1 | |
| prj-data-dm-procurement-[env] | DS_sls_dataquality_kpi.V_PF1_MARC | ODS.ODS_SPF_0000_F001_F_W_MARC |
| DS_sls_dataquality_kpi.V_WP1_MARC | ODS.ODS_SPW_0000_F001_F_W_MARC |
[env] is one of the following: dev, test, ppd, prod
Selfservice Project
| Project | Table/View | Source Tables/Views |
| prj-data-dm-selfservice-[env] | DM.DIM_date | - |
DM.DIM_domain | - | |
DM.DIM_kpi_dimension | - | |
DM.DIM_quality_rule | - | |
DM.Dataplex_quality | - | |
DM.FACT_data_quality | - | |
DM.FACT_failed_records | - | |
DPL.V_DIM_DATE | prj-data-dm-selfservice-[env].DM.DIM_date | |
DPL.V_DIM_DOMAIN | prj-data-dm-selfservice-[env].DM.DIM_domain | |
DPL.V_DIM_KPI_DIMENSION | prj-data-dm-selfservice-[env].DM.DIM_kpi_dimension | |
DPL.V_FACT_QUALITY | prj-data-dm-selfservice-[env].DM.FACT_data_quality | |
DPL.V_RULE_QUALITY | prj-data-dm-selfservice-[env].DM.DIM_quality_rule | |
DataOcean_dataquality_kpi.V_ActiveEmployeeInActiveLegalEntity | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob prj-data-dm-hr-[env].DS_prj_dqkpi.V_Company | |
DataOcean_dataquality_kpi.V_COSTCTR_0001 | prj-data-dm-finance-[env].DS_prj_sls_dataquality_kpi.V_COSTCTR_0001 | |
DataOcean_dataquality_kpi.V_COSTCTR_0001_ProfitCenter | prj-data-dm-finance-[env].DS_prj_sls_dataquality_kpi.V_COSTCTR_0001 | |
DataOcean_dataquality_kpi.V_EmpBusiness | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpBusiness | |
DataOcean_dataquality_kpi.V_EmpCompensationJob | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpCompensationJob | |
DataOcean_dataquality_kpi.V_EmpJob_CC | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob_CC | |
DataOcean_dataquality_kpi.V_EmpJob_Cadres | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob | |
DataOcean_dataquality_kpi.V_EmpJob_Compensation_AIS | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob_CompensationGroupSumCalc | |
DataOcean_dataquality_kpi.V_EmpJob_Compensation_AIS_ANSG | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob_CompensationGroupSumCalc | |
DataOcean_dataquality_kpi.V_EmpJob_Compensation_ANSG | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob_CompensationGroupSumCalc | |
DataOcean_dataquality_kpi.V_EmpJob_External_Pay | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob | |
DataOcean_dataquality_kpi.V_EmpJob_External_Position | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob | |
DataOcean_dataquality_kpi.V_EmpJob_LocGroup | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob_LocGroup | |
DataOcean_dataquality_kpi.V_EmpJob_Location | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob_Location | |
DataOcean_dataquality_kpi.V_EmpJob_Supervisor | prj-data-dm-hr-[env].DS_prj_dqkpi.V_EmpJob | |
DataOcean_dataquality_kpi.V_FOLocation | prj-data-dm-hr-[env].DS_prj_dqkpi.V_FOLocation | |
DataOcean_dataquality_kpi.V_PF1 | prj-data-dm-structure-[env].DS_prj_sls_dataquality_kpi.V_PF1 | |
DataOcean_dataquality_kpi.V_PF1_KNA1 | prj-data-dm-marketing-[env].DS_prj_sls_dataquality_kpi.V_PF1_KNA1 | |
DataOcean_dataquality_kpi.V_PF1_MARC | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_proc_PF1_MARC prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1 | |
DataOcean_dataquality_kpi.V_PF1_MVKE_T001W_TVKOT_T001K_U | prj-data-dm-structure-[env].DS_prj_sls_dataquality_kpi.V_PF1_MVKE_T001W_TVKOT_T001K_U | |
DataOcean_dataquality_kpi.V_PositionJobInfo | prj-data-dm-hr-[env].DS_prj_dqkpi.V_PositionJobInfo | |
DataOcean_dataquality_kpi.V_PositionJobInfo_BU | prj-data-dm-hr-[env].DS_prj_dqkpi.V_PositionJobInfo | |
DataOcean_dataquality_kpi.V_PositionJobInfo_CC | prj-data-dm-hr-[env].DS_prj_dqkpi.V_PositionJobInfo | |
DataOcean_dataquality_kpi.V_WP1 | prj-data-dm-structure-[env].DS_prj_sls_dataquality_kpi.V_WP1 | |
DataOcean_dataquality_kpi.V_WP1_KNA1 | prj-data-dm-marketing-[env].DS_prj_sls_dataquality_kpi.V_WP1_KNA1 | |
DataOcean_dataquality_kpi.V_WP1_MARC | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_proc_WP1_MARC prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1 | |
DataOcean_dataquality_kpi.V_WP1_MVKE_T001W_TVKOT_T001K_U | prj-data-dm-structure-[env].DS_prj_sls_dataquality_kpi.V_WP1_MVKE_T001W_TVKOT_T001K_U | |
DataOcean_dataquality_kpi.V_proc_PF1_MARC | prj-data-dm-procurement-[env].DS_sls_dataquality_kpi.V_PF1_MAR | |
DataOcean_dataquality_kpi.V_proc_WP1_MARC | prj-data-dm-procurement-[env].DS_sls_dataquality_kpi.V_WP1_MARC |
[env] is one of the following: dev, test, ppd, prod
Routines
| Project | Routine | Output |
| prj-data-dm-selfservice-[env] | DM.insert_DIM_Domain | Populates the Data Model Dimension Table DM.DIM_domain |
| DM.insert_DIM_kpi_dimension | Populates the Data Model Dimension Table DM.DIM_kpi_dimension | |
| DM.RT_DPtoDMmapping_Datespecific | Populates the Data Model Fact Tables: |
[env] is one of the following: dev, test, ppd, prod
Dataplex
Data Profile Scan
| Data Profile Scan | Scanned Table |
| V_COSTCTR_0001-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001 |
| V_COSTCTR_0001_PROFITCENTER-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001_ProfitCenter |
| V_EMPBUSINESS-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpBusiness |
| V_POSITIONJOBINFO_BU-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PositionJobInfo_BU |
| V_EMPJOB_COMPENSATION_AIS-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Compensation_AIS |
| V_EMPJOB_COMPENSATION_ANSG-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Compensation_ANSG |
| V_EMPCOMPENSATIONJOB-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpCompensationJob |
| V_EMPJOB_CADRES-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Cadres |
| V_EMPJOB_CC-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_CC |
| V_POSITIONJOBINFO_CC-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PositionJobInfo_CC |
| V_EMPJOB_EXTERNAL_PAY-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_External_Pay |
| V_EMPJOB_EXTERNAL_POSITION-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_External_Position |
| V_POSITIONJOBINFO-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PositionJobInfo |
| V_EMPJOB_COMPENSATION_AIS_ANSG-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Compensation_AIS_ANSG |
| V_ACTIVEEMPLOYEEINACTIVELEGALENTITY-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_ActiveEmployeeInActiveLegalEntity |
| V_EMPJOB_LOCGROUP-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_LocGroup |
| V_EMPJOB_LOCATION-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Location |
| V_EMPJOB_SUPERVISOR-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Supervisor |
| V_PF1_KNA1-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1_KNA1 |
| V_WP1_KNA1-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1_KNA1 |
| V_PF1_MARC-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1_MARC |
| V_WP1_MARC-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1_MARC |
| V_PF1_MVKE_T001W_TVKOT_T001K_U-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1_MVKE_T001W_TVKOT_T001K_U |
| V_WP1_MVKE_T001W_TVKOT_T001K_U-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1_MVKE_T001W_TVKOT_T001K_U |
| V_PF1-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1 |
| V_WP1-SCAN | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1 |
[env] is one of the following: dev, test, ppd, prod
Data Quality Scans
| Data Quality Scan | Rule | Domain | KPI Dimension | Scanned Table | Scanned Column | Failed Records Query |
| Accuracy of assigning "Inactive" Cost centers to the EDISCXX node | fin-accuracy-inactive-cc-ediscxx-node | Finance | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001 | ZCBSLevel4Name | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001` WHERE NOT (ZCBSLevel4Name != "DISCONTINUED" AND ZCBSLevel4Name != ""); |
| Active CCs to L4 in the ZCBS hierarchy | fin-active-cc-l4-zcbs-hierarchy | Finance | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001 | ZCBSLevel4Code | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001` WHERE NOT (ZCBSLevel4Code != '#' OR PARSE_DATE('%d.%m.%Y', CreationDate) > (CURRENT_DATE() - INTERVAL 3 DAY)); |
| All cost centers are assigned to a BSA | fin-all-cc-assigned-bsa | Finance | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001 | BSAName | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001` WHERE NOT (BSAName != "#" OR PARSE_DATE('%d.%m.%Y', CreationDate) > (CURRENT_DATE() - INTERVAL 3 DAY)); |
| All cost centers are assigned to an active GBU Cluster | fin-cc-active-gbu-cluster | Finance | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001 | ClusterCode | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001` WHERE NOT (ClusterCode != "OUT" AND ClusterCode != " "); |
| Cost Centers with Profit Centers | fin-cc-profitcenters | Finance | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001_ProfitCenter | ProfitCenterName | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001_ProfitCenter` WHERE NOT (ProfitCenterName != "Not assigned" AND ProfitCenterName NOT LIKE "%/Not assigned"); |
| SRM7 responsible codification | fin-srm7-responsible-codification | Finance | Conformity | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001 | PositionCode | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_COSTCTR_0001` WHERE NOT ((LENGTH(PositionCode) = 8 AND PositionCode LIKE '500%')); |
| Business Unit is active | hr-business-unit-is-active | Human Resources | Conformity | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpBusiness | BusinessUnitStatus | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpBusiness` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Dormant') AND NOT(BusinessUnitStatus='A'); |
| Business Unit is the same at Position and Job Info | hr-business-unit-same-position-job-info | Human Resources | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PositionJobInfo_BU | BusinessUnitEntityGrouping | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PositionJobInfo_BU` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Inactive Leave', 'Dormant') AND NOT(BusinessUnitEntityGrouping=BusinessUnit); |
| Cadres have annual indicative salary | hr-cadres-have-annual-indicative-salary | Human Resources | Completeness | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Compensation_AIS | AnnualIndicativeSalary | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Compensation_AIS` WHERE EmployeeStatus IN ('Active', 'Active Leave') AND WorkforceCategory IN ('Cadre', 'Sales Cadre') AND NOT (AnnualIndicativeSalary IS NOT NULL); |
| Cadres have Annual Salary (9ANS) | hr-cadres-have-annual-salary-9ANS | Human Resources | Completeness | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Compensation_ANSG | AnnualSalary9ANSGGlobal | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Compensation_ANSG` WHERE EmployeeStatus IN ('Active', 'Active Leave') AND WorkforceCategory IN ('Cadre', 'Sales Cadre') AND NOT (AnnualSalary9ANSGGlobal IS NOT NULL); |
| Cadres should always have a bonus plan assigned | hr-cadres-have-bonus-plan-assigned | Human Resources | Completeness | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpCompensationJob | BonusPlan | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpCompensationJob` WHERE EmployeeStatus IN ('Active', 'Active Leave') AND WorkforceCategory IN ('Cadre', 'Sales Cadre') AND NOT (BonusPlan IS NOT NULL); |
| Cadres have Pay grade between S15 and S18 | hr-cadres-have-pay-grade-between-s15-and-s18 | Human Resources | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Cadres | PayGrade | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Cadres` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Inactive Leave', 'Dormant') AND EmploymentType IN ('M0', 'M1', 'M3', 'M4', 'M5', 'MM', 'MR', 'MS', 'MT', 'NI', 'V1', 'VL', 'VT', 'UA') AND NOT (PayGrade IN ('S15', 'S16', 'S17', 'S18')); |
| Cadres have Pay grade between S19 and S22 | hr-cadres-have-pay-grade-between-s19-and-s22 | Human Resources | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Cadres | PayGrade | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Cadres` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Inactive Leave', 'Dormant') AND EmploymentType IN ('N0', 'N1', 'N2', 'N3', 'N4', 'N5', 'N6', 'NM', 'NR', 'NS', 'NT', 'V1', 'VL', 'VT', 'UA') AND NOT (PayGrade IN ('S19', 'S20', 'S21', 'S22')); |
| Cadres have Pay grade between S23 and S27 | hr-cadres-have-pay-grade-between-s23-and-s27 | Human Resources | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Cadres | PayGrade | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Cadres` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Inactive Leave', 'Dormant') AND EmploymentType IN ('O0', 'O1', 'O2', 'O3', 'O4', 'OS', 'P0', 'P3', 'P4', 'Q0', 'Q4', 'QB', 'QC', 'V1', 'VL', 'VT', 'ZB', 'UA') AND NOT(PayGrade IN ('S23', 'S24a', 'S24b', 'S25a', 'S25b', 'S26a', 'S26b', 'S27')); |
| Cost Centre is active | hr-costcenter-is-active | Human Resources | Conformity | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_CC | Status | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_CC` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Inactive Leave', 'Dormant') AND NOT(Status = 'A'); |
| Cost Center is the same at Position and Job Info | hr-costcenter-same-position-jobinfo | Human Resources | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PositionJobInfo_CC | CostCenter | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PositionJobInfo_CC` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Inactive Leave', 'Dormant') AND NOT(CostCenter=CostCenterPosition); |
| External workforce have Pay Grade "Not applicable" | hr-external-workforce-paygrade-na | Human Resources | Accuracy | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_External_Pay | PayGrade | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_External_Pay` WHERE EmployeeStatus IN ('Active', 'Active Leave') AND WorkforceCategory IN ('Trainees & Apprentices', 'Contractor') AND NOT(PayGrade = 'N/A'); |
| External workforce have Position Grade "Not applicable" | hr-external-workforce-postion-grade-na | Human Resources | Accuracy | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_External_Position | PositionGrade | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_External_Position` WHERE EmployeeStatus IN ('Active', 'Active Leave') AND WorkforceCategory IN ('Trainees & Apprentices', 'Contractor') AND NOT(PositionGrade='N/A'); |
| Incumbents Position is active | hr-incumbent-position-is-active | Human Resources | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PositionJobInfo | PositionStatus | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PositionJobInfo` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Inactive Leave', 'Dormant') AND NOT(PositionStatus='A'); |
| Indicative Salary amount is the same as the Annual Salary (9ans) | hr-indicative-salary-amount-same-annual-salary | Human Resources | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Compensation_AIS_ANSG | AnnualIndicativeSalary | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Compensation_AIS_ANSG` WHERE EmployeeStatus IN ('Active', 'Active Leave') AND WorkforceCategory IN ('Cadre', 'Sales Cadre') AND NOT(ABS(CAST(AnnualSalary9ANSGGlobal AS FLOAT64) - CAST(AnnualIndicativeSalary AS FLOAT64)) < 2); |
| Legal Entity is active | hr-legal-entity-is-active | Human Resources | Conformity | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_ActiveEmployeeInActiveLegalEntity | CompanyStatus | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_ActiveEmployeeInActiveLegalEntity` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Inactive Leave', 'Dormant') AND NOT(CompanyStatus <> 'I' OR CompanyStatus IS NULL); |
| Location Group (site) is active | hr-location-group-is-active | Human Resources | Conformity | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_LocGroup | Status | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_LocGroup` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Inactive Leave', 'Dormant') AND NOT(Status = 'A'); |
| Location (PA/PSA) is active | hr-location-is-active | Human Resources | Conformity | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Location | Status | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Location` WHERE EmployeeStatus IN ('Active', 'Active Leave', 'Inactive Leave', 'Dormant') AND WorkforceCategory NOT IN ('Contractor', 'Trainees & Apprentices') AND NOT(Status <>'I' OR Status IS NULL); |
| Sales Cadre have SIP Bonus Plan | hr-sales-cadre-have-sip-bonus-plan | Human Resources | Accuracy | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpCompensationJob | BonusPlan | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpCompensationJob` WHERE EmployeeStatus IN ('Active', 'Active Leave') AND WorkforceCategory IN ('Sales Cadre') AND NOT (BonusPlan = 'SIP'); |
| Supervisor is assigned | hr-supervisor-is-assigned | Human Resources | Completeness | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Supervisor | SupervisorId | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_EmpJob_Supervisor` WHERE EmployeeStatus IN ('Active', 'Active Leave') AND NOT(SupervisorId <> 'NO_MANAGER'); |
| Duplicate customer PF1 | mrk-kna1-unq-rcc-duplicateusers-pf1 | Marketing & Sales | Uniqueness | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1_KNA1 | dupcusdata | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1_KNA1` WHERE LOEVM != 'X' AND dupcusdata IN ( SELECT dupcusdata FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1_KNA1` WHERE LOEVM != 'X' GROUP BY dupcusdata HAVING COUNT(dupcusdata) > 1 ); |
| Duplicate customer WP1 | mrk-kna1-unq-rcc-duplicateusers-wp1 | Marketing & Sales | Uniqueness | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1_KNA1 | dupcusdata | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1_KNA1` WHERE LOEVM != 'X' AND dupcusdata IN ( SELECT dupcusdata FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1_KNA1` GROUP BY dupcusdata HAVING COUNT(dupcusdata) > 1 ); |
| PF1 No active material codes connected to obsolete plant | ssr-activemat-obsoleteplant-pf1 | Structures & Shared | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1_MARC | PlantName | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1_MARC` WHERE MaterialStatus="" AND NOT (PlantName NOT LIKE '%*D*'); |
| WP1 No active material codes connected to obsolete plant | ssr-activemat-obsoleteplant-wp1 | Structures & Shared | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1_MARC | PlantName | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1_MARC` WHERE MaterialStatus="" AND NOT (PlantName NOT LIKE 'ZZZ%'); |
| PF1 No active materials linked to obsolete sales org | ssr-activemat-obsoletesales-pf1 | Structures & Shared | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1_MVKE_T001W_TVKOT_T001K_U | SalesOrgName | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1_MVKE_T001W_TVKOT_T001K_U` WHERE NOT(SalesOrgName NOT LIKE '%*D*'); |
| WP1 No active materials linked to obsolete sales org | ssr-activemat-obsoletesales-wp1 | Structures & Shared | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1_MVKE_T001W_TVKOT_T001K_U | SalesOrgName | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1_MVKE_T001W_TVKOT_T001K_U` WHERE NOT(SalesOrgName NOT LIKE 'ZZZ%'); |
| PF1 No active plants linked to obsolete companies | ssr-activeplants-obsoletecompanies-pf1 | Structures & Shared | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1 | CompanyName | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_PF1` WHERE PlantName NOT LIKE '%*D*' AND NOT (CompanyName NOT LIKE '%*D*'); |
| WP1 No active plants linked to obsolete companies | ssr-activeplants-obsoletecompanies-wp1 | Structures & Shared | Consistency | prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1 | CompanyName | SELECT * FROM `prj-data-dq-selfservice-[env].DataOcean_dataquality_kpi.V_WP1` WHERE PlantName NOT LIKE 'ZZZ%' AND NOT (CompanyName NOT LIKE '%*D*'); |
[env] is one of the following: dev, test, ppd, prod