Scan for data quality issues

Console

1. In the Google Cloud console, on the BigQuery Metadata curationpage, go to the Data profiling & qualitytab.

   Go to Data profiling & quality

2. Click Create data quality scan.

3. In the Define scanwindow, fill in the following fields:

   1. Optional: Enter a Display name.

   2. Enter an ID. See the resource naming conventions .

   3. Optional: Enter a Description.

   4. In the Tablefield, click Browse. Choose the table to scan, and then click Select. Only standard BigQuery tables are supported.

      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.

   5. In the Scopefield, choose Incrementalor Entire data.

      • If you choose Incremental: In the Timestamp columnfield, 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. It can be a column that partitions the table.

   6. To filter your data, select the Filter rowscheckbox. Provide a row filter consisting of a valid SQL expression that can be used as a part of a WHERE clause in GoogleSQL syntax . For example, col1 >= 0 . The filter can be a combination of multiple column conditions. For example, col1 >= 0 AND col2 < 10 .

   7. To sample your data, in the Sampling sizelist, 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 quality scan samples between 1-10 TB of data. For incremental data scans, the data quality scan applies sampling to the latest increment.

   8. To publish the data quality scan results as Dataplex Universal Catalog metadata, select the Publish results to Dataplex Catalogcheckbox.

      You can view the latest scan results on the Data qualitytab 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 quality scan results section of this document.

   9. In the Schedulesection, choose one of the following options:

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

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

      • One-time: Run the data quality scan once now, and remove the scan after the time-to-live period.

      • Time to live: The time-to-live value is the time span between when the scan is executed and when the scan is deleted. A data quality scan without a specified time-to-live is automatically deleted 24 hours after its execution. The time-to-live can range from 0 seconds (immediate deletion) to 365 days.

   10. Click Continue.

4. In the Data quality ruleswindow, define the rules to configure for this data quality scan.

   1. Click Add rules, and then choose from the following options.

      • Profile based recommendations: Build rules from the recommendations based on an existing data profiling scan.

        1. Choose columns: Select the columns to get recommended rules for.

        2. Choose scan project: If the data profiling scan is in a different project than the project where you are creating the data quality scan, then select the project to pull profile scans from.

        3. Choose profile results: Select one or more profile results and then click OK. This populates a list of suggested rules that you can use as a starting point.

        4. Select the checkbox for the rules that you want to add, and then click Select. Once selected, the rules are added to your current rule list. Then, you can edit the rules.

      • Built-in rule types: Build rules from predefined rules. See the list of predefined rules .

        1. Choose columns: Select the columns to select rules for.

        2. Choose rule types: Select the rule types that you want to choose from, and then click OK. The rule types that appear depend on the columns that you selected.

        3. Select the checkbox for the rules that you want to add, and then click Select. Once selected, the rules are added to your current rules list. Then, you can edit the rules.

      • SQL row check rule: Create a custom SQL rule to apply to each row.

        1. In Dimension, choose one dimension.

        2. In Passing threshold, choose a percentage of records that must pass the check.

        3. In Column name, choose a column.

        4. In the Provide a SQL expressionfield, enter a SQL expression that evaluates to a boolean true (pass) or false (fail). For more information, see Supported custom SQL rule types and the examples in Define data quality rules .

        5. Click Add.

      • SQL aggregate check rule: Create a custom SQL table condition rule.

        1. In Dimension, choose one dimension.

        2. In Column name, choose a column.

        3. In the Provide a SQL expressionfield, enter a SQL expression that evaluates to a boolean true (pass) or false (fail). For more information, see Supported custom SQL rule types and the examples in Define data quality rules .

        4. Click Add.

      • SQL assertion rule: Create a custom SQL assertion rule to check for an invalid state of the data.

        1. In Dimension, choose one dimension.

        2. Optional: In Column name, choose a column.

        3. In the Provide a SQL statementfield, enter a SQL statement that returns rows that match the invalid state. If any rows are returned, this rule fails. Omit the trailing semicolon from the SQL statement. For more information, see Supported custom SQL rule types and the examples in Define data quality rules .

        4. Click Add.

   2. Optional: For any data quality rule, you can assign a custom rule name to use for monitoring and alerting, and a description. To do this, edit a rule and specify the following details:

      • Rule name: Enter a custom rule name with up to 63 characters. The rule name can include letters (a-z, A-Z), digits (0-9), and hyphens (-) and must start with a letter and end with a number or a letter.
      • Description: Enter a rule description with a maximum length of 1,024 characters.

   3. Repeat the previous steps to add additional rules to the data quality scan. When finished, click Continue.

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

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

   2. In the BigQuery tablefield, specify the table to store the data quality 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.

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

7. Optional: Set up email notification reports to alert people about the status and results of a data quality scan job. In the Notification reportsection, click add Add email IDand enter up to five email addresses. Then, select the scenarios that you want to send reports for:

   • Quality score (<=): sends a report when a job succeeds with a data quality score that is lower than the specified target score. Enter a target quality score between 0 and 100.
   • Job failures: sends a report when the job itself fails, regardless of the data quality results.
   • Job completion (success or failure): sends a report when the job ends, regardless of the data quality results.

