Profile your data

This document explains how to use data profile scans to better understand your data. BigQuery uses Dataplex Universal Catalog to analyze the statistical characteristics of your data, such as average values, unique values, and maximum values. Dataplex Universal Catalog also uses this information to recommend rules for data quality checks.

For more information about data profiling, see About data profiling.

Tip: The steps in this document show how to manage data profile scans across your project. You can also create and manage data profile scans when working with a specific table. For more information, see the Manage data profile scans for a specific table section of this document.

Before you begin

Enable the Dataplex API.

Enable the API

Required roles

To profile BigQuery tables, you need the following permissions:

• To run a data profile 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.

  Note: Dataplex Universal Catalog doesn't create a BigQuery job in the project. However, you need this permission to create a DryRun job to check for permissions for the table.

• If the BigQuery table and the data profile scan are in different projects, then you need to give the Dataplex Universal Catalog service account read permission on the corresponding BigQuery table.

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

• If the BigQuery data is organized in a Dataplex Universal Catalog lake, then to create a data profile scan, you need the Dataplex Universal Catalog roles roles/dataplex.metadataReader and roles/dataplex.viewer. This grants 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, then assign the Dataplex Universal Catalog service account either the Storage Object Viewer (roles/storage.objectViewer) role or the following permissions on the bucket:

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

• If you want to publish the data profile scan results in the BigQuery and Dataplex Universal Catalog pages in the Google Cloud console for the source tables, you must be granted the BigQuery Data Editor (roles/bigquery.dataEditor) role on the table. Alternatively, you need all of the following permissions:

  • bigquery.tables.get
  • bigquery.tables.update
  • bigquery.tables.updateData
  • bigquery.tables.delete

• To export the scan results to a BigQuery table, your Dataplex Universal Catalog service account needs the BigQuery Data Editor (roles/bigquery.dataEditor) role. This grants the following permissions:

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

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

Data scan roles and permissions

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

• roles/dataplex.dataScanAdmin: Full access to DataScan resources.
• roles/dataplex.dataScanEditor: Write access to DataScan resources.
• roles/dataplex.dataScanViewer: Read access to DataScan resources, excluding the results.
• roles/dataplex.dataScanDataViewer: Read access to DataScan resources, including the results.

The following table lists the data scan permissions:

Permission name	Grants permission to do the following:
dataplex.datascans.create	Create a DataScan
dataplex.datascans.delete	Delete a DataScan
dataplex.datascans.get	View DataScan details excluding results
dataplex.datascans.getData	View DataScan details including results
dataplex.datascans.list	List DataScans
dataplex.datascans.run	Run a DataScan
dataplex.datascans.update	Update the description of a DataScan
dataplex.datascans.getIamPolicy	View the current IAM permissions on the scan
dataplex.datascans.setIamPolicy	Set IAM permissions on the scan

Create a data profile scan

Console

1. In the Google Cloud console, on the BigQuery Metadata curation page, go to the Data profiling & quality tab.

   Go to Data profiling & quality

2. Click Create data profile scan.

3. Optional: Enter a Display name.

4. Enter an ID. See the Resource naming conventions.

5. Optional: Enter a Description.

6. In the Table field, click Browse. Choose the table to scan, and then click Select.

   For tables in multi-region datasets, choose a region where to create the data scan.

   To browse the tables organized within Dataplex Universal Catalog lakes, click Browse within Dataplex Lakes.

7. In the Scope field, choose Incremental or Entire data.

   • If you choose Incremental data, in the Timestamp column field, select a column of type DATE or TIMESTAMP from your BigQuery table that increases as new records are added, and that can be used to identify new records. For tables partitioned on a column of type DATE or TIMESTAMP, we recommend using the partition column as the timestamp field.

8. Optional: To filter your data, do any of the following:

   • To filter by rows, click select the Filter rows checkbox. Enter a valid SQL expression that can be used in a WHERE clause in GoogleSQL syntax. For example: col1 >= 0.

     The filter can be a combination of SQL conditions over multiple columns. For example: col1 >= 0 AND col2 < 10.

   • To filter by columns, select the Filter columns checkbox.

     • To include columns in the profile scan, in the Include columns field, click Browse. Select the columns to include, and then click Select.

     • To exclude columns from the profile scan, in the Exclude columns field, click Browse. Select the columns to exclude, and then click Select.

     Note: You can use Include columns, Exclude columns, or both. If you use both the fields, then the data profile scan first selects the columns based on your input in the Include columns field and then excludes the columns based on your input in the Exclude columns field.

