Production integration

Overview

While the application UI is critical to configuring, tuning, and visualizing the matching system, you will also need to integrate the RIS processing into your production system. To make that possible, the RIS service exposes several API endpoints as UDFs (User-Defined Functions) that can be called from your SQL or integration code.

To run the SQL described on this page:

1. In the Snowflake web UI, navigate to Project > Workspaces.

2. In the RCIDR.sql worksheet, run the SQL command.

System state

At any time you can request the “system state”, which contains a high-level view of processing:

SELECT app_public.get_system_state();

Running and monitoring a production job

You can launch a production match job at any time, monitor its progress, and determine the success or failure of the job run.

Prerequisites

To start a match job, you will have first loaded the SHARED.INPUT table with data from your source tables. The onboarding process should have assisted in the mapping of your source tables to the INPUT table columns and generated SQL for you, or you can use whatever process or SQL you want. Refer to the Getting started guide for more information on loading your INPUT table.

The INPUT table should be loaded with all of your PII data, not just the changed data; you must delete records from the INPUT table that no longer correspond to your source tables. Clearing and loading the entire INPUT table is often the best approach, unless you have very large data and want to optimize for incremental change.

Start a production match job

To start a production match job, call the start_production_job UDF as follows:

SELECT app_public.start_production_job(incremental BOOLEAN);

Set incremental as follows:

• FALSE to run a full match (default)

• TRUE to run an incremental match

If you are certain that a small number of changes (< 0.1% of the database) have occurred since the last job, you can run an incremental job (incremental TRUE) in the call. This will tend to run faster for very small changes. It is purely a time optimization.

Monitor job progress

During or after a production job runs, to get the messages for the given phase of the production job, run:

SELECT app_public.get_production_job_messages(job_phase VARCHAR);

Set job_phase to one of:

• hygiene

• segment_individual

• segment_household

• match_individual

• match_household

• report

Abort production job

If you need to halt the production match job, run:

SELECT app_public.abort_production_job();

Match Results table

The output table is SHARED.MATCH_RESULT. It has the following columns:

 Standardized address table

When a production match job cleanses addresses during the hygiene phase, RIS writes the standardized results to the SHARED.STANDARDIZED_ADDRESS table. Use this table to consume parsed, standardized address components without needing access to the internal PRIVATE schema. The ID column of the SHARED.STANDARDIZED_ADDRESS table joins 1:1 with records in your SHARED.INPUT table.

Prerequisites

Before SHARED.STANDARDIZED_ADDRESS contains data:

• A production match job must have run with address hygiene enabled. The hygiene phase collects addresses from INPUT and standardizes them before matching begins.

• Standardized address output is license-gated. Check with your Redpoint representative to confirm your license includes this feature.

About the three address alternates

The INPUT table supports up to three name-and-address instances per record, grouped as alternates: for example, address columns group together (ADDRESS1/CITY1/PROVINCE1/... through ADDRESS3/CITY3/PROVINCE3/...), the same way name and birth-date columns group together (NAME1/FNAME1/MNAME1/LNAME1/GENERATION1/DATE_OF_BIRTH1/GENDER1, and so on for sets 2 and 3). Alternate groups are typically used to capture changes over time (a customer moved or married) or multiple values of the same kind (mobile, work, and home phone).

SHARED.STANDARDIZED_ADDRESS mirrors the INPUT address alternates: each row holds up to three standardized address sets, suffixed 1, 2, and 3, corresponding to the first, second, and third address on the matching INPUT record. Sets 2 and 3 are alternates of set 1; if every column in an alternate group is null or blank, RIS ignores that group.

The ID column matches the ID column in INPUT, so you can join the two tables directly.

Table structure

Each of the three address sets repeats the same 21 columns, distinguished only by the suffix (1, 2, or 3). The pattern is described once below; for example, the first set's street name is STREET1, the second's is STREET2, and so on.

GEO_PRECISION values

Things to know

• Which service standardizes an address depends on country. RIS routes US addresses through CASS, Canadian addresses through SERP, and other countries through Loqate. If an input record doesn't specify a country, RIS assumes USA.

