Use auto data quality

This document describes how to use Knowledge Catalog (formerly Dataplex Universal Catalog) data quality scans to measure, monitor, and manage the quality of your data. Data quality scans help you automate the process of validating your data for completeness, validity, and consistency.

With data quality scans, you can define rules to check for missing values, ensure values match a regular expression or belong to a set, verify uniqueness, or use custom SQL for more complex validations such as anomaly detection. This document explains how to create and manage data quality scans.

To learn more about data quality scans, see About auto data quality .

Before you begin

1. Enable the Dataplex API.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role ( roles/serviceusage.serviceUsageAdmin ), which contains the serviceusage.services.enable permission. Learn how to grant roles .

   Enable the API

2. Optional: If you want Knowledge Catalog to generate recommendations for data quality rules based on the results of a data profile scan, create and run the data profile scan .

Required roles and permissions

This section describes the IAM roles and permissions needed to use Knowledge Catalog data quality scans.

User roles and permissions

To get the permissions that you need to run and manage data quality scans, ask your administrator to grant you the following IAM roles:

• Run a data quality scan on a BigQuery table:
  • BigQuery Job User ( roles/bigquery.jobUser ) on the project to run scan jobs
  • BigQuery Data Viewer ( roles/bigquery.dataViewer ) on the BigQuery table to be scanned
• Publish data quality scan results to Knowledge Catalog:
  • BigQuery Data Editor ( roles/bigquery.dataEditor ) on the scanned table
  • Dataplex Catalog Editor ( roles/dataplex.catalogEditor ) on the @bigquery entry group in the same location as the table
• Perform specific tasks on DataScan resources:
  • Dataplex DataScan Administrator ( roles/dataplex.dataScanAdmin ) on the project for full access
  • Dataplex DataScan Creator ( roles/dataplex.dataScanCreator ) on the project to create scans
  • Dataplex DataScan Editor ( roles/dataplex.dataScanEditor ) on the project for write access
  • Dataplex DataScan Viewer ( roles/dataplex.dataScanViewer ) on the project to read scan metadata
  • Dataplex DataScan DataViewer ( roles/dataplex.dataScanDataViewer ) on the project to read scan data including rules and results

For more information about granting roles, see Manage access to projects, folders, and organizations .

These predefined roles contain the permissions required to run and manage data quality scans. To see the exact permissions that are required, expand the Required permissionssection:

You might also be able to get these permissions with custom roles or other predefined roles .

If you need to access columns protected by BigQuery column-level access policies, then you also need permissions for those columns.

Knowledge Catalog service account roles and permissions

If you haven't created any data quality or data profile scans or you don't have a Knowledge Catalog lake in this project, create a service identifier by running: gcloud beta services identity create --service=dataplex.googleapis.com . This command returns a Knowledge Catalog service identifier if it exists.

To ensure that the Knowledge Catalog service account of the project containing the data quality scan has the necessary permissions to read data from various sources and export results, ask your administrator to grant the following IAM roles to the Knowledge Catalog service account of the project containing the data quality scan:

• Read BigQuery table data: BigQuery Data Viewer ( roles/bigquery.dataViewer ) on BigQuery tables to be scanned and any other tables referenced in rules
• Export scan results to a BigQuery table: BigQuery Data Editor ( roles/bigquery.dataEditor ) on the results dataset and table
• Scan BigQuery data organized in a Knowledge Catalog lake:
  • Dataplex Metadata Reader ( roles/dataplex.metadataReader ) on Dataplex resources
  • Dataplex Viewer ( roles/dataplex.viewer ) on Dataplex resources
• Scan a BigQuery external table from Cloud Storage: Storage Object Viewer ( roles/storage.objectViewer ) on the Cloud Storage bucket

For more information about granting roles, see Manage access to projects, folders, and organizations .

These predefined roles contain the permissions required to read data from various sources and export results. To see the exact permissions that are required, expand the Required permissionssection:

Your administrator might also be able to give the Knowledge Catalog service account of the project containing the data quality scan these permissions with custom roles or other predefined roles .

If you need to access columns protected by BigQuery column-level access policies, then assign the Knowledge Catalog service account permissions for those columns.

If a table has BigQuery row-level access policies enabled, then you can only scan rows visible to the Knowledge Catalog service account. Note that the individual user's access privileges are not evaluated for row-level policies.

Define data quality rules

You can define data quality rules by using built-in rules or custom SQL checks . If you're using the Google Cloud CLI, you can define these rules in a JSON or YAML file .

The examples in the following sections show how to define a variety of data quality rules. The rules validate a sample table that contains data about customer transactions. Assume the table has the following schema:

Define data quality rules using built-in rule types

