Amplitude SQL: Table schema and field shortcuts

This article helps 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. 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 Data type 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.
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 theAccounts 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_timeTIMESTAMP 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 Data type 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.
Was this page helpful?

Thanks for your feedback!

May 30th, 2024

Need help? Contact Support


Have a look at the Amplitude Blog

Learn more at Amplitude Academy

© 2024 Amplitude, Inc. All rights reserved. Amplitude is a registered trademark of Amplitude, Inc.