Extensions
Data warehouse sync

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

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 will continue to receive the most up-to-date data every 24 hours.

Setup

You'll need to work with our support team to connect your data warehouse. We'll need to know the destination type of your data warehouse, and will ask you for some additional information based on which destination type you're configuring. The databases supported as destinations for Knock are listed here.

Once we have the information we need, Knock generates a magic link that you'll use to complete the setup process. The link will take you through a setup wizard that is specific to your destination.

If the connection is 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.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE TABLE <name of schema>.messages (
      message_id character varying(65535) NOT NULL ENCODE raw distkey,
        account_id character varying(65535) ENCODE lzo,
        environment_id character varying(65535) ENCODE lzo,
        environment_name character varying(65535) ENCODE lzo,
        environment_slug character varying(65535) ENCODE lzo,
        channel_id character varying(65535) ENCODE lzo,
        channel_name character varying(65535) ENCODE lzo,
        channel_key character varying(65535) ENCODE lzo,
        channel_type character varying(65535) ENCODE lzo,
        channel_provider character varying(65535) ENCODE lzo,
        workflow_id character varying(65535) ENCODE lzo,
        workflow_key character varying(65535) ENCODE lzo,
        combined_trigger_data character varying(32768) ENCODE lzo,
        step_ref character varying(65535) ENCODE lzo,
        recipient_id character varying(65535) ENCODE lzo,
        recipient_type character varying(65535) ENCODE lzo,
        tenant_id character varying(65535) ENCODE lzo,
        exec_mode character varying(65535) ENCODE lzo,
        message_status character varying(65535) ENCODE lzo,
        inserted_at timestamp with time zone ENCODE az64,
        updated_at timestamp with time zone ENCODE raw,
        seen_at timestamp with time zone ENCODE az64,
        read_at timestamp with time zone ENCODE az64,
        clicked_at timestamp with time zone ENCODE az64,
        interacted_at timestamp with time zone ENCODE az64,
        archived_at timestamp with time zone ENCODE az64,
        has_been_seen bigint ENCODE az64,
        has_been_read bigint ENCODE az64,
        has_been_clicked bigint ENCODE az64,
        has_been_interacted bigint ENCODE az64,
        has_been_archived bigint ENCODE az64,
        actors character varying(65535) ENCODE lzo
        PRIMARY KEY (message_id)
    ) DISTSTYLE KEY SORTKEY (message_id, updated_at);

See possible engagement statuses for a message.

NameTypeDescription
message_idstringThe unique identifier for this message and the primary key for this table
account_idstringThe UUID of the message's account
environment_idstringThe UUID of the message's environment
environment_namestringThe name of the message's environment
environment_slugstringThe slug of the message's environment
channel_idstringThe UUID of the channel the message was sent on
channel_keystringThe unique key of the channel the message was sent on
channel_namestringThe name of the channel the message was sent on
channel_providerstringThe provider for the channel the message was sent on
channel_typestringThe type of channel the message was sent on
workflow_idstringThe UUID of the version of the workflow that the message belongs to
workflow_keystringThe unique key of the workflow the message belongs to
combined_trigger_datajson
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_refstringThe reference of the step on the workflow that the message belongs to
recipient_idstringThe ID of the recipient for the message
recipient_typestring
The type of recipient for the message, can be a user or an object
tenant_idstringThe tenant associated with this message
exec_modestring
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_statusstring
inserted_attimestampThe timestamp of when the message was created
updated_attimestampThe timestamp of when the message was last updated
archived_attimestampThe timestamp of when the message was archived
seen_attimestampThe timestamp of when the message was seen
read_attimestampThe timestamp of when the message was read
clicked_attimestampThe timestamp of when a link in the message was clicked
interacted_attimestampThe timestamp of when the message was interacted with
has_been_seeninteger (0 = false, 1 = true)Whether the message has been seen
has_been_readinteger (0 = false, 1 = true)Whether the message has been read
has_been_clickedinteger (0 = false, 1 = true)Whether a link in the message has been clicked
has_been_interactedinteger (0 = false, 1 = true)Whether the message has been interacted with
has_been_archivedinteger (0 = false, 1 = true)Whether the message has been archived
actorsjson
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.

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.

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.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE <name of schema>.recipient_change_stream (
      id character varying(65535) NOT NULL ENCODE raw distkey,
        account_id character varying(65535) ENCODE lzo,
        environment_id character varying(65535) ENCODE lzo,
        environment_name character varying(65535) ENCODE lzo,
        environment_slug character varying(65535) ENCODE lzo,
        recipient_id character varying(65535) ENCODE lzo,
        recipient_type character varying(65535) ENCODE lzo,
        event_Type character varying(65535) ENCODE lzo,
        properties json ENCODE lzo,
        preferences json ENCODE lzo,
        timestamp timestamp with time zone ENCODE az64,
        PRIMARY KEY (id)
    ) DISTSTYLE KEY SORTKEY (id, timestamp);
NameTypeDescription
idstringUnique event ID. Can be used to deduplicate events.
account_idstringThe UUID of the recipient's account
environment_idstringThe UUID of the recipient's environment
environment_namestringThe name of the recipient's environment
environment_slugstringThe slug of the recipient's environment
recipient_idstringThe ID of the recipient for the message
recipient_typestring
event_typestring
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.
propertiesjson
All properties currently assigned to the recipient. Will be empty for recipient.deleted.
preferencesjson
All preference sets currently assigned to the recipient, keyed by preference set ID. Will be empty for recipient.deleted.
timestamptimestampThe timestamp of when the event was emitted.