The following example rules are based on built-in rule types. You can create rules based on built-in rule types using the Google Cloud console or the API. Knowledge Catalog might recommend some of these rules.

Define data quality rules using custom SQL rules

To build custom SQL rules, use the following framework:

• When you create a rule that evaluates one row at a time, create an expression that generates the number of successful rows when Knowledge Catalog evaluates the query SELECT COUNTIF( CUSTOM_SQL_EXPRESSION ) FROM TABLE . Knowledge Catalog checks the number of successful rows against the threshold.

• When you create a rule that evaluates across the rows or uses a table condition, create an expression that returns success or failure when Knowledge Catalog evaluates the query SELECT IF( CUSTOM_SQL_EXPRESSION ) FROM TABLE .

• When you create a rule that evaluates the invalid state of a dataset, provide a statement that returns invalid rows. If any rows are returned, the rule fails. Omit the trailing semicolon from the SQL statement.

• You can refer to a data source table and all of its precondition filters by using the data reference parameter ${data()} in a rule, instead of explicitly mentioning the source table and its filters. Examples of precondition filters include row filters, sampling percents, and incremental filters. The ${data()} parameter is case-sensitive.

The following example rules are based on custom SQL rules.

CREATE OR REPLACE FUNCTION
        myProject.myDataset.average_by_country (
          country STRING, average FLOAT64)
        RETURNS BOOL LANGUAGE js AS R"""
        if (country = "CAN" && average < 5000){
          return 1
        } else if (country = "IND" && average < 1000){
          return 1
        } else { return 0 }
        """;

myProject.myDataset.average_by_country(
        "CAN",
        (SELECT avg(amount) FROM
          myProject.myDataset.transactions_table
            WHERE currency_id = 'CAN'
        ))

CREATE MODEL
  model-project-id.dataset-id.model-name
        OPTIONS(model_type='logistic_reg') AS
  SELECT
  IF(discount_pct IS NULL, 0, 1) AS label,
  IFNULL(customer_id, "") AS customer,
  IFNULL(currency_id, "") AS currency,
  IFNULL(amount, 0.0) AS amount
  FROM
  `data-project-id.dataset-id.table-names`
  WHERE transaction_timestamp < '2022-01-01';

SELECT
        accuracy > 0.99
      FROM
       ML.EVALUATE
        (MODEL model-project-id.dataset-id.model-name,
         (
          SELECT
            customer_id,
            currency_id,
            amount,
            discount_pct
          FROM
            data-project-id.dataset-id.table-names
          WHERE transaction_timestamp > '2022-01-01';
         )
        )

CREATE MODEL
  model-project-id.dataset-id.model-name
        OPTIONS(model_type='logistic_reg') AS
  SELECT
  IF(discount_pct IS NULL, 0, 1) AS label,
  IFNULL(customer_id, "") AS customer,
  IFNULL(currency_id, "") AS currency,
  IFNULL(amount, 0.0) AS amount
  FROM
  `data-project-id.dataset-id.table-names`
  WHERE transaction_timestamp < '2022-01-01';

IF(discount_pct > 0, 1, 0)
          =(SELECT predicted_label FROM
           ML.PREDICT(
            MODEL model-project-id.dataset-id.model-name,
              (
                SELECT
                  customer_id,
                  currency_id,
                  amount,
                  discount_pct
                FROM
                  data-project-id.dataset-id.table-names AS t
                    WHERE t.transaction_timestamp =
                     transaction_timestamp
                   LIMIT 1
              )
            )
         )

Define data quality rules using the gcloud CLI

