Sync data from Amazon Redshift to Data Studio

Please note: if you're a Super Admin, opt your account into the Data Studio beta to use this feature.

In Data Studio, you can create comprehensive datasets by syncing data from Amazon Redshift to your HubSpot CRM. Continuously sync Amazon Redshift data and merge it with other sources to refine and expand your datasets.

Connect Amazon Redshift to your account

1. In your HubSpot account, navigate to Data Management > Data Studio. In the top right, click Connect external data source. Search Amazon Redshift, then click Amazon Redshift.
2. To connect your account, you must create a role tied to your HubSpot account and set up access to your AWS Account. You may use your own tools to make this connection, or you can paste the cope snippet into your Amazon console. 

3. Paste the ARN of the AWS role you created in the Input your AWS Role ARN textbox.
4. Once completed, click Connect to Amazon Redshift.

Configure your Amazon Redshift data source

Before selecting your data, you must ensure the correct permissions are set in AWS and Redshift.

Update AWS permissions

To update AWS permissions:

1. If you have a provisioned Redshift cluster:

• • Locate your cluster identifier, region, AWS account ID, and the name of the Redshift database you want to sync.

• • Construct your Cluster and DB ARNs:

• • • Cluster ARN: arn:aws:redshift:YOUR_REGION:YOUR_ACCOUNT_ID:cluster:YOUR_CLUSTER_IDENTIFIER
    • DB ARN: arn:aws:redshift:YOUR_REGION:YOUR_ACCOUNT_ID:dbname:YOUR_CLUSTER_ID/YOUR_DATABASE

  • Update the permission policy with your cluster details and attach it to the role you created in your AWS IAM settings.

2. If you have a serverless Redshift workgroup:

   • Locate the detail page of your workgroup in AWS Redshift.

• • Copy the Workgroup ARN, update the permission policy, and attach it to the role you created in your AWS IAM settings.

3. Once completed, paste the endpoint string from your AWS console into HubSpot.

Update Redshift database permissions

To update your Redshift database permissions:

1. In your Redshift query editor, run the following SQL script (replacing the bracketed values with your specific database, schema, and role name):

2. Then, open the Redshift query editor and run the updated SQL script.

Configure columns

On the Columns screen:

1. Preview your data in the table. Click the checkboxes on each column you want to sync, and select the data type for each column (e.g., text, number).
2. Click Refresh table if you want to see your columns refreshed after making your selection.
3. Once you have finished selecting your columns, click Add data source.

Use your Amazon Redshift data source

To view and manage your Amazon Redshift data source:

1. In your HubSpot account, navigate to Data Management > Data Studio.
2. Click the Source library tab.
3. In the left sidebar, click Warehouses. Then, click an Amazon Redshift data source from the table to view it. Information will display such as the health of the sync, the number of records and properties synced, and the last refresh date.

Please note: data is automatically refreshed every five minutes.

4. To use your data source to build a dataset, click Use to build dataset. Learn more about building and activating datasets in Data Studio.