Prepare and test your skills
Prepare and test your skills
Verifying data validity and uniqueness means checking that values follow business rules and that key identifiers appear only once. In Knowledge Catalog (opens in a new tab) (formerly Dataplex Universal Catalog), a data quality scan validates one BigQuery (opens in a new tab) table against built-in rules and reports which rows pass or fail. Uniqueness rules catch duplicate records, while validity rules check that values match a required format, pattern, range, or allowed set.
A uniqueness check verifies that values in a column, such as transaction_id or trip_id, appear only once across the whole table. Primary keys and transaction identifiers should be 100% unique, so any duplication signals repeated records or ingestion errors. In a data profile scan, the Unique count and % metric reveals this problem when uniqueness is lower than 100%, such as about 98% for trip_id. The built-in uniqueness check uses the Uniqueness dimension and its threshold is Not Applicable, because the check evaluates duplication across the table rather than counting a passing percentage per row.
A validity check verifies that each value in a column conforms to a required format, pattern, range, or allowed list defined by business rules. These are row-level rules where each row independently passes or fails, and they require a passing threshold such as 100%. When the percentage of passing rows falls below the threshold, the rule fails and the failing rows can be reviewed for troubleshooting.
| Rule type | What it validates | Example rule parameter |
|---|---|---|
| Regex check | Value matches a required format or pattern | customer_id must match ^[a-zA-Z]{8}[0-9]{16}$, which means 8 letters followed by 16 digits |
| Range check | Numeric, date, or timestamp value falls between a minimum and maximum | discount_pct must be between 0 and 100, or duration_minutes ≥ 1.0 to require positive ride times |
| Value set check | Value belongs to an allowed list | currency_id must be one of USD,JPY,INR,GBP,CAN |
A range check for validity requires at least a min value or a max value, plus a passing threshold percentage. The range check can also use strict minimum to check with > instead of >=, strict maximum to check with < instead of <=, and ignore null to skip null values during the check.
A data quality scan is a DataScan object of type data quality that runs against only one BigQuery table. The scan applies the defined uniqueness and validity rules to table data and produces results that show pass rates, failure counts, and failing records. Creating and using a data quality scan follows an ordered workflow:
Exam tip: A uniqueness check uses a threshold of Not Applicable, while row-level validity checks for regex, range, and set use a percentage threshold such as 100%.
Evaluating data accuracy and consistency means checking whether data is fit for processing and gives the same answer everywhere. Data accuracy means the values stored in a dataset correctly represent the real source values without errors from transfer or transformation. Data consistency means the same data matches across source and destination systems, tables, and columns, so structural anomalies and broken quality constraints are found before analysis.
How data flows accurately into BigQuery is an ordered transfer where Google Cloud Dataflow (Dataflow) (opens in a new tab) transforms data and BigQuery (opens in a new tab) stores and checks the result. Dataflow defines and runs complex data transformations and enrichment graphs using the Apache Beam model, and it is serverless and fully managed with virtually limitless processing capacity. Data typically flows from the existing warehouse through staging into BigQuery in this sequence:
This order matters because validation can only prove accuracy after the load is complete, and transforming on Google Cloud avoids the compute and bandwidth limits of transforming in the source environment.
The Data Validation Tool (DVT) (opens in a new tab) is an open source Python command-line tool that compares source data to the target in BigQuery. DVT connects to both data stores, runs the same aggregations on each side, and reports mismatches that signal lost rows, altered values, or incorrect schema mappings. DVT supports multi-leveled validation functions, including profiling-style column checks that reveal distribution problems. Basic DVT validations include:
count, sum, avg, min, max, or group by to detect value shifts.If any aggregation differs between source and destination, the dataset is not yet accurate and the transfer or transformation must be corrected before downstream use.
Enforcing consistency with validation rules and integrity constraints means blocking bad data inside BigQuery before it is published. Dataform enforces accuracy inside BigQuery by running custom data validation rules during pipeline execution. A validation rule is defined in a .sqlx assertion file as a query that should return zero rows, such as ensuring a column is never null, checking that primary keys are unique, or validating that numeric values fall within an allowed range. During pipeline execution, Dataform runs these assertion queries against the newly built tables. If an assertion query returns one or more rows, the validation fails and the pipeline execution is immediately marked as failed, which prevents bad data from flowing to dashboards and reports.
A common integrity pattern combines two constraints in one assertion, such as checking that customer_number_kunnr is never null and that no duplicate customer number exists for the same client. The null check selects rows where the key is missing, while the duplicate check groups by the key and keeps groups with a count greater than one. Either condition returning rows means the uniqueness and completeness constraints are violated, so consistency across the customers dataset cannot be trusted.
| Validation approach | What it compares | When it runs |
|---|---|---|
| Data Validation Tool (DVT) | Source tables versus BigQuery target tables using row counts and aggregations | After extraction, upload, and load to prove the migration matched |
| Dataform assertions | New BigQuery tables versus business rules such as not-null, unique, and range constraints | During pipeline execution to stop inconsistent data before it is published |
Exam tip: DVT proves the target matches the source after a load, while a Dataform assertion proves the table meets its own integrity rules during a pipeline run.
Evaluating data completeness means checking whether a dataset has all the values and rows needed for reliable processing. A complete dataset gives a model enough information to learn patterns, while gaps make the data less useful for training. If data is biased or contains missing or erroneous values, the quality of the model is affected, so completeness should be evaluated before training to ensure the data is fit for processing.
Recognizing gaps in datasets means finding missing, null, or inconsistent values that reduce fitness for processing. Agent Platform (opens in a new tab) provides an overview of a dataset after it is imported, which shows each column, its detected variable type, and its nullability. Nullability determines whether a column can have missing or NULL values, so reviewing it shows where blanks are allowed and where they signal a problem. For forecasting use cases, a gap can also appear as a missing row when the interval between training rows is not consistent.
Handling incomplete data is the process of correcting gaps where possible and preparing the remaining missing values for training. Common cleanup actions focus on filling what can be fixed and leaving the rest in a form the platform can process:
Agent Platform can handle missing values, and for forecasting it can impute missing values to fill gaps in the time sequence. Results are more likely to be optimal when all values are available and all rows are present, so correction and consistent intervals remain the preferred path.
The impact of incomplete data on analysis and decisions is lower model quality and less trustworthy predictions. The more missing values a dataset has, the less useful the data will be for training a machine learning model. Biased, missing, or erroneous values carry through training into predictions, which can lead to poor performance in production. When a model behaves unexpectedly during evaluation, returning to check the data for completeness is often the most effective fix.
Exam tip: Agent Platform can process nullable blanks and can impute missing forecasting rows, but optimal results still require complete values and consistent row intervals.
In a Knowledge Catalog data quality scan, a uniqueness check verifies that values in a column appear only once across the whole table, while a validity check verifies that each value conforms to a required format, pattern, range, or allowed list. Uniqueness checks use a threshold of Not Applicable because they evaluate duplication across the table, while row-level validity checks use a percentage threshold such as 100 percent because each row independently passes or fails.
Use the Data Validation Tool (DVT) after extraction, upload, and load to prove that BigQuery target tables match source tables using row counts and aggregations. Use Dataform assertions during pipeline execution to check new BigQuery tables against business rules such as not-null, unique, and range constraints and stop inconsistent data before it is published.
Agent Platform can handle missing values and can impute missing values to fill gaps in the time sequence for forecasting. Results are more likely to be optimal when all values are available and all rows are present with consistent intervals between training rows.
A data practitioner is configuring a data transformation pipeline in Google Cloud using Dataform. To ensure compliance with business rules and quality standards before downstream analytics processing, the dataset must meet specific validation requirements:
email column must match a specific format pattern (%@%.%).signup_date column must fall within a valid date range (greater than 2022-08-01).Which feature in Dataform should the practitioner configure in the table definition?
Last updated