Amplitude SQL: Table schema and field shortcuts

  • Updated

 

This article will help you:

  • Use field shortcuts to query your Snowflake database in Amplitude SQL
  • Understand the schema of the $events table

Special field shortcuts

Because Amplitude SQL is built directly into the Amplitude chart experience, you can leverage much of the same familiar Amplitude user interface, such as the datepicker chart saving experience. You can do this by using Amplitude SQL's special fields for powerful shortcuts:

  • $date: When using this shortcut, the time range chosen by the datepicker is automatically applied and will update the query over time. It refers to the event time on the event and will respect the timezone the project has been set to. Otherwise, Amplitude SQL will return data in UTC.
    • Note: This must be used in conjunction with $events.
  • $events: The shorthand used to refer to the table in your current project. When using this table, merged users are automatically handled.
    • Note: This must be used in conjunction with $date.
  • $amplitude_id: The original Amplitude ID for the user. Use this field to automatically handle merged users.

Table schema

Amplitude SQL uses a one-table schema. The table, $events, handles the merged user mappings automatically. But, the merged users table will also be available to view. This will allow you to quickly see the number of users that have been merged into one.

The following tables show the schema of $events and merged users. 

$events Table

Column Description
$amplitude_id

NUMBER(38,0)

The original Amplitude ID for the user. Use this field to automatically handle merged users.

adid

VARCHAR(16777216)

(Android) Google Play Services advertising ID (AdID). This usually is wiped after ingestion and therefore will be blank.

amplitude_attribution_ids Anonymized hash of the advertising IDs that we store for internal purposes; not useful for the customer by any means. But this will appear if advertising IDs were sent which proves that adid/idfv existed even though currently wiped.
amplitude_event_type

VARCHAR(16777216)

Amplitude specific identifiers based on events Amplitude generates. This is a legacy field so event_type should suffice for all queries.
amplitude_id

NUMBER(38,0)

An internal ID used to count unique users.
app

NUMBER(38,0)

Project ID found in your project's Settings page.
city

VARCHAR

City.

client_event_time

TIMESTAMP

Local timestamp (UTC) of when the device logged the event.
client_upload_time

TIMESTAMP

The local timestamp (UTC) of when the device uploaded the event.
country

VARCHAR

Country.
data

VARIANT

Dictionary where certain fields such as first_event and merged_amplitude_id are stored.
device_brand

VARCHAR(16777216)

Device brand.
device_carrier

VARCHAR(16777216)

Device carrier.
device_family

VARCHAR(16777216)

Device family.
device_id

VARCHAR(16777216)

The device specific identifier.
device_manufacturer

VARCHAR(16777216)

Device manufacturer.
device_model

VARCHAR(16777216)

The device model.
device_type

VARCHAR(16777216)

Device type.

dma

VARCHAR(16777216)

Designated marketing area (DMA).
event_id

NUMBER(38,0)

A counter that distinguishes events.
event_time

TIMESTAMP

Amplitude timestamp (UTC) which is the client_event_time adjusted by the difference between server_received_time and client_upload_time, specifically:

event_time = client_event_time + (server_received_time - client_upload_time)

We use this timestamp to organize events on Amplitude charts.

NOTE: If the difference between server_received_time and client_upload_time is less than 60 seconds, the event_time will not be adjusted and will equal the client_event_time.

event_type

VARCHAR(16777216)

The assigned type of event.
followed_an_identify

BOOLEAN

True if there was an identify event between this current SDK event and the last SDK event seen.

groups

VARIANT

Group types. See the Accounts documentation for more information.
idfa

VARCHAR(16777216)

(iOS) Identifier for Advertiser. This usually is wiped after ingestion and therefore will be blank.
ip_address

VARCHAR(16777216)

IP address.
location_lat

FLOAT

Latitude. 
location_lng

FLOAT

Longitude.
os_name

VARCHAR(16777216)

OS name.
os_version

VARCHAR(16777216)

OS version.
paying

VARCHAR

True if the user has ever logged any revenue, otherwise '(none)'. The property value can be modified via the Identify API.

region

VARCHAR

Region.
server_upload_time

TIMESTAMP

Amplitude timestamp (UTC) of when our servers received the event.
session_id

NUMBER(38,0)

The session start time in milliseconds since epoch.
start_version

VARCHAR

App version the user was first tracked on.
user_creation_time

TIMESTAMP

Event_time (UTC) of the user's first event.

user_id

VARCHAR(16777216)

A readable ID specified by you.
uuid

VARCHAR(16777216)

A unique identifier per row (event sent).

version_name

VARCHAR(16777216)

The app version.

Merged users table

See this article to learn more about how Amplitude tracks unique users.

Column Description
amplitude_id

NUMBER(38,0)

The Amplitude ID that is being merged into a user's original Amplitude ID.

merge_event_time

TIMESTAMP

The time of the event a user's new Amplitude ID was associated with their original Amplitude ID.

merge_server_time

TIMESTAMP

The server time of the event when a user's new Amplitude ID was associated with their original Amplitude ID.

merged_amplitude_id

NUMBER(38,0)

The originally assigned Amplitude ID when the user is first created.