5.3 Pub/Sub BigQuery/Cloud Storage Direct Subscriptions and Dead-Letter Topics

5.3 Pub/Sub BigQuery/Cloud Storage Direct Subscriptions and Dead-Letter Topics

Exam Focus: The Google Cloud Professional Data Engineer exam expects you to recognize when to replace complex streaming ETL pipelines (Cloud Dataflow or Cloud Functions) with zero-compute Direct Subscriptions to BigQuery and Cloud Storage. You must master schema mapping flags (use_topic_schema, write_metadata, drop_unknown_fields), understand Cloud Storage batching triggers (max_duration, max_bytes), configure Dead-Letter Topics (DLQ), and assign the exact IAM permissions required by the Pub/Sub service agent.

Historically, landing real-time streaming data from Cloud Pub/Sub into analytical data stores required deploying intermediate compute infrastructure. Engineers had to provision, monitor, and scale Apache Beam pipelines on Cloud Dataflow or maintain dozens of event-driven Cloud Functions simply to deserialize JSON payloads and stream them into BigQuery or write them as files into Cloud Storage. With the introduction of Pub/Sub Direct Subscriptions, Google Cloud eliminated intermediate compute for straightforward ingestion, providing serverless, zero-maintenance streaming pipelines at wire speed.

---

1. BigQuery Direct Subscriptions: Serverless, Zero-Pipeline Ingestion

A BigQuery Direct Subscription writes messages directly from a Cloud Pub/Sub topic into an existing BigQuery table without requiring a Dataflow worker pool or serverless functions.

[ Publishers ] ──> [ Pub/Sub Topic ] ──(Direct Subscription)──> [ BigQuery Table ]
                                              │
                                     (Under the Hood)
                                              ▼
                              [ BigQuery Storage Write API ]
                              (Zero VM Compute, Sub-Second Latency)

How BigQuery Direct Subscriptions Operate

Under the hood, Cloud Pub/Sub integrates directly with the BigQuery Storage Write API using committed streams. Pub/Sub handles all connection lifecycle management, batching, auto-scaling, and backoff retries internally:

• Cost Efficiency: Customers pay only for standard Pub/Sub delivery fees and BigQuery Storage Write API volume ingestion charges ($0.025 per GB, with the first 2 TB per month free). There are zero virtual machine, memory, or worker compute costs.
• Latency: Direct subscriptions achieve sub-second delivery latency from message publication to table queryability.

Key Configuration Parameters

When provisioning a BigQuery direct subscription, data engineers configure three critical behaviors:

gcloud pubsub subscriptions create bq-direct-sub \
    --topic=telemetry-topic \
    --bigquery-table=my-project:analytics_lake.sensor_telemetry \
    --use-topic-schema \
    --write-metadata \
    --drop-unknown-fields

1. use_topic_schema:

   • When enabled, Pub/Sub parses message payloads using the schema registered on the topic (Avro or Protocol Buffers) and maps fields directly to corresponding BigQuery columns with matching names and compatible data types.
   • If disabled (or if the topic has no schema), Pub/Sub writes the raw, unparsed message payload into a designated string or JSON column named data in the destination table.

2. write_metadata:

   • Automatically injects system metadata columns into the destination BigQuery table alongside message fields. The BigQuery table schema must define these exact column names:
     • subscription_name (STRING): Fully qualified resource name of the subscription.
     • message_id (STRING): Unique identifier assigned by Pub/Sub.
     • publish_time (TIMESTAMP): Time the message was committed to Pub/Sub storage.
     • attributes (JSON or STRING): Key-value dictionary of user-defined message attributes.
     • ordering_key (STRING): Present if message ordering was used.
   • Architecture Value: Essential for data lineage, auditing, partition clustering, and downstream deduplication queries.

3. drop_unknown_fields:

   • Dictates how Pub/Sub handles schema evolution when upstream producers send records containing fields not present in the BigQuery table schema.
   • If true: Pub/Sub automatically ignores and drops any unrecognized fields, successfully writing all matching fields into BigQuery.
   • If false: Pub/Sub rejects the message write operation because the record does not conform to the BigQuery table schema. The message remains unacknowledged and will either trigger delivery retries or be routed to a Dead-Letter Topic.

Mandatory IAM Permissions for BigQuery Direct Ingestion

