✨ Generating Match Reports from Google Cloud SQL PostgreSQL Data

1Create the Cloud SQL Instance

Determine which data table in Cloud SQL you will connect to and generate an Interzoid Match Report with. Any table containing company names, individual names, or street addresses is a good candidate. If you already have a Cloud SQL instance holding data like that, skip ahead to Step 2.

If you do not have sample data, you can load the following CSV file into a Cloud SQL table. You can get this sample data at the following address:

https://dl.interzoid.com/csv/companies.csv

Creating the Instance

In the Google Cloud console, open SQL from the navigation menu, click Create instance, and choose PostgreSQL. Cloud SQL presents a single creation form, with no express or easy variant to steer around.

• Edition: Cloud SQL Enterprise. The Enterprise Plus edition adds performance and availability features that a demonstration does not need.
• Preset: Sandbox. This picks a small shared-core machine, a single zone, and minimal backups, which is the cheapest configuration the form offers. You can change any of it afterward.
• Instance ID: A name for the instance, such as interzoid-demo. This becomes part of the instance connection name and cannot be changed later.
• Password: The password for the built-in postgres user. Set it here and record it, since you will paste it into a connection string shortly. Use the generate button if you would rather not invent one, and copy it before leaving the page.
• Database version: Any currently supported PostgreSQL version. The guide assumes nothing version-specific.
• Region: Anywhere convenient. Region affects latency to the matching service slightly and nothing else in this walkthrough.

Connections

Open the Connections section of the form and set the following. These two settings are what make the instance reachable by the matching wizard, and both can be changed later if you miss them:

• Public IP: Enabled. Cloud SQL assigns the instance a static IPv4 address. Instances do not support IPv6.
• SSL mode: Choose the option requiring encryption for all connections. Cloud SQL will otherwise accept unencrypted connections, and Google specifically recommends enforcing SSL on any instance exposed by public IP. Requiring encryption does not require client certificates, which are a separate and stricter setting.

Click Create instance. Provisioning takes several minutes, and the instance is ready when it appears as running in the instances list.

Create the Database

Cloud SQL creates an instance containing a default postgres database, and databases are managed separately from instance creation rather than through a field on the creation form. Open your instance, choose Databases in the left navigation, click Create database, and name it interzoiddemo. This is a small but easy thing to overlook, since the instance is perfectly healthy without it and your connection string simply has nothing useful to point at.

2Authorize the Matching Service

A Cloud SQL instance with a public IP accepts connections only from addresses you list explicitly. Nothing reaches it until you add one, and this is where most first connections fail.

Adding the Authorized Network

Open your instance, choose Connections in the left navigation, and select the Networking tab. Under Authorized networks, click Add a network and enter:

• The Interzoid matching service, so a match can run: The Interzoid service address as a /32 CIDR block, with a name such as interzoid-matching. The current address is published on the service IP addresses page and is also shown on the wizard's connection screen. This is the entry the wizard depends on.
• Your own workstation, only if you plan to use psql: Your current public address. Cloud SQL Studio, described in Step 3, runs inside the console and does not travel over the public IP path, so it needs no entry here at all. Skip this unless Step 4 sends you to psql. Note that a residential connection may hand you a new address later, in which case the entry needs updating.

Click Done, then Save at the bottom of the page. Changes take a moment to apply and require no restart.

You can confirm the path is open before involving the wizard. From any host that should be able to reach the instance, check the port directly:

> nc -zv 203.0.113.42 5432

A success message means the instance is permitting that source, and any later failure is about credentials or the database name rather than the network. A hang means the entry is still missing or the source address is not what you expected.

3Create the Target Table

Cloud SQL includes Cloud SQL Studio, a SQL editor built into the Google Cloud console. This is a real convenience relative to other managed PostgreSQL services, several of which offer no browser-based query interface at all, and it means you can complete this step without installing a client or authorizing your own IP address.

Option A: Cloud SQL Studio

