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
.
- Note: This must be used in conjunction with
-
$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
.
- Note: This must be used in conjunction with
-
$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 |
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. |