Direct subscriptions do not run under user credentials; they execute under the Google-managed Cloud Pub/Sub Service Agent: service-[PROJECT_NUMBER]@gcp-sa-pubsub.iam.gserviceaccount.com

To write data into BigQuery, the service agent must be granted the following IAM roles:

• roles/bigquery.dataEditor on the destination BigQuery table or dataset (grants data plane write permissions via Storage Write API).
• roles/bigquery.metadataViewer on the dataset or project (allows Pub/Sub to inspect table schema and verify column mapping).

---

2. Cloud Storage Direct Subscriptions: Direct Batch File Ingestion

A Cloud Storage Direct Subscription automatically streams and batches messages directly into Google Cloud Storage buckets without spinning up compute workers. This pattern is ideal for landing raw data lake records, creating cold compliance archives, and feeding batch analytical engines like Dataproc or BigLake.

Output Formats

Cloud Storage direct subscriptions support two file formats:

1. Text (Plaintext / Newline-Delimited JSON): Each message payload is written on a new line. Messages can be stored with or without trailing newlines.
2. Avro (Binary Container Format): Messages are packaged into structured Avro files containing schema definitions and metadata. Avro files support block-level compression (such as Snappy or Deflate) and are splittable across distributed queries in BigLake, Apache Spark, and Presto/Trino.

Batching Flush Triggers

Writing individual 1 KB files to Cloud Storage for every incoming message would cause extreme file fragmentation, overwhelming Cloud Storage metadata operations and creating the dreaded "small file problem" for downstream analytical engines. To prevent this, Cloud Storage direct subscriptions buffer incoming messages in memory and flush them to a new file whenever any one of three configurable triggers is satisfied:

+----------------------------------------------------------------------------------+
|               CLOUD STORAGE DIRECT SUBSCRIPTION FLUSH TRIGGERS                  |
+----------------------------------------------------------------------------------+
|                                                                                  |
|  Incoming Streaming Messages ──> [ In-Memory Buffer ]                            |
|                                          │                                       |
|         ┌────────────────────────────────┼────────────────────────────────┐      |
|         ▼                                ▼                                ▼      |
|  Condition 1:                     Condition 2:                     Condition 3:  |
|  max_duration reached             max_bytes reached                max_messages  |
|  (e.g., 5 minutes)                (e.g., 1 GiB)                    reached       |
|         │                                │                                │      |
|         └────────────────────────────────┼────────────────────────────────┘      |
|                                          ▼                                       |
|                    Flushes Buffer -> Commits New File to GCS                     |
|                    gs://data-lake-raw/events/2026/09/15/file_uuid.avro           |
+----------------------------------------------------------------------------------+

• max_duration: The maximum amount of time Pub/Sub buffers data before writing a file (configurable from 1 minute to 10 minutes, default: 5 minutes).
• max_bytes: The maximum accumulated size of buffered messages before flushing (configurable from 1 MiB to 10 GiB, default: 1 GiB).
• max_messages: The maximum message count allowed in a single file (configurable up to 100,000+ messages).

Customizing Object Naming and Partitioning

Direct subscriptions allow configuring dynamic directory structures and file extensions:

