Sync data from Azure Synapse 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 Azure Synapse to your HubSpot CRM. Continuously sync Azure Synapse data and merge it with other sources to refine and expand your datasets.

Connect Azure Synapse to your account

1. In your HubSpot account, navigate to Data Management > Data Studio. In the top right, click Connect external data source. Search Microsoft Azure Synapse Analytics, then click Microsoft Azure Synapse Analytics.
2. To connect your account, in your Microsoft account, navigate to App registrations, then click + New registration.
3. Name it HubSpot registration. This creates a service principal that allows HubSpot to securely access your Microsoft resources. 
4. On the Overview page, copy the Directory (tenant) ID and Application (client) ID, then paste it into the corresponding textboxes in HubSpot.
5. In the same Microsoft app registration workflow, navigate to Certificates & secrets, then click + New client secret. Add a description, set an expiration, and click Add. Copy the secret value immediately, and paste it into the Client secret textbox.
6. Click Connect to Microsoft Azure Snapse Analytics.

Allow HubSpot IPs

In your Azure Portal, add HubSpot's IP range to your Synapse workspace firewall rules.

1. In your Microsoft account, navigate to Synapse workspace > Security > Networking. 
2. Add HubSpot's IP range to your firewall rules.

Configure your Amazon Azure Synapse data source

Before selecting your data, you must ensure the correct permissions are set in Microsoft Azure.

Enable authentication

To update permissions, run these SQL statements in the master database. This requires Synapse Admin access. This allows the service principal to authenticate to the workspace.

Grant read access

Run these SQL statements in your target database. This enables reading data for tables and views, discovering schemas, tables, and columns via INFORMATION_SCHEMA, and detecting table modifications for incremental sync.

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 Azure Synapse data source

To view and manage your Azure Synapse 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 Azure Synapse 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. Learn more about how to sync data to Data Studio.

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.