Open your instance and choose Cloud SQL Studio in the left navigation. Sign in with the database name (interzoiddemo), the user (postgres), and the password you set in Step 1. The Explorer pane lists the objects in your database, and the editor runs DDL, DML, and query statements alike, so everything in this guide can be run there.

Option B: psql

Copy the public IP address from the instance overview page. Cloud SQL gives you a bare IPv4 address rather than a host name, which is worth noting since every other field in a connection string looks the same as usual:

> psql "postgresql://postgres:YOUR-PASSWORD@203.0.113.42:5432/interzoiddemo?sslmode=require"

Because the host is an IP address rather than a name, sslmode=verify-full has nothing to verify a host name against and will fail. Use sslmode=require, which encrypts the connection without checking the server's identity. If you do want full verification, download the server CA certificate from the instance's Security settings and use sslmode=verify-ca, which validates the certificate authority without requiring a host name match.

The Table

Whichever client you used, create a table whose columns line up with the columns in the CSV file you are about to load:

CREATE TABLE companies (
  id        SERIAL PRIMARY KEY,
  company   TEXT,
  category  TEXT
);

The sample file has two columns, company and category, and contains 91 records. The id column above is added for convenience and is populated automatically by its sequence, so it does not appear in the CSV. If you are loading your own file, adjust the CREATE TABLE statement so the columns appear in the same order as the header row, and use TEXT for anything you intend to match on. Matching operates on the values as they were entered, so there is no benefit to narrower types here.

4Load the CSV

Cloud SQL offers a built-in import that most managed PostgreSQL services do not, and it is the simplest path for a file of this size.

Option A: Console Import

Download the sample file, then open your instance and click Import:

> curl -O https://dl.interzoid.com/csv/companies.csv

In the import dialog, supply the file either by uploading it directly or by pointing at a file in a Cloud Storage bucket, choose CSV as the format, and specify the database (interzoiddemo) and the table (companies). The table must already exist, which is why Step 3 comes first. The import loads the file into the columns of that table in order.

Because the sample file's first column, company, does not line up with the table's first column, id, supply an explicit column list through the gcloud command line if you kept the id column, or create the table without id and add it afterward. The simplest path for a demonstration is to drop id from the CREATE TABLE statement in Step 3 entirely, since matching does not require it.

Option B: psql

The server-side COPY ... FROM '/path/to/file.csv' form does not work, because it reads from the database server's own filesystem, which Google does not give you access to on a managed instance. The \copy meta-command streams the file from your local machine instead, handles the header row properly, and takes an explicit column list:

interzoiddemo=> \copy companies(company, category) FROM 'C:/data/companies.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');
COPY 91

interzoiddemo=> SELECT COUNT(*) FROM companies;

The \copy command reports the number of rows loaded, shown here as COPY 91 for the sample file. The COUNT query confirms it against the table. The count should match the number of data rows in the file, with the header row consumed rather than loaded.

Option C: Cloud SQL Studio

Studio runs statements rather than transferring files, so there is no \copy equivalent there. For a small sample file you can paste an INSERT statement with multiple value rows, which avoids both the header-row issue and the need for a local client. For anything larger, use one of the two options above.

5Compose Your Cloud SQL Connection String

Interzoid connects to Cloud SQL the same way any other PostgreSQL client does, using a standard connection string. Google does not hand you a ready-made one, so you assemble it from the instance overview page and the database you created in Step 1.

postgresql://postgres:YOUR-PASSWORD@203.0.113.42:5432/interzoiddemo?sslmode=require

Reading the Connection String

• Username: The built-in postgres user, or the read-only role described below.
• Host: The instance's public IP address, shown on the overview page. Unlike most managed database services, this is a bare IPv4 address rather than a host name. The instance connection name, in the form project:region:instance, is used by the Auth Proxy and does not belong in a direct connection string.
• Port: 5432.
• Database: The database you created from the Databases tab, not the instance ID. These are two different names and it is easy to reach for the wrong one.
• SSL: Use sslmode=require, which matches the SSL mode set in Step 1 and works with an IP address as the host.