• filename_prefix: Static directory prefix (e.g., telemetry/raw/).
• filename_suffix: File extension (e.g., .avro, .json).
• filename_datetime_format: Formats directory hierarchies based on current UTC time (e.g., YYYY/MM/DD/hh/mm/). This enables automatic Hive-compatible directory partitioning (gs://bucket/telemetry/raw/2026/09/15/12/events_abc.avro) that external query engines can prune efficiently.

IAM Requirements for Cloud Storage Direct Subscriptions

The Pub/Sub Service Agent (service-[PROJECT_NUMBER]@gcp-sa-pubsub.iam.gserviceaccount.com) must possess:

• roles/storage.objectCreator (or roles/storage.admin) on the destination bucket.

---

3. Architectural Decision Matrix: Direct Subscriptions vs. Dataflow vs. Cloud Functions

---

4. Dead-Letter Topics (DLQ) Mechanics and Architecture

A Dead-Letter Topic (DLQ) is a secondary Pub/Sub topic designated to capture undeliverable or repeatedly failing messages (often called poison pills).

Why Dead-Letter Topics Are Essential

In streaming pipelines, unparseable messages can cause catastrophic failures:

• In Push Subscriptions, a malformed message causes the webhook endpoint to return HTTP 500, triggering infinite retries that consume CPU and swamp the service.
• In Ordered Subscriptions, a failed message causes Head-of-Line blocking, freezing processing for that ordering key.
• In BigQuery Direct Subscriptions, a message that violates data types (e.g., passing a string into an integer column) will fail write operations indefinitely.

┌──────────────────────────────────────── Primary Subscription ────────────────────────────────────────┐
│                                                                                                     │
│  Incoming Messages ──> [ Attempt 1: Failed ] ──> [ Attempt 2: Failed ] ... ──> [ Attempt 5: Failed ] │
└──────────────────────────────────────────────────────────────────────────────────┬──────────────────┘
                                                                                   │
                                                                    (Forward to DLQ & ACK Primary)
                                                                                   ▼
┌─────────────────────────────────────── Dead-Letter Topic ───────────────────────────────────────────┐
│                                                                                                     │
│  [ Captured Poison-Pill Message ] ──> Preserves Payload + CloudPubSubDeadLetterSourceDeliveryAttempt │
│                                              │                                                      │
│                                     ┌────────┴────────┐                                             │
│                                     ▼                 ▼                                             │
│                             [ Cloud Logging Alert ] [ Quarantine BQ Table ]                         │
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘

How Dead-Letter Queuing Works

1. Threshold Configuration (maxDeliveryAttempts): The data engineer configures a Dead-Letter Topic and sets maxDeliveryAttempts (an integer between 5 and 100, default: 5):

   gcloud pubsub subscriptions create main-orders-sub \
       --topic=orders-topic \
       --dead-letter-topic=orders-dlq-topic \
       --max-delivery-attempts=5
   

2. Delivery Attempt Counter: Pub/Sub increments an internal counter (delivery_attempt) every time it attempts delivery to a subscriber and the delivery fails (due to ack deadline expiration, explicit nack, or push endpoint error).
3. Forwarding to DLQ: When the delivery attempt count reaches maxDeliveryAttempts, Pub/Sub automatically:
   • Publishes the failed message to the configured Dead-Letter Topic.
   • Injects a system attribute: CloudPubSubDeadLetterSourceDeliveryAttempt indicating total failed delivery attempts.
   • Acknowledges the message on the primary subscription, removing it from the active delivery queue and immediately resolving any Head-of-Line block.

Mandatory IAM Permissions for Dead-Letter Topics

For Pub/Sub to divert failing messages, the Pub/Sub Service Agent (service-[PROJECT_NUMBER]@gcp-sa-pubsub.iam.gserviceaccount.com) must possess two specific IAM permissions:

1. roles/pubsub.publisher on the Dead-Letter Topic (to publish the evicted message).
2. roles/pubsub.subscriber on the Primary Subscription (to acknowledge and evict the failing message).

Exam Trap — Silent DLQ Failure: If you configure a Dead-Letter Topic but fail to grant roles/pubsub.publisher and roles/pubsub.subscriber to the Pub/Sub Service Agent, Pub/Sub cannot forward the message. Instead, the message will continue to be redelivered to consumers indefinitely, exceeding maxDeliveryAttempts without ever routing to the DLQ.

---

5. DLQ Operations, Cloud Monitoring, and Poison-Pill Remediation

Isolating poison-pill messages in a Dead-Letter Topic is only the first step. Production architectures must monitor, alert on, and remediate failures:

1. Cloud Monitoring Alerting Policies:

   • Monitor the metric pubsub.googleapis.com/subscription/dead_letter_message_count.
   • Configure an alerting policy in Cloud Monitoring that triggers an urgent PagerDuty or Slack notification whenever the publish rate to the Dead-Letter Topic is greater than zero.
   • Monitor pubsub.googleapis.com/subscription/oldest_unacked_message_age to detect consumer lag across both primary and DLQ subscriptions.

2. Triage and Quarantine Sink:

   • Attach a BigQuery direct subscription to the Dead-Letter Topic, writing failed records into a quarantine_errors table.
   • Engineers inspect the quarantine_errors table to analyze payload discrepancies, schema incompatibilities, or frontend client bugs.

3. Re-Ingestion Workflow:

   • Once the application bug or schema definition is fixed, a recovery script or Dataflow job reads messages from the quarantine table or DLQ subscription, applies necessary transformations, and republishes the sanitized records back to the primary topic for reprocessing.

---

6. Realistic Exam Scenarios & Architecture Pitfalls