Amazon Redshift reverse ETL

Best Practices

Before you add a Reverse ETL source, you should take some measures to ensure the security of your customers’ data and limit performance impacts to your database and Customer.io workspace.

• Create a new database user/service account. Implement a database user with minimal privileges specifically for Customer.io import/sync operations. This account only requires read permissions with access limited to the tables you want to sync from.

• Avoid using your main database instance. Consider creating a read-only database instance with replication in place, lightening the load and preventing data loss on your main instance.

• Sync only the data that you’ll use in Customer.io. Limiting your query can improve performance, and minimizes the potential to expose sensitive data. Select only the columns you care about, and make sure you use the {{last_sync_time}} to limit your query to data that changed since the previous sync.

• Limit your sync frequency so you don’t sync more than necessary and consume unnecessary resources. If the previous reverse ETL operation is still in progress when the next interval occurs, we’ll skip the operation and catch up your data on the next interval. You should monitor your first few reverse ETL intervals to ensure that your sync doesn’t impact your system’s security and performance—frequently skipped operations may indicate that you’re syncing too often.

Granting us access to your database

We support both SSL and non-SSL database connections. As a part of setup, you'll need to provide the credentials of a database user with read-access to the tables you want to select data from.

This integration supports SSH tunneling. You can configure SSH tunneling when you set up your database connection in Customer.io.

If you use a firewall or an allowlist, you must allow the following IP addresses so we can connect to your database. Make sure you use the correct IP addresses for your account region.

Set up a Service Account

When you set up an Amazon Redshift reverse ETL integration, you should create a user account that Customer.io will use to connect to Redshift. This user has limited access to your Redshift instance and gives you granular control over access to your Redshift instance.

1. Log in to Redshift and select the Redshift cluster you want to integrate with Customer.io.

2. Run the commands below to create a user named customerio.

    -- create a user named "customerio"
    CREATE USER customerio PASSWORD '<enter password here>';
   
    -- allows the "customerio" user to create new schemas on the specified database. 
    -- (This is the name you chose when provisioning your cluster)
    GRANT CREATE ON DATABASE "<enter database name here>" TO "customerio";

Add Amazon Redshift to Customer.io

As a part of this setup, you’ll provide Customer.io with user credentials that we’ll use to query your database. We recommend that you create a new user with Read Only access specifically for Customer.io, so you can manage Customer.io access to your database independent of any other Microsoft SQL users you have.

1. Go to Integrations. In the Directory tab, pick Amazon Redshift

2. Provide your database information, including credentials to connect to your database, and click Save database.

   • The Name is a friendly name you’ll use to recognize your database whenever you reference it in Customer.io.
   • Enter Host address and the name of the database you want to connect to.
   • Enter a database user’s credentials and click Add database. We suggest that you create a service account and use service account credentials to set up your database.
   • Optional: Set up an SSL connection to your database.
   • Optional: Set up SSH tunneling.

   

3. Set up a Sync. A sync is the type of data (identify, track, etc) you want to import from your database and click Next: Define Query. You can set up syncs for each type of data you want to import.

   1. Provide a Name and Description for the sync. This helps you understand the sync at a glance when you look at your integration’s Overview later.
   2. Select the type of data you want to import.
   3. Set the Sync Frequency, indicating how often you want to query your database for new data. You should set the frequency such that sync operations don’t overlap. Learn more about sync frequency.
   4. Select when you want to start the sync: whether you want to begin importing data immediately, or schedule the sync to start at a later date.

      

4. Enter the query that selects the data you want to import. See Queries below for more information about the information you’ll want to select for your sync. Click Run Query to preview results and make sure that your query selects the right information.

   

5. Click Enable to enable your sync.

Now you can set up additional syncs and connect your integration one or more destinations.

Adding syncs

After you set up your incoming integration, you can add additional syncs to import different types of data from your database. For example, you might want to import identify data for your users, and track data for their actions. Subsequent syncs can rely on your existing database, or you can add another database within your integration.

