Data Quality user documentation
home/Integrations/Snowflake/Get started/Test, then scale

Test, then scale

Use the app's Test, then scale page to test single records, run bulk validation, and create reusable views. You can also run the same validation procedures directly from SQL.

The examples below assume the app is installed as EXPERIAN_DATA_VALIDATION.

Test single records

Single-record procedures return the response as a JSON string. Use RESULT_SCAN(LAST_QUERY_ID()) and PARSE_JSON to parse the response and extract fields in SQL.

-- Validate one address.
CALL EXPERIAN_DATA_VALIDATION.CONTACT.Address(
  '125 Summer St, Boston, MA 02110',
  '<GUID>',
  'USA'
);

-- Extract fields from the JSON returned by the previous CALL.
WITH response AS (
  SELECT PARSE_JSON($1) AS validation_json
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
)
SELECT
  validation_json:is_success::BOOLEAN AS is_success,
  validation_json:result:confidence::STRING AS confidence,
  validation_json:result:address:address_line_1::STRING AS address_line_1,
  validation_json:result:address:locality::STRING AS locality,
  validation_json:result:address:postal_code::STRING AS postal_code,
  validation_json:error:title::STRING AS error_title
FROM response;

The third and fourth parameters are optional. You can provide a unique tracking ID as the third parameter and override the default country with the fourth parameter. You can pass a country value such as USA or United States, or a any value configured in Country mappings.

-- Validate one email address.
CALL EXPERIAN_DATA_VALIDATION.CONTACT.Email('test@example.com');

-- Extract fields from the JSON returned by the previous CALL.
WITH response AS (
  SELECT PARSE_JSON($1) AS validation_json
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
)
SELECT
  validation_json:is_success::BOOLEAN AS is_success,
  validation_json:result:confidence::STRING AS confidence,
  validation_json:result:email::STRING AS email,
  validation_json:result:did_you_mean::STRING AS suggested_correction,
  validation_json:error:title::STRING AS error_title
FROM response;

-- Validate one phone number.
CALL EXPERIAN_DATA_VALIDATION.CONTACT.Phone('+14155552671');

-- Extract fields from the JSON returned by the previous CALL.
WITH response AS (
  SELECT PARSE_JSON($1) AS validation_json
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
)
SELECT
  validation_json:is_success::BOOLEAN AS is_success,
  validation_json:result:confidence::STRING AS confidence,
  validation_json:result:formatted_phone_number::STRING AS formatted_phone,
  validation_json:result:phone_type::STRING AS phone_type,
  validation_json:error:title::STRING AS error_title
FROM response;

Run bulk validation

Bulk procedures queue an asynchronous job and return immediately with a job_id, status, output_table, and a message. The serverless background task processes source data in 10,000-record chunks and writes results to the app's RESULTS schema.

Before submitting a bulk job, allow the app to read your source table:

GRANT USAGE ON DATABASE SOURCE_DB TO APPLICATION EXPERIAN_DATA_VALIDATION;
GRANT USAGE ON SCHEMA SOURCE_DB.SCHEMA TO APPLICATION EXPERIAN_DATA_VALIDATION;
GRANT SELECT ON TABLE SOURCE_DB.SCHEMA.CONTACTS_TABLE TO APPLICATION EXPERIAN_DATA_VALIDATION;

CALL EXPERIAN_DATA_VALIDATION.CONTACT.BulkAddress(
  'SOURCE_DB.SCHEMA.CONTACTS_TABLE',
  'address_column',
  'id_column',
  'ADDRESS',
  'is_active = TRUE',
  'country_iso',
  'datasets'
);
Parameter Required Description
source_table_fqn Yes Source table or view, in DB.SCHEMA.TABLE form.
address_column Yes Column containing the address string to validate.
id_column Yes Stable ID column used as record_id in the results.
output_table_name Yes Name of the result table to create in the app's RESULTS schema.
where_clause No Filter expression without the WHERE keyword.
country_column No Per-row country override. Values can be ISO3 codes or configured country names.
datasets_column No Per-row dataset override. Values can be a dataset string, comma-separated list, or JSON array.

Address result tables include:

Field group Fields
Job and source job_id, chunk_id, timestamp, record_id, address_input, country_iso, datasets_json
Status is_valid, is_success, http_status, error_type, error_title, error_detail, error_instance
Match result confidence, match_type, match_confidence
Standardized address address_line_1, address_line_2, address_line_3, locality, region, postal_code, country
Detailed JSON addresses_formatted, components, metadata, match_info, result_json 
CALL EXPERIAN_DATA_VALIDATION.CONTACT.BulkEmail(
  'SOURCE_DB.SCHEMA.CONTACTS_TABLE',
  'email_column',
  'id_column',
  'EMAIL',
  'is_active = TRUE'
);
Parameter Required Description
source_table_fqn Yes Source table or view, in DB.SCHEMA.TABLE form.
email_column Yes Column containing the email address.
id_column Yes Stable ID column used as record_id in the results.
output_table_name Yes Name of the result table to create in the app's RESULTS schema.
where_clause No Filter expression without the WHERE keyword.

Email result tables include job_id, chunk_id, timestamp, record_id, email_address, is_valid, is_success, http_status, error fields, confidence, email, verbose_output, did_you_mean and metadata_json.

CALL EXPERIAN_DATA_VALIDATION.CONTACT.BulkPhone(
  'SOURCE_DB.SCHEMA.CONTACTS_TABLE',
  'phone_column',
  'id_column',
  'PHONE',
  'is_active = TRUE'
);
Parameter Required Description
source_table_fqn Yes Source table or view, in DB.SCHEMA.TABLE form.
phone_column Yes Column containing the phone number.
id_column Yes Stable ID column used as record_id in the results.
output_table_name Yes Name of the result table to create in the app's RESULTS schema.
where_clause No Filter expression without the WHERE keyword.

Phone result tables include job_id, chunk_id, timestamp, record_id, phone_number, is_valid, is_success, http_status, error fields, confidence, number, validated_phone_number, formatted_phone_number, phone_type, ported_date, disposable_number and metadata_json.

Monitor and query results

Use JOBS.Status to monitor progress:

CALL EXPERIAN_DATA_VALIDATION.JOBS.Status('<job_id>');

When the job status is completed or partial, query the output table returned by the submit procedure:

SELECT *
FROM EXPERIAN_DATA_VALIDATION.RESULTS.EMAIL
WHERE job_id = '<job_id>'
LIMIT 100;

List recent jobs:

SELECT * FROM TABLE(EXPERIAN_DATA_VALIDATION.JOBS.List());

Cancel or retry a job:

CALL EXPERIAN_DATA_VALIDATION.JOBS.Cancel('<job_id>');
CALL EXPERIAN_DATA_VALIDATION.JOBS.Retry('<job_id>');

Create reusable views

Use Create reusable views in Test, then scale to turn result tables into views in your own schema.

For address result tables, use View generator to flatten address, component, metadata, and match-info fields into generated CREATE OR REPLACE VIEW SQL.

Snowflake

Get started