# Query Snowplow data in your warehouse > Connect to your warehouse or lake and query Snowplow data, including self-describing events, entities, example queries, and duplicate events. > Source: https://docs.snowplow.io/docs/destinations/warehouses-lakes/querying-data/ > Documentation index: https://docs.snowplow.io/llms.txt You will typically find most of your Snowplow data in the `events` table. If you are using Redshift, there will be extra tables for [self-describing events](/docs/fundamentals/events/#self-describing-events) and [entities](/docs/fundamentals/entities/) — see [below](#self-describing-events). Please refer to [the structure of Snowplow data](/docs/fundamentals/canonical-event/) for the principles behind our approach, as well as the descriptions of the various standard columns. > **Tip: Data models** > > Querying the `events` table directly can be useful for exploring your events or building custom analytics. However, for many common use cases it’s much easier to use our [data models](/docs/modeling-your-data/modeling-your-data-with-dbt/), which provide a pre-aggregated view of your data. The simplest query could look like this: ```sql SELECT * FROM WHERE event_name = 'page_view' ``` You will need to replace `` with the appropriate location — the database, schema and table name will depend on your configuration. > **Note:** With large data volumes (read: any production system), you should always include a filter on the partition key (normally, `collector_tstamp`), for example: > > ```sql > WHERE ... AND collector_tstamp between timestamp '2023-10-23' and timestamp '2023-11-23' > ``` > > This ensures that you read from the minimum number of (micro-)partitions necessary, making the query run much faster and reducing compute cost (where applicable). ## Connect to your warehouse How you connect depends on your choice of storage and your Snowplow platform. ### Snowplow CDI For Snowplow CDI Private Managed Cloud or Cloud customers, you can find your connection details in [Snowplow Console](https://console.snowplowanalytics.com/destinations/warehouses-lakes), under the destination you've selected. #### Get example queries from Console When viewing an [event specification](/docs/event-studio/tracking-plans/event-specifications/) in Console, the **Working with this event** section provides ready-to-use code snippets. The **Querying** tab provides example queries to help you retrieve and analyze that event's data. Choose your warehouse to see SQL written for it. ![\"Working with this event\" panel on the Querying tab showing a Snowflake SQL query that selects flattened event and entity columns from atomic events, filtered by event name and vendor](/assets/images/sql-example-d64be1abac9a42e1162f0ed311e2db80.png) ### Snowplow Self-Hosted If you don't have access to Snowplow Console, use these instructions to connect to the storage you set up with Terraform: **Postgres:** Your database will be named according to the `postgres_db_name` Terraform variable. It will contain two schemas: - `atomic` — the validated events - `atomic_bad` — the [failed events](/docs/fundamentals/failed-events/) You can connect to the database using the credentials you provided for the loader in the Terraform variables (`postgres_db_username` and `postgres_db_password`), along with the `postgres_db_address` and `postgres_db_port` Terraform outputs. > **Tip:** If you need to reset your username or password, you can follow [these steps](https://aws.amazon.com/premiumsupport/knowledge-center/reset-master-user-password-rds/). See [the AWS RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html) for more details on how to connect. > **Note:** If you opted for the `secure` option, you will first need to create a tunnel into your VPC to be able to connect to your RDS instance and be able to query the data. A common solution to this issue is to configure a bastion host as described [here](https://repost.aws/knowledge-center/rds-connect-using-bastion-host-linux). **Redshift:** The database name and the schema name will be defined by the `redshift_database` and `redshift_schema` variables in Terraform. There are two different ways to login to the database: - The first option is to use the credentials you configured for the loader in the Terraform variables (`redshift_loader_user` and `redshift_loader_password`) - The second option is to grant `SELECT` permissions on the schema to an existing user To connect, you can use the Redshift UI or something like [`psql`](https://www.postgresql.org/docs/current/app-psql.html). **BigQuery:** The database will be called `_snowplow_db`, where `` is the prefix you picked in your Terraform variables file. It will contain an `atomic` schema with your validated events. You can access the database via the [BigQuery UI](https://console.cloud.google.com/bigquery). **Snowflake:** The database name and the schema name will be defined by the `snowflake_database` and `snowflake_schema` variables in Terraform. There are two different ways to login to the database: - The first option is to use the credentials you configured for the loader in the Terraform variables (`snowflake_loader_user` and `snowflake_loader_password`) - The second option is to grant `SELECT` permissions on the schema to an existing user To connect, you can use either Snowflake dashboard or [SnowSQL](https://docs.snowflake.com/en/user-guide/snowsql.html). **Databricks:** > **Info: Azure-specific instructions** > > On Azure, you have created an external table in the [last step of the guide](/docs/get-started/self-hosted/quick-start/#configure-the-destination). Use this table and ignore the text below. The database name and the schema name will be defined by the `databricks_database` and `databricks_schema` variables in Terraform. There are two different ways to login to the database: - The first option is to use the credentials you configured for the loader in the Terraform variables (`databricks_loader_user` and `databricks_loader_password`, or alternatively the `databricks_auth_token`) - The second option is to grant `SELECT` permissions on the schema to an existing user See the [Databricks tutorial](https://docs.databricks.com/getting-started/quick-start.html) for more details on how to connect. The documentation on [Unity Catalog](https://docs.databricks.com/data-governance/unity-catalog/queries.html) is also useful. **Synapse Analytics:** In Synapse Analytics, you can connect directly to the data residing in ADLS. You will need to know the names of the storage account (set in the `storage_account_name` Terraform variable) and the storage container (it’s a fixed value: `lake-container`). Follow [the Synapse documentation](https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-delta-lake-format) and use the `OPENROWSET` function. If you created a data source in the [last step](/docs/get-started/self-hosted/quick-start/#configure-the-destination) of the quick start guide, your queries will be a bit simpler. > **Tip: Fabric and OneLake** > > If you created a OneLake shortcut in the [last step](/docs/get-started/self-hosted/quick-start/#configure-the-destination) of the quick start guide, you will be able to explore Snowplow data in Fabric, for example, using Spark SQL. *** ## Self-describing events [Self-describing events](/docs/fundamentals/events/#self-describing-events) can contain their own set of fields, defined by their [schema](/docs/fundamentals/schemas/). **Redshift:** For Redshift users, self-describing events are not part of the standard `events` table. Instead, each type of event is in its own table. The table name and the fields in the table will be determined by the event’s schema. See [how schemas translate to the warehouse](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/) for more details. You can query just the table for that particular self-describing event, if that's all that's required for your analysis, or join that table back to the `events` table: ```sql SELECT ... FROM . ev LEFT JOIN .my_example_event_table sde ON sde.root_id = ev.event_id AND sde.root_tstamp = ev.collector_tstamp ``` > **Note:** You may need to take care of [duplicate events](#dealing-with-duplicates). **BigQuery:** Each type of self-describing event is in a dedicated `RECORD`-type column. The column name and the fields in the record will be determined by the event’s schema. See [how schemas translate to the warehouse](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/) for more details. You can query fields in the self-describing event like so: ```sql SELECT ... unstruct_event_my_example_event_1.my_field, ... FROM ``` > **Note:** The column name produced by previous versions of the BigQuery Loader (<2.0.0) would contain full schema version, e.g. `unstruct_event_my_example_event_1_0_0`. The [BigQuery Loader upgrade guide](/docs/api-reference/loaders-storage-targets/bigquery-loader/upgrade-guides/2-0-0-upgrade-guide/) describes how to enable the legacy column names in the 2.0.0 loader. **Snowflake:** Each type of self-describing event is in a dedicated `OBJECT`-type column. The column name will be determined by the event’s schema. See [how schemas translate to the warehouse](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/) for more details. You can query fields in the self-describing event like so: ```sql SELECT ... unstruct_event_my_example_event_1:myField::varchar, -- field will be variant type so important to cast ... FROM ``` **Databricks, Spark SQL:** Each type of self-describing event is in a dedicated `STRUCT`-type column. The column name and the fields in the `STRUCT` will be determined by the event’s schema. See [how schemas translate to the warehouse](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/) for more details. You can query fields in the self-describing event by extracting them like so: ```sql SELECT ... unstruct_event_my_example_event_1.my_field, ... FROM ``` **Synapse Analytics:** Each type of self-describing event is in a dedicated column in JSON format. The column name will be determined by the event’s schema. See [how schemas translate to the warehouse](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/) for more details. You can query fields in the self-describing event like so: ```sql SELECT ... JSON_VALUE(unstruct_event_my_example_event_1, '$.my_field') ... FROM OPENROWSET(BULK 'events', DATA_SOURCE = '', FORMAT = 'DELTA') AS events ``` *** ## Entities [Entities](/docs/fundamentals/entities/) (also known as contexts) provide extra information about the event, such as data describing a product or a user. **Redshift:** For Redshift users, entities are not part of the standard `events` table. Instead, each type of entity is in its own table. The table name and the fields in the table will be determined by the entity’s schema. See [how schemas translate to the warehouse](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/) for more details. The entities can be joined back to the core `events` table by the following, which is a one-to-one join (for a single record entity) or a one-to-many join (for a multi-record entity), assuming no duplicates. ```sql SELECT ... FROM . ev LEFT JOIN -- assumes no duplicates, and will return all events regardless of if they have this entity .my_entity ent ON ent.root_id = ev.event_id AND ent.root_tstamp = ev.collector_tstamp ``` > **Note:** You may need to take care of [duplicate events](#dealing-with-duplicates). **BigQuery:** Each type of entity is in a dedicated `REPEATED RECORD`-type column. The column name and the fields in the record will be determined by the entity’s schema. See [how schemas translate to the warehouse](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/) for more details. You can query a single entity’s fields by extracting them like so: ```sql SELECT ... contexts_my_entity_1[SAFE_OFFSET(0)].my_field AS my_field, ... FROM ``` Alternatively, you can use the [`unnest`](https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#flattening_arrays) function to explode out the array into one row per entity value. ```sql SELECT ... my_ent.my_field AS my_field, ... FROM LEFT JOIN unnest(contexts_my_entity_1) AS my_ent -- left join to avoid discarding events without values in this entity ``` > **Note:** Column name produced by previous versions of the BigQuery Loader (<2.0.0) would contain full schema version, e.g. `contexts_my_entity_1_0_0`. **Snowflake:** Each type of entity is in a dedicated `ARRAY`-type column. The column name will be determined by the entity’s schema. See [how schemas translate to the warehouse](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/) for more details. You can query a single entity’s fields by extracting them like so: ```sql SELECT ... contexts_my_entity_1[0]:myField::varchar, -- field will be variant type so important to cast ... FROM ``` Alternatively, you can use the [`lateral flatten`](https://docs.snowflake.com/en/sql-reference/functions/flatten) function to explode out the array into one row per entity value. ```sql SELECT ... r.value:myField::varchar, -- field will be variant type so important to cast ... FROM AS t, LATERAL FLATTEN(input => t.contexts_my_entity_1) r ``` **Databricks, Spark SQL:** Each type of entity is in a dedicated `ARRAY`-type column. The column name and the fields in the `STRUCT` will be determined by the entity’s schema. See [how schemas translate to the warehouse](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/) for more details. You can query a single entity’s fields by extracting them like so: ```sql SELECT ... contexts_my_entity_1[0].my_field, ... FROM ``` Alternatively, you can use the [`LATERAL VIEW`](https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-select-lateral-view.html) clause combined with [`EXPLODE`](https://docs.databricks.com/sql/language-manual/functions/explode.html) to explode out the array into one row per entity value. ```sql SELECT ... my_ent.my_field, ... FROM LATERAL VIEW EXPLODE(contexts_my_entity_1) AS my_ent ``` **Synapse Analytics:** Each type of entity is in a dedicated column in JSON format. The column name will be determined by the entity’s schema. See [how schemas translate to the warehouse](/docs/api-reference/loaders-storage-targets/schemas-in-warehouse/) for more details. You can query a single entity’s fields by extracting them like so: ```sql SELECT ... JSON_VALUE(contexts_my_entity_1, '$[0].my_field') ... FROM OPENROWSET(BULK 'events', DATA_SOURCE = '', FORMAT = 'DELTA') AS events ``` Alternatively, you can use the [`CROSS APPLY` clause combined with `OPENJSON`](https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-nested-types#project-values-from-repeated-columns) to explode out the array into one row per entity value. ```sql SELECT ... JSON_VALUE(my_ent.[value], '$.my_field') ... FROM OPENROWSET(BULK 'events', DATA_SOURCE = '', FORMAT = 'DELTA') as events CROSS APPLY OPENJSON(contexts_my_entity_1) AS my_ent ``` *** ## Failed events Failed events aren't in the `events` table. To query them, [load failed events into a separate table](/docs/monitoring/exploring-failed-events/). The [failed events hub](/docs/failed-events/) lists every page about finding, monitoring, and recovering them. ## Dealing with duplicates In some cases, your data might contain duplicate events (full deduplication _before_ the data lands in the warehouse is optionally available for [Redshift, Snowflake and Databricks on AWS](/docs/api-reference/loaders-storage-targets/snowplow-rdb-loader/transforming-enriched-data/deduplication/)). While our [data models](/docs/modeling-your-data/modeling-your-data-with-dbt/) deal with duplicates for you, there may be cases where you need to de-duplicate the events table yourself. **Redshift:** In Redshift, you must first generate a `ROW_NUMBER()` on your events and use this to de-duplicate. ```sql WITH unique_events AS ( SELECT ... ROW_NUMBER() OVER (PARTITION BY a.event_id ORDER BY a.collector_tstamp) AS event_id_dedupe_index FROM a ) SELECT ... FROM unique_events WHERE event_id_dedupe_index = 1 ``` Things get a little more complicated if you want to join your event data with a table containing [entities](#entities). Suppose your entity is called `my_entity`. If you know that each of your events has at most 1 such entity attached, the de-duplication requires the use of a row number over `event_id` to get each unique event: ```sql WITH unique_events AS ( SELECT ev.*, ROW_NUMBER() OVER (PARTITION BY a.event_id ORDER BY a.collector_tstamp) AS event_id_dedupe_index FROM . ev ), unique_my_entity AS ( SELECT ent.*, ROW_NUMBER() OVER (PARTITION BY a.root_id ORDER BY a.root_tstamp) AS my_entity_index FROM .my_entity_1 ent ) SELECT ... FROM unique_events u_ev LEFT JOIN unique_my_entity u_ent ON u_ent.root_id = u_ev.event_id AND u_ent.root_tstamp = u_ev.collector_tstamp AND u_ent.my_entity_index = 1 WHERE u_ev.event_id_dedupe_index = 1 ``` If your events might have more than one `my_entity` attached, the logic is slightly more complex. **Details** First, de-duplicate the events table in the same way as above, but also keep track of the number of duplicates (see `event_id_dedupe_count` below). In the entity table, generate a row number per unique combination of _all_ fields in the record. Then join on `root_id` and `root_tstamp` as before, but with an _additional_ clause that the row number is a multiple of the number of duplicates, to support the 1-to-many join. This ensures all duplicates are removed while retaining all original records of the entity. This may look like a weird join condition, but it works. Unfortunately, listing all fields manually can be quite tedious, but we have added support for this in the [de-duplication logic](/docs/modeling-your-data/modeling-your-data-with-dbt/package-mechanics/deduplication/) of our dbt packages. ```sql WITH unique_events AS ( SELECT ev.*, ROW_NUMBER() OVER (PARTITION BY a.event_id ORDER BY a.collector_tstamp) AS event_id_dedupe_index, COUNT(*) OVER (PARTITION BY a.event_id) AS event_id_dedupe_count FROM . ev ), unique_my_entity AS ( SELECT ent.*, ROW_NUMBER() OVER (PARTITION BY a.root_id, a.root_tstamp, ... /*all columns listed here for your entity */ ORDER BY a.root_tstamp) AS my_entity_index FROM .my_entity_1 ent ) SELECT ... FROM unique_events u_ev LEFT JOIN unique_my_entity u_ent ON u_ent.root_id = u_ev.event_id AND u_ent.root_tstamp = u_ev.collector_tstamp AND mod(u_ent.my_entity_index, u_ev.event_id_dedupe_count) = 0 WHERE u_ev.event_id_dedupe_index = 1 ``` **BigQuery:** In BigQuery it is as simple as using a `QUALIFY` statement over your initial query: ```sql SELECT ... FROM a QUALIFY ROW_NUMBER() OVER (PARTITION BY a.event_id ORDER BY a.collector_tstamp) = 1 ``` **Snowflake:** In Snowflake it is as simple as using a `qualify` statement over your initial query: ```sql SELECT ... FROM a QUALIFY ROW_NUMBER() OVER (PARTITION BY a.event_id ORDER BY a.collector_tstamp) = 1 ``` **Databricks, Spark SQL:** In Databricks it is as simple as using a `qualify` statement over your initial query: ```sql SELECT ... FROM a QUALIFY ROW_NUMBER() OVER (PARTITION BY a.event_id ORDER BY a.collector_tstamp) = 1 ``` **Synapse Analytics:** In Synapse you must first generate a `ROW_NUMBER()` on your events and use this to de-duplicate. ```sql WITH unique_events AS ( SELECT ... ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY collector_tstamp) AS event_id_dedupe_index FROM OPENROWSET(BULK 'events', DATA_SOURCE = '', FORMAT = 'DELTA') AS events ) SELECT ... FROM unique_events WHERE event_id_dedupe_index = 1 ``` ***