Profile and validate data quality

This quickstart shows you how to use Knowledge Catalog (formerly Dataplex Universal Catalog) to profile a BigQuery table, define data quality rules based on profile insights, and run a data quality scan.

You complete the following steps:

Create a BigQuery dataset and table with sample bikeshare data that contains intentional anomalies, such as duplicates and null values, to test scanning capabilities.
Create and run a data profile scan on the table. Data profiling calculates column-level statistics such as null percentages, unique value counts, and value distributions. For more information, see About data profiling.
Review the data profile scan results to find patterns and potential anomalies.
Define data quality rules based on your profile findings and run a data quality scan. Data quality scans validate your data against defined rules to identify anomalies. For more information, see About auto data quality.
Review the evaluation results to see which quality rules passed or failed.

Before you begin

Set up your project:

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
Roles required to select or create a project
- Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
- Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.
Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector
If you're using an existing project for this guide, verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.
Verify that billing is enabled for your Google Cloud project.
Enable the Knowledge Catalog and BigQuery APIs.
Roles required to enable APIs
To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.
Enable the APIs

Required roles

To get the permissions that you need to create and run data profile and data quality scans, and manage BigQuery resources, ask your administrator to grant you the following IAM roles on the project:

Create, run, and delete data scans: Dataplex DataScan Editor (roles/dataplex.dataScanEditor)
Create, populate, and delete sample tables: BigQuery Data Owner (roles/bigquery.dataOwner)
Run SQL queries in BigQuery: BigQuery Job User (roles/bigquery.jobUser)

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

You might also be able to get the required permissions through custom roles or other predefined roles.

If you have the necessary permissions to manage IAM access in your project, you can grant these roles to your own user account by running the following gcloud commands:

gcloud projects add-iam-policy-binding PROJECT_ID \
    --member="user:USER_EMAIL" \
    --role="roles/dataplex.dataScanEditor"

gcloud projects add-iam-policy-binding PROJECT_ID \
    --member="user:USER_EMAIL" \
    --role="roles/bigquery.dataOwner"

gcloud projects add-iam-policy-binding PROJECT_ID \
    --member="user:USER_EMAIL" \
    --role="roles/bigquery.jobUser"

Replace the following:

PROJECT_ID: your Google Cloud project ID.
USER_EMAIL: your user account email address (for example, name@example.com).

Grant permissions to the Knowledge Catalog service agent

A service agent is a Google-managed service account that Knowledge Catalog uses to run scan queries in BigQuery on your behalf.

In the Google Cloud console, click Activate Cloud Shell in the toolbar. The environment takes a few moments to provision and connect.
Create the Knowledge Catalog service agent:
```
gcloud beta services identity create --service=dataplex.googleapis.com
```
This command creates the service agent if it hasn't been provisioned yet and outputs its email. If your project already has a Knowledge Catalog service agent, the command returns the existing identity without making any changes.

The output is similar to the following:
```
serviceAccount:service-PROJECT_NUMBER@gcp-sa-dataplex.
```
Note the PROJECT_NUMBER in the output for the next steps.
Grant the BigQuery Job User (roles/bigquery.jobUser) role so Knowledge Catalog can run query jobs in your project:
```
gcloud projects add-iam-policy-binding PROJECT_ID \
   --member="serviceAccount:service-PROJECT_NUMBER@gcp-sa-dataplex." \
   --role="roles/bigquery.jobUser"
```
Replace the following:
- PROJECT_ID: your Google Cloud project ID.
- PROJECT_NUMBER: your Google Cloud project number.
Grant the BigQuery Data Viewer (roles/bigquery.dataViewer) role so the service agent can read your table data and schema:
```
gcloud projects add-iam-policy-binding PROJECT_ID \
   --member="serviceAccount:service-PROJECT_NUMBER@gcp-sa-dataplex." \
   --role="roles/bigquery.dataViewer"
```
Replace the following:
- PROJECT_ID: your Google Cloud project ID.
- PROJECT_NUMBER: your Google Cloud project number.

Create a sample dataset and table

To try out profiling and data quality scans safely without touching production data, set up a dedicated BigQuery dataset and create a table containing sample data directly in your project.

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the Explorer pane, click View actions next to your project ID, and then click Create dataset.
In the Dataset ID field, enter quickstart_data_profile.
In the Data location list, select us-central1 (Iowa).
Click Create dataset.

In the query editor, enter the following SQL query to generate sample bikeshare data in your bikeshare_trips table:

CREATE OR REPLACE TABLE `PROJECT_ID.quickstart_data_profile.bikeshare_trips` AS
SELECT
-- Duplicate and null IDs
IF(MOD(x, 100) = 0, NULL, IF(x > 9900, 1000 + (x - 9900), 1000 + x)) AS trip_id,
-- Nulls and unrecognized category values
CASE
  WHEN MOD(x, 50) = 0 THEN 'INVALID_TIER'
  WHEN MOD(x, 25) = 0 THEN NULL
  WHEN MOD(x, 4) = 0 THEN 'Local Rider'
  WHEN MOD(x, 4) = 1 THEN 'Walk Up'
  WHEN MOD(x, 4) = 2 THEN 'Student Membership'
  ELSE 'Weekender'
END AS subscriber_type,
-- Nulls and malformed bike IDs
CASE
  WHEN MOD(x, 60) = 0 THEN 'UNKNOWN'
  WHEN MOD(x, 30) = 0 THEN NULL
  ELSE CAST(2000 + x AS STRING)
END AS bike_id,
-- Null dates and future timestamps
CASE
  WHEN MOD(x, 70) = 0 THEN NULL
  WHEN MOD(x, 40) = 0 THEN TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL x MINUTE)
  ELSE TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL x MINUTE)
END AS start_time,
-- Nulls and placeholder station values
CASE
  WHEN MOD(x, 20) = 0 THEN 'STATION_UNKNOWN'
  WHEN MOD(x, 10) = 0 THEN NULL
  ELSE CAST(100 + MOD(x, 50) AS STRING)
END AS start_station_id,
-- Negative durations, zeros, and extreme outliers
CASE
  WHEN MOD(x, 15) = 0 THEN -10.0
  WHEN MOD(x, 35) = 0 THEN 0.0
  WHEN