> ## Documentation Index
> Fetch the complete documentation index at: https://docs.conversion.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Amazon Redshift

> Connect Amazon Redshift to sync contacts, custom objects, and events into Conversion.

This guide walks you through connecting Amazon Redshift to Conversion and setting up your first sync.

## Before You Begin

Before connecting Redshift, we recommend the following:

* **Create a dedicated database user:** Set up a user specifically for Conversion with read-only access to the tables you need. This limits exposure and makes it easy to revoke access if needed.
* **Use a read replica if possible:** Running queries against a replica prevents sync operations from impacting your production cluster.
* **Sync only the data you need:** Select only the columns you'll use, and always filter with `last_sync_time` to limit results to changed rows.

<Warning>
  Queries that return millions of rows should not run more frequently than once per day. Large, frequent syncs can impact both your Redshift costs and Conversion performance.
</Warning>

***

## Step 1: Create a Database User

Create a dedicated user for Conversion with read access to the tables you want to sync.

Connect to your Redshift cluster and run:

```sql theme={null}
-- Create a user for Conversion
CREATE USER conversion_sync PASSWORD 'your_strong_password';

-- Grant access to the schema containing your tables
GRANT USAGE ON SCHEMA public TO conversion_sync;

-- Grant SELECT on specific tables
GRANT SELECT ON TABLE public.users TO conversion_sync;
GRANT SELECT ON TABLE public.products TO conversion_sync;
GRANT SELECT ON TABLE public.orders TO conversion_sync;
-- Add more tables as needed
```

<Info>
  Replace `public` with your schema name if you use a different schema. Grant SELECT only on the specific tables you want to sync.
</Info>

***

## Step 2: Allow Conversion's IP Addresses

If your Redshift cluster uses a VPC security group or firewall, allow inbound connections from Conversion's IP addresses:

| Region | IP Addresses                                                                            |
| :----- | :-------------------------------------------------------------------------------------- |
| US     | 35.239.90.161, 35.188.167.166, 34.56.101.43, 34.122.97.230, 34.29.176.66, 35.226.154.44 |

Add these IPs to your Redshift cluster's security group, allowing inbound traffic on your Redshift port (default: 5439).

***

## Step 3: Connect Redshift to Conversion

1. In Conversion, go to **Settings → Integrations → Data Warehouse**
2. Click **Add Integration** and select **Amazon Redshift**
3. Enter your connection details:
   * **Name:** A friendly name for this connection (e.g., "Production Redshift")
   * **Host:** Your Redshift cluster endpoint (e.g., `my-cluster.abc123.us-east-1.redshift.amazonaws.com`)
   * **Port:** The port number (default: 5439)
   * **Database:** The database name
   * **User:** The username you created
   * **Password:** The user's password
4. Optional: Enable **SSL** for encrypted connections
5. Optional: Configure **SSH tunneling** if your cluster isn't publicly accessible
6. Click **Connect**

Conversion will verify the connection. If successful, you're ready to create syncs.

### SSH Tunneling

If your Redshift cluster is in a private subnet, you can connect through an SSH tunnel via a bastion host:

1. Enable **SSH Tunneling** in the connection settings
2. Enter your bastion host details:
   * **SSH Host:** The bastion host address
   * **SSH Port:** The SSH port (default: 22)
   * **SSH User:** The SSH username
3. Click **Generate Public Key**
4. Add the generated public key to the bastion host's `~/.ssh/authorized_keys` file
5. Click **Connect**

***

## Step 4: Create a Sync

