Redshift schema description

This article describes Redshift schema for customers still using the Redshift ETL add-on. Each project has its own schema.

NOTE: For most customers, the Help Center article on exporting Amplitude data to Redshift will be more useful.

Schema structure

There is a schema created for each project you have on Amplitude. To list out all the schemas on Redshift, please use the following command when you log into pSQL:

\dn

To see all of the tables and views associated with a schema, use the following commands (with XXXX = app_id):

set search_path = appXXXX;

\d

Use \d+ view_name to see a definition of each view.

By default, we have views created for each project in the public schema for easy access. The format of these views is: eventsXXXX, where XXXX = app_id.

Tables nomenclature

There are three types of tables on Amplitude: dedicated tables, generic tables, and a universal table. Please note that the easiest way to query Amplitude data is to query off of the views.

Dedicated Tables: The first 20 event-types get their own tables.

• Example format: events<app_id>_<event_type_id>. event_type_id is internal identifier on Amplitude.

Generic Tables: There are 50 more generic tables with each generic table accommodating 20 event-types. Each event in this table has an individual view created in the project schema.

• Example format: generic_<app_id>_y, where y goes from 1 to 20 because there are 20 generic tables.
  • First generic table created is generic_<app_id>_1, and once 50 event types are put in it, for the 51st event type, we created generic_<app_id>_2 and so on.

Universal Tables: Everything else after the first 1020 event-types fall into a universal table. First 1020 event-types also have individual views created in the project schema.

Views nomenclature

You may find that there are various tables (they are technically views) you can perform your Redshift queries on. Find below explanations of the nomenclature of the views you will be querying from.

• events###: A view of all event data in a specific project. The numbers following "event" are your Project ID, which you can find in your project's Settings page.
• events### is located in the public schema. An equivalent view can be found in the project schema, where "appXXX" is the project schema and "events" is the table name.
• Example: event123 is a view of all event data for Project 123 in the public schema. This very same view is called events in the app schema. So events### = app###.events.
• Query on this view when you want to query on data of multiple events.
• Event and user properties are stored in a single column as a JSON string.
• app###.event_type: A view of all data for a specific event (event_type) in a specific project. The numbers following "app" are your Project ID and event_type is the name of the event you are querying on.
• Example: app123.add_friend is a view of all the add_friend event data for Project 123.
• Each event and user property has its own column.

Column schema

There is a limit of 400 user properties and 50 event properties that will be pulled into their own columns. Anything past the limit will still require the JSON_EXTRACT_PATH_TEXT function on the 'event_properties' or 'user_properties' columns.

Below is the list of the columns in an event view (event###), specifically the column type and a brief description of the column.