The following example YAML file uses some of the same rules as the sample rules using built-in types and the sample custom SQL rules . This YAML file also contains other specifications for the data quality scan, such as filters and sampling percent. When you use the gcloud CLI to create or update a data quality scan, you can use a YAML file like this as input to the --data-quality-spec-file argument.

   rules 
 : 
 - 
  
 uniquenessExpectation 
 : 
  
 {} 
  
 column 
 : 
  
 transaction_id 
  
 dimension 
 : 
  
 UNIQUENESS 
 - 
  
 nonNullExpectation 
 : 
  
 {} 
  
 column 
 : 
  
 amount 
  
 dimension 
 : 
  
 COMPLETENESS 
  
 threshold 
 : 
  
 1 
 - 
  
 regexExpectation 
 : 
  
 regex 
 : 
  
 '^[0-9]{8}[a-zA-Z]{16}$' 
  
 column 
 : 
  
 customer_id 
  
 ignoreNull 
 : 
  
 true 
  
 dimension 
 : 
  
 VALIDITY 
  
 threshold 
 : 
  
 1 
 - 
  
 setExpectation 
 : 
  
 values 
 : 
  
 - 
  
 'USD' 
  
 - 
  
 'JPY' 
  
 - 
  
 'INR' 
  
 - 
  
 'GBP' 
  
 - 
  
 'CAN' 
  
 column 
 : 
  
 currency_id 
  
 ignoreNull 
 : 
  
 true 
  
 dimension 
 : 
  
 VALIDITY 
  
 threshold 
 : 
  
 1 
 - 
  
 rangeExpectation 
 : 
  
 minValue 
 : 
  
 '0' 
  
 maxValue 
 : 
  
 '100' 
  
 column 
 : 
  
 discount_pct 
  
 ignoreNull 
 : 
  
 true 
  
 dimension 
 : 
  
 VALIDITY 
  
 threshold 
 : 
  
 1 
 - 
  
 rowConditionExpectation 
 : 
  
 sqlExpression 
 : 
  
 0 < `discount_pct` AND `discount_pct` < 100 
  
 column 
 : 
  
 discount_pct 
  
 dimension 
 : 
  
 VALIDITY 
  
 threshold 
 : 
  
 1 
 - 
  
 rowConditionExpectation 
 : 
  
 sqlExpression 
 : 
  
 currency_id in (select id from `my_project_id.dim_dataset.dim_currency`) 
  
 column 
 : 
  
 currency_id 
  
 dimension 
 : 
  
 VALIDITY 
  
 threshold 
 : 
  
 1 
 - 
  
 tableConditionExpectation 
 : 
  
 sqlExpression 
 : 
  
 30 < avg(discount_pct) AND avg(discount_pct) < 50 
  
 dimension 
 : 
  
 VALIDITY 
 - 
  
 rowConditionExpectation 
 : 
  
 sqlExpression 
 : 
  
 TIMESTAMP(transaction_timestamp) < CURRENT_TIMESTAMP() 
  
 column 
 : 
  
 transaction_timestamp 
  
 dimension 
 : 
  
 VALIDITY 
  
 threshold 
 : 
  
 1 
 - 
  
 sqlAssertion 
 : 
  
 sqlStatement 
 : 
  
 SELECT * FROM `my_project_id.dim_dataset.dim_currency` WHERE discount_pct > 100 
  
 dimension 
 : 
  
 VALIDITY 
  
 debugQueries 
 : 
  
 - 
  
 sqlStatement 
 : 
  
 SELECT MAX(discount_pct) FROM `my_project_id.dim_dataset.dim_currency` 
 samplingPercent 
 : 
  
 50 
 rowFilter 
 : 
  
 discount_pct > 100 
 postScanActions 
 : 
  
 bigqueryExport 
 : 
  
 resultsTable 
 : 
  
 projects/my_project_id/datasets/dim_dataset/tables/dim_currency 
  
 notificationReport 
 : 
  
 recipients 
 : 
  
 emails 
 : 
  
 - 
  
 '222larabrown@gmail.com' 
  
 - 
  
 'cloudysanfrancisco@gmail.com' 
  
 scoreThresholdTrigger 
 : 
  
 scoreThreshold 
 : 
  
 50 
  
 jobFailureTrigger 
 : 
  
 {} 
  
 jobEndTrigger 
 : 
  
 {} 
 catalogPublishingEnabled 
 : 
  
 true 
 

Configure execution identity

By default, data quality scans run using the Knowledge Catalog service account. You can override this to use a custom service account or your own End-User Credentials (EUC).

Using a custom execution identity changes how you are billed for the scan. When you specify a custom execution identity, the compute and storage costs associated with the scan are billed directly to your BigQuery project, bypassing the standard Knowledge Catalog Premium SKUs.

Required permissions for custom execution identities

To configure a custom service account or use end-user credentials, you must have the following additional IAM permissions:

• To use a custom service account, you need:
  • The iam.serviceAccounts.actAs permission granted for the project that contains the service account (for example, roles/iam.serviceAccountUser ).
  • Your project's Service Agent ( service-PROJECT_NUMBER@gcp-sa-dataplex.iam.gserviceaccount.com ) needs the iam.serviceAccounts.getAccessToken permission on the custom service account (for example, by having the roles/iam.serviceAccountTokenCreator role).
  • The custom service account needs bigquery.tables.getData on the table to scan, bigquery.jobs.insert in the scan project, and bigquery.dataEditor on the export dataset (if using export).
• To use End-User Credentials, you need:
  • bigquery.tables.getData on the table to scan.
  • bigquery.jobs.insert in the scan project.
  • bigquery.dataEditor on the export dataset (if using export).

Console

To configure the execution identity in the Google Cloud console, select the identity when you create your data quality scan .

1. In the Execution Identitysection, select one of the following:
   • Dataplex Service Agent: The default behavior.
   • Specific Service Account: Enter the email address of the service account you want to use.
   • User Credentials: Use your own credentials to run the scan.