Before you begin
Ensure that you have:
- A HubSpot account with an Operations Hub subscription.
Step 1. (Optional) Create a cluster
If you already have a cluster for your webhook sources, you can skip this step.
To create a cluster in Materialize, use the
CREATE CLUSTER command:
CREATE CLUSTER webhooks_cluster (SIZE = '3xsmall');
Step 2. Create a secret
To validate requests between HubSpot and Materialize, you must create a secret:
CREATE SECRET hubspot_webhook_secret AS '<secret_value>';
<secret_value> to a unique value that only you know and store it in
a secure location.
Step 3. Set up a webhook source
Using the secret from the previous step, create a webhook source in Materialize to ingest data from HubSpot:
CREATE SOURCE hubspot_source IN CLUSTER webhooks_cluster
BODY FORMAT JSON
BODY AS body,
After a successful run, the command returns a
NOTICE message containing the
unique webhook URL
that allows you to
POST events to the source. Copy and store it. You will need
it for the next step.
The URL will have the following format:
If you missed the notice, you can find the URLs for all webhook sources in the
Access and authentication
CHECK statement, all requests will be accepted. To prevent bad
actors from injecting data into your source, it is strongly encouraged that
you define a
CHECK statement with your webhook sources.
CHECK clause defines how to validate each request. At the time of writing,
HubSpot supports API key authentication, which you can use to validate
The above webhook source uses basic authentication. This enables a simple and rudimentary way to grant authorization to your webhook source.
Step 4. Create a webhook workflow in HubSpot
A webhook in HubSpot is a workflow action that sends data to a webhook URL. You can create a webhook workflow in HubSpot to send data to the webhook source you created in the previous step.
In HubSpot, go to Automation > Workflows.
Click the Name of the workflow you want to add the webhook to, or create a new one.
Click the + icon to add an action.
In the right panel, search for Send a webhook.
Click the Method dropdown menu, then select
Enter the URL from Step 3..
Authenticate the request using the API key option. Use the secret created in Step 2..
For the API Key Name, enter
authorization. This is the key used in the
CHECKclause of the webhook source.
Step 5. Configure the request body in HubSpot
The request body is the data that HubSpot sends to the webhook URL. You can configure the request body to send the data you want to ingest into Materialize.
In HubSpot, go to the webhook workflow created in Step 4..
Go to the Request body section, and click Customize request body.
In the Request body section, click Add property.
From the dropdown menu, select the property you want to send to Materialize. Repeat this step for each property you want to send to Materialize.
Click Test Mapping to validate that the webhook is working. If Test Mapping fails and throws a
failed to validate the requesterror, this means that the secret is not correct. To fix this:
- In HubSpot, go to the webhook workflow.
- Go to the Authentication section.
- Enter the secret created in Step 2..
- Verify that the API Key Name is
- Click Save.
After a succesful test, click Save.
Step 6. Validate incoming data
With the source set up in Materialize and the webhook workflow configured in HubSpot, you can now query the incoming data:
In the Materialize console, navigate to the SQL Shell.
Use SQL queries to inspect and analyze the incoming data:
SELECT * FROM hubspot_source LIMIT 10;
Step 7. Transform incoming data
Webhook data is ingested as a JSON blob. We recommend creating a parsing view on
top of your webhook source that uses
to map the individual fields to columns with the required data types.
CREATE VIEW parse_hubspot AS SELECT
body->>'city' AS city,
body->>'firstname' AS firstname,
body->>'ip_city' AS ip_city,
-- Add all of the fields you want to ingest
With the vast amount of data processed and potential network issues, it’s not
uncommon to receive duplicate records. You can use the
DISTINCT ON clause to
efficiently remove duplicates. For more details, refer to the webhook source
With Materialize ingesting your HubSpot data, you can start exploring it, computing real-time results that stay up-to-date as new data arrives, and serving results efficiently. For more details, check out the HubSpot documentation and the webhook source reference documentation.