BetaAccess your HubSpot data from Amazon Redshift

Use Data Out for Amazon Redshift to export selected HubSpot CRM data to tables in an Amazon Redshift database that you own. Each sync run writes a full snapshot of the selected data and replaces the previous snapshot. After the sync, you can query the tables with SQL or with your preferred BI and analytics tools to combine HubSpot data with other business data.

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

Understand limitations & considerations

Before you begin working with this feature, make sure to fully understand what steps should be taken ahead of time, as well as the limitations of the feature and potential consequences of using it.

• Each sync replaces the destination tables with the latest snapshot. HubSpot doesn't keep previous snapshots in Redshift.
• HubSpot doesn't delete the Parquet files it stages in your S3 bucket. To remove them automatically, set up an S3 lifecycle policy on the bucket or prefix.
• Each object, event type, or association pair can be included in one recurring Redshift Data Out sync per HubSpot account.
• Sensitive data isn't supported yet. Sensitive properties may be excluded from exports.
• Object-specific tables, such as OBJECTS_CONTACTS, aren't supported yet.

Understand the data you can export

You can sync the following data from HubSpot to Amazon Redshift:

• Objects: standard and custom objects, such as contacts, companies, deals, tickets, and products.
• Records: individual records for the selected objects.
• Properties: selected property values and property history on records.
• Associations: selected relationships between records, including association definitions.
• Events: selected standard HubSpot events and custom behavioral events, including selected event properties.

Data for different objects is combined in the object tables. When you read an object table, use OBJECTTYPEID to filter to a specific object. For example, contacts use 0-1. Custom objects are assigned an ID when they're created and are identified as 2-{unique_id}, such as 2-12345.

Understand the export format

During setup, you choose:

• The Redshift database and the cluster or Serverless workgroup where HubSpot will create tables.
• The S3 bucket HubSpot uses to stage data before loading it into Redshift.
• The data sources to sync: objects, events, associations, or a combination of these sources.
• The sync schedule.

HubSpot exports the data as Parquet files, stages the files in your S3 bucket, and loads them into Redshift tables.

Please note: HubSpot doesn't delete data from the staging bucket. It's recommended to set a bucket lifecycle policy to automatically clean up the bucket.

Find your data in Redshift

HubSpot creates one table per data source in a HubSpot-managed schema named after your destination:

<database>.hubspot_data_out_<destination-id>.<table-name>

You can find the schema name on the configuration page of your Redshift sync. For example:

• your_database.hubspot_data_out_456.OBJECT_WITH_OBJECT_PROPERTIES
• your_database.hubspot_data_out_456.EVENTS_VISITED_PAGE
• your_database.hubspot_data_out_456.ASSOCIATIONS_CONTACTS_TO_COMPANIES

Each sync replaces the table with the latest snapshot. If a table fails during a sync, its previous data stays in place until the next successful sync.

HubSpot stages Parquet files in your S3 bucket under a HubSpot-managed prefix, then loads them into Redshift:

s3://<bucket>/<prefix>/hubspot-dataout/<portal-id>/<destination-id>/<run-id>/<table-name>/

These staging files stay in your bucket until you remove them. To remove them automatically, configure an S3 lifecycle policy on the bucket or prefix.

Please note: table and column names come from the exported data. They may be shortened to meet Redshift's 127-character identifier limit. Column types follow the exported data.

Review object tables

If you select object data, HubSpot exports the following tables.

OBJECT_PROPERTIES

This table contains one row per property value. To read a specific object, filter by OBJECTTYPEID.

OBJECT_WITH_OBJECT_PROPERTIES

This table contains one row per record, with the selected property values grouped into a single PROPERTIES column. To read a specific object, filter by OBJECTTYPEID.

OBJECT_PROPERTIES_HISTORY

This table contains property change history. Unlike OBJECT_PROPERTIES, which holds only the current value, this table has one row for each historical value of a property. To see the full timeline for a single record and property, order by UPDATEDAT.

Review association tables

If you select association data, HubSpot exports association records and association definitions.

ASSOCIATIONS

This table contains one row per selected association between two records.

ASSOCIATION_DEFINITIONS

This table contains one row per selected association definition.

ASSOCIATIONS_<FROM_OBJECT>_TO_<TO_OBJECT>

For selected association pairs, HubSpot can also export pair-specific association tables, such as ASSOCIATIONS_CONTACTS_TO_COMPANIES. These tables include association records together with association definition metadata.

Review event tables

If you select event data, HubSpot exports one table per event type. Table names use the format EVENTS_<EVENT_NAME>, such as EVENTS_VISITED_PAGE.

Each event table contains standard event columns and one column for each selected event property.

Manage the sync schedule

You configure the sync schedule during setup in HubSpot. You can also manually trigger a re-sync from HubSpot. Each sync run produces a full snapshot of the selected data. To control data volume, choose which objects, events, associations, and properties to include.

Please note: during the beta, only a limited set of sync frequencies may be available, such as once, every 6 hours, every 12 hours, daily, weekly, and monthly. Additional frequency options may be added in future releases.

Monitor syncs and handle errors

You can monitor Amazon Redshift syncs in HubSpot. A sync can have one of the following states:

When a sync fails:

• HubSpot shows a Failed status with per-table details showing which tables succeeded or failed.
• A table is updated only after HubSpot finishes loading all of its data, so you never see a partially loaded table.
• If a table fails, its previous data stays in place until the next successful sync.
• The next scheduled sync automatically reattempts a full sync.
• You can manually trigger a re-sync at any time in HubSpot.

Tables that succeeded in a previous run aren't modified when a later run fails.

FAQs

How do I query the data?

Query the tables in your destination schema with SQL. For example:

Read all contact records:

SELECT * FROM your_database.hubspot_data_out_456.OBJECT_WITH_OBJECT_PROPERTIES WHERE OBJECTTYPEID = '0-1';

Read a specific property from the PROPERTIES JSON column:

SELECT OBJECTID, JSON_EXTRACT_PATH_TEXT(PROPERTIES, 'email') AS email FROM your_database.hubspot_data_out_456.OBJECT_WITH_OBJECT_PROPERTIES WHERE OBJECTTYPEID = '0-1';

Can I export one data type in multiple Redshift syncs?

Each object, event type, or association pair can be included in one recurring Redshift Data Out sync per HubSpot account. For example, if contacts are already exported to one recurring Redshift destination, you can't add contacts to another recurring Redshift sync until you archive the first sync.

Are object-specific tables supported?

No. Object-specific tables, such as OBJECTS_CONTACTS, aren't supported yet. Use OBJECT_WITH_OBJECT_PROPERTIES and filter by OBJECTTYPEID to get records for a specific object.

Is sensitive data supported?

No. Sensitive data isn't supported for Data Out to Redshift yet. Sensitive properties may be excluded from exports.

Does HubSpot keep previous snapshots?

No. Each sync replaces the destination table with the latest snapshot, so previous snapshots aren't kept in Redshift. HubSpot also doesn't delete the temporary Parquet files it stages in your S3 bucket. Configure an S3 lifecycle policy to expire them automatically.