CREATE ROLE interzoid_reader LOGIN PASSWORD 'strong-password-here';
GRANT CONNECT ON DATABASE interzoiddemo TO interzoid_reader;
GRANT USAGE ON SCHEMA public TO interzoid_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO interzoid_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO interzoid_reader;

6Launch the Wizard and Enter Your API Key

Open the Postgres Data Matching Wizard in your browser. Before beginning, enter your Interzoid API key in the top-right area of the header bar. Your key is saved in your browser for future sessions.

• API Key Field: Type or paste your API key into the input field in the header. Click the lock or eye icon to toggle visibility.
• Check Credits: Click the Credits button to verify your current balance before starting a job. The sample company file is small, so a demonstration run costs very little.
• Language Selection: Click the language dropdown in the navigation bar to switch between any of the 17 supported languages. The entire interface updates immediately. You can also set the language by URL parameter: ?lang=fr for French, ?lang=ja for Japanese, and so on.

Once your API key is entered, click Get Started on the introduction screen to begin.

7Select a Matching Function

The wizard presents six matching functions. Choose the one that fits your data and use case. Each function card shows a description and the column parameters it requires.

Single-Column Functions

Combination Functions

These functions use two columns together for higher matching precision:

For the sample company data, select Company Name Matching. Click the card for your chosen function, then click Next.

8Connect to Cloud SQL

This is where the connection details from Step 5 come in. The wizard connects to PostgreSQL in real time and presents the available objects at each level through cascading dropdown menus.

Connection Flow

• Switch to connection string mode: Use the toggle at the top of the form to select the connection string option rather than individual fields.
• Paste your string: Drop in the connection string you assembled in Step 5. The field is masked by default. Use the eye icon to reveal it and the clipboard icon to copy it back out.
• Click Connect: The wizard validates the connection. Because the database name is already carried in the connection string, the database dropdown is skipped and you go straight to schema selection.
• Select the schema: Choose public, which is where the table you created in Step 3 lives. Internal schemas such as pg_catalog and information_schema are filtered out of the list.

Using Connection Fields Instead

If you prefer to fill in the fields individually, take the values from the same places: host is the instance's public IP address, port is 5432, username is postgres or your read-only role, database is the one you created in Step 1, and SSL Mode is require.

Saving Your Connection Details

Below the credentials is a Remember these connection details on this browser toggle. It is off by default, and nothing connection-related is saved until you turn it on. When enabled, the wizard stores your connection mode, host, port, username, password, SSL mode, and connection string in your browser. When disabled, any previously saved values are erased immediately. Because the stored values include your password, leave this toggle off on shared computers.

9Select Your Table, Columns, and Options

Choose the table to match against and configure which columns to use for matching and which columns to include in the output.

Table Selection

The wizard presents a dropdown of all tables and views available in the schema you selected. Choose the table holding the records you want to match, such as the companies table you loaded in Step 4. Once selected, the wizard loads the column names from that table in their natural column order.

Match Columns

For each matching parameter required by your chosen function, select the corresponding column from the dropdown. Having chosen Company Name Matching, select the column containing company names.

• Single-column functions: Select one column for the matching parameter.
• Combination functions: Select two different columns, one for each parameter. The two columns must be different.

Output Columns

Use the checkboxes to select which columns you want to include in the match report output. At least one column must be selected. The match columns are automatically included even if you do not check them separately, which ensures the data you matched on always appears in the results.

Output Options

• Show Similarity Keys: When enabled (the default), each output record includes the generated similarity key as the last column. Records sharing a key are matches. Disable this if you want clean output with only the selected data columns.
• Matches Only: When enabled (the default), only records that have at least one other matching record are shown. Disable this to see every record in the table after processing, sorted by similarity key. If you plan to load the results back into Cloud SQL, turn this option off. See Step 12 for why.

Click Next when your selections and options are configured.

10Review and Run the Match

The final screen shows a summary of all your selections: matching function, server, SSL mode, database, schema, table, column assignments, and output options. Review these carefully before proceeding.

Click the green Run Match button to start processing. The wizard will:

