Use auto data quality

This page describes how to create a Dataplex Universal Catalog data quality scan.

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

Before you begin

1. Enable the Dataplex API.

   Enable the API

2. Optional: If you want Dataplex Universal 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

• To run a data quality scan on a BigQuery table, you need permission to read the BigQuery table and permission to create a BigQuery job in the project used to scan the table.

• If the BigQuery table and the data quality scan are in different projects, then you need to give the Dataplex Universal Catalog service account of the project containing the data quality scan read permission for the corresponding BigQuery table.

• If the data quality rules refer to additional tables, then the scan project's service account must have read permissions on the same tables.

• To get the permissions that you need to export the scan results to a BigQuery table, ask your administrator to grant the Dataplex Universal Catalog service account the BigQuery Data Editor (roles/bigquery.dataEditor) IAM role on the results dataset and table. This grants the following permissions:

  • bigquery.datasets.get
  • bigquery.tables.create
  • bigquery.tables.get
  • bigquery.tables.getData
  • bigquery.tables.update
  • bigquery.tables.updateData

• If the BigQuery data is organized in a Dataplex Universal Catalog lake, grant the Dataplex Universal Catalog service account the Dataplex Metadata Reader (roles/dataplex.metadataReader) and Dataplex Viewer (roles/dataplex.viewer) IAM roles. Alternatively, you need all of the following permissions:

  • dataplex.lakes.list
  • dataplex.lakes.get
  • dataplex.zones.list
  • dataplex.zones.get
  • dataplex.entities.list
  • dataplex.entities.get
  • dataplex.operations.get

• If you're scanning a BigQuery external table from Cloud Storage, grant the Dataplex Universal Catalog service account the Storage Object Viewer (roles/storage.objectViewer) role for the bucket. Alternatively, assign the Dataplex Universal Catalog service account the following permissions:

  • storage.buckets.get
  • storage.objects.get

• If you want to publish the data quality scan results as Dataplex Universal Catalog metadata, you must be granted the BigQuery Data Editor (roles/bigquery.dataEditor) IAM role for the table, and the dataplex.entryGroups.useDataQualityScorecardAspect permission on the @bigquery entry group in the same location as the table. Alternatively, you must be granted the Dataplex Catalog Editor (roles/dataplex.catalogEditor) role for the @bigquery entry group in the same location as the table.

  Alternatively, you need all of the following permissions:

  • bigquery.tables.get - on the table
  • bigquery.tables.update - on the table
  • bigquery.tables.updateData - on the table
  • bigquery.tables.delete - on the table
  • dataplex.entryGroups.useDataQualityScorecardAspect - on the @bigquery entry group

  Or, you need all of the following permissions:

  • dataplex.entries.update - on the @bigquery entry group
  • dataplex.entryGroups.useDataQualityScorecardAspect - on the @bigquery entry group

• If you need to access columns protected by BigQuery column-level access policies, then assign the Dataplex Universal Catalog service account permissions for those columns. The user creating or updating a data scan also needs permissions for the columns.

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

Required data scan roles

To use auto data quality, ask your administrator to grant you one of the following IAM roles:

• Full access to DataScan resources: Dataplex DataScan Administrator (roles/dataplex.dataScanAdmin)
• To create DataScan resources: Dataplex DataScan Creator (roles/dataplex.dataScanCreator) on the project
• Write access to DataScan resources: Dataplex DataScan Editor (roles/dataplex.dataScanEditor)
• Read access to DataScan resources excluding rules and results: Dataplex DataScan Viewer (roles/dataplex.dataScanViewer)
• Read access to DataScan resources, including rules and results: Dataplex DataScan DataViewer (roles/dataplex.dataScanDataViewer)

The following table lists the DataScan permissions:

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. Dataplex Universal 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 Dataplex Universal Catalog evaluates the query SELECT COUNTIF(CUSTOM_SQL_EXPRESSION) FROM TABLE. Dataplex Universal 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 Dataplex Universal 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
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

Create a data quality scan

gcloud dataplex datascans create data-quality DATASCAN \
    --location=LOCATION \
    --data-quality-spec-file=DATA_QUALITY_SPEC_FILE \
    --data-source-entity=DATA_SOURCE_ENTITY

