This article will help you:
- Query your Snowflake database using custom SQL
The Amplitude Query product allows customers to query their raw data via their Amplitude-managed Snowflake database. Query also includes a powerful new chart type called Amplitude SQL that allows customers to write custom SQL against their Amplitude data directly inside the Amplitude platform.
NOTE: Data is loaded into Snowflake every 30 minutes.
This feature is available to users on Growth and Enterprise plans only. See our pricing page for more details.
Amplitude SQL is accessible just like any other chart type, via Create New > Chart > All chart types. It can be saved, shared, and added to a dashboard just like any other chart.
NOTE: This feature currently does not support queuing data for Portfolio Views.
Alternatively, you can connect directly to your Snowflake database via a terminal or 3rd-party application, such as SQL Workbench or the Snowflake connector for Python. Please reach out to us or contact your Success Manager for your Snowflake credentials.
Set up and syntax
The Query package uses a simplified table schema for Snowflake and Amplitude SQL. The schema includes a single table that can be referenced using the shorthand
If you want to access other tables, you can use the full name which can be found by clicking Show Schema.
With Query, one of the major benefits is the unlimited number of data fields that can exist in the table. Custom user properties and event properties are stored as variants, and they are queryable as individual columns. Custom user properties are prefixed with
user_properties: and all event properties are prefixed with
NOTE: If your user or event properties contain a period or a space, then you will need to wrap the name of the property in quotes. For example,
If you are searching for a certain value, you will have to wrap the value of the property in single quotes. For example,
By default, Amplitude SQL shows a simple SQL query for events performed in the past 30 days. The SQL syntax includes the following fields:
$dateas Date (the date of the events)
COUNT(DISTINCT $amplitude_id)with the alias Uniques (count of unique users)
COUNT($amplitude_id)as Totals (total count of users)
The SQL syntax will be highlighted to help you distinguish SQL commands from the rest of your query.
In addition, Amplitude SQL supports autocomplete of columns in the table. As you type, the query editor will recommend columns to help expedite your typing.
Once you have completed the desired SQL, click Compute to execute your query. When your query has finished running, you will see:
- Query results displayed in a data table.
- A time series chart of the results below the table.
To customize the time series visualization, there is a set of controls that you can use below the query editor. The options available in the visualization controls are the fields that you return in your SQL
SELECT statement. For example, in the query above, the 3 fields we can visualize are DATE, UNIQUES, and TOTALS.
- X-axis column: Select what is plotted on the X-axis. Currently, only time series are supported.
Metric column: Select which field is returned in your SQL
SELECTstatement to plot it on the Y-axis.
To group the chart by a column, enter the name of the column in the Label columns field. Then click Compute.
Sharing and saving queries
Like other Amplitude charts, you are able to export the results as a PNG, PDF, or CSV file by navigating to More > Export. You can also save your analysis and share it with your team or even add the visualizations you create to a dashboard in Amplitude.
NOTE: The results of the data table and the .CSV export are capped at 1,000 rows.
Next, learn about special field shortcuts to query your Snowflake data more quickly .