After connecting, open your new Redshift connection and go to the **Syncs** tab to create a sync. Read more about [Setting Up a Sync](https://docs.conversion.ai/product-docs/sync/data-warehouse/overview#setting-up-a-sync).

***

## Redshift SQL Reference

### Converting Timestamps

Conversion expects Unix timestamps for date/time fields. Use `EXTRACT(EPOCH FROM column)` to convert Redshift `TIMESTAMP` values:

```sql theme={null}
SELECT 
  email,
  EXTRACT(EPOCH FROM created_at)::bigint AS created_at,
  EXTRACT(EPOCH FROM last_login)::bigint AS last_login
FROM users
WHERE updated_at >= '{{last_sync_time}}'::timestamp
```

### Using last\_sync\_time

The `last_sync_time` variable is a Unix timestamp. Cast it to a timestamp to compare against `TIMESTAMP` columns:

```sql theme={null}
WHERE updated_at >= '{{last_sync_time}}'::timestamp
```

Or use `TO_TIMESTAMP`:

```sql theme={null}
WHERE updated_at >= TO_TIMESTAMP({{last_sync_time}})
```

### Building Nested Objects with JSON\_BUILD\_OBJECT

Use `JSON_BUILD_OBJECT` to create nested JSON objects like `relationshipFields`. Cast the result to text:

```sql theme={null}
JSON_BUILD_OBJECT(
  'role', role,
  'quantity', quantity,
  'started_at', EXTRACT(EPOCH FROM started_at)::bigint
)::text AS "relationshipFields"
```

### Handling NULLs

Use `COALESCE` or `NVL` to provide default values:

```sql theme={null}
SELECT 
  email,
  COALESCE(first_name, '') AS first_name,
  NVL(phone, '') AS phone
FROM users
```

### Casting Types

Use `::` or `CAST` to convert between types:

```sql theme={null}
SELECT 
  user_id::text AS id,
  score::integer AS lead_score
FROM users
```

### Querying Across Schemas

Use schema-qualified table names:

```sql theme={null}
SELECT 
  u.email,
  o.order_id
FROM public.users u
JOIN sales.orders o ON u.id = o.user_id
```

***

## Troubleshooting

### "Permission denied" errors

Ensure your user has the required grants:

* `USAGE` on the schema
* `SELECT` on each table you want to query

```sql theme={null}
GRANT USAGE ON SCHEMA public TO conversion_sync;
GRANT SELECT ON TABLE public.users TO conversion_sync;
```

### "Could not connect" errors

Verify that:

* Your Redshift cluster is accessible (check security groups and VPC settings)
* Conversion's IP addresses are allowed in your security group
* The host, port, database, username, and password are correct
* SSL is enabled if your cluster requires it

### Connection timeouts

If your cluster is in a private subnet:

* Use SSH tunneling through a bastion host
* Ensure the bastion host can reach the Redshift cluster
* Verify the SSH public key is correctly added to the bastion host

### Sync taking too long

* Ensure you're filtering by `last_sync_time` to reduce rows
* Select only the columns you need
* Consider using Redshift's sort keys on your timestamp columns
* Reduce sync frequency for large datasets

***

## Frequently Asked Questions

<AccordionGroup>
  <Accordion title="What Redshift permissions does Conversion need?">
    The user needs `USAGE` on the schema and `SELECT` on the specific tables you want to sync. Conversion only reads data; it never writes to your Redshift cluster.
  </Accordion>

  <Accordion title="Does Conversion support Redshift Serverless?">
    Yes. Use your Redshift Serverless workgroup endpoint as the host. The setup is otherwise identical to a provisioned cluster.
  </Accordion>

  <Accordion title="Can I use SSL?">
    Yes. Enable SSL in the connection settings. Redshift supports SSL connections by default, and we recommend using them for security.
  </Accordion>

  <Accordion title="Can I connect to a cluster in a private VPC?">
    Yes, using SSH tunneling. Configure a bastion host in your VPC that can reach the Redshift cluster, then set up the SSH tunnel in Conversion's connection settings.
  </Accordion>

  <Accordion title="What if a contact doesn't exist yet?">
    If you sync a custom object with an `email` that doesn't exist in Conversion, we create the contact automatically. You can enrich that contact's profile through a separate contacts sync.
  </Accordion>
</AccordionGroup>