8. Click Create.

   After the scan is created, you can run it at any time by clicking Run now.

gcloud

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

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

 gcloud  
dataplex  
datascans  
create  
data-quality  
 DATASCAN 
  
 \ 
  
--location = 
 LOCATION 
  
 \ 
  
--data-quality-spec-file = 
 DATA_QUALITY_SPEC_FILE 
  
 \ 
  
--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-quality  
 DATASCAN 
  
 \ 
  
--location = 
 LOCATION 
  
 \ 
  
--data-quality-spec-file = 
 DATA_QUALITY_SPEC_FILE 
  
 \ 
  
--data-source-resource = 
 DATA_SOURCE_RESOURCE 
 

Replace the following variables:

• DATASCAN : The name of the data quality scan.
• LOCATION : The Google Cloud region in which to create the data quality scan.
• DATA_QUALITY_SPEC_FILE : The path to the JSON or YAML file containing the specifications for the data quality scan. The file can be a local file or a Cloud Storage path with the prefix gs:// . Use this file to specify the data quality rules for the scan. You can also specify additional details in this file, such as filters, sampling percent, and post-scan actions like exporting to BigQuery or sending email notification reports. See the documentation for JSON representation and the example YAML representation .
• DATA_SOURCE_ENTITY : The Dataplex Universal Catalog entity that contains the data for the data quality 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 quality scan. For example, //bigquery.googleapis.com/projects/test-project/datasets/test-dataset/tables/test-table .

C#

C#

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment .

  using 
  
  Google.Api.Gax.ResourceNames 
 
 ; 
 using 
  
  Google.Cloud.Dataplex.V1 
 
 ; 
 using 
  
  Google.LongRunning 
 
 ; 
 public 
  
 sealed 
  
 partial 
  
 class 
  
 GeneratedDataScanServiceClientSnippets 
 { 
  
 /// <summary>Snippet for CreateDataScan</summary> 
  
 /// <remarks> 
  
 /// This snippet has been automatically generated and should be regarded as a code template only. 
  
 /// It will require modifications to work: 
  
 /// - It may require correct/in-range values for request initialization. 
  
 /// - It may require specifying regional endpoints when creating the service client as shown in 
  
 ///   https://cloud.google.com/dotnet/docs/reference/help/client-configuration#endpoint. 
  
 /// </remarks> 
  
 public 
  
 void 
  
 CreateDataScanRequestObject 
 () 
  
 { 
  
 // Create client 
  
  DataScanServiceClient 
 
  
 dataScanServiceClient 
  
 = 
  
  DataScanServiceClient 
 
 . 
  Create 
 
 (); 
  
 // Initialize request argument(s) 
  
  CreateDataScanRequest 
 
  
 request 
  
 = 
  
 new 
  
  CreateDataScanRequest 
 
  
 { 
  
 ParentAsLocationName 
  
 = 
  
  LocationName 
 
 . 
  FromProjectLocation 
 
 ( 
 "[PROJECT]" 
 , 
  
 "[LOCATION]" 
 ), 
  
 DataScan 
  
 = 
  
 new 
  
  DataScan 
 
 (), 
  
 DataScanId 
  
 = 
  
 "" 
 , 
  
 ValidateOnly 
  
 = 
  
 false 
 , 
  
 }; 
  
 // Make the request 
  
 Operation<DataScan 
 , 
  
 OperationMetadata 
>  
 response 
  
 = 
  
 dataScanServiceClient 
 . 
  CreateDataScan 
 
 ( 
 request 
 ); 
  
 // Poll until the returned long-running operation is complete 
  
 Operation<DataScan 
 , 
  
 OperationMetadata 
>  
 completedResponse 
  
 = 
  
 response 
 . 
 PollUntilCompleted 
 (); 
  
 // Retrieve the operation result 
  
  DataScan 
 
  
 result 
  
 = 
  
 completedResponse 
 . 
 Result 
 ; 
  
 // Or get the name of the operation 
  
 string 
  
 operationName 
  
 = 
  
 response 
 . 
 Name 
 ; 
  
 // This name can be stored, then the long-running operation retrieved later by name 
  
 Operation<DataScan 
 , 
  
 OperationMetadata 
>  
 retrievedResponse 
  
 = 
  
 dataScanServiceClient 
 . 
  PollOnceCreateDataScan 
 
 ( 
 operationName 
 ); 
  
 // Check if the retrieved long-running operation has completed 
  
 if 
  
 ( 
 retrievedResponse 
 . 
 IsCompleted 
 ) 
  
 { 
  
 // If it has completed, then access the result 
  
  DataScan 
 
  
 retrievedResult 
  
 = 
  
 retrievedResponse 
 . 
 Result 
 ; 
  
 } 
  
 } 
 }