Warehouse Schemas

Warehouse tables

The table below describes the schema in Segment Warehouses:

Identifies table

The identifies table stores the .identify() method calls =. Query it to find out user-level information. It has the following columns:

Querying the Identifies table

To see a list of the columns in the identifies table for your <source> run:

SELECT column_name AS Columns
FROM columns
WHERE schema_name = '<source>'
AND table_name = 'identifies'
ORDER by column_name

The identifies table is where you can query information about your users and their traits. For example, if you wanted to see the number of unique users you’ve seen on your site each day:

SELECT DATE(sent_at) AS Day, COUNT(DISTINCT(user_id)) AS Users
FROM <source>.identifies
GROUP BY day
ORDER BY day

Groups table

The groups table stores the group method calls. Query it to find out group-level information. It has the following columns:

Querying the Groups table

To see a list of the columns in the groups table for your <source> run:

SELECT column_name AS Columns
FROM columns
WHERE schema_name = '<source>'
AND table_name = 'groups'
ORDER by column_name

To see a list of the groups using your product run:

SELECT name AS Company
FROM <source>.groups
GROUP BY name

Pages and Screens tables

The pages and screens tables store the page and screen method calls. Query it to find out information about page views or screen views. It has the following columns:

Querying the Pages and Screens tables

To see a list of the columns in the pages table for your <source> run:

SELECT column_name AS Columns
FROM columns
WHERE schema_name = '<source>'
AND table_name = 'pages'
ORDER by column_name

The pages table can give you interesting information about page views that happen on your site, for example you can see the number of page views grouped by day:

SELECT DATE(sent_at) AS Day, COUNT(*) AS Views
FROM <source>.pages
GROUP BY day
ORDER BY day

Tracks table

The tracks table stores the track method calls. Query it to find out information about the events your users have triggered. It has the following columns:

Querying the Tracks table

Your tracks table is a rollup of the different event-specific tables, for quick querying of just a single type. For example, you could see the number of unique users signed up each day:

SELECT DATE(sent_at) AS Day, COUNT(DISTINCT(user_id)) AS Users
FROM segment.tracks
WHERE event = 'signed_up'
GROUP BY day
ORDER BY day

Event Tables

Your event tables are a series of table for each custom event you record to Segment. We break them out into their own tables because the properties, and thus the columns, differ for each event. Query these tables to find out information about specific properties of your custom events. They have the following columns:

Querying the Events tables

To see a list of the event tables for a given <source> you can run:

SELECT schema as source, "table" as Event
FROM disk
WHERE schema = '<source>'
  AND "table" != 'aliases'
  AND "table" != 'groups'
  AND "table" != 'identifies'
  AND "table" != 'pages'
  AND "table" != 'screens'
  AND "table" != 'tracks'
ORDER BY "table"

To see a list of the columns in one of your event tables run:

SELECT column_name AS Columns
FROM columns
WHERE schema_name = '<source>'
AND table_name = '<event>'
ORDER by column_name

Note: If you send us an array, we will stringify it in Redshift. That way you don’t end up having to pollute your events. It won’t work if you have a lot of array elements but should work decently to store and query those. We also flatten nested objects. 

Tracks vs. Events Tables

To see the tables for your organization, you can run this query:

SELECT schema || '.' || "table" AS table, rows
FROM disk
ORDER BY 1

The source.event tables have the same columns as the source.track tables, but they also include columns specific to the properties of each event.

If you’re recording an event like:

analytics.track('Register', {
  plan: 'Pro Annual',
  accountType: 'Facebook'
});

Then you can expect to see columns named plan and account_type as well as the default event, id, etc. That way you can write queries against any of the custom data send in track calls.

Note: Because properties and traits are added as un-prefixed columns to your tables, there is a chance of collision with our reserved column names. For this reason, properties with the same name as reserved column name (for example. user_id) are discarded.

Your event tables are one of the more powerful datasets in Segment SQL. They allow you to see which actions users are performing when interacting with your product.

Because every source has different events, what you can do with them will vary. Here’s an example where you can see the number of “Enterprise” users signed up for each day:

SELECT DATE(sent_at) AS Day, COUNT(DISTINCT(user_id)) AS Users
FROM <source>.signed_up
WHERE account_type = 'Enterprise'
GROUP BY day
ORDER BY day

Here’s an example that queries the daily revenue for an ecommerce store:

SELECT DATE(sent_at) AS Day, SUM(total) AS Revenue
FROM <source>.completed_order
GROUP BY day
ORDER BY day

New Columns

New event properties and traits create columns. Segment processes the incoming data in batches, based on either data size or an interval of time. If the table doesn’t exist we lock and create the table. If the table exists but new columns need to be created, we perform a diff and alter the table to append new columns.

When Segment process a new batch and discover a new column to add, we take the most recent occurrence of a column and choose its datatype.

The datatypes that we support right now are: 

• timestamp
• integer 
• float
• boolean
• varchar

Column Sizing

After analyzing the data from dozens of customers we set the string column length limit at 512 characters. Longer strings are truncated. We found this was the sweet spot for good performance and ignoring non-useful data.

We special-case compression for some known columns like event names and timestamps. The others default to LZO. We may add look-ahead sampling down the road, but from inspecting the datasets today this would be unnecessary complexity.

Timestamps

The Segment API associates four timestamps with every call: timestamp, original_timestamp, sent_at and received_at.

All four timestamps pass through to your Warehouse for every ETL’d event. In most cases the timestamps are close together, but they have different meanings which are important.

timestamp is the UTC-converted timestamp which is set by the Segment library. If you are importing historical events using a server-side library, this is the timestamp you’ll want to reference in your queries!

original_timestamp is the original timestamp set by the Segment library at the time the event is created. Keep in mind, this timestamp can be affected by device clock skew. You can override this value by manually passing in a value for timestamp which will then be relabed as original_timestamp. Generally, this timestamp should be ignored in favor of the timestamp column.

sent_at is the UTC timestamp set by library when the Segment API call was sent. This timestamp can also be affected by device clock skew.

received_at is UTC timestamp set by the Segment API when the API receives the payload from client or server. All tables use received_at for the sort key.

received_at does not ensure chronology of events. For queries based on event chronology, timestamp should be used.

Here’s additional documentation on timestamps in the context of our spec.

id

Each row in your database will have an id which is equivalent to the messageId which is passed through in the raw JSON events. The id is a unique message id associated with the row.

uuid and uuid_ts

The uuid column is used to prevent duplicates. You can ignore this column.

The uuid_ts column is used to keep track of when the specific event was last processed by our connector, specifically for deduping and debugging purposes. You can generally ignore this column.

Sort Key

All tables use received_at for the sort key. Amazon Redshift stores your data on disk in sorted order according to the sort key. The Redshift query optimizer uses sort order when it determines optimal query plans.

More Help

How do I send custom data to my warehouse?

How do I give users permissions to my warehouse?

Check out our Frequently Asked Questions about Warehouses and a list of helpful queries to get you started.