Data warehouse sync

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. To set up this sync, please contact our support team.

✨

Enterprise plan feature. Data warehouse sync is available exclusively with our Enterprise plan.

How it works

Knock leverages Prequel to integrate with your data warehouse. There is no self-service UI to connect your account to start receiving data; instead when you contact us to set up a sync we'll send you a secure link to fill in the information about your data warehouse. Once it's connected, you'll begin receiving a backfill of historical data, and then going forward you will receive the most up to date data on an ongoing basis.

Setup

First, you'll have to give Knock your database type and host destination address for your data warehouse. The databases supported as destinations for Knock are those listed on Prequel.

Next, Knock generates a magic link you'll fill out. See what information you'll need for your particular database here.

Once the information is filled out, Prequel will provide you with a table definition. Additionally, you can run a test connection to make sure it's wired up. If it's successful, you can save it and it will proceed with the backfill transfer.

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.

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 in the Prequel docs.

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
`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
`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 guide 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: `trigger` - from the API `rehearse` - test run `rehearse_step` - test run a single step `integration` - from an event integration source `scheduled` - previously scheduled execution
`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

Recipient change stream table

👉

Note: at this time, the recipient change stream table contains only users. Please contact us if you need to sync other recipient types.

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.

Name	Type	Description
`id`	string	Unique event ID. Can be used to deduplicate events.
`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
`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.

←Heap