Snowflake

Sync Expectations

The following table describes the sync behavior of Fullstory events to Snowflake.

Destination	Sync Interval	Update Window*	Relevant Timestamps	Setup Guide
Snowflake	Approximately processed_time rounded to the next hour + 1 hour	99% stable within 3 hours	event_time updated_time processed_time	Snowflake Help Doc

* Update Window: These are observed aggregates and may vary based on the specifics of the data capture for your site or app.

Data Schema

Events Table Schema

The following table contains the schema for the Fullstory events in Snowflake. For information on the data types, see the Snowflake documentation.

Field	Type	Description
event_id	VARCHAR	The unique identifier for the event.
event_time	TIMESTAMP	The time in UTC that the event occurred.
processed_time	TIMESTAMP	The time in UTC that the event was processed by Fullstory's servers.
updated_time	TIMESTAMP	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	The type of this event.
event_properties	VARIANT	A json object containing the associated event properties.
source_type	VARCHAR	The source type of the event.
source_properties	VARIANT	A json object containing the associated source properties.

Element Definitions Table Schema

The following table contains the schema for Fullstory named element definitions.

Property	Type	Description
id	VARCHAR(MAX)	The named element's ID. Use the ID to join on the element_definition_id.
name	VARCHAR(MAX)	The name of the named element.
description	VARCHAR(MAX)	The description of the named element.
state	VARCHAR(MAX)	The state of the named element (e.g., active, archived).
created_time	TIMESTAMPTZ	Timestamp of when the named element was created.
created_by	VARCHAR(MAX)	The Fullstory user that created the named element.
modified_time	TIMESTAMPTZ	Timestamp of when the named element was last modified.
modified_by	VARCHAR(MAX)	The Fullstory user that last modified the named element.
updated_time	TIMESTAMPTZ	Timestamp of when this record was last synced.

Event Definitions Table Schema

The following table contains the schema for Fullstory defined event definitions.

Property	Type	Description
id	VARCHAR(MAX)	The defined event's ID. Use the ID to join on the event_definition_id.
name	VARCHAR(MAX)	The name of the defined event.
description	VARCHAR(MAX)	The description of the defined event.
state	VARCHAR(MAX)	The state of the defined event (e.g., active, archived).
created_time	TIMESTAMPTZ	Timestamp of when the defined event was created.
created_by	VARCHAR(MAX)	The Fullstory user that created the defined event.
modified_time	TIMESTAMPTZ	Timestamp of when the defined event was last modified.
modified_by	VARCHAR(MAX)	The Fullstory user that modified the defined event.
updated_time	TIMESTAMPTZ	Timestamp of when this record was last synced.

Page Definitions Table Schema

The following table contains the schema for Fullstory page definitions.

Property	Type	Description
id	VARCHAR(MAX)	The page ID. Use the ID to join on the page_definition_id.
fs_link_id	VARCHAR(MAX)	The ID that can be used to construct a url in the Fullstory app, e.g. https://app.fullstory.com/ui/<org_id>/settings/pages/<link_id>
name	VARCHAR(MAX)	The name of the page.
description	VARCHAR(MAX)	The customer-entered description of the page.
is_user_defined	BOOLEAN	True if the page was defined by the user, False if defined by a Fullstory algorithm.
state	VARCHAR(MAX)	The state of the page (e.g., active, archived).
created_time	TIMESTAMPTZ	Timestamp of when the page was created.
created_by	VARCHAR(MAX)	The user that created the page.
modified_time	TIMESTAMPTZ	Timestamp of when the page was last modified.
modified_by	VARCHAR(MAX)	The user that modified the page.
updated_time	TIMESTAMPTZ	Timestamp of when this record was synced.

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::string as browser,
  source_properties:url.path::string 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::int > 0
GROUP BY
  browser,
  path
ORDER BY
  rage_clicks DESC;

Analyze with DBT

Fullstory provides a DBT package to help you get started with Snowflake. The package includes models for Fullstory events, sessions, and users.