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, for example, source_name) to create a schema in your data warehouse.

The following tables are created in your data warehouse for each RudderStack source connected to it:

Standard RudderStack properties

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

Identify

For every Identify call, RudderStack creates a record in the identifies table and upserts the records in the users table based on the 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

Track

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

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 are as shown:

Table: tracks

Table: add_to_cart

Page/Screen

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

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/screens table is as shown:

Table: pages/screens

Group

For every group call, RudderStack creates a record in the corresponding groups table.

A sample group call is shown below:

Copy
rudderanalytics.group(
  "groupId", {
    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 schema created for the groups table is as shown:

Table: groups

Alias

For every alias call, RudderStack creates a record in the corresponding aliases table.

A sample alias call is shown below:

Copy
rudderanalytics.alias("9bb5d4c2", "e6ab2c5e")

Table: aliases

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.

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.