Data warehouse sync | Knock Docs

Docs

Integrations

Extensions

Data warehouse sync

Sync notification analytics data from Knock into your own data warehouse

You can bring your notification analytics from Knock into your own data warehouse so that you can analyze it alongside the rest of your data.

Connecting your data warehouse

Please contact our support team to get started with the warehouse connection process. Your message should include the destination type of the warehouse that you'd like to connect, as well as which of the tables you'd like to sync from the available data listed below.

We will ask you for additional information according to the destination type that you'd like to connect. Once we have the required details, Knock will provide you with a magic link to a step-by-step wizard for completing the setup process.

After successfully connecting your destination, the first data sync includes a backfill of historical data. Subsequent syncs run once a day by default. You can also choose to sync every 1, 6, or 12 hours. Contact the Knock team to request a different cadence. On Sundays (U.S. Eastern time), all syncs follow the default daily schedule regardless of cadence while Knock runs a full weekly rebuild of the source data.

Network access

If your data warehouse restricts inbound connections by IP address, you'll need to allowlist 35.192.85.117. This is the static egress IP for Prequel's US Cloud Hosted deployment, which Knock uses to sync data to your warehouse.

Supported destinations

We currently support the following destination database types:

Vendor	Type
`athena`	OLAP
`bigquery`	OLAP
`clickhouse`	OLAP
`databricks`	OLAP
`redshift`	OLAP
`snowflake`	OLAP
`aurora_mysql`	OLTP
`aurora_postgres`	OLTP
`mysql`	OLTP
`postgres`	OLTP
`abs`	Object storage
`gcs`	Object storage
`s3`	Object storage
`s3_compatible`	Object storage
`google_sheets`	Spreadsheet

Available data

Messages table

Our data warehouse connector syncs data from our messages table. Each message represents a notification that was executed for a single recipient. You can read more about messages as a concept here.

The backfill of messages on initial connection will include all historical message data that is available in Knock.

Below is a description of the columns included in the table and the data type of each. To see how this data type will map onto the data types of your destination table, check the destination type mapping table.

Please note that the table name will vary based on the name of the schema provided when filling out the form.

See possible engagement statuses for a message.

Name	Type	Description
`message_id`	string	The unique identifier for this message and the primary key for this table
`account_id`	string	The UUID of the message's account
`environment_id`	string	The UUID of the message's environment
`environment_name`	string	The name of the message's environment
`environment_slug`	string	The slug of the message's environment
`channel_id`	string	The UUID of the channel the message was sent on
`channel_key`	string	The unique key of the channel the message was sent on
`channel_name`	string	The name of the channel the message was sent on
`channel_provider`	string	The provider for the channel the message was sent on
`external_provider_id`	string	The provider identifier returned for the message
`channel_type`	string	The type of channel the message was sent on
`workflow_id`	string	The UUID of the version of the workflow that the message belongs to
`workflow_key`	string	The unique key of the workflow the message belongs to
`workflow_run_id`	string	The workflow run ID for the trigger request that generated the message
`workflow_recipient_run_id`	string	The workflow recipient run ID for the recipient-specific workflow run that generated the message
`combined_trigger_data`	json	Combined and truncated trigger data for the workflow run that generated the message, at the time the message was created. See the trigger data filtering documentation for more info.
`step_ref`	string	The reference of the step on the workflow that the message belongs to
`recipient_id`	string	The ID of the recipient for the message
`recipient_type`	string	The type of recipient for the message, can be a user or an object
`tenant_id`	string	The tenant associated with this message
`exec_mode`	string	The execution mode of the workflow. Possible values are: `audience` - from audience member events `trigger` - from the API `rehearse` - test run of a workflow `integration` - from an event integration source `rehearse_integration` - test run of an integration-triggered message `scheduled` - previously scheduled workflow execution `workflow_step_trigger` - from another workflow's trigger workflow step `broadcast_trigger` - from a broadcast `rehearse_broadcast` - test run of a broadcast `guide` - messages generated from guides
`message_status`	string	The latest delivery status of a message.
`inserted_at`	timestamp	The timestamp of when the message was created
`updated_at`	timestamp	The timestamp of when the message was last updated
`archived_at`	timestamp	The timestamp of when the message was archived
`seen_at`	timestamp	The timestamp of when the message was seen
`read_at`	timestamp	The timestamp of when the message was read
`clicked_at`	timestamp	The timestamp of when a link in the message was clicked
`interacted_at`	timestamp	The timestamp of when the message was interacted with
`has_been_seen`	integer (0 = false, 1 = true)	Whether the message has been seen
`has_been_read`	integer (0 = false, 1 = true)	Whether the message has been read
`has_been_clicked`	integer (0 = false, 1 = true)	Whether a link in the message has been clicked
`has_been_interacted`	integer (0 = false, 1 = true)	Whether the message has been interacted with
`has_been_archived`	integer (0 = false, 1 = true)	Whether the message has been archived
`actors`	json	A JSON array of actor users or objects. Users are provided as string IDs. Objects are provided as JSON dictionaries with keys for "id" and "collection". See the `RecipientIdentifier` definition for more info.
`guide_id`	string	Unique identifier for the guide, if message was sent for a guide
`guide_key`	string	They key of the guide, if message was sent for a guide
`branch_path`	string	The branch ancestry of the step that produced the message, as a JSON array of `{step_ref, step_type, branch_id}` segments ordered outermost to innermost. Null if not nested under a branch step.

