Issue testing PubSub to BigQuery deadletter settings

[gcp-fun-learner]

Hello! I am working with PubSub and BigQuery and was trying to test out deadletter settings to ensure messages are published to deadletter queue since I need proof of it working. At the moment, I have the following configuration, all done manually for the moment through gcp console.

In BigQuery, I have a dataset with a table called "learningTable".

In PubSub I have a topic called "mainTopic", and a topic called "deadletterTopic". Both of these have the default pull subscriptions. In mainTopic, I have created another subscription called "BigQueryTest-sub" with the configations as follows:

• Delivery Type: Write to BigQuery, Use Topic Schema
• Message retention duration: 10 mins
• Ack deadline: 10 seconds
• Retry policy: Retry immediately
• Dead lettering: deadLetterTopic, and max delivery attempts of 5.

After creating this subscription, I went to the dead lettering tab and granted role permissions for publisher and subscriber roles. Everything shows up blue. When I manually publish messages to my mainTopic, I can see them populating in BigQuery, and I see them in my default subscriber. Now to test if the deadletter is working, I deleted my learningTable, and published some messages. I saw the messages in my default subscription, and in my BigQueryTest-sub I saw the unacked messages and oldest unacked message age graphs go up. So there were messages not sent. However, once I go to the dead lettering tab, I see nothing. And when I check the deadLetterTopic, there is nothing published to it either.

I believe I have this setup correctly because I went through and made a push subscription as well, with an endpoint that wouldn't work. When I did this (and went through the same relevant steps as above), the messages would show up in the dead letter queue.

To complicate things more, I sent some 16 messages through the gcp console for the mainTopic while the table in BigQuery was deleted. Of those 16, one of them made it through to the dead letter queue, but the other 15 or so were shown as being unacked until my retention window was down.

Maybe I'm bad at googling, but I was not finding anything on this topic. Are we unable to test BigQuery subscriptions and dead letter queues in the way I am testing it?

I appreciate any help you may give, thank you.

[ms4446 · Accepted solution]

Testing dead lettering in this context can indeed be complex, and there are several factors to consider that might affect the behavior of messages in the dead letter queue:

1. BigQuery Deletion Semantics: When you delete a BigQuery table, Pub/Sub's recognition of this deletion as a trigger for dead lettering can vary. If Pub/Sub detects the table as unavailable, it should ideally send messages to the dead letter queue after the specified number of delivery attempts. However, this detection might not be immediate, affecting the dead lettering process.

2. Delivery Attempts and Retention: With your configuration of "retry immediately" and a maximum of 5 delivery attempts within a 10-minute retention period, messages might be retried quickly. If the BigQuery table becomes available again within this window, the messages could be successfully delivered, bypassing the dead letter queue. If the table remains unavailable and delivery attempts are exhausted, the messages should be moved to the dead letter queue.

3. Visibility of Dead-lettered Messages: It's important to note that dead-lettered messages are not visible in the dead lettering tab of the subscription. They are sent to the specified dead letter topic. To view these messages, you need to create a subscription to the dead letter topic.

To enhance your testing of dead lettering with BigQuery subscriptions, consider the following approaches:

• Inject Errors: Modify the message payload or the BigQuery table schema to intentionally create a mismatch or error during message processing. This controlled approach can reliably trigger dead lettering.

• Use a Dedicated Test Table: Create a separate BigQuery table specifically for testing. This allows you to experiment with deleting and recreating the table without impacting your actual data.

• Simulate Unavailability: Simulating the unavailability of BigQuery, either by disabling the instance or altering network configurations, can mimic conditions where Pub/Sub cannot deliver messages, thus triggering dead lettering after retry attempts fail.

Additionally, leveraging Google Cloud's monitoring and logging tools can provide deeper insights into the message delivery process and help identify why certain messages are not being dead-lettered as expected.

Here are some resources that might help you understand more about testing dead letter queues with BigQuery subscriptions in Google Cloud Pub/Sub:

1. Handle message failures | Cloud Pub/Sub | Google Cloud: This page explains how to handle processing failures using a subscription retry policy or by forwarding undelivered messages to a dead-letter topic.

2. Troubleshooting BigQuery subscriptions | Cloud Pub/Sub | Google Cloud: This resource provides steps to check the state of a subscription and troubleshoot common issues with BigQuery subscriptions in Pub/Sub.

3. Pub/Sub: How to retrieve messages sent to a dead letter topic - Stack Overflow: A discussion on Stack Overflow about how to retrieve messages sent to a dead-letter topic and store them in BigQuery.

4. Monitor Pub/Sub in Cloud Monitoring - Google Cloud: This page guides you on how to monitor your Pub/Sub project using Cloud Monitoring, which can be helpful in understanding the behavior of your subscriptions and dead letter queues.

5. Create subscription with DeadLetterPolicy | Cloud Pub/Sub | Google Cloud: This resource provides a code sample for creating a subscription that forwards undeliverable messages to a dead-letter topic.

These links should provide a good starting point for understanding and troubleshooting your Pub/Sub and BigQuery integration, especially in the context of dead letter queues.

[gcp-fun-learner]

Hi thank you for the reply, this seems to work for my needs 🙂