Skip to main content
Log inGet a demo

Identity Resolution in SQL

What is identity, and how does it relate to customer data? Identity can have many different meanings but essentially, it involves unifying different pieces of data about your customers. Read on to learn more.

Joshua Curl.

Joshua Curl

November 4, 2020

7 minutes

Identity Resolution in SQL.

First off: what is identity, and how does it relate to customer data? Identity can have many different meanings, but they all involve unifying different pieces of data around your customers. Identity can include projects like merging CRM data with product usage data, or bridging user data between multiple business units.

This blog post is going to focus on a simple, common scenario around first-party event data. This is data that you collect on your customer, rather than receiving from another source. These events are typically collected on landing pages and online applications. First-party event data usually falls into two categories:

Anonymous data - the user has not logged in, and so events are only associated with a randomly generated anonymous ID

Identified data - the user has logged in and events are associated with a known user ID

A common problem with collecting this event data is in unifying identified and anonymous data once a user logs in.

Let's imagine we're an e-commerce store. Users can come to our site, browse items anonymously, and then optionally login and purchase items. Let's also assume that we're collecting events for both anonymous (before login) and identified (after login) traffic on our site.

The challenge now is how to associate and unify anonymous data and identified data. Specifically, the challenge is in asking questions like "How many times did this user view products of this category?" when the events they performed while anonymous aren't associated with their user ID.

This blog posts assumes all of these events are collected into a modern warehouse like Snowflake or BigQuery, and then proposes a warehouse-native solution to this problem. If this feels daunting, or you're looking for a code-free way to unify identities within the data warehouse, we've built an Identity Resolution feature that will help you instead.

Warehouse specifics

Typically services like Segment, Google Analytics, RudderStack, or in-house solutions handle event collection and then push these events into a data warehouse. All events around your users are stored in a single place, alongside all of the other customer data relevant to your business.

Depending on the specific event collection service being used, the data will be loaded in your warehouse with slightly different schemas. In this article we'll use a schema resembling that of Segment. Most event collection schemas look quite similar, so even if yours doesn't look exactly like this much of the principles in this blog post remain the same.

We're going to have two tables in our warehouse relating to event collection. Normally these tables are provisioned by the service pushing events into your warehouse, but we'll show the schemas here to give a sense of how this data is usually structured.


The events table holds a sequence of events: the name of the event, relevant properties, the timestamp, and who performed it. If the the event is anonymous, it'll include only an anonymous ID. If the event is identified, it'll include a user ID, and typically an anonymous ID as well.

    timestamp timestamp,
    user_id text,
    anonymous_id text,
    event text,
    properties jsonb


The user identities table tracks relationships between anonymous and user IDs. This table is usually populated during user during signup or login.