• Validate your API key and check that your account has sufficient credits for the job.
• Connect to Cloud SQL and read the selected columns from your table.
• Process each record through the selected matching algorithm using concurrent workers for performance.
• Generate the match report with records sorted and grouped into clusters of matching entries.

A progress indicator is shown while the job runs. Processing time depends on the number of records. The sample company file completes in seconds, while very large tables (up to 500,000 records) may take a minute or more.

11Interpret the Match Report

The match report appears in the results panel at the bottom of the screen. Records are organized into clusters, groups of records that the AI has determined to be matches. Each cluster is separated by a blank line for readability.

Example Output

For a company name match on the sample table, with the company and category columns selected for output and similarity keys enabled:

IBM Corporation,Technology,d477E1d7sG6dja3hDNsk9P
I.B.M. Corp,Information Technology,d477E1d7sG6dja3hDNsk9P

Microsoft Inc.,Software,k8Rp2mNx4wQjL9vB3cYh7T
Microsoft Corporation,Software,k8Rp2mNx4wQjL9vB3cYh7T
MSFT Corp,Technology,k8Rp2mNx4wQjL9vB3cYh7T

In this example, the first cluster contains two records identified as variations of IBM, and the second cluster contains three records identified as variations of Microsoft. The last column in each row is the similarity key. All records sharing the same key are considered matches, even though not one of the company name values is spelled identically to another. Notice that the category values also vary within a cluster, which is exactly the kind of inconsistency a match report surfaces.

What the Clusters Tell You

• Duplicate records: Two or more rows in the same cluster that should be a single record. These are candidates for merging.
• Inconsistent data: Clusters where the entity is clearly the same but the formatting varies. These reveal where standardization is needed upstream.
• Entity resolution: A cluster establishes that separate rows refer to one real-world organization, person, or location, which is the foundation for building a single view of a customer.
• Join keys: The similarity key gives you something to join on across tables where no shared identifier exists. Two tables processed with the same matching function produce the same key for the same entity.

12Save and Use Your Results

Click the Save Results button above the results panel to download the match report as a CSV file. On supported browsers, a save dialog appears allowing you to choose the file name and location. On other browsers, the file downloads automatically.

The saved file is clean, delimited text that can be imported directly into spreadsheets, databases, or other data processing tools for further analysis.

Bringing the Results Back into Cloud SQL

If your goal is to load the results back into Cloud SQL rather than simply review them, go back to the options in Step 9 and uncheck the Matches Only option, leaving Show Similarity Keys enabled.

With Matches Only turned off, every input row is included in the output with its similarity key appended, not just the rows that landed in a cluster. That gives you a complete, one-to-one copy of your source data with a new key column, which is exactly what you want to load back into a Cloud SQL table. Records that matched nothing still carry a key, so nothing is silently dropped and row counts reconcile against the original table.

Create a table for the results, remove the blank cluster separator lines from the file, then load it through the console import or \copy exactly as in Step 4. The match report has no header row, which suits the console import well:

CREATE TABLE match_results (
  company         TEXT,
  category        TEXT,
  similarity_key  TEXT
);

\copy match_results FROM 'C:/data/match_report.csv' WITH (FORMAT csv, ENCODING 'UTF8')

Once the keyed data is in a table, matching becomes an ordinary SQL operation, and you can perform fuzzy joins within your Cloud SQL data tables by joining on the similarity key instead of on exact text:

-- Count the records in each match cluster
SELECT similarity_key, COUNT(*) AS record_count
FROM match_results
GROUP BY similarity_key
HAVING COUNT(*) > 1
ORDER BY record_count DESC;

-- Fuzzy join: match customers to prospects on entity
-- similarity rather than on exact text
SELECT c.company, p.company, c.similarity_key
FROM customer_matches c
JOIN prospect_matches p ON c.similarity_key = p.similarity_key;

Cleaning Up

A Cloud SQL instance bills for every hour it runs. When you are finished testing, either stop the instance, which pauses compute billing while storage and the reserved IP address continue to bill, or delete it outright. Take a final backup or export first if you want the data back later. Removing the authorized network entries is worth doing at the same time.