1. Home
  2. Docs
  3. Snowplow Open Source Quick Start
  4. Quick Start Installation Guide on AWS
  5. Query your data

Query your data

If you were using the default or secure example scripts unedited in the last section, you will have created a Postgres database where all of your data is stored. Your Postgres database will contain the following standard Snowplow schemas:

  • atomic: this is your rich, high quality data
  • atomic_bad: this is the data that has failed pipeline validation
Step 1. Querying your good data in Postgres

To query the good data in atomic.events, you will first you need to connect to your Postgres database.

  • Connect to the database using the username and password you provided when creating the pipeline, along with the db_address and db_port you noted down after the pipeline was created.
    • If you need to reset your username or password you can follow these steps
    • If your Postgres RDS was configured to be publically accessible, there are a number of tools you can use to connect to a Postgres database from your local machine:
  • Run a query against your atomic.events table to take a look at the page view event that you generated in the previous step (where event_name = ‘page_view’). You can understand more about each field in the canonical event here.
    • SELECT * FROM atomic.events WHERE event_name = 'page_view';

By default, there are 5 enrichments enabled, as listed below. These enrichments add extra properties and values to your events in real time as they are being processed by the Enrich application.

Some enrichments are legacy and therefore populate your atomic.events table. From the above list, these are the campaign attribution, referer parser and event fingerprint enrichments. The UA parser and YAUAA enrichment also add a separate entity to each event (these are also referred to as contexts since they add additional context to the events in your atomic.events table). The contexts are loaded into separate tables: 

  • atomic.com_snowplowanalytics_snowplow_ua_parser_context_1
  • atomic.nl_basjes_yauaa_context_1

Note: you can join these contexts back to your atomic.events using root_id = event_id.

Step 2. Querying your bad data in Postgres

Your atomic_bad schema holds events that have failed to be processed by your pipeline. These are called failed events.

You will see in Postgres that you have a table called atomic_bad.com_snowplowanalytics_snowplow_badrows_schema_violation_1.

In the last section, we sent a test event that would fail to be processed by your pipeline (specifically one that fails to validate against a schema). This is a fundamental aspect of Snowplow; ensuring that only good quality data reaches your stream, lake and warehouse and syphoning off poor quality data so that you have the ability to correct and recover it. 

As the custom product_view event passed through your pipeline, the Enrich application fetches the schema for the event. It does this so it can validate that the structure of the event conforms to what was defined up front, therefore ensuring it is of the quality expected.  Since the schema for the product_view event doesn’t yet exist in your Iglu schema registry, the event failed to validate and landed in atomic.bad.

In the next section, we guide you through creating a custom schema so that your custom event would validate against it and not become a failed event. 

Schemas

Learn more about self-describing events and schemas, and the different types of failures here.

Note: you might also see adapter failure failed events in Postgres. Many adaptor failures are caused by bot traffic, so do not be surprised to see some of them in your pipeline. Find out more here.

Step 3: Querying your data on S3

S3 provides an important backup of your data and can also serve as your data lake. 

  • Navigate to the AWS management console, search for S3 and select 
  • If you have multiple buckets on S3 already, you can navigate to the correct one by searching for the s3 bucket name that you entered when spinning up your pipeline

When you created your pipeline you also created three directories in your S3 bucket: 

This image has an empty alt attribute; its file name is 0Po29TzPLmqccxJBUrqhdzE3h1_pGQt4cnoSXMz8jrOdfVqStRt6vOSXTRJ7QbP9UX_wp-wDevdtVANkHrVLZiOPpU4nsMKTHp8TdNDSGHew3XaGwSPI-1CJOO1zCHs0mwJ_QzDh

The enriched/ and bad/ directory holds your enriched data, and the data that has failed to be validated by your pipeline. We took a look at this data in Postgres in the last step.  

The raw/ directory holds the events that come straight out of your collector and have not yet been validated (i.e. quality checked) or enriched by the Enrich application. They are thrift records and are therefore a little tricky to decode –  there are not many reasons to use this data, but backing this data up gives you the flexibility to replay this data should something go wrong further downstream in the pipeline. 

Athena

You can learn more about querying your data at scale on S3 using Athena here.