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 = 'atomic_bad';

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

SELECT COUNT(*) FROM atomic_bad.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 atomic_bad.com_snowplowanalytics_snowplow_badrows_schema_violations_2
GROUP BY 1,2
ORDER BY 3 DESC