1. In your integration, go to the Syncs tab and click Add Sync.
2. Select your database or add a new one and click Next: Create Sync.
3. Set up a sync and click Next: Define Query. You can set up syncs for each type of data you want to import.
   1. Provide a Name and Description for the sync. This helps you understand the sync at a glance when you look at your source Overview later.
   2. Select the type of data you want to import.
   3. Set the Sync Frequency, indicating how often you want to query your database for new data. You should set the frequency such that sync operations don’t overlap. Learn more about sync frequency.
   4. Select when you want to start the sync: whether you want to begin importing data immediately, or schedule the sync to start at a later date.

      

4. Enter the query that selects the data you want to import. See Queries below for more information about the information you’ll want to select for your sync. Click Run Query to preview results and make sure that your query selects the right information.

   

5. Click Enable to enable your sync.

Sync Frequency

You can sync data as often as every minute. However, we recommend that you set your sync frequency such that sync operations don’t overlap. If you schedule syncs such that a sync operation is scheduled to start while the previous operation is still we’ll skip the next sync operation.

Semantic events: Deleting profiles, groups, and more

You may notice that this integration doesn’t have sync types to delete profiles, groups, or other objects. To do these kinds of operations, you’ll use what we call semantic events. These are events with specific names that indicate a delete operation. When your Track sync picks up events with an event name we recognize, we’ll perform the associated action—like deleting a profile or group.

For example, if you send an event with the name User Deleted, we’ll delete the profile from your workspace. See Customer.io Semantic Events for more information.

The semantic events we support are:

Queries for each sync type

When you create a database sync, you provide a query selecting the profiles or objects you want to import, and respective properties. You’ll build your queries using the same principles from our Pipelines API.

Each row returned from your query represents an individual operation (like an identify call, a track event, etc). Columns represent the traits or properties that you want to apply to the profile, group, or event that your sync imports.

While we support queries that return millions of rows and hundreds of columns, syncing large amounts of data more then once a day can impact your account’s performance—including delaying automations or messages. When you set up your query, consider how much data you want to send and how often; and make sure you limit your results using the last_sync_time.

Preserve column name casing with double quotes

Redshift lowercases unquoted identifiers and attributes, which are case-sensitive in Customer.io. Column names like userId and groupId become userid and groupid in your query results.

With identifiers, this can cause unexpected behavior; by default, Customer.io doesn’t recognize userid or groupid as valid identifiers. For attributes, this can cause unexpected behavior or mismatched attributes.

SELECT id AS "userId", email_address AS "email"
FROM users
WHERE last_updated >= {{last_sync_time}}

SET enable_case_sensitive_identifier TO true;

last_sync_time and limiting your results

We strongly recommend that you index a column in your database representing the date-time each row was last-updated. When you write your query, you should add a WHERE clause comparing your “last updated” column to the {{last_sync_time}}.

The last sync time is a Unix timestamp representing the date-time when the previous successful sync started. Comparing a “last-updated” column to this timestamp helps you limit your sync operations to the columns that changed since the previous sync.This helps you avoid syncing the same records repeatedly, which can make your syncs take longer and impact your workspace’s performance.

If you use ISO date-times, you can convert them to Unix timestamps in your query.

Identify

The identify method tells us who someone is and lets you assign unique traits to a person. Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

You can identify profiles by anonymousId and/or userId.

• anonymousId only: This assigns traits to a profile before you know who they are.
• userId only: Identifies a user and sets traits.
• both userId and anonymousId: Associates the data from the anonymousId with the person you identify by userId.

SELECT id AS "userId", email_address AS "email", fname, lname, msisdn AS "phone"
FROM users
WHERE last_updated >= {{last_sync_time}}

Identify profiles by email or ID

If you identify profiles by email and a unique ID, you can use a CASE statement or the COALESCE function to set the userId to prioritize the customer ID when available, falling back to email for profiles that don’t have a unique ID yet. This kind of setup is common when you support both leads (identified by email) and customers (identified by a unique ID after they make a purchase, or otherwise convert).

