> This page is part of the [Customer.io documentation](https://docs.customer.io). For the complete index, see [llms.txt](https://docs.customer.io/llms.txt).
> Last updated: November 20, 2026
> To take actions in Customer.io from an agent, use the [Customer.io CLI](https://docs.customer.io/ai/cli/get-started.md) or the [MCP server](https://docs.customer.io/ai/mcp/get-started.md).
> This page has tabbed examples for: Redshift, Databricks, BigQuery, Microsoft SQL, MySQL, PostgreSQL, Snowflake, COALESCE, CASE. Add `?tab=<name>` to the URL to get only that tab, and repeat it for more than one, for example `?tab=redshift&tab=databricks`.

# Queries for each sync type

Every reverse ETL sync runs a SQL query that you write. This page shows what each sync type expects, with examples for each database we support.

When you create a sync, you provide a query selecting the people or objects you want to import, and their properties. You build your queries using the same principles as our [Pipelines API](https://docs.customer.io/integrations/api/cdp/): each row your query returns becomes one API call.

Choose your database from the **Database** menu, and every example on the page switches to it.

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 person, 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`](#last-sync-time).

Make sure you compare timestamps against `last_sync_time`

Our examples below include a `last_sync_time` value. **You must compare a timestamp to this value** to avoid sending duplicate traffic to Customer.io which could impact your workspace’s performance.

## Column names and data types

<!-- tabs -->

<!-- tab: Redshift -->

#### Redshift

Redshift lowercases unquoted identifiers. Customer.io recognizes identifiers like `userId` and `groupId` in any case, but every other column becomes an [attribute](https://docs.customer.io/journeys/people/manage/attributes/) named exactly as Redshift returns it, and attribute names are case-sensitive. An alias like `firstName` arrives as `firstname`. Double-quote the aliases you want to preserve:

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

Enable case-sensitive identifiers in your Redshift cluster

By default, Redshift ignores the casing of double-quoted identifiers. Set `enable_case_sensitive_identifier` to `true` for double-quoted aliases to preserve their casing.

Enable it for your cluster or workgroup in the [parameter group settings ↗](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-parameter-groups.html), or set it per session:

```
SET enable_case_sensitive_identifier TO true;
```

Set it in your parameter group so it applies to every session, including the queries Customer.io runs on your behalf.

<!-- /tab -->

<!-- tab: Databricks -->

#### Databricks

Databricks `ARRAY`, `MAP`, and `STRUCT` columns arrive in Customer.io as structured JSON. An `ARRAY` column becomes an array attribute, and a `STRUCT` or `MAP` column becomes an object attribute. You don’t need to serialize these types yourself. Select the column and we handle the conversion.

Databricks resolves unqualified table names against the catalog and schema on your connection. Fully qualify anything else as `catalog.schema.table`.

<!-- /tab -->

<!-- tab: BigQuery -->

#### BigQuery

When you set up a sync, you’re modeling your data in the [Pipelines API](https://docs.customer.io/integrations/api/cdp/) format. Some attributes in our API are nested, and you might want to nest others based on how you expect them in Customer.io or in the destinations you send data to.

To nest data, build a `json_object` from items in your query, alternating keys and values in order: `key, value, key, value`.

```
SELECT
   id AS groupId,
   name,
   json_object(
      'job_title', job_title,
      'primary_contact', primary_contact
   ) AS relationshipAttributes
```

You do the same for other nested objects, like device context. See the [Pipelines API documentation](https://docs.customer.io/integrations/api/cdp/) for the structure of our payloads.

<!-- /tab -->

<!-- tab: Microsoft SQL -->

#### Microsoft SQL

Map column names to attributes in Customer.io by aliasing each column to the name you want in Customer.io, and we use it as written.

<!-- /tab -->

<!-- tab: MySQL -->

#### MySQL

Map column names to attributes in Customer.io by aliasing each column to the name you want in Customer.io, and we use it as written.

<!-- /tab -->

<!-- tab: PostgreSQL -->

#### PostgreSQL

PostgreSQL lowercases unquoted aliases. Customer.io recognizes identifiers like `userId` and `groupId` in any case, but every other column becomes an [attribute](https://docs.customer.io/journeys/people/manage/attributes/) named exactly as PostgreSQL returns it. An alias like `firstName` arrives as `firstname`. Double-quote an alias to keep its case, like `first_name AS "firstName"`.

<!-- /tab -->

<!-- tab: Snowflake -->

#### Snowflake

Snowflake column names are in all caps, but Customer.io and the destinations you send Snowflake data to don’t typically expect property names formatted that way. You’ll need to convert your column names to lowercase, snake-cased formats, or whichever format fits your data model outside of Snowflake. If you have a column named `EMAIL`, convert it to `email` to use it as an identifier in Customer.io Journeys.

Queries aren’t case-sensitive, but results are. Snowflake returns the same rows whether you write `EMAIL`, `email`, or `Email`. The column name stays `EMAIL` unless you change it with `AS`, and that name is the trait we record in Customer.io.

```
SELECT ID AS "id", EMAIL AS "email", PRIMARY_PHONE AS "phone"
FROM people
WHERE LAST_UPDATED >= {{last_sync_time}}
```

If Snowflake returns uppercase attributes despite the `AS` clause, adjust the `QUOTED_IDENTIFIERS_IGNORE_CASE` setting for your Snowflake user or role. `QUOTED_IDENTIFIERS_IGNORE_CASE` controls whether Snowflake treats quoted identifiers as case-sensitive. Set it at the role or user level rather than the account level, so you don’t affect other consumers of your Snowflake data.

```
ALTER USER customerio_reverse_etl_user
  SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;

-- or, for the Customer.io role
ALTER ROLE customerio_reverse_etl
  SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;
```

Snowflake stores boolean values as binary (0 and 1) to reduce file size on disk, and its UI may display them as “True” or “False” even though the underlying data is binary. As [Snowflake’s documentation ↗](https://docs.snowflake.com/en/user-guide/binary-input-output.html#example-input-output) puts it: “BINARY input/output can be confusing because what you see is not necessarily what you get.”

Convert binary booleans to strings with Snowflake’s `IFF` function, so your teammates can set branch and action conditions on `true`/`false` rather than `1`/`0`:

```
SELECT
  ID AS "userId",
  IFF(IS_ACTIVE, 'true', 'false') AS "is_active"
FROM
  users
WHERE
  LAST_UPDATED >= {{last_sync_time}}
```

<!-- /tab -->

<!-- /tabs -->

## `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.

This value is `0` until at least one sync is *Completed*

If you’re just getting started, or if all of your previous syncs have a **Some Rows Failed** status, this value is `0`. If your previous syncs show **Some Rows Failed**, you should download the error report and fix those errors so that an import finishes completely and the `last_sync_time` obtains a non-zero value.

<!-- tabs -->

<!-- tab: Redshift -->

#### Redshift

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

<!-- /tab -->

<!-- tab: Databricks -->

#### Databricks

We replace the placeholder with a plain number of Unix seconds. If your column stores a `TIMESTAMP` or a `DATE`, Databricks won’t compare it to a number, and the query fails with a type error. You need to convert the placeholder to match your column:

```
SELECT user_id AS userId, email
FROM users
WHERE updated_at >= timestamp_seconds({{last_sync_time}})
```

You can convert either side of the comparison. We recommend converting the placeholder, because Databricks applies `timestamp_seconds()` to a single number, while `unix_timestamp(updated_at)` runs against every row the query scans.

The Databricks examples on this page assume `TIMESTAMP` columns and use this conversion. If your column stores Unix seconds instead, compare it to `{{last_sync_time}}` directly.

The Databricks examples use `>=` rather than `>`. A strict `>` can permanently skip a row whose timestamp exactly matches the last sync time. With `>=`, the sync reads that row again on the next run, and we recognize it as a duplicate and don’t process it twice. Compare against a timestamp that changes per row, like a last-updated column, so the number of rows the sync reads again stays small.

When you preview a query, we set `{{last_sync_time}}` to 30 days ago. To use a different date, click the gear icon next to the `last_sync_time` date in the preview. If your table’s timestamps are older than the preview date, the preview can return no rows even though a real sync would.

<!-- /tab -->

<!-- tab: BigQuery -->

#### BigQuery

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

<!-- /tab -->

<!-- tab: Microsoft SQL -->

#### Microsoft SQL

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

<!-- /tab -->

<!-- tab: MySQL -->

#### MySQL

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

<!-- /tab -->

<!-- tab: PostgreSQL -->

#### PostgreSQL

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

<!-- /tab -->

<!-- tab: Snowflake -->

#### Snowflake

The examples below compare your “last updated” column directly to the placeholder, which works when that column stores Unix seconds. If it stores a date-time instead, convert one side of the comparison so both sides are the same type.

<!-- /tab -->

<!-- /tabs -->

## Identify

The `identify` method tells us who someone is and lets you assign unique [traits](https://docs.customer.io/journeys/people/manage/attributes/) 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 people by `anonymousId` and/or `userId`.

*   **`anonymousId` only**: This assigns traits to a person 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`.

<!-- tabs -->

<!-- tab: Redshift -->

#### Redshift

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

<!-- /tab -->

<!-- tab: Databricks -->

#### Databricks

```
SELECT user_id AS userId, email, phone, first_name, last_name
FROM users
WHERE updated_at >= timestamp_seconds({{last_sync_time}})
```

<!-- /tab -->

<!-- tab: BigQuery -->

#### BigQuery

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

<!-- /tab -->

<!-- tab: Microsoft SQL -->

#### Microsoft SQL

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

<!-- /tab -->

<!-- tab: MySQL -->

#### MySQL

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

<!-- /tab -->

<!-- tab: PostgreSQL -->

#### PostgreSQL

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

<!-- /tab -->

<!-- tab: Snowflake -->

#### Snowflake

```
SELECT ID AS "userId", EMAIL AS "email", MSISDN AS "phone", FNAME AS "first_name", LNAME AS "last_name"
FROM users
WHERE TIMESTAMP >= {{last_sync_time}}
```

<!-- /tab -->

<!-- /tabs -->

One of:Known UserAnonymous User

*   userIdstringrequired

    The unique identifier for a person. This value should be unique across systems, so you recognize the same person in your sources \_and\_ destinations.

*   anonymousIdstring

    A unique substitute for a User ID in cases when you don’t have an absolutely unique identifier. Our libraries generate this value automatically to help you track people before they sign up, log in, provide their email, etc.

*   contextone of

    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.

*   integrationsobject

    Contains a list of booleans indicating the integrations that are enabled (true) or disabled (false). By default, all integrations are enabled (returning an empty object). Set `"All": false` to reverse this behavior.

*   messageIdstring

    A unique identifier for a Pipelines call, ensuring that each individual event is unique. This is set by Customer.io

*   originalTimestampstring(date-time)

    In general, you can use `timestamp` rather than this field if you want to back-date events. This is the timestamp on the client device you invoke a call or the timestamp value you manually passed in a server-side library call.

*   sentAtstring(date-time)

    The ISO-8601 timestamp when a library sends an event to Pipelines.

*   timestampstring(date-time)

    The ISO-8601 timestamp when the event originally took place. This is mostly useful when you backfill past events. If you're not backfilling data, you can leave this field empty and we'll use the current time or server time.

*   traitsobject

    Additional properties that you know about a person. We've listed some common/reserved traits below, but you can add any traits that you might use in another system.

### Identify people by email or ID

If you identify people by email and by a unique ID, use a `CASE` statement or the `COALESCE` function to set the `userId` to the customer ID when it’s available and fall back to email for people who don’t have one yet. This is common when you support both leads, identified by email, and customers, identified by a unique ID after they convert.

<!-- tabs -->

<!-- tab: Redshift -->

#### Redshift

<!-- tabs -->

<!-- tab: COALESCE -->

#### COALESCE

`COALESCE` returns the first value in the list that isn’t null:

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

<!-- /tab -->

<!-- tab: CASE -->

#### CASE

`CASE` checks whether `user_id` exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

```
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}}
```

<!-- /tab -->

<!-- /tabs -->

<!-- /tab -->

<!-- tab: Databricks -->

#### Databricks

<!-- tabs -->

<!-- tab: COALESCE -->

#### COALESCE

`COALESCE` returns the first value in the list that isn’t null:

```
SELECT
  COALESCE(CAST(user_id AS STRING), email) AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE updated_at >= timestamp_seconds({{last_sync_time}})
```

<!-- /tab -->

<!-- tab: CASE -->

#### CASE

`CASE` checks whether `user_id` exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

```
SELECT
  CASE
    WHEN user_id IS NOT NULL THEN CAST(user_id AS STRING)
    ELSE email
  END AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE updated_at >= timestamp_seconds({{last_sync_time}})
```

<!-- /tab -->

<!-- /tabs -->

<!-- /tab -->

<!-- tab: BigQuery -->

#### BigQuery

<!-- tabs -->

<!-- tab: COALESCE -->

#### COALESCE

`COALESCE` returns the first value in the list that isn’t null:

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

<!-- /tab -->

<!-- tab: CASE -->

#### CASE

`CASE` checks whether `user_id` exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

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

<!-- /tab -->

<!-- /tabs -->

<!-- /tab -->

<!-- tab: Microsoft SQL -->

#### Microsoft SQL

<!-- tabs -->

<!-- tab: COALESCE -->

#### COALESCE

`COALESCE` returns the first value in the list that isn’t null:

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

<!-- /tab -->

<!-- tab: CASE -->

#### CASE

`CASE` checks whether `user_id` exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

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

<!-- /tab -->

<!-- /tabs -->

<!-- /tab -->

<!-- tab: MySQL -->

#### MySQL

<!-- tabs -->

<!-- tab: COALESCE -->

#### COALESCE

`COALESCE` returns the first value in the list that isn’t null:

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

<!-- /tab -->

<!-- tab: CASE -->

#### CASE

`CASE` checks whether `user_id` exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

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

<!-- /tab -->

<!-- /tabs -->

<!-- /tab -->

<!-- tab: PostgreSQL -->

#### PostgreSQL

<!-- tabs -->

<!-- tab: COALESCE -->

#### COALESCE

`COALESCE` returns the first value in the list that isn’t null:

```
SELECT
  COALESCE(user_id::TEXT, email) AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}
```

<!-- /tab -->

<!-- tab: CASE -->

#### CASE

`CASE` checks whether `user_id` exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

```
SELECT
  CASE
    WHEN user_id IS NOT NULL THEN user_id::TEXT
    ELSE email
  END AS userId,
  email,
  first_name,
  last_name
FROM users
WHERE last_updated >= {{last_sync_time}}
```

<!-- /tab -->

<!-- /tabs -->

<!-- /tab -->

<!-- tab: Snowflake -->

#### Snowflake

You need to use `AS` with quotes so the column name stays lowercase.

<!-- tabs -->

<!-- tab: COALESCE -->

#### COALESCE

`COALESCE` returns the first value in the list that isn’t null:

```
SELECT
  COALESCE(CAST(USER_ID AS VARCHAR), EMAIL) AS "userId",
  EMAIL AS "email",
  FIRST_NAME AS "first_name",
  LAST_NAME AS "last_name"
FROM users
WHERE LAST_UPDATED >= {{last_sync_time}}
```

<!-- /tab -->

<!-- tab: CASE -->

#### CASE

`CASE` checks whether `USER_ID` exists. If it does, the query converts the ID to a string. If it doesn’t, the query uses the email address:

```
SELECT
  CASE
    WHEN USER_ID IS NOT NULL THEN CAST(USER_ID AS VARCHAR)
    ELSE EMAIL
  END AS "userId",
  EMAIL AS "email",
  FIRST_NAME AS "first_name",
  LAST_NAME AS "last_name"
FROM users
WHERE LAST_UPDATED >= {{last_sync_time}}
```

<!-- /tab -->

<!-- /tabs -->

<!-- /tab -->

<!-- /tabs -->

## Track

[The `track` method](https://docs.customer.io/integrations/api/cdp/tag/default/track/) records things people 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](https://docs.customer.io/journeys/send/campaigns/overview/) in Journeys. For example, if your audience performs a **Video Viewed** or **Item Purchased** event, you might respond with other videos or products the person 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.**

<!-- tabs -->

<!-- tab: Redshift -->

#### Redshift

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

<!-- /tab -->

<!-- tab: Databricks -->

#### Databricks

```
SELECT user_id AS userId, event_name AS event, products, total_price AS value
FROM events
WHERE created_at >= timestamp_seconds({{last_sync_time}})
```

<!-- /tab -->

<!-- tab: BigQuery -->

#### BigQuery

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

<!-- /tab -->

<!-- tab: Microsoft SQL -->

#### Microsoft SQL

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

<!-- /tab -->

<!-- tab: MySQL -->

#### MySQL

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

<!-- /tab -->

<!-- tab: PostgreSQL -->

#### PostgreSQL

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

<!-- /tab -->

<!-- tab: Snowflake -->

#### Snowflake

```
SELECT ID AS "userId", EVENT_NAME AS "event", PRODUCTS AS "products", TOTAL_PRICE AS "value"
FROM events
WHERE TIMESTAMP > {{last_sync_time}}
```

<!-- /tab -->

<!-- /tabs -->

One of:Known UserAnonymous UserGroup

*   eventstringrequired

    The name of the event

*   userIdstringrequired

    The unique identifier for a person. This value should be unique across systems, so you recognize the same person in your sources \_and\_ destinations.

*   contextone of

    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.

    One of:Non-mobileMobile

    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.

    *   activeboolean

        Whether a user is active.

        This is usually used when you send an .identify() call to update the traits independently of when you've “last seen” a user.

    *   campaignobject

        Contains information about the campaign that resulted in the API call, gathered from, or mapping to, UTM parameters (e.g. `utm_source`).

    *   channelstring

        The channel the event originated from.

        Accepted values: `browser`, `server`, `mobile`

    *   ipstring

        The user's IP address. This isn't captured by our libraries, but by our servers when we receive client-side events (like from our JavaScript source).

    *   localestring

        The locale string for the current user, e.g. `en-US`.

    *   pageobject

        Contains information about the current page in the browser. This is automatically collected by our JavaScript source.

    *   userAgentstring

        The user agent of the device making the request

*   integrationsobject

    Contains a list of booleans indicating the integrations that are enabled (true) or disabled (false). By default, all integrations are enabled (returning an empty object). Set `"All": false` to reverse this behavior.

    *   Enabled/Disabled integrations \*boolean

*   messageIdstring

    A unique identifier for a Pipelines call, ensuring that each individual event is unique. This is set by Customer.io

*   originalTimestampstring(date-time)

    In general, you can use `timestamp` rather than this field if you want to back-date events. This is the timestamp on the client device you invoke a call or the timestamp value you manually passed in a server-side library call.

*   propertiesobject

    Additional properties for your event.

    *   Event Properties \*any type

        Additional properties that you want to capture in the event. These can take any JSON shape.

*   sentAtstring(date-time)

    The ISO-8601 timestamp when a library sends an event to Pipelines.

*   timestampstring(date-time)

    The ISO-8601 timestamp when the event originally took place. This is mostly useful when you backfill past events. If you're not backfilling data, you can leave this field empty and we'll use the current time or server 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 person 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](https://docs.customer.io/messaging/objects-data/objects/start/) . If the group/object or person 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 person with the group. You can also include `traits` to provide additional information about the group (or the relationship between the person and the group). Find more details about the `group` method in our [API specifications](https://docs.customer.io/integrations/api/cdp/tag/default/group/).

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

<!-- tabs -->

<!-- tab: Redshift -->

#### Redshift

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

<!-- /tab -->

<!-- tab: Databricks -->

#### Databricks

```
SELECT company_id AS groupId, object_type_id AS objectTypeId, company_name, employees, user_id AS userId
FROM companies
WHERE updated_at >= timestamp_seconds({{last_sync_time}})
```

<!-- /tab -->

<!-- tab: BigQuery -->

#### BigQuery

Remember, group calls represent both an organization/group *and* relationships with users (by `userId`). Your query should include not only the `groupId`, but the `userId` so that you can capture relationships between users and groups.

If the `userId` doesn’t exist in Customer.io, we’ll create a new person to represent the new `userId` and their relationship to the group.

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

<!-- /tab -->

<!-- tab: Microsoft SQL -->

#### Microsoft SQL

Remember, group calls represent both an organization/group *and* relationships with users (by `userId`). Your query should include not only the `groupId`, but the `userId` so that you can capture relationships between users and groups.

If the `userId` doesn’t exist in Customer.io, we’ll create a new person to represent the new `userId` and their relationship to the group.

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

<!-- /tab -->

<!-- tab: MySQL -->

#### MySQL

Remember, group calls represent both an organization/group *and* relationships with users (by `userId`). Your query should include not only the `groupId`, but the `userId` so that you can capture relationships between users and groups.

If the `userId` doesn’t exist in Customer.io, we’ll create a new person to represent the new `userId` and their relationship to the group.

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

<!-- /tab -->

<!-- tab: PostgreSQL -->

#### PostgreSQL

Remember, group calls represent both an organization/group *and* relationships with users (by `userId`). Your query should include not only the `groupId`, but the `userId` so that you can capture relationships between users and groups.

If the `userId` doesn’t exist in Customer.io, we’ll create a new person to represent the new `userId` and their relationship to the group.

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

<!-- /tab -->

<!-- tab: Snowflake -->

#### Snowflake

```
SELECT COMPANYID AS "groupId", OBJECT_TYPE_ID AS "objectTypeId", COMPANYNAME AS "company_name", EMPLOYEES AS "employees", PERSONID AS "userId"
FROM companies
WHERE LAST_UPDATED >= {{last_sync_time}}
```

<!-- /tab -->

<!-- /tabs -->

Include `objectTypeId` when you send data to Customer.io

Customer.io supports different kinds of groups (called [objects](https://docs.customer.io/messaging/objects-data/objects/start/) ) where each object has an [object type](https://docs.customer.io/messaging/objects-data/objects/types/) represented by an incrementing integer beginning at 1. If you send `group` calls to Customer.io, include `traits.objectTypeId` or we’ll assume that the object type is 1.

One of:Known UserAnonymous User

*   groupIdstringrequired

    ID of the group

*   contextone of

    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.

    One of:Non-mobileMobile

    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.

    *   activeboolean

        Whether a user is active.

        This is usually used when you send an .identify() call to update the traits independently of when you've “last seen” a user.

    *   campaignobject

        Contains information about the campaign that resulted in the API call, gathered from, or mapping to, UTM parameters (e.g. `utm_source`).

    *   channelstring

        The channel the event originated from.

        Accepted values: `browser`, `server`, `mobile`

    *   ipstring

        The user's IP address. This isn't captured by our libraries, but by our servers when we receive client-side events (like from our JavaScript source).

    *   localestring

        The locale string for the current user, e.g. `en-US`.

    *   pageobject

        Contains information about the current page in the browser. This is automatically collected by our JavaScript source.

    *   userAgentstring

        The user agent of the device making the request

*   integrationsobject

    Contains a list of booleans indicating the integrations that are enabled (true) or disabled (false). By default, all integrations are enabled (returning an empty object). Set `"All": false` to reverse this behavior.

    *   Enabled/Disabled integrations \*boolean

*   messageIdstring

    A unique identifier for a Pipelines call, ensuring that each individual event is unique. This is set by Customer.io

*   originalTimestampstring(date-time)

    In general, you can use `timestamp` rather than this field if you want to back-date events. This is the timestamp on the client device you invoke a call or the timestamp value you manually passed in a server-side library call.

*   sentAtstring(date-time)

    The ISO-8601 timestamp when a library sends an event to Pipelines.

*   timestampstring(date-time)

    The ISO-8601 timestamp when the event originally took place. This is mostly useful when you backfill past events. If you're not backfilling data, you can leave this field empty and we'll use the current time or server time.

*   traitsobject

    Additional information about the group.

    *   objectTypeIdstring

        If you use Customer.io Journeys as a destination, this value is the type of group/object your group belongs to; object type IDs are stringified integers. If you don't include this value, we assume the object type ID is `1`. See [objects in Customer.io Journeys](https://docs.customer.io/journeys/objects-data/objects/types/) for more information.

    *   relationshipAttributesobject

        Attributes that you want to set for the relationship between the person and the group.

    *   Group Traits \*any type

*   userIdstring

    The unique identifier for a person. This value should be unique across systems, so you recognize the same person in your sources \_and\_ destinations.

### Relationship attributes

In Customer.io, you can assign [attributes](https://docs.customer.io/journeys/people/manage/attributes/) to both the group (called a [custom object](https://docs.customer.io/messaging/objects-data/objects/start/) in Customer.io) *and* to the [relationship](https://docs.customer.io/messaging/objects-data/objects/relationships/) between the object and the person. By default, attributes are stored on the custom object itself, but you can assign relationship attributes using the `relationshipAttributes` JSON object.

<!-- tabs -->

<!-- tab: Redshift -->

#### Redshift

Build the object with `OBJECT` and serialize it with `JSON_SERIALIZE`:

```
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}}
```

<!-- /tab -->

<!-- tab: Databricks -->

#### Databricks

Build the object with `named_struct`:

```
SELECT company_id AS groupId, object_type_id AS objectTypeId,
    company_name, employees,
    user_id AS userId,
    named_struct(
        'is_manager', is_manager,
        'role', role,
        'start_date', start_date
    ) AS relationshipAttributes
