Auto data quality overview Stay organized with collections Save and categorize content based on your preferences.

Knowledge Catalog (formerly Dataplex Universal Catalog) lets you define and measure the quality of the data in your BigQuery and Iceberg REST Catalog tables. You can automate the data scanning, validate data against defined rules, and log alerts if your data doesn't meet quality requirements. Auto data quality lets you manage data quality rules and deployments as code, improving the integrity of data production pipelines.

To scan data for anomalies, see Knowledge Catalog data profile scan. The scan can generate data quality rules. You can also use built-in quality rules or build custom rules.

Knowledge Catalog provides monitoring, troubleshooting, and Cloud Logging alerting that's integrated with auto data quality.

Conceptual model

[A data quality scan applies quality rules to table data to report results.]

A data quality scan is a type of Knowledge Catalog data scan that validates your data against a set of built-in rules. A data scan is a Knowledge Catalog job that samples data from BigQuery and Cloud Storage (through BigQuery external tables) and infers various types of metadata. To measure the quality of a table using auto data quality, you create a DataScan object of type data quality. The scan runs on only one BigQuery table. The scan uses resources in a Google tenant project, so you don't need to set up your own infrastructure.

Creating and using a data quality scan consists of the following steps:

1. Define data quality rules
2. Configure rule execution
3. Analyze data quality scan results
4. Set up monitoring and alerting
5. Troubleshoot data quality failures

Rule definition

Data quality rules associated with a data quality scan define data expectations. You can create data quality rules in the following ways:

• Use recommendations from Knowledge Catalog data profiling
• Use the built-in rules or system rule templates
• Create custom SQL rules
• Reuse data quality rules

Built-in rules

Knowledge Catalog supports the following categories of built-in rules:

Row-level

For row-level category rules, the expectation is applied against each data row. Each row independently passes or fails the condition. For example, column_A_value < 1.

Row-level checks require you to specify a passing threshold. When the percentage of rows passing the rule falls below the threshold value, the rule fails.

Aggregate

For aggregate rules, the expectation is applied against a single value aggregated across the entire data. For example, Avg(someCol) >= 10. To pass, the check must evaluate to the boolean true. Aggregate rules don't provide an independent pass or fail count for each row.

For both of the rule categories, you can set the following parameters:

• The column to which the rule applies
• A dimension

The following table lists the supported row-level and aggregate rule types:

Supported custom SQL rule types

SQL rules provide flexibility to expand the validation with custom logic. These rules come in the following types.

For example rules, see auto data quality sample rules.

For SQL functions supported, see GoogleSQL reference.

Reuse data quality rules

You can reuse Knowledge Catalog data quality rules to share complex or standardized business rule definitions across multiple data quality rules by using rule templates. For example, you can create a rule template for an email validation or for foreign key validation between two tables, and then reuse those templates across your data scans.

Rule reusability provides the following key features:

• Data quality rule templates: Create custom rule templates to store complex or standardized business rule definitions that can be shared across multiple data quality rules. Create a data-quality-rule-template entry and add a data-quality-rule-template aspect to it to define the template logic.
• Data rules as metadata: Declare data quality rules as aspects in Knowledge Catalog on entries such as BigQuery tables or business glossary terms. Use the data-rules aspect type to attach these rules to entries.
• System rule templates: Use system rule templates for commonly used rules.

For more information, see Reuse data quality rules.

Dimensions

Dimensions let you aggregate the results of multiple data quality rules for monitoring and alerting. You must associate every data quality rule with a dimension. Knowledge Catalog provides the following dimensions:

Freshness

Freshness measures when the data was last updated. Having this information can help you determine whether the data is recent enough to be useful.

Volume

Volume measures whether all of the expected data is present.

Completeness

Completeness assesses whether the data contains all of the information that's required for its intended purpose.

Validity

Validity evaluates whether the data conforms to built-in standards for format, acceptable ranges, or other criteria. For example, if a valid date needs to have the format YYYY/mm/dd, then 08-12-2019 is invalid data. As another example, if a valid sales price for an item is between $10 and $20, then a sales price of $100 is invalid data.