gcloud dataplex datascans create data-quality DATASCAN \
    --location=LOCATION \
    --data-quality-spec-file=DATA_QUALITY_SPEC_FILE \
    --data-source-resource=DATA_SOURCE_RESOURCE

Export table schema

To export the data quality scan results to an existing BigQuery table, make sure that it is compatible with the following table schema:

When you configure BigQueryExport for a data quality scan job, follow these guidelines:

• For the field resultsTable, use the format: //bigquery.googleapis.com/projects/{project-id}/datasets/{dataset-id}/tables/{table-id}.
• Use a BigQuery standard table.
• If the table doesn't exist when the scan is created or updated, Dataplex Universal Catalog creates the table for you.
• By default, the table is partitioned on the job_start_time column daily.
• If you want the table to be partitioned in other configurations or if you don't want the partition, then recreate the table with the required schema and configurations and then provide the pre-created table as the results table.
• Make sure the results table is in the same location as the source table.
• If VPC-SC is configured on the project, then the results table must be in the same VPC-SC perimeter as the source table.
• If the table is modified during the scan execution stage, then the current running job exports to the previous results table and the table change takes effect from the next scan job.
• Don't modify the table schema. If you need customized columns, create a view upon the table.
• To reduce costs, set an expiration on the partition based on your use case. For more information, see how to set the partition expiration.

Run a data quality scan

gcloud dataplex datascans run DATASCAN \
--location=LOCATION \

View the data quality scan results

gcloud dataplex datascans jobs describe JOB \
--location=LOCATION \
--datascan=DATASCAN \
--view=FULL

View published results

If the data quality scan results are published as Dataplex Universal Catalog metadata, then you can see the latest scan results on the BigQuery and Dataplex Universal Catalog pages in the Google Cloud console, on the source table's Data quality tab.

1. In the Google Cloud console, go to the Dataplex Universal Catalog Search page.

   Go to Search

2. Search for and then select the table.

3. Click the Data quality tab.

   The latest published results are displayed.

View historical scan results

Dataplex Universal Catalog saves the data quality scan history of the last 300 jobs or for the past year, whichever occurs first.

gcloud dataplex datascans jobs list \
--location=LOCATION \
--datascan=DATASCAN \

Grant access to data quality scan results

To enable the users in your organization to view the scan results, do the following:

1. In the Google Cloud console, go to the Dataplex Universal Catalog Data profiling & quality page.

   Go to Data profiling & quality

2. Click the data quality scan you want to share the results of.

3. Click the Permissions tab.

4. Do the following:

   • To grant access to a principal, click person_add Grant access. Grant the Dataplex DataScan DataViewer role to the associated principal.
   • To remove access from a principal, select the principal that you want to remove the Dataplex DataScan DataViewer role from. Click person_remove Remove access, and then confirm when prompted.

Set alerts in Cloud Logging

To set alerts for data quality failures using the logs in Cloud Logging, follow these steps:

Sample queries for setting job level or dimension level alerts

• A sample query to set alerts on overall data quality failures for a data quality scan:

  resource.type="dataplex.googleapis.com/DataScan"
  AND labels."dataplex.googleapis.com/data_scan_state"="SUCCEEDED"
  AND resource.labels.resource_container="projects/112233445566"
  AND resource.labels.datascan_id="a0-test-dec6-dq-3"
  AND NOT jsonPayload.dataQuality.passed=true
  

• A sample query to set alerts on data quality failures for a dimension (for example, uniqueness) of a given data quality scan:

  resource.type="dataplex.googleapis.com/DataScan"
  AND labels."dataplex.googleapis.com/data_scan_state"="SUCCEEDED"
  AND resource.labels.resource_container="projects/112233445566"
  AND resource.labels.datascan_id="a0-test-dec6-dq-3"
  AND jsonPayload.dataQuality.dimensionPassed.UNIQUENESS=false
  