9. To apply sampling to your data profile scan, in the Sampling size list, select a sampling percentage. Choose a percentage value that ranges between 0.0% and 100.0% with up to 3 decimal digits.

   • For larger datasets, choose a lower sampling percentage. For example, for a 1 PB table, if you enter a value between 0.1% and 1.0%, the data profile samples between 1-10 TB of data.

   • There must be at least 100 records in the sampled data to return a result.

   • For incremental data scans, the data profile scan applies sampling to the latest increment.

10. Optional: Publish the data profile scan results in the BigQuery and Dataplex Universal Catalog pages in the Google Cloud console for the source table. Select the Publish results to the BigQuery and Dataplex Catalog UI checkbox.

    You can view the latest scan results in the Data profile tab in the BigQuery and Dataplex Universal Catalog pages for the source table. To enable users to access the published scan results, see the Grant access to data profile scan results section of this document.

    The publishing option might not be available in the following cases:

    • You don't have the required permissions on the table.
    • Another data quality scan is set to publish results.

11. In the Schedule section, choose one of the following options:

    • Repeat: Run the data profile scan on a schedule: hourly, daily, weekly, monthly, or custom. Specify how often the scan should run and at what time. If you choose custom, use cron format to specify the schedule.

    • On-demand: Run the data profile scan on demand.

12. Click Continue.

13. Optional: Export the scan results to a BigQuery standard table. In the Export scan results to BigQuery table section, do the following:

    1. In the Select BigQuery dataset field, click Browse. Select a BigQuery dataset to store the data profile scan results.

    2. In the BigQuery table field, specify the table to store the data profile scan results. If you're using an existing table, make sure that it is compatible with the export table schema. If the specified table doesn't exist, Dataplex Universal Catalog creates it for you.

       Note: You can use the same results table for multiple data profile scans.

14. Optional: Add labels. Labels are key-value pairs that let you group related objects together or with other Google Cloud resources.

15. To create the scan, click Create.

    If you set the schedule to on-demand, you can also run the scan now by clicking Run scan.

gcloud

To create a data profile scan, use the gcloud dataplex datascans create data-profile command.

If the source data is organized in a Dataplex Universal Catalog lake, include the --data-source-entity flag:

gcloud dataplex datascans create data-profile DATASCAN \
--location=LOCATION \
--data-source-entity=DATA_SOURCE_ENTITY

If the source data isn't organized in a Dataplex Universal Catalog lake, include the --data-source-resource flag:

gcloud dataplex datascans create data-profile DATASCAN \
--location=LOCATION \
--data-source-resource=DATA_SOURCE_RESOURCE

Replace the following variables:

• DATASCAN: The name of the data profile scan.
• LOCATION: The Google Cloud region in which to create the data profile scan.
• DATA_SOURCE_ENTITY: The Dataplex Universal Catalog entity that contains the data for the data profile scan. For example, projects/test-project/locations/test-location/lakes/test-lake/zones/test-zone/entities/test-entity.
• DATA_SOURCE_RESOURCE: The name of the resource that contains the data for the data profile scan. For example, //bigquery.googleapis.com/projects/test-project/datasets/test-dataset/tables/test-table.

REST

To create a data profile scan, use the dataScans.create method.

Note: If your BigQuery table is configured with the Require partition filter setting set to true, use the table's partition column as the data profile scan's row filter or timestamp column.

Create multiple data profile scans

You can configure data profile scans for multiple tables in a BigQuery dataset at the same time by using the Google Cloud console.

1. In the Google Cloud console, on the BigQuery Metadata curation page, go to the Data profiling & quality tab.

   Go to Data profiling & quality

2. Click Create data profile scan.

3. Select the Multiple data profile scans option.

4. Enter an ID prefix. Dataplex Universal Catalog automatically generates scan IDs by using the provided prefix and unique suffixes.

