Warehouse Schemas

When sending your events to a data warehouse via RudderStack, you don't need to define a schema for your event data. RudderStack automatically does that for you by following a predefined warehouse schema.

This guide details the structure of this warehouse schema and the columns created in various tables based on different event types.

Schema

RudderStack uses the source name (written in snake case, e.g., source_name) to create a schema in your data warehouse (dataset, in the case of BigQuery).

The following tables are created for each RudderStack source connected to your warehouse destination:

Standard RudderStack properties

RudderStack sets the following standard properties on all the above-mentioned tables:

Clock skew considerations

RudderStack considers the time at its end to be absolute and assumes any difference on the client-side. Thus, the client clock skew is relative.

As mentioned in the above section:

Case 1: original_timestamp < received_at

The following table demonstrates an example of original_timestamp < received_at(when the client-side time is less than the time registered by RudderStack):

In this case, timestamp will be greater than original_timestamp.

Case 2: original_timestamp > received_at

The following table demonstrates an example of original_timestamp > received_at(when the client-side time is more than the time registered by RudderStack):

In this case, timestamp will be less than original_timestamp.

Table schemas

This section covers the major table schemas for different event types.

Track

RudderStack creates a record in both the tracks and <event_name> tables for every track call.

A sample track event named Add to Cart is shown below:

Copy
rudderanalytics.track(
  "Add to Cart", {
    price: 5,
    currency: "USD",
    product_id: "P12345",
    product_name: "N95 Mask",
  }, {
    context: {
      ip: "0.0.0.0",
    },
    anonymousId: "59b703e3-467a-4a1d-9fe6-da27ed319619",
  }
)

The corresponding schemas created for the tracks and add_to_cart tables is as shown:

Table: tracks

Table: add_to_cart

Identify

RudderStack creates a record in the identifies table and upserts the records in the users table for every identify call, based on userId.

A sample identify call is shown below:

Copy
rudderanalytics.identify(
  "userId",
  {
    email: "alex@company.com",
    first_name: "Alex",
    last_name: "Keener",
    age: 35,
  },
  {
    context: {
      ip: "0.0.0.0",
    },
    anonymousId: "59b703e3-467a-4a1d-9fe6-da27ed319619",
  }
)

The corresponding schemas created for the identifies and users tables are shown in the following sections:

Table: identifies

Table: users

Page/Screen

RudderStack creates a record in the pages or screens table for every page/screen call.

A sample page event is shown below:

Copy
rudderanalytics.page(
  "Cart",
  "Cart Viewed",
  {
    path: "/cart",
    title: "Shopping Cart",
    url: "https://rudderstack.com",
  },
  {
    context: {
      ip: "0.0.0.0",
    },
    anonymousId: "59b703e3-467a-4a1d-9fe6-da27ed319619",
  }
)

The corresponding schema created for the pages table is as shown:

Table: pages/screens

Group

RudderStack creates a record in the groups table for every group call.

A sample group call is shown below:

Copy
rudderanalytics.group(
  "DevOps", {
    email: "alex@keener.com",
    first_name: "Alex",
    last_name: "Keener",
    age: 35,
  }, {
    context: {
      ip: "0.0.0.0",
    },
    anonymousId: "59b703e3-467a-4a1d-9fe6-da27ed319619",
  }
)

The corresponding schemas created for the groups table is as shown:

Table: groups

Accepted timestamp formats

RudderStack recognizes only the following subsets of the ISO 8601 timestamp format:

• 2019-09-26
• 2009-05-19 14:39:22
• 2019-09-26T06:30:12.984Z
• 2020-02-11 04:56:55.175116
• 2019-09-26T06:30:12.984+0530
• 2019-09-26T06:30:12.984+05:30

Reserved keywords

There are some limitations when it comes to using the reserved words in a schema, table, or column names. If these words are used in the event names, traits or properties, RudderStack automatically prefixes an underscore(_) when creating the tables or columns for them in your schema.

The following table lists the warehouse-specific documentation references for reserved keywords:

How RudderStack handles data type mismatch

Once RudderStack recognizes and sets a data type for a table column, it will not accept any values for the column that cannot be cast to the specified data type.

The rudder_discards table schema is shown below:

The following snippet highlights a sample event whose properties are discarded due to a data type mismatch:

Copy
// intial track call using the RudderStack JavaScript SDK

rudderanalytics.track(
  "Add to Cart",
  {
    price: 5, // originally a int value
    currency: "USD",
    product_id: "P12345",
    product_name: "N95 Mask",
    added_at: "2020-05-19 14:39:22", // originally a datetime value
  },
  {
    context: {
      ip: "0.0.0.0",
    },
    anonymousId: "59b703e3-467a-4a1d-9fe6-da27ed319619",
  }
)

// subsequent track call using the RudderStack JavaScript SDK

rudderanalytics.track(
  "Add to Cart",
  {
    price: "NA", // sent as a string in latest event
    currency: "USD",
    product_id: 789, // sent as int but can be casted into original string data type
    product_name: "N95 Mask",
    added_at: "05/25/2020", // sent as invalid datetime value
  },
  {
    context: {
      ip: "0.0.0.0",
    },
    anonymousId: "59b703e3-467a-4a1d-9fe6-da27ed319619",
  }
)

The subsequent records created in the rudder_discards table for the discarded properties from the second event shown in the following table:

FAQs

Can I change the namespace (schema name) of my data warehouse in RudderStack?

Yes, you can. Although the default namespace will be the source name, RudderStack gives you the option to explicitly set the namespace while setting up your warehouse destination. For more information, refer to the warehouse-specific destination settings for configuring the namespace in the RudderStack dashboard.

Contact us

For queries on any of the sections covered in this guide, you can contact us or start a conversation in our Slack community.