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

​

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