• Standardized addresses are cached for months at a time, and the table has no timestamp column by design. RIS caches standardized address results for an extended period. There's intentionally no version or timestamp column on SHARED.STANDARDIZED_ADDRESS; given the long caching duration, a timestamp could be misleading about how current a row's values are. Factor this into any downstream sync you build on this table.

• ADDRSTAT* and STATUS_EXT* serve different purposes. ADDRSTAT* is a normalized 0–3 status common to every address source, so you can filter or report on it consistently. STATUS_EXT* carries the finer-grained, source-specific code (for example, USPS DPV codes from CASS, or a GeoAccuracy Code from Loqate) for cases where you need more detail than the normalized status provides. For details, refer to the next section.

ADDRSTAT{1-3} and STATUS_EXT{1-3} in SHARED.STANDARDIZED_ADDRESS

Each ID can carry up to three input addresses. The suffix 1, 2 or 3 says which one a column describes: ADDRSTAT2 and STATUS_EXT2 belong to the second address, and so on. Every address slot has two status columns:

• ADDRSTAT is the same four-value status whichever address service handled the address.

• STATUS_EXT is the raw code from that service, passed through unchanged. Its meaning depends on which service produced it.

Where the values come from. The full match run doesn't compute these columns. Hygiene writes them into the private SNAPSHOT table (IrAddressHygiene.java:548-583). At the end of the match run, copyMatchResultsToShared (DataStorageRdbms.java:2040-2077) empties SHARED.STANDARDIZED_ADDRESS and copies the slot columns from SNAPSHOT, for the IDs written to MATCH_RESULT. The copy only happens when the tenant has the return-standard-address license feature (IR-679). Without it, the table is still emptied, so it stays empty.

ADDRSTAT{1-3}: the common status

The values come from HygieneStatus in the hygiene repo:

Each service's raw code is turned into one of these values as follows:

The CASS match codes mean the following (from DL_CassResultsInternal.h:35-50):

• 9: fully coded.

• 7: several candidates, but all in the same ZIP and carrier route. The ZIP and route are correct, but there's no +4.

• 5: several candidates, all in the same ZIP. The ZIP is correct, but there's no route or +4.

• S: standardized but not coded. For example, "Post Office Box" was changed to "PO Box", or the street suffix was abbreviated.

• X: not coded.

• E: expired database.

One consequence: 9, 7 and 5 all come out as VERIFIED. So ADDRSTAT = VERIFIED does not guarantee a +4 code. Check STATUS_EXT for 9, or check whether POSTCODE_EXT is filled in.

STATUS_EXT{1-3}: the raw code from the service

This column was added under IR-667. It sits alongside ADDRSTAT; it does not replace it. It is text up to 32 characters, and it is an empty string when a code isn't available.

This means STATUS_EXT values from different services mean different things. They are only comparable when you also know the country or service. For Loqate in particular, the address-quality code (AQI) is the one ADDRSTAT is based on, and it is not stored. What STATUS_EXT holds for Loqate is the geocode accuracy code.

Caveats

• The code sets are the first, minimal version. Whether STATUS_EXT should be one string or structured, and exactly which codes each service contributes, is still open question OQ-6 in the IR-667 design doc (hygiene/docs/superpowers/specs/2026-06-26-ir-667-...md). The full CASS DPV, LACS and RDI codes were deliberately left out for Redis cache-cost reasons.

• Older rows may be wrong for CASS S. Before the IR-667 fix, the S case was missing its break, so standardized-but-not-coded US addresses were recorded as UNVERIFIED instead of PARSED. Rows served from the hygiene cache before that fix may still show the old value, until the cache entry is refreshed or the data is re-run through the hygiene service.

• The columns may be NULL for some tenants. The comment in copyMatchResultsToShared says these fields stay NULL until the hygiene projects are re-wired to write the new _OUT fields into SNAPSHOT (IR-700). hygiene_full.dlp does appear to route STATUS_EXT_OUT into STATUS_EXT1-3 now. Even so, a tenant whose hygiene ran on an older project may see NULLs.

• Lat/long precision is a separate column. GEO_PRECISION{1-3} is the column for that.

Last updated: September 25, 2026