Consistency

Consistency refers to having the same values for data across multiple instances, such as tables and columns. Inconsistency in data arises when, for example, the revenue of a product differs when it is read from a sales database or a usage database.

Accuracy

Accuracy reflects the correctness of the data. Note that data that is valid isn't necessarily accurate. For example, a valid hair color might be brown, but if a person doesn't have brown hair, that is inaccurate data.

Uniqueness

Uniqueness measures whether the data is distinct with no duplicates.

Typed input in rules

All value parameters are passed as string values to the API. Knowledge Catalog requires inputs to follow the BigQuery specified format.

Binary-typed parameters can be passed as a base64-encoded string.

Data reference parameter

When you create a custom SQL rule, you can refer to a data source table and all of its precondition filters by using the data reference parameter ${data()} in the rule, instead of explicitly mentioning the source table and its filters. Knowledge Catalog interprets the parameter as a reference to the source table and its filters. Examples of precondition filters include row filters, sampling percents, and incremental filters.

For example, say that you have a data source table called my_project_id.dim_dataset.dim_currency. You want to run an incremental data quality scan that scans only on new daily data. A row filter that filters for today's entries, transaction_timestamp >= current_date(), is applied on the table.

A custom SQL rule to find rows with discount_pct for today looks like this:

discount_pct IN (SELECT discount_pct FROM my_project_id.dim_dataset.dim_currency WHERE transaction_timestamp >= current_date())

If you use the data reference parameter, you can simplify the rule. Replace the mention of the table and its precondition filters with the ${data()} parameter:

discount_pct IN (SELECT discount_pct FROM ${data()})

Knowledge Catalog interprets the ${data()} parameter as a reference to the data source table with today's entries, my_project_id.dim_dataset.dim_currency WHERE transaction_timestamp >= current_date(). In this example, the data reference parameter refers only to the incremental data.

The ${data()} parameter is case sensitive.

When you use an alias within a subquery to refer to columns in the source table, either use the data reference parameter to refer to the source table, or omit the table reference. Don't refer to the columns in the source table by using a direct table reference in the WHERE clause.

Recommended:

• Use the data reference parameter to refer to the source table:

  discount_pct IN (
  SELECT discount_pct FROM
  `my_project_id.dim_dataset.dim_currency` AS temp-table
  WHERE
  temp-table.transaction_timestamp = ${data()}.timestamp
  )
  

