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

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.

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:

Project Access and Service Accounts

Google Groups

The following Google Groups have access to the DQMT GCP projects, organized by role:

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:

BigQuery in the Data Quality Monitoring Project

Big query:

• Datasets:
  • DM
  • DPL
  • DataOcean_dataquality_kpi
  • Dataplex_profiles_scans
• Tables:
  • DM:
    
    • DIM_date
    • DIM_domain
    • DIM_kpi_dimension
    • DIM_quality_rule
    • Dataplex_quality
    • FACT_data_quality
    • FACT_failed_records
  • DPL: (Views)
  • DataOcean_dataquality_kpi: (Views)
  • Dataplex_profiles_scans:
    • EmpBusiness-scan
    • PositionJobInfo
    • businessunit
    • empJobPositionJoin
    • empLocGroup_scan
    • empcomp-scan
    • empjob_profile
    • emploc
• SQL Queries/views – Logic.
  • DM: (Tables)
  • DPL:
    • V_DIM_DATE
    • V_DIM_DOMAIN
    • V_DIM_KPI_DIMENSION
    • V_FACT_QUALITY
    • V_RULE_QUALITY
    • V_data_quality_metrics_dev
  • DataOcean_dataquality_kpi:
    • V_EmpJobRelationships
    • V_EmpWorkPermit
    • V_FOLocation
    • V_LocationGroup
    • V_PositionJobInfo
    • V_User
    • V_businessunit
    • V_company
    • V_costCenter
    • V_empJobCC
    • V_empLocGroup
    • V_emp_compensation_job
    • V_position
    • V_ActiveEmployeeInActiveLegalEntity
    • V_BusinessUnit
    • V_CcHrFin
    • V_Company
    • V_EmpBusiness
    • V_EmpCompPay
    • V_EmpCompensation
    • V_EmpJob
    • V_EmpJobCompPay
  • Dataplex_profiles_scans: (Partition Tables)

• Routines (Stored Procedures):
  • DM.RT_DPtoDMmapping_Datespecific ( It is the main mapping function in order to populate the Model according to the latest weekly runs)

  -- Populate DIM_date table
  -- Populate DIM_quality_rule table
  -- Populate FACT_data_quality table
  -- Populate FACT_failed_records table

• • RT_DPtoDMmapping_specific: (used to map specific rule in case there's an on demand run)

  -- Populate DIM_date table
  -- Populate DIM_quality_rule table
  -- Populate FACT_data_quality table
  -- Populate FACT_failed_records table

Data flow:

• • Data Quality Check: Sources → Talend → Data Ocean → prj-data-dq-selfservice-*** → DataOcean_dataquality_kpi (Data Set)→ DataOcean_dataquality_kpi.Views (source views) → Dataplex → Dataplex_quality (Table) →RT_DPtoDMmapping_specific (Stored Procedure) → DIM_dateDIM_domainDIM_kpi_dimensionDIM_quality_ruleDataplex_qualityFACT_data_qualityFACT_failed_records (DM tables) → (DPL Views) → QlikSense

ProjectName:

• prj-data-dq-selfservice-test
• prj-data-dq-selfservice-dev
• prj-data-dq-selfservice-ppd
• prj-data-dq-selfservice-prod

prj-data-dq-selfservice-prod: 

STG Schemas:

STG Schemas1:

STG Schema2:

STG Schema3:

Tablelist:

Data Ocean Schemas:

Reporting Schemas: