✨ Generating Match Reports from Amazon Aurora PostgreSQL Data

1Create the Aurora PostgreSQL Cluster

Determine which data table in Aurora 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 an Aurora cluster holding data like that, skip ahead to Step 2.

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

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

Choosing the Right Creation Flow

In the AWS Management Console, open RDS, choose Databases in the left navigation, and click Create database. The console offers an express path that creates Aurora clusters in a single click, and it is worth understanding why that path does not work here.

Filling In the Form

• Engine options: Choose Aurora (PostgreSQL Compatible). This is preselected in most regions, so for this guide it is the tile you want.
• Templates: Dev/Test. This provisions a single writer without the Multi-AZ reader that Production adds, which is what you want for a test.
• DB cluster identifier: A name for the cluster, such as interzoid-aurora-demo. Note that Aurora asks for a cluster identifier rather than an instance identifier, which is the quickest way to confirm you are on the Aurora form rather than the RDS one.
• Master username and password: The default username is postgres. Choose Self managed for the password and record it. Do not choose IAM authentication, for the reason described above.
• Instance configuration: Serverless v2 is the simplest choice for a demonstration, since it scales with load instead of committing you to an instance size. If you prefer provisioned, db.t4g.medium is the smallest Aurora class.
• Public access: Set this to Yes. Without it the cluster has no public endpoint and nothing outside your VPC can reach it, including the matching wizard.
• VPC security group: Choose Create new and name it something like interzoid-aurora-demo-sg. A dedicated group keeps the port open only for this cluster and can be deleted cleanly afterward.
• Set up EC2 connection: Choose Don't connect to an EC2 compute resource. That helper is for applications running inside the same VPC, and it can reset Public access to No.

Serverless v2 Capacity and Cost

Serverless v2 capacity is expressed in Aurora Capacity Units, and you set a minimum and a maximum. The minimum is the setting that matters for a test cluster:

• Minimum capacity 0 ACU: The cluster pauses after a period of inactivity and compute billing stops entirely. Storage continues to bill. This is the cheapest way to leave a demonstration cluster in place between sessions, and it introduces a resume delay covered in Step 8.
• Minimum capacity 0.5 ACU: The cluster stays warm and responds immediately. This is the setting to use while you are actively testing, since it removes the resume delay as a variable.
• Maximum capacity: Lower this from the default. Matching is a straightforward sequential read, so it will not drive capacity anywhere near a high ceiling, and a low maximum caps what a runaway query can cost you. A value of 2 to 4 ACU is ample for a demonstration.

Set the Initial Database Name

Open Additional configuration near the bottom of the form and enter an Initial database name, for example interzoiddemo. This field is easy to miss, and leaving it blank is the single most common snag in this process: Aurora creates the cluster but no application database, so your connection string has nothing useful to point at. If you have already created the cluster without one, connect to the built-in postgres database and run CREATE DATABASE interzoiddemo; instead.

While you are in this section, consider enabling the RDS Data API if you would rather create and load your table through the console than through psql. Step 3 explains what that gets you. Then click Create database. Aurora provisions the cluster and its writer instance, which takes several minutes. The cluster is ready when its status reads Available.

2Open Network Access to the Cluster

An Aurora cluster sits behind a VPC security group that denies all inbound traffic by default, and opening it is where most first connections fail. The important thing to know is which client actually opens the database connection.

Adding the Inbound Rules

On the cluster page, open the Connectivity & security tab and click the VPC security group listed there. In the Inbound rules tab, choose Edit inbound rules and add:

• The Interzoid matching service, so a match can run: Type PostgreSQL, Protocol TCP, Port 5432, Source set to the Interzoid service address as a /32 CIDR block. The current address is published on the service IP addresses page and is also shown on the wizard's connection screen. This is the rule the wizard depends on.
• Your own workstation, so you can build the table: Type PostgreSQL, Protocol TCP, Port 5432, Source My IP, which the console fills in automatically. This rule has nothing to do with matching. It exists only if you plan to create and load the table through psql in Steps 3 and 4. Skip it if you are using the Query Editor, or if you are pointing the wizard at a table that already exists.

Give each rule a description so its purpose is obvious months from now. Rules take effect immediately, with no reboot and no downtime.

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

> nc -zv interzoid-aurora-demo.cluster-ro-abcdefghijkl.us-east-1.rds.amazonaws.com 5432

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

3Create the Target Table

Aurora PostgreSQL gives you two ways to run SQL against the cluster, and this is one of the few places where it is genuinely more convenient than RDS for PostgreSQL.

Option A: The RDS Query Editor

If you enabled the RDS Data API on the cluster, the console offers a browser-based Query Editor. Open the cluster in the RDS console, choose Query Editor, and connect using the database name and either the master credentials or a Secrets Manager secret. No client installation, no security group rule, and no connection string, since the Data API reaches the cluster over an HTTPS endpoint rather than the Postgres wire protocol.

Data API can be enabled after creation as well, through Modify on the cluster. Note that it is available for Aurora PostgreSQL specifically, in a subset of AWS regions, and only against the writer instance.

Option B: psql

Copy the writer endpoint from the Connectivity & security tab. Aurora writer endpoints contain .cluster-, as in interzoid-aurora-demo.cluster-abcdefghijkl.us-east-1.rds.amazonaws.com. Table creation is a write, so it has to go to the writer:

> psql "postgresql://postgres:YOUR-PASSWORD@interzoid-aurora-demo.cluster-abcdefghijkl.us-east-1.rds.amazonaws.com:5432/interzoiddemo?sslmode=require"

root certificate file "./global-bundle.pem" does not exist

> curl -O https://truststore.pki.rds.amazonaws.com/global-bundle.pem

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

The server-side COPY ... FROM '/path/to/file.csv' form does not work on Aurora, because it reads from the database server's own filesystem, which AWS does not give you access to on a managed cluster. The supported approach is the \copy meta-command in psql, which streams the file from your local machine over the connection you already have open.

Download the Sample File

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

Load and Verify

In the same psql session from Step 3, run \copy against the writer endpoint. The column list is given explicitly so that the id column continues to populate from its sequence:

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.

5Compose Your Aurora Connection String

Interzoid connects to Aurora the same way any other PostgreSQL client does, using a standard connection string. AWS does not hand you a ready-made one, so you assemble it from the values on the Connectivity & security tab of the cluster page.

Use the Reader Endpoint

Aurora publishes more than one endpoint for a cluster, and choosing the right one is the most useful decision in this guide. Matching is a read-only workload, so point it at the reader:

The two differ by the -ro in the host name, which is easy to overlook. Reading through the reader endpoint keeps a long match job off the writer, so it never competes with application traffic, and on a cluster with more than one reader instance Aurora load-balances connections across them. On a single-instance Dev/Test cluster the reader endpoint resolves to the writer anyway, so nothing is lost by using it from the start, and nothing needs changing when you later add a reader.

postgresql://postgres:YOUR-PASSWORD@interzoid-aurora-demo.cluster-ro-abcdefghijkl.us-east-1.rds.amazonaws.com:5432/interzoiddemo?sslmode=require

Reading the Connection String

• Username: The master username you set at creation, postgres by default, or the read-only role described below.
• Host: The reader endpoint. Copy it from the console rather than typing it, and check for the -ro.
• Port: 5432 unless you changed it at creation.
• Database: The initial database name from Step 1, not the cluster identifier. These are two different names and it is easy to reach for the wrong one.
• SSL: Use sslmode=require. Aurora PostgreSQL enforces TLS on current versions, and encrypting the connection is worth doing regardless.

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 Amazon Aurora

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 reader endpoint 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 Aurora reader endpoint, port is 5432, username is postgres or your read-only role, database is the initial database name from 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 Aurora, 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 Aurora 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 Aurora

If your goal is to load the results back into Aurora 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 an Aurora table. Records that matched nothing still carry a key, so nothing is silently dropped and row counts reconcile against the original table.

Loading it back uses the same psql approach as Step 4, against the writer endpoint. Create a table for the results, remove the blank cluster separator lines from the file, then run \copy:

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 Aurora 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 Serverless v2 cluster with a minimum of 0 ACU stops charging for compute once it pauses, and it continues to charge for storage, so an idle demonstration cluster is cheap rather than free. When you are finished testing, delete the cluster outright, which on Aurora means deleting its instances and then the cluster itself. Take a final snapshot first if you want the data back later, and remove the inbound security group rules at the same time.