Redshift
Sync Expectations
The following table describes the sync behavior of Fullstory events to Redshift.
| Destination | Sync Interval | Update Window* | Relevant Timestamps | Setup Guide |
|---|---|---|---|---|
| Redshift | Approximately processed_time rounded to the next hour + 1 hour | 99% stable within 3 hours | event_time updated_time processed_time | Redshift Help Doc |
* Update Window: These are observed aggregates and may vary based on the specifics of the data capture for your site or app.
Events Table Schema
The following table contains the schema for the Fullstory events in Redshift. For information on the data types, see the Redshift documentation.
| Field | Type | Description |
|---|---|---|
event_id | VARCHAR(MAX) | The unique identifier for the event. |
event_time | TIMESTAMPTZ | The time in UTC that the event occurred. |
processed_time | TIMESTAMPTZ | The time in UTC that the event was processed by Fullstory's servers. |
updated_time | TIMESTAMPTZ | The time in UTC that event was updated in the database. Set via SYSDATE when merging. |
device_id | BIGINT | The device ID as defined in the base event model. |
session_id | BIGINT | The session ID as defined in the base event model. |
view_id | BIGINT | The view ID as defined in the base event model. |
event_type | VARCHAR(MAX) | The type of this event. |
event_properties | SUPER | A json object containing the associated event properties. |
source_type | VARCHAR(MAX) | The source type of the event. |
source_properties | SUPER | A json object containing the associated source properties. |
Querying the Events Table
To demonstrate how to query the events table, the following example shows how to count the number of rage clicks broken down by browser and URL for a single day.
SELECT
source_properties.user_agent.browser::varchar as browser,
source_properties.url.path::varchar as path,
count(1) as rage_clicks
FROM
FULLSTORY_DB.FULLSTORY_ORG_ID.events
WHERE
date(event_time) = '2024-01-01'
AND event_type = 'click'
AND event_properties.fs_rage_count > 0
GROUP BY
browser,
path
ORDER BY
rage_clicks DESC;
Analyze with DBT
Fullstory provides a DBT package to help you get started with Redshift. The package includes models for Fullstory events, sessions, and users.