5. Enter a Description for all of the data profile scans.

6. In the Dataset field, click Browse. Select a dataset to pick tables from. Click Select.

7. If the dataset is multi-regional, select a Region in which to create the data profile scans.

8. Configure the common settings for the scans:

   1. In the Scope field, choose Incremental or Entire data.

      Note: If you choose Incremental data, you can select only tables that are partitioned on a column of type DATE or TIMESTAMP.

   2. To apply sampling to the data profile scans, in the Sampling size list, select a sampling percentage.

      Choose a percentage value between 0.0% and 100.0% with up to 3 decimal digits.

   3. Optional: Publish the data profile scan results in the BigQuery and Dataplex Universal Catalog pages in the Google Cloud console for the source table. Select the Publish results to the BigQuery and Dataplex Catalog UI checkbox.

      You can view the latest scan results in the Data profile tab in the BigQuery and Dataplex Universal Catalog pages for the source table. To enable users to access the published scan results, see the Grant access to data profile scan results section of this document.

      Note: You must choose tables that don't have any existing scans publishing their results.

   4. In the Schedule section, choose one of the following options:

      • Repeat: Run the data profile scans on a schedule: hourly, daily, weekly, monthly, or custom. Specify how often the scans should run and at what time. If you choose custom, use cron format to specify the schedule.

      • On-demand: Run the data profile scans on demand.

9. Click Continue.

10. In the Choose tables field, click Browse. Choose one or more tables to scan, and then click Select.

11. Click Continue.

12. Optional: Export the scan results to a BigQuery standard table. In the Export scan results to BigQuery table section, do the following:

    1. In the Select BigQuery dataset field, click Browse. Select a BigQuery dataset to store the data profile scan results.

    2. In the BigQuery table field, specify the table to store the data profile scan results. If you're using an existing table, make sure that it is compatible with the export table schema. If the specified table doesn't exist, Dataplex Universal Catalog creates it for you.

       Dataplex Universal Catalog uses the same results table for all of the data profile scans.

13. Optional: Add labels. Labels are key-value pairs that let you group related objects together or with other Google Cloud resources.

14. To create the scans, click Create.

    If you set the schedule to on-demand, you can also run the scans now by clicking Run scan.

Run a data profile scan

Console

1. In the Google Cloud console, on the BigQuery Metadata curation page, go to the Data profiling & quality tab.

   Go to Data profiling & quality

2. Click the data profile scan to run.
3. Click Run now.

gcloud

To run a data profile scan, use the gcloud dataplex datascans run command:

gcloud dataplex datascans run DATASCAN \
--location=LOCATION

Replace the following variables:

• DATASCAN: The name of the data profile scan.
• LOCATION: The Google Cloud region in which the data profile scan was created.

REST

To run a data profile scan, use the dataScans.run method.

View data profile scan results

Console

1. In the Google Cloud console, on the BigQuery Metadata curation page, go to the Data profiling & quality tab.

   Go to Data profiling & quality

2. Click the name of a data profile scan.

   • The Overview section displays information about the most recent jobs, including when the scan was run, the number of table records scanned, and the job status.

   • The Data profile scan configuration section displays details about the scan.

3. To see detailed information about a job, such as the scanned table's columns, statistics about the columns that were found in the scan, and the job logs, click the Jobs history tab. Then, click a job ID.

Note: If you exported the scan results to a BigQuery table, then you can also access the scan results from the table.

gcloud

To view the results of a data profile scan job, use the gcloud dataplex datascans jobs describe command:

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

Replace the following variables:

• JOB: The job ID of the data profile scan job.
• LOCATION: The Google Cloud region in which the data profile scan was created.
• DATASCAN: The name of the data profile scan the job belongs to.
• --view=FULL: To see the scan job result, specify FULL.

REST

To view the results of a data profile scan, use the dataScans.get method.

View published results

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

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

   Go to BigQuery

2. In the Explorer pane, select the table whose data profile scan results you want to see.

3. Click the Data profile tab.

   The latest published results are displayed.

   Note: Published results might not be available if a scan is running for the first time.

View the most recent data profile scan job

