Warehouse Schemas

When sending your events to a data warehouse via RudderStack, you don't need to define a schema for the event data before sending it from your source. Instead, 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 different tables based on different events.

Schema

RudderStack uses the source name (written in snake case, for example, source_name) to create a schema in your data warehouse (also called a dataset, in the case of Google BigQuery).

The following is a list of tables created for each RudderStack source that is connected to the warehouse:

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.

Here's an example of original_timestamp < received_at(The client-side time is less than the server-side time):

In this case, timestamp will be greater than original_timestamp.

Here's an example of original_timestamp > received_at (The client-side time is more than the server-side time):

In this case, timestamp will be less than original_timestamp.

Table Schemas

This section covers the major table schemas for different types of events.

Track

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

A sample Add to Cart event made from the JavaScript SDK is as shown below:

Copy
// track call using JavaScript SDK
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 the userId.

A sample identify event made from the JavaScript SDK is as shown below:

Copy
rudderanalytics.identify(
  "U-12345",
  {
    email: "john@rudderstack.com",
    first_name: "John",
    last_name: "Doe",
    age: 35,
  },
  {
    context: {
      ip: "0.0.0.0",
    },
    anonymousId: "59b703e3-467a-4a1d-9fe6-da27ed319619",
  }
)

The corresponding schemas created for the identifies and users tables is as shown:

Table: identifies

Table: users

Page & Screen

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

A sample page event is as 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 as shown below:

Copy
rudderanalytics.group(
  "DevOps",
  {
    email: "john@rudderstack.com",
    first_name: "John",
    last_name: "Doe",
    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 only recognizes a subset of the ISO 8601 timestamp formats as a timestamp. Listed below are the accepted timestamp formats:

• 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

Note that 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 a _ when creating tables or columns for them in your schema.

Also, note that integers are not allowed at the start of the schema or table name. Such schema, column, or table names will be prefixed with a _. For instance, 25dollarpurchase will be changed to _25dollarpurchase.

Find more details about the list of reserved keywords in the following warehouse-specific docs.

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 schema of the rudder_discards table is as shown below:

Shown below is a sample event whose properties are discarded due to a mismatch in the data type of the previous events:

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 listed in the example above are as shown:

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 with some modifications to it, RudderStack also provides an option in the dashboard to explicitly set the namespace of the dataset in the warehouse.

Refer to the warehouse-specific destination settings for configuring the namespace in the RudderStack dashboard.

Contact Us

For any queries on any of the sections covered in this guide, feel free to contact us or start a conversation on our Slack channel.