• Omit the table reference:

  discount_pct IN (
  SELECT discount_pct FROM
  `another_project.another_dataset.another_table` AS temp-table
  WHERE
  temp-table.transaction_timestamp = timestamp
  

Not recommended:

• Don't use a direct table reference to refer to columns in the source table:

  discount_pct IN (
  SELECT discount_pct FROM
  `my_project_id.dim_dataset.dim_currency` AS temp-table
  WHERE
  temp-table.transaction_timestamp = `my_project_id.dim_dataset.dim_currency`.timestamp
  )
  

Valid use of different tables:

• You can use a direct table reference when comparing columns from a different table:

  discount_pct IN (
  SELECT discount_pct FROM
  `my_project_id.dim_dataset.dim_currency` AS temp-table
  WHERE
  temp-table.transaction_timestamp = `another_project.another_dataset.another_table`.timestamp
  )
  

Debug queries

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

When you create a rule, you can optionally include a debug query to run alongside the rule. A debug query is a SQL statement that returns up to 10 scalar values. These values can help diagnose the cause if the rule fails. You can add at most one debug query per rule, and it must not exceed 1024 characters in length.

Consider the following SQL assertion rule on the example_project.example_dataset.table table that checks whether the average revenue per item exceeds 100:

SELECT
  *
FROM
  `example_project.example_dataset.table`
WHERE
  SUM(revenue) / COUNT(DISTINCT item_id) > 100

If the preceding rule fails, you can view metrics such as total revenue, number of distinct items, and average revenue per item to help diagnose the issue. The following debug query returns these metrics:

SELECT
  SUM(revenue),
  COUNT(DISTINCT item_id),
  SUM(revenue) / COUNT(DISTINCT item_id)
FROM `example_project.example_dataset.table`

Rule execution

You can schedule data quality scans to run at a specific interval, or you can run a scan on demand.

Execution identity

By default, Knowledge Catalog uses a centralized service agent (service-PROJECT_NUMBER@gcp-sa-dataplex.iam.gserviceaccount.com) to run data quality scans.

You can override this default execution identity by specifying a custom service account or by using your own End-User Credentials (EUC). This provides several benefits:

• Principle of least privilege: Grant only the exact IAM permissions required for specific data quality tasks to a dedicated service account, minimizing overprovisioned access.
• Fine-grained access control: Scope permissions to specific resources, allowing integration with row-level and column-level access policies in BigQuery.
• Improved auditability: Assign custom service accounts or user credentials to specific scans, making tracking and logging of activities much clearer in audit logs.
• Billing unification: When you use a custom execution identity, the processing and storage charges are centralized directly under BigQuery (bypassing Knowledge Catalog Premium SKUs). This lets you take advantage of BigQuery enterprise discounts and slot commitments.

For instructions on how to configure a custom execution identity, see Configure execution identity.

Networking requirements

To run a scan, you must enable Private Google Access on the VPC subnet that you use for the scan. If you don't specify a subnet, make sure your default subnet has Private Google Access enabled.

When you run a data quality scan, Knowledge Catalog creates a job. If a job is misconfigured or running longer than expected, you can cancel the job.

As part of the specification of a data quality scan, you can specify the scope of a job to be one of the following:

Full Table

Each job validates the entire table.

Incremental

Each job validates incremental data. To determine increments, provide a Date / Timestamp column in the table that can be used as a marker. Typically, this is the column on which the table is partitioned.

Filter data

You can filter data to be scanned for data quality by using a row filter. Creating a row filter lets you focus on data within a specific time period or specific segment, such as a certain region. Using filters can reduce the run time and cost. For example, you can filter out data with a timestamp before a certain date.

Sample data

You can specify a percentage of records from your data to sample for running a data quality scan. Creating data quality scans on a smaller sample of data can reduce the run time and the cost relative to querying the entire dataset.

Filter rules

When you run a data quality scan, you can use AIP-160 filter syntax to selectively execute specific rules. Knowledge Catalog performs filtering on the metadata of the rules defined in the scan or rules attached to the catalog entry through the data-rules aspect.

Filter syntax

The filter syntax follows AIP-160 guidelines. You can use standard AIP-160 operators (such as =, !=, >, <, =~) and combine multiple conditions using AND or OR.

When you use an AIP-160 filter string, do the following:

• In the Google Cloud console: Enter the filter directly using AIP-160 syntax, for example, name = "critical_check".
• In an API call: The filter string is often a value within another JSON string literal. This requires escaping double quotes within the AIP-160 string. For example, "filter": "name = \"critical_check\"".

Filterable fields

You can filter on most fields available in the rule definition:

• name: The display name of the rule.
• dimension: The data quality dimension (for example, VALIDITY).
• column: The name of the column the rule applies to.
• threshold: The passing threshold for the rule.
• ignore_null: A boolean value. When true, NULL rows are considered passing.
• attributes: Custom key-value pairs assigned to the rule.

The following examples show common filter patterns. Numeric and boolean values aren't quoted.

Filter by name

• Match exactly: name = "critical_check"
• Match a pattern: name =~ "temp_.*"

Filter by dimension

• Match a specific dimension: dimension = "COMPLETENESS"
• Match multiple dimensions: dimension = "VALIDITY" OR dimension = "ACCURACY"

Filter by column and threshold

• Match a specific column: column = "user_id"
• Match a threshold: threshold > 0.95
• Match a range: threshold >= 0.8 AND threshold < 0.9

Filter by ignore_null

• Match boolean value: ignore_null = true

Filter by custom attributes

• Check for key presence: attributes:environment
• Match key and value: attributes.environment = "prod"
• Match using regular expressions: attributes.tag =~ "prio-.*"
• Match combinations: attributes.environment = "prod" AND attributes.criticality = "high"

Data quality scan results

The results of your data quality scans are available in Knowledge Catalog and BigQuery. You can also review and analyze the scan results by using the following methods:

• Export results to BigQuery

  You can export the scan results to a BigQuery table for further analysis. To customize reporting, you can connect the BigQuery table data to a Looker dashboard. You can build an aggregated report by using the same results table across multiple scans.

• Publish results as Knowledge Catalog metadata

  You can publish the data quality scan results as Knowledge Catalog metadata. The latest results are saved to the Knowledge Catalog entry that represents the source table, under the data-quality-scorecard system aspect type. You can view the results on the source table's BigQuery and Knowledge Catalog pages in the Google Cloud console, on the Data quality tab. You can also retrieve the results by using the API.

  Note: If an existing data quality scan published the results to the BigQuery and Knowledge Catalog pages in the Google Cloud console, and you instead want to publish future scan results as Knowledge Catalog metadata, you must edit the scan to re-enable publishing.

  For more information about Knowledge Catalog metadata, see About metadata management in Knowledge Catalog.

• Review data quality scores

  Each scan result provides data quality scores that indicate the percentage of rules that passed. The scores are reported at the overall job level, the column level (if the rule is evaluated against a column), and the dimension level. Use the data quality scores to normalize data quality across tables or columns, track trends, and identify data that doesn't meet quality requirements.

For more information, see View the data quality scan results.

Monitoring and alerting

You can monitor and get alerts about data quality scans by using the following methods:

• Set alerts in Cloud Logging

  You can monitor the data quality jobs using the data_scan and data_quality_scan_rule_result logs in the Logs Explorer.

  For each data quality job, the data_scan log with the data_scan_type field set to DATA_QUALITY contains the following information:

  • Data source used for the data scan.
  • Job execution details, such as creation time, start time, end time, and job state.
  • Result of the data quality job: pass or fail.
  • Dimension level pass or fail.

  Every succeeded job contains a data_quality_scan_rule_result log with the following detailed information about each rule in that job:

  • Configuration information, such as rule name, rule type, evaluation type, and dimension.
  • Result information, such as pass or failure, total row count, passing row count, null row count, and evaluated row count.

  The information in the logs is available through the API and Google Cloud console. You can use this information to set up alerts. For more information, see Set alerts in Logging.

  Note: For the latest successful job, this information is also available in the parent data quality scan.

• Send email notification reports

  You can send email notification reports to alert people about the status and results of a data quality job. Notification reports are available for the following scenarios:

  • The data quality score is lower than a specified target score
  • The job failed
  • The job finished

  You configure notification reports when you create a data quality scan.

Troubleshoot data quality failures

When a rule execution fails, Knowledge Catalog provides a query to get the failed records. Run this query to see the records that didn't match your rule. For more information, see Troubleshoot a data quality failure.

Limitations

• Rule recommendations aren't supported in the gcloud CLI.
• The choice of dimensions is fixed to one of the predefined seven dimensions.
• The number of rules per data quality scan is limited to 1000.
• Data quality scores that are reported at the column level are supported only in the API.
• You can run data quality rules only on BigQuery and Iceberg REST Catalog tables.

Pricing

For more information about pricing, see Knowledge Catalog pricing.

What's next

• Learn how to use auto data quality.
• Learn how to reuse data quality rules.
• Learn how to manage your data quality rules as code.
• Follow a tutorial to build a policy-as-code data quality workflow.
• Learn about the available Terraform resources for data profiling. See the following:
  • Dataplex data scan resource in the Terraform registry.
  • The Dataplex data scan resource documentation on GitHub, which supports YAML-based rule configuration.
• Learn about data profiling.
• Learn how to use data profiling.