• A sample query to set alerts on data quality failures for a table.

  • Set alerts on data quality failures for a BigQuery table that isn't organized in a Dataplex Universal Catalog lake:

    resource.type="dataplex.googleapis.com/DataScan"
    AND jsonPayload.dataSource="//bigquery.googleapis.com/projects/test-project/datasets/testdataset/table/chicago_taxi_trips"
    AND labels."dataplex.googleapis.com/data_scan_state"="SUCCEEDED"
    AND resource.labels.resource_container="projects/112233445566"
    AND NOT jsonPayload.dataQuality.passed=true
    

  • Set alerts on data quality failures for a BigQuery table that's organized in a Dataplex Universal Catalog lake:

    resource.type="dataplex.googleapis.com/DataScan"
    AND jsonPayload.dataSource="projects/test-project/datasets/testdataset/table/chicago_taxi_trips"
    AND labels."dataplex.googleapis.com/data_scan_state"="SUCCEEDED"
    AND resource.labels.resource_container="projects/112233445566"
    AND NOT jsonPayload.dataQuality.passed=true
    

Sample queries to set per rule alerts

• A sample query to set alerts on all failing data quality rules with the specified custom rule name for a data quality scan:

  resource.type="dataplex.googleapis.com/DataScan"
  AND jsonPayload.ruleName="custom-name"
  AND jsonPayload.result="FAILED"
  

• A sample query to set alerts on all failing data quality rules of a specific evaluation type for a data quality scan:

  resource.type="dataplex.googleapis.com/DataScan"
  AND jsonPayload.evalutionType="PER_ROW"
  AND jsonPayload.result="FAILED"
  

• A sample query to set alerts on all failing data quality rules for a column in the table used for a data quality scan:

  resource.type="dataplex.googleapis.com/DataScan"
  AND jsonPayload.column="CInteger"
  AND jsonPayload.result="FAILED"
  

Troubleshoot a data quality failure

For each job with row-level rules that fail, Dataplex Universal Catalog provides a query to get the failed records. Run this query to see the records that did not match your rule.

Manage data quality scans for a specific table

The steps in this document show how to manage data profile scans across your project by using the Dataplex Universal Catalog Data profiling & quality page in the Google Cloud console.

You can also create and manage data profile scans when working with a specific table. In the Google Cloud console, on the Dataplex Universal Catalog page for the table, use the Data quality tab. Do the following:

1. In the Google Cloud console, go to the Dataplex Universal Catalog Search page.

   Go to Search

   Search for and then select the table.

2. Click the Data quality tab.

3. Depending on whether the table has a data quality scan whose results are published as Dataplex Universal Catalog metadata, you can work with the table's data quality scans in the following ways:

   • Data quality scan results are published: the latest scan results are displayed on the page.

     To manage the data quality scans for this table, click Data quality scan, and then select from the following options:

     • Create new scan: create a new data quality scan. For more information, see the Create a data quality scan section of this document. When you create a scan from a table's details page, the table is preselected.

     • Run now: run the scan.

     • Edit scan configuration: edit settings including the display name, filters, and schedule.

       To edit the data quality rules, on the Data quality tab, click the Rules tab. Click Modify rules. Update the rules and then click Save.

     • Manage scan permissions: control who can access the scan results. For more information, see the Grant access to data quality scan results section of this document.

     • View historical results: view detailed information about previous data quality scan jobs. For more information, see the View data quality scan results and View historical scan results sections of this document.

     • View all scans: view a list of data quality scans that apply to this table.

   • Data quality scan results aren't published: select from the following options:

     • Create data quality scan: create a new data quality scan. For more information, see the Create a data quality scan section of this document. When you create a scan from a table's details page, the table is preselected.

     • View existing scans: view a list of data quality scans that apply to this table.

Update a data quality scan

You can edit various settings for an existing data quality scan, such as the display name, filters, schedule, and data quality rules.

gcloud dataplex datascans update data-quality DATASCAN \
--location=LOCATION \
--description=DESCRIPTION

Delete a data quality scan

gcloud dataplex datascans delete DATASCAN \
--location=LOCATION \
--async

What's next?

• Learn about data profiling.
• Learn how to use data profiling.
• Follow a tutorial to manage data quality rules as code with Terraform.