SELECT
  COALESCE(CAST(user_id AS VARCHAR), email) AS "userId",
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

SELECT
  CASE
    WHEN user_id IS NOT NULL THEN CAST(user_id AS VARCHAR)
    ELSE email
  END AS "userId",
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}

Track

The track method records things profiles do. Every track call represents an event.

You should track your audience’s activities with events both as performance indicators and so you can respond to your audience’s activities with automations in Journeys. For example, if your audience performs a Video Viewed or Item Purchased event, you might respond with other videos or products the profile might enjoy.

Track calls require an event name describing what a person did. They must also include an anonymousId or a userId. Calls that you make with an anonymousId are associated with a userId when you identify someone by their userId.

In most cases, your query should compare a timestamp to the last_sync_time to ensure that you only import new events.

SELECT id AS "userId", event_name AS event, products, total_price AS value
FROM events
WHERE timestamp > {{last_sync_time}}

Backfilling events

In your initial sync, the last_sync_time is 0, and we’ll capture all events that otherwise match your query. After that, we only capture events that occur after the last_sync_time—events that occurred since the previous sync. This prevents you from importing the same events multiple times, but also means that you can’t backfill event history.

If you need to backfill event history after your initial sync, you’ll need to set up a new sync to import the events you want to backfill. In general, you’ll:

1. Create a new sync with a new query that captures the events you want to backfill.
2. Run the sync to backfill events.
3. Disable the backfilling sync so that you don’t capture events that your normal event query would otherwise import.

Group

The Group method associates a profile with a group—like a company, organization, project, online class or any other collective noun you come up with for the same concept. In Customer.io Journeys, we call groups objects. If the group/object or profile in your group call don’t exist, this operation creates them.

Group calls require a groupId to represent the group. In almost every case, a group call should also include a userId to associate the profile with the group. You can also include traits to provide additional information about the group (or the relationship between the profile and the group). Find more details about the group method in our API specifications.

Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

SELECT companyId AS "groupId", objectTypeId AS "objectTypeId", companyname, employees, personId AS "userId"
FROM companies
WHERE last_updated >= {{last_sync_time}}

Relationship attributes

In Customer.io, you can assign attributes to both the group (called a custom object in Customer.io) and to the relationship between the object and the profile. By default, attributes are stored on the custom object itself, but you can assign relationship attributes using the relationshipAttributes JSON object.

SELECT companyId AS "groupId", objectTypeId AS "objectTypeId", companyname, employees, personId AS "userId",
    JSON_SERIALIZE(
        OBJECT(
            'is_manager', is_manager,
            'role', role,
            'start_date', start_date,
            'department', department
        )
    ) AS "relationshipAttributes"
FROM companies
WHERE last_updated >= {{last_sync_time}}

Page

The Page method records page views on your website, along with optional extra information about the page a person visited.

Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

SELECT id AS "userId", metatitle AS name, url, time_on_page
FROM pages
WHERE timestamp > {{last_sync_time}}

Screen

The Screen method sends screen view events for mobile devices. These help you understand the screens that profiles use in your app.

Your query should compare a timestamp to the last_sync_time to ensure that you only import new data.

SELECT id AS "userId", screen_name AS name, session_started
FROM screens
WHERE timestamp > {{last_sync_time}}

Alias

The Alias method combines two previously unassociated user identities. Some integrations automatically reconcile profiles with different identifiers based on whether you send anonymousId, userId, or another trait that the integration expects to be unique. But for integrations that don’t, you may need to send alias requests to do this.

In general, you won’t need to use the alias call; we try to handle user identification gracefully so you don’t need to merge profiles. But you may need to send alias calls to manage user identities in some data-out integrations.

For example, in Mixpanel it’s used to associate an anonymous user with an identified user once they sign up.

SELECT id AS "userId", old_id AS "previousId"
FROM user_resolution
WHERE timestamp >= {{last_sync_time}}