Reason for bq bad events topic

For engineers Storage targets
1

Hi,

We have set up the snowplow pipeline and is running fine without any issues. However, we have noticed that a valid message is getting inserted into bigquery bad pubsub topic. So, we would like to know the list of reasons for bq bad events. What type of events doesn’t get processed by bq loader? It would be great if you could provide list of valid reasons.

Thanks

2

Failed events can be generated for a whole number of reasons. You’ll want to start with Understanding failed events.

Then I’d suggest running the GCS Loader, to load your bad pubsub topic to GCS where you can then better assess your failed events.

3

Hi @PaulBoocock ,

Thanks for the quick response. I think we have three topics in bq loader config. typesTopic, badRows, and failedInserts. We understand what failedinserts are. But, we would like to understand when badRows are filed.

Just mentioning the config here,

    "input": "enriched-good-sub",
    "typesTopic": "bq-test-types",
    "typesSubscription": "bq-test-types-sub",
    "badRows": "bq-test-bad-rows",
    "failedInserts": "bq-test-bad-inserts"

Thank you.

4

Hey @Hanumanth,

One possible reason is invalid data coming from PubSub (that cannot be parsed into enriched event), which never happens unless some deployment mistake. Another slightly more common case for bad rows in BQ Loader is unavailable Iglu registry, because the Loader needs a schema to transform JSONs (contexts and self-describing events) into BigQuery format (e.g. to understand that a string with format: date-string is actually a TIMESTAMP and not a plain string).

Overall, bad rows coming from a BigQuery Loader is very rare occasion. So rare that I’d say most users never see them. But at the same time, from architecture point of view we cannot remove it because it means that if one of above scenarios happen, the data will be siliently dropped.

We’re trying to keep Snowplow pipeline as lossless as possible and as a result we prefer to keep a topic for bad data even if the chance of generating this bad data is abysmall.

5

Hi @anton ,

Thank you for the clarification. I was wondering that even iglu registry is available, the message was processed by loader but failed at validation, and it was sent to a bq bad pubsub topic. Mentioning the message

Output from Enricher,

pc	2021-08-24 11:36:42.443	2021-08-24 11:36:40.613	2021-08-24 11:36:40.470	unstruct	090eb5b1-7878-4b20-8722-a5cf5294d6e4			py-0.9.0	ssc-2.3.0-googlepubsub	beam-enrich-2.0.1-common-2.0.1	web-tracker	124.40.247.196				1f5d4046-b6f7-4e42-93fa-f12db7a3e7af																																									{"schema":"iglu:com.snowplowanalytics.snowplow/unstruct_event/jsonschema/1-0-0","data":{"schema":"iglu:com.mcd_japan/user_details/jsonschema/2-0-0","data":{"user_id":"jeff","age":56,"gender":"male","marital_status":false}}}																			python-requests/2.26.0						en																																				2021-08-24 11:36:40.000					2021-08-24 11:36:40.613	com.mcd_japan	user_details	jsonschema	2-0-0

Message in bq bad pubsub topic:

{"schema":"iglu:com.snowplowanalytics.snowplow.badrows/loader_parsing_error/jsonschema/2-0-0","data":{"processor":{"artifact":"snowplow-bigquery-loader","version":"0.6.4"},"failure":{"type":"FieldNumberMismatch","fieldCount":128},"payload":"pc\t2021-08-24 11:36:42.443\t2021-08-24 11:36:40.613\t2021-08-24 11:36:40.470\tunstruct\t090eb5b1-7878-4b20-8722-a5cf5294d6e4\t\t\tpy-0.9.0\tssc-2.3.0-googlepubsub\tbeam-enrich-2.0.1-common-2.0.1\tweb-tracker\t124.40.247.196\t\t\t\t1f5d4046-b6f7-4e42-93fa-f12db7a3e7af\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t{\"schema\":\"iglu:com.snowplowanalytics.snowplow/unstruct_event/jsonschema/1-0-0\",\"data\":{\"schema\":\"iglu:com.mcd_japan/user_details/jsonschema/2-0-0\",\"data\":{\"user_id\":\"jeff\",\"age\":56,\"gender\":\"male\",\"marital_status\":false}}}\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tpython-requests/2.26.0\t\t\t\t\t\ten\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t2021-08-24 11:36:40.000\t\t\t\t\t2021-08-24 11:36:40.613\tcom.mcd_japan\tuser_details\tjsonschema\t2-0-0"}}

Could you please help me understand this situation if you have an idea? Thank you.

6

By any chance, is there some other software sitting between Beam Enrich and BigQuery Loader?

This event has likely been sent not by Beam Enrich into enriched topic. As the error indicates the TSV line misses several columns that necessary for a Snowplow enriched event, it has 128 columns instead of expected 131. Namely, it misses:

  • app_id - first column, before pc
  • event_fingerprint a hash after your last column (2-0-0)
  • true_stamp - a timestamp at the very end of the TSV

All these columns can be empty (just \t), but they must be there. It seems something has trimmed these columns as they were empty in the original line produced by Beam Enrich.

7

Hi @anton ,

Thank you for clarification, It was working fine after formatting the TSV. Understood that snowplow gives importance to the format of the event. We were under impression that if the corresponding entity was not available then the loader loads the event as null. Thank you for the clarification.