FROM companies
WHERE updated_at >= timestamp_seconds({{last_sync_time}})
```

<!-- /tab -->

<!-- tab: BigQuery -->

#### BigQuery

Build the object with `JSON_OBJECT`, alternating keys and values in order:

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

<!-- /tab -->

<!-- tab: Microsoft SQL -->

#### Microsoft SQL

Build the object with a `FOR JSON PATH` subquery:

```
SELECT companyId AS groupId, objectTypeId, companyname, employees, personId AS userId,
    (SELECT is_manager, role, start_date FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS relationshipAttributes
FROM companies
WHERE last_updated >= {{last_sync_time}}
```

<!-- /tab -->

<!-- tab: MySQL -->

#### MySQL

Build the object with `JSON_OBJECT`:

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

<!-- /tab -->

<!-- tab: PostgreSQL -->

#### PostgreSQL

Build the object with `JSON_BUILD_OBJECT`:

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

<!-- /tab -->

<!-- tab: Snowflake -->

#### Snowflake

Build the object with `OBJECT_CONSTRUCT`:

```
SELECT COMPANYID AS "groupId", OBJECTTYPEID AS "objectTypeId",
    COMPANYNAME AS "companyname", EMPLOYEES AS "employees",
    PERSONID AS "userId",
    OBJECT_CONSTRUCT(
        'is_manager', IS_MANAGER,
        'role', ROLE,
        'start_date', START_DATE
    ) AS "relationshipAttributes"
FROM companies
WHERE LAST_UPDATED >= {{last_sync_time}}
```

<!-- /tab -->

<!-- /tabs -->

## Page

The [Page](https://docs.customer.io/integrations/api/cdp/tag/default/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.**

<!-- tabs -->

<!-- tab: Redshift -->

#### Redshift

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

<!-- /tab -->

<!-- tab: Databricks -->

#### Databricks

```
SELECT user_id AS userId, meta_title AS name, url, time_on_page
FROM pages
WHERE created_at >= timestamp_seconds({{last_sync_time}})
```

<!-- /tab -->

<!-- tab: BigQuery -->

#### BigQuery

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

<!-- /tab -->

<!-- tab: Microsoft SQL -->

#### Microsoft SQL

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

<!-- /tab -->

<!-- tab: MySQL -->

#### MySQL

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

<!-- /tab -->

<!-- tab: PostgreSQL -->

#### PostgreSQL

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

<!-- /tab -->

<!-- tab: Snowflake -->

#### Snowflake

```
SELECT ID AS "userId", METATITLE AS "name", URL AS "url", TIME_ON_PAGE AS "time_on_page"
FROM pages
WHERE TIMESTAMP > {{last_sync_time}}
```

<!-- /tab -->

<!-- /tabs -->

One of:Known UserAnonymous User

*   userIdstringrequired

    The unique identifier for a person. This value should be unique across systems, so you recognize the same person in your sources \_and\_ destinations.

*   contextobject

    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.

    *   activeboolean

        Whether a user is active.

        This is usually used when you send an .identify() call to update the traits independently of when you've “last seen” a user.

    *   campaignobject

        Contains information about the campaign that resulted in the API call, gathered from, or mapping to, UTM parameters (e.g. `utm_source`).

    *   channelstring

        The channel the event originated from.

        Accepted values: `browser`, `server`, `mobile`

    *   ipstring

        The user's IP address. This isn't captured by our libraries, but by our servers when we receive client-side events (like from our JavaScript source).

    *   localestring

        The locale string for the current user, e.g. `en-US`.

    *   pageobject

        Contains information about the current page in the browser. This is automatically collected by our JavaScript source.

    *   userAgentstring

        The user agent of the device making the request

*   integrationsobject

    Contains a list of booleans indicating the integrations that are enabled (true) or disabled (false). By default, all integrations are enabled (returning an empty object). Set `"All": false` to reverse this behavior.

    *   Enabled/Disabled integrations \*boolean

*   messageIdstring

    A unique identifier for a Pipelines call, ensuring that each individual event is unique. This is set by Customer.io

*   namestring

    The name of the page.

*   originalTimestampstring(date-time)

    In general, you can use `timestamp` rather than this field if you want to back-date events. This is the timestamp on the client device you invoke a call or the timestamp value you manually passed in a server-side library call.

*   propertiesobject

    Additional `page` properties. Analytics.js automatically collects `url`, `title`, `referrer`, `path`, and `search` properties. But, if you use our other sources or you write your own integration, you should consider sending these properties yourself. Destination actions that take `page` events often rely on the `url` and `title` properties.

    *   categorystring

        The category of the page. This might be useful if you have a single page routes or have a flattened URL structure.

    *   pathstring

        The path of the page. This defaults to `location.pathname`, but can be overridden.

    *   referrerstring

        The referrer of the page, if applicable. This defaults to `document.referrer`, but can be overridden.

    *   searchstring

        The search query in the URL, if present. This defaults to `location.search`, but can be overridden.

    *   titlestring

        The title of the page. This defaults to `document.title`, but can be overridden.

    *   urlstring

        The URL of the page. This defaults to a canonical url if available, and falls back to `document.location.href`.

    *   Page Properties \*any type

*   sentAtstring(date-time)

    The ISO-8601 timestamp when a library sends an event to Pipelines.

*   timestampstring(date-time)

    The ISO-8601 timestamp when the event originally took place. This is mostly useful when you backfill past events. If you're not backfilling data, you can leave this field empty and we'll use the current time or server time.

## Screen

The [Screen](https://docs.customer.io/integrations/api/cdp/tag/default/screen/) method sends screen view events for mobile devices. These help you understand the screens that people use in your app.

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

<!-- tabs -->

<!-- tab: Redshift -->

#### Redshift

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

<!-- /tab -->

<!-- tab: Databricks -->

#### Databricks

```
SELECT user_id AS userId, screen_name AS name, session_started
FROM screens
WHERE created_at >= timestamp_seconds({{last_sync_time}})
```

<!-- /tab -->

<!-- tab: BigQuery -->

#### BigQuery

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

<!-- /tab -->

<!-- tab: Microsoft SQL -->

#### Microsoft SQL

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

<!-- /tab -->

<!-- tab: MySQL -->

#### MySQL

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

<!-- /tab -->

<!-- tab: PostgreSQL -->

#### PostgreSQL

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

<!-- /tab -->

<!-- tab: Snowflake -->

#### Snowflake

```
SELECT ID AS "userId", SCREEN_NAME AS "name", SESSION_STARTED AS "session_started"
FROM screens
WHERE TIMESTAMP > {{last_sync_time}}
```

<!-- /tab -->

<!-- /tabs -->

One of:Known UserAnonymous User

*   userIdstringrequired

    The unique identifier for a person. This value should be unique across systems, so you recognize the same person in your sources \_and\_ destinations.

*   contextobject

    A dictionary of context about a source call/event, like the user’s IP address or locale. Context is automatically collected by our source libraries.

    *   activeboolean

        Whether a user is active.

        This is usually used when you send an .identify() call to update the traits independently of when you've “last seen” a user.

    *   appobject

        Contains information about the mobile app the event originated from, automatically collected by our mobile libraries when possible.

    *   channelstring

        The channel the event originated from.

        Accepted values: `browser`, `server`, `mobile`

    *   deviceobject

        Contains information about the device the event originated from.

    *   ipstring

        The user's IP address. This isn't captured by our libraries, but by our servers when we receive client-side events (like from our JavaScript source).

    *   localestring

        The locale string for the current user, e.g. `en-US`.

    *   networkobject

        Information about the current network connection, containing `bluetooth`, `carrier`, `cellular`, and `wifi`. If the `context.network.cellular` and `context.network.wifi` fields are empty, then the user is offline.

    *   osobject

        Dictionary of information about the operating system, containing `name` and `version`.

    *   userAgentstring

        The user agent of the device making the request

*   integrationsobject

    Contains a list of booleans indicating the integrations that are enabled (true) or disabled (false). By default, all integrations are enabled (returning an empty object). Set `"All": false` to reverse this behavior.

    *   Enabled/Disabled integrations \*boolean

*   messageIdstring

    A unique identifier for a Pipelines call, ensuring that each individual event is unique. This is set by Customer.io

*   namestring

    The name of the screen the person visited.

*   originalTimestampstring(date-time)

    In general, you can use `timestamp` rather than this field if you want to back-date events. This is the timestamp on the client device you invoke a call or the timestamp value you manually passed in a server-side library call.

*   propertiesobject

    Additional properties for your screen.

    *   Screen Properties \*any type

*   sentAtstring(date-time)

    The ISO-8601 timestamp when a library sends an event to Pipelines.

*   timestampstring(date-time)

    The ISO-8601 timestamp when the event originally took place. This is mostly useful when you backfill past events. If you're not backfilling data, you can leave this field empty and we'll use the current time or server time.

## Alias

The Alias method combines two previously unassociated user identities. Some integrations automatically reconcile people 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 people. But you may need to send `alias` calls to manage user identities in *some* data-out integrations.

For example, in [Mixpanel](https://docs.customer.io/integrations/data-out/connections/mixpanel/#alias) it’s used to associate an anonymous user with an identified user once they sign up.

<!-- tabs -->

<!-- tab: Redshift -->

#### Redshift

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

<!-- /tab -->

<!-- tab: Databricks -->

#### Databricks

```
SELECT user_id AS userId, old_id AS previousId
FROM user_resolution
WHERE updated_at >= timestamp_seconds({{last_sync_time}})
```

<!-- /tab -->

<!-- tab: BigQuery -->

#### BigQuery

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

<!-- /tab -->

<!-- tab: Microsoft SQL -->

#### Microsoft SQL

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

<!-- /tab -->

<!-- tab: MySQL -->

#### MySQL

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

<!-- /tab -->

<!-- tab: PostgreSQL -->

#### PostgreSQL

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

<!-- /tab -->

<!-- tab: Snowflake -->

#### Snowflake

```
SELECT ID AS "userId", OLD_ID AS "previousId"
FROM user_resolution
WHERE TIMESTAMP >= {{last_sync_time}}
```

<!-- /tab -->

<!-- /tabs -->

*   previousIdstringrequired

    The anonymousId or userId value that you want to merge into the canonical profile.

*   userIdstringrequired

    The userId that you want to keep. This is required if you haven't already identified someone with one of our web or server-side libraries.
