Amazon Redshift

​

Before You Begin

• 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.

​

Step 1: Create a Database User

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

​

Step 2: Allow Conversion’s IP Addresses

​

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

​

SSH Tunneling

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

1. Click Add Sync
2. Select the destination type:
   • Contacts — Create or update contact records
   • Custom Objects — Create or update custom object records (with optional relationships)
   • Events — Record events on contacts
3. For Custom Objects, select the specific object type
4. Enter your SQL query (see examples below)
5. Click Run Query to preview results
6. Set the sync schedule (how often to run)
7. Click Enable

​

Writing Queries for Redshift

​

Contact Sync

SELECT 
  email,
  first_name,
  last_name,
  title AS job_title,
  phone,
  city
FROM users
WHERE updated_at >= '{{last_sync_time}}'::timestamp

​

Custom Objects (Without Relationships)

SELECT 
  id,
  name,
  status,
  plan_tier,
  monthly_price
FROM subscriptions
WHERE updated_at >= '{{last_sync_time}}'::timestamp

​

Custom Objects with Relationships

SELECT 
  -- Object fields
  p.id,
  p.name,
  p.sku,
  p.category,
  p.price,
  
  -- Contact to relate this object to
  u.email,
  
  -- Relationship-specific fields
  JSON_BUILD_OBJECT(
    'role', up.role,
    'quantity', up.quantity,
    'purchased_at', EXTRACT(EPOCH FROM up.purchased_at)::bigint
  )::text AS "relationshipFields"
  
FROM user_products up
JOIN products p ON up.product_id = p.id
JOIN users u ON up.user_id = u.id
WHERE up.updated_at >= '{{last_sync_time}}'::timestamp

• Object fields (id, name, sku, category, price) are stored on the Product object
• email identifies which contact to create a relationship with
• relationshipFields contains fields stored on the relationship itself (this contact’s role, quantity, etc.)

​

Events

SELECT 
  u.email,
  'purchase_completed' AS event_name,
  o.created_at AS timestamp,
  o.id::text AS message_id,
  o.total_amount,
  o.product_name,
  o.currency
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '{{last_sync_time}}'::timestamp

​

Redshift SQL Reference

​

Converting Timestamps

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

WHERE updated_at >= '{{last_sync_time}}'::timestamp

WHERE updated_at >= TO_TIMESTAMP({{last_sync_time}})

​

Building Nested Objects with JSON_BUILD_OBJECT

JSON_BUILD_OBJECT(
  'role', role,
  'quantity', quantity,
  'started_at', EXTRACT(EPOCH FROM started_at)::bigint
)::text AS "relationshipFields"

​

Handling NULLs

SELECT 
  email,
  COALESCE(first_name, '') AS first_name,
  NVL(phone, '') AS phone
FROM users

​

Casting Types

SELECT 
  user_id::text AS id,
  score::integer AS lead_score
FROM users

​

Querying Across Schemas

SELECT 
  u.email,
  o.order_id
FROM public.users u
JOIN sales.orders o ON u.id = o.user_id

​

Troubleshooting

​

”Permission denied” errors

• USAGE on the schema
• SELECT on each table you want to query

GRANT USAGE ON SCHEMA public TO conversion_sync;
GRANT SELECT ON TABLE public.users TO conversion_sync;

​

”Could not connect” errors

• 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

• 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