CREATE TABLE user_identities(
    user_id text,
    anonymous_id text,
    PRIMARY KEY ( user_id, anonymous_id )

Here's the challenge, and where identity handling comes in. The data in these tables aren't in a format that's ideal for analysis. If you wanted to email all users who viewed a certain item, you'd likely want to include users who viewed that item anonymously (before they logged in).


Let's explore a solution where identity is resolved in the warehouse. Specifically, we're going to produce a table called identified_events that contains events after identity resolution.

Here's an example scenario that we can work with. Someone visits your site, views two items, logs in, and then views two more products.

example=> select * from events;

2020-09-10 01:52:40.844441782d69de-86cd-41d2-8cb8-4d27d1f2ff39Product Viewed{"product_id": 100}
2020-09-10 01:53:40.844441782d69de-86cd-41d2-8cb8-4d27d1f2ff39Product Viewed{"product_id": 105}
2020-09-10 01:54:40.8444418fe77474-2c75-471e-8334-ee947b98a5a3782d69de-86cd-41d2-8cb8-4d27d1f2ff39Logged In{}
2020-09-10 01:55:40.8444418fe77474-2c75-471e-8334-ee947b98a5a3782d69de-86cd-41d2-8cb8-4d27d1f2ff39Product Viewed{"product_id": 111}
2020-09-10 01:59:40.8444418fe77474-2c75-471e-8334-ee947b98a5a3782d69de-86cd-41d2-8cb8-4d27d1f2ff39Product Viewed{"product_id": 115}

(4 rows)

Notice how user_id is NULL for the first two events. These events were actually performed by user with ID 8fe77474-2c75-471e-8334-ee947b98a5a3, but the raw data doesn't reflect this. In identified_events we'd want this user ID to be backfilled into these events.

When using a service like Segment, you generally perform an identify call on login. This call takes both a user ID and anonymous ID and gets populated in the user_identities table.

example=> select * from user_identities;

(1 row)

This is the table that establishes the link between user ID 8fe77474-2c75-471e-8334-ee947b98a5a3 and 782d69de-86cd-41d2-8cb8-4d27d1f2ff39. As a result, this is the table we're going to join our events table with to backfill user IDs.

We're going to create identified_events as a SQL view. To summarize the logic in this view, we're filling in the user ID of the event by looking up the correct ID in the user_identities table, and leaving events that already have a user ID the same.

CREATE VIEW identified_events AS
    FROM events
    JOIN user_identities ON user_identities.anonymous_id = events.anonymous_id
    WHERE events.user_id IS NULL
    FROM events
    WHERE events.user_id IS NOT NULL

Now if we look at identified_events we'll get the following.

dbt=> select * from identified_events;
2020-09-10 01:52:40.8444418fe77474-2c75-471e-8334-ee947b98a5a3782d69de-86cd-41d2-8cb8-4d27d1f2ff39Product Viewed{"product_id": 100}
2020-09-10 01:52:40.8444418fe77474-2c75-471e-8334-ee947b98a5a3782d69de-86cd-41d2-8cb8-4d27d1f2ff39Product Viewed{"product_id": 115}
2020-09-10 01:52:40.8444418fe77474-2c75-471e-8334-ee947b98a5a3782d69de-86cd-41d2-8cb8-4d27d1f2ff39Product Viewed{"product_id": 105}
2020-09-10 01:52:40.8444418fe77474-2c75-471e-8334-ee947b98a5a3782d69de-86cd-41d2-8cb8-4d27d1f2ff39Product Viewed{"product_id": 111}

(4 rows)

Success! We can now use the identified_events view anywhere we would have used the raw events table.

For simple query example, we could find the number of product views for user with ID '8fe77474-2c75-471e-8334-ee947b98a5a3.

dbt=> select count(*) from identified_events where user_id = '8fe77474-2c75-471e-8334-ee947b98a5a3';

(1 row)

Were we using the raw events table, the result would be two, which is not the full picture for this user.

Depending on the situation, it might make sense to make identified_events a materialized view. These views are pre-computed and saved to disk. The upside is that querying these views are faster because they're pre-computed, and the downside is that they need to be "refreshed" and can contain stale data. dbt ( is a popular tool for organizing and scheduling materialized views.


What this blog post contained was a very simple example of identity, namely joining anonymous and identified event streams. Identity projects as a whole can be much more complicated. Here are some examples of other scenarios where getting a unified view of customer identity is much harder.

• You have multiple business units, each with completely different applications, SaaS tooling, and user databases

• You're a B2B company, and context on your customer is scattered across systems like Salesforce, Zendesk, and internal databases

• You have both an online and physical (brick-and-mortar) presence

Identity problems and solutions vary a lot between companies are highly specific to your business. At Hightouch, we believe the best place to manage customer identity is in data warehouses, the typical place where all data on your customers resides. This is why we've built a feature to help organizations resolve complex identity and entity resolution processes in their warehouse, with a code-free editor.

A special thanks to Huy Nguyen of Holistics and Graham Murphy for giving feedback on this article!‍

More on the blog

  • What is Reverse ETL? The Definitive Guide .

    What is Reverse ETL? The Definitive Guide

    Learn how Reverse ETL works, why it's different from traditional ETL, and how you can use it to activate your data.

  • Friends Don’t Let Friends Buy a CDP.

    Friends Don’t Let Friends Buy a CDP

    How spending the first half of his professional career at Segment drove Tejas Manohar to disrupt the 3.5 billion dollar CDP category.

  • What is a Composable CDP?.

    What is a Composable CDP?

    Learn why Composable CDPs are seeing such rapid adoption, how they work, and why they're replacing traditional CDPs.

Recognized as an industry leader
by industry leaders


Reverse ETL Category Leader


Marketplace Partner of the Year


Cool Vendor in Marketing Data & Analytics


Ecosystem Partner of the Year


Best Estimated ROI


One to Watch for Activation & Measurement


CDP Category Leader


Easiest Setup & Fastest Implementation

Activate your data in less than 5 minutes