Console

1. In the Google Cloud console, on the BigQuery Metadata curation page, go to the Data profiling & quality tab.

   Go to Data profiling & quality

2. Click the name of a data profile scan.

3. Click the Latest job results tab.

   The Latest job results tab, when there is at least one successfully completed run, provides information about the most recent job. It lists the scanned table's columns and statistics about the columns that were found in the scan.

gcloud

To view the most recent successful data profile scan, use the gcloud dataplex datascans describe command:

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

Replace the following variables:

• DATASCAN: The name of the data profile scan to view the most recent job for.
• LOCATION: The Google Cloud region in which the data profile scan was created.
• --view=FULL: To see the scan job result, specify FULL.

REST

To view the most recent scan job, use the dataScans.get method.

View historical scan results

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

Console

1. In the Google Cloud console, on the BigQuery Metadata curation page, go to the Data profiling & quality tab.

   Go to Data profiling & quality

2. Click the name of a data profile scan.

3. Click the Jobs history tab.

   The Jobs history tab provides information about past jobs, such as the number of records scanned in each job, the job status, and the time the job was run.

4. To view detailed information about a job, click any of the jobs in the Job ID column.

gcloud

To view historical data profile scan jobs, use the gcloud dataplex datascans jobs list command:

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

Replace the following variables:

• LOCATION: The Google Cloud region in which the data profile scan was created.
• DATASCAN: The name of the data profile scan to view jobs for.

REST

To view historical data profile scan jobs, use the dataScans.jobs.list method.

View the data profile scans for a table

To view the data profile scans that apply to a specific table, do the following:

1. In the Google Cloud console, on the BigQuery Metadata curation page, go to the Data profiling & quality tab.

   Go to Data profiling & quality

2. Filter the list by table name and scan type.

Grant access to data profile scan results

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

1. In the Google Cloud console, on the BigQuery Metadata curation page, go to the Data profiling & quality tab.

   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 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 Remove access, and then confirm when prompted.

Manage data profile scans for a specific table

The steps in this document show how to manage data profile scans across your project by using the BigQuery Metadata curation > 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 BigQuery page for the table, use the Data profile tab. Do the following:

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

   Go to BigQuery

   In the Explorer pane, select the table.

2. Click the Data profile tab.

3. Depending on whether the table has a data profile scan whose results are published, you can work with the table's data profile scans in the following ways:

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

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

     • Create new scan: create a new data profile scan. For more information, see the Create a data profile 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, sampling size, and schedule.

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

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

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

   • Data profile scan results aren't published: click the menu next to Quick data profile, and then select from the following options:

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

     • View previous profiles: view a list of data profile scans that apply to this table.

Update a data profile scan

Console

1. In the Google Cloud console, on the BigQuery Metadata curation page, go to the Data profiling & quality tab.

   Go to Data profiling & quality

2. Click the name of a data profile scan.

3. Click Edit, and then edit the values.

4. Click Save.

gcloud

To update a data profile scan, use the gcloud dataplex datascans update data-profile command:

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

Replace the following variables:

• DATASCAN: The name of the data profile scan to update.
• LOCATION: The Google Cloud region in which the data profile scan was created.
• DESCRIPTION: The new description for the data profile scan.

Note: You can update specification fields, such as rowFilter, samplingPercent, or includeFields, in the data quality specification file. See the JSON format.

REST

To edit a data profile scan, use the dataScans.patch method.

Delete a data profile scan

Console

1. In the Google Cloud console, on the BigQuery Metadata curation page, go to the Data profiling & quality tab.

   Go to Data profiling & quality

2. Click the scan you want to delete.

3. Click Delete, and then confirm when prompted.

gcloud

To delete a data profile scan, use the gcloud dataplex datascans delete command:

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

Replace the following variables:

• DATASCAN: The name of the data profile scan to delete.
• LOCATION: The Google Cloud region in which the data profile scan was created.

REST

To delete a data profile scan, use the dataScans.delete method.

What's next

• Learn how to explore your data by generating data insights.
• Learn more about data governance in BigQuery.
• Learn how to scan your data for data quality issues.
• Learn how to examine table data and create queries with table explorer.