Message events table

The message_events export contains the event-level records behind message delivery and engagement. Each row captures one delivery or engagement event for a message, along with the workflow, channel, and recipient context around it. You can read more about message events.

The historical backfill of message events on initial connection will include the previous 90 days of data.

Use the column reference below to understand the fields included in this export. To see how these data types map onto the data types of your destination table, check the destination type mapping table.

The table name will vary based on the schema name provided during setup.

Name	Type	Description
`id`	string	Unique event ID. Can be used to deduplicate events.
`environment_id`	string	The UUID of the environment for the message event
`message_id`	string	The unique identifier for the message associated with this event
`channel_id`	string	The UUID of the channel the message was sent on
`channel_type`	string	The type of channel the message was sent on
`channel_provider`	string	The provider for the channel the message was sent on
`type`	string	The message event type
`data`	json	The payload data for the message event
`inserted_at`	timestamp	The timestamp of when the message event was created
`recipient_type`	string	The type of recipient for the message event, such as `users`, `tenants`, or `objects`
`object_collection_name`	string	The object collection name when `recipient_type` is `objects`. Blank for user and tenant recipients.
`recipient_id`	string	The ID of the recipient for the message event
`exec_mode`	string	The execution mode of the workflow
`tenant_id`	string	The tenant associated with this message event
`workflow_key`	string	The unique key of the workflow the message event belongs to
`workflow_id`	string	The UUID of the version of the workflow the message event belongs to
`step_ref`	string	The reference of the workflow step the message event belongs to
`guide_key`	string	The key of the guide, if the message event was emitted for a guide
`guide_id`	string	The UUID of the version of the guide, if the message event was emitted for a guide
`branch_path`	string	The branch ancestry of the step the message event belongs to, as a JSON array of `{step_ref, step_type, branch_id}` segments ordered outermost to innermost. Null if not nested under a branch step.

Recipient change stream table

Our data warehouse connector syncs data from our recipients table. Each row captures the properties and preferences of a Recipient at a given moment in time.

Each row has an event_type indicating how the event was generated. Possible values are:

recipient.created - indicates the first time a recipient was identified
recipient.snapshot - is emitted every time a recipient's properties or preferences are updated, and contains the complete set of properties and preferences at that moment in time. This event may also be generated manually for all or a subset of recipients (by ID) by contacting support.
recipient.deleted - contains no properties or preferences, but indicates when a recipient was deleted. A recipient may be re-identified after being deleted, which will generate another recipient.created event

Events in the recipient change stream table are retained for 7 days. The data available on initial connection will include a snapshot of the current state of all recipients, but no historical data.

Please note that the table name will vary based on the name of the schema provided when filling out the form.

Name	Type	Description
`id`	string	Unique event ID. Can be used to deduplicate events.
`account_id`	string	The UUID of the recipient's account
`environment_id`	string	The UUID of the recipient's environment
`environment_name`	string	The name of the recipient's environment
`environment_slug`	string	The slug of the recipient's environment
`recipient_id`	string	The ID of the recipient for the message
`recipient_type`	string	The type of recipient recorded
`event_type`	string	Possible values are: `recipient.created` - indicates the first time a recipient was identified `recipient.snapshot` - is emitted every time a recipient properties or preferences are updated, and contains the complete set of properties and preferences at that moment in time `recipient.deleted` - contains no properties or preferences, but indicates when a recipient was deleted. A recipient may be re-identified after being deleted, which will generate another recipient.created event A manual snapshot of the current state of all or a subset of recipients (by ID) can be made by contacting support.
`properties`	json	All properties currently assigned to the recipient. Will be empty for `recipient.deleted`.
`preferences`	json	All preference sets currently assigned to the recipient, keyed by preference set ID. Will be empty for `recipient.deleted`.
`timestamp`	timestamp	The timestamp of when the event was emitted.