Querying failed events in Postgres

If you use the Postgres Loader (not recommended for large volume production use cases), you can load your failed events into Postgres.

Each type of failed event is stored in its own table. You can get a full list of tables with the following query:

SELECT * FROM information_schema.tables
WHERE table_schema = 'badrows';

Database schema name

The example above uses badrows as the database schema name in Postgres. This will depend on how you’ve set up your loader. Typically, it’s badrows for Try Snowplow and atomic_bad for Open Source Quick Start.

We will use badrows throughout the rest of this page — feel free to substitute your own schema name.

For instance, to check the number of schema violations, you can query the respective table:

SELECT COUNT(*) FROM badrows.com_snowplowanalytics_snowplow_badrows_schema_violations_2;

Taking it further, you can check how many failed events you have by schema and error type:

SELECT
   "failure.messages"->0->'error'->'error' AS error,
   "failure.messages"->0->'schemaKey' AS schema,
   count(*) AS failed_events
FROM badrows.com_snowplowanalytics_snowplow_badrows_schema_violations_2
GROUP BY 1,2
ORDER BY 3 DESC