Set an audit goal before choosing a subscriber
A small museum may publish workshop check-ins, ticket scanner events, and accessibility feedback into Pub/Sub. Its analytics staff want a queryable history in BigQuery, not another custom subscriber process to maintain. Google documents a BigQuery subscription as an export subscription that writes published messages to an existing BigQuery table. That removes a separate client from a straightforward pipeline, but it does not decide which messages represent a unique attendance or whether a visitor gave permission for a use of their data. Start with one narrow event type and a data-minimization plan. This is a fictional museum workflow, not a claim that a particular dataset has been ingested or tested.
Pick a table and an explicit schema strategy
Create or identify the destination BigQuery table, then decide how incoming fields will map into it. With Use topic schema, Pub/Sub uses the topic’s Avro or Protocol Buffer schema and matches compatible table columns. With Use table schema, it expects JSON messages with compatible BigQuery columns. Without either setting, the table needs a suitable data column for the raw message payload. The two schema modes are alternatives, not switches to enable simultaneously. Keep the public event type, timestamp, and nonpersonal deduplication key distinct. Do not populate a database field called ‘verified’ merely because a value passed type validation; that would confuse syntactic compatibility with an approved real-world event.
Configure the export subscription with narrow permissions
Follow Google’s documented create-subscription flow using the console, CLI, client library, or API. Give the subscription’s service account the appropriate destination write permissions, limiting the scope to its actual dataset wherever feasible. A schema-compatible table and topic should be prepared before activation. If the organization has policies limiting visitor data collection, filter or transform events upstream rather than exporting entire records and trying to erase sensitive columns afterward. Record the topic, subscription, destination table, schema revision, and service-account ownership in an internal deployment note. A successful creation response confirms configuration, not that every subsequent message will be written without duplication.
Understand why two rows can represent one message
The official BigQuery subscription overview explicitly describes at-least-once delivery. When a write fails, Pub/Sub can resend the message, and duplicates can occur; the simple export path is not an exactly-once analytics ledger. This matters when a museum measures workshop attendance or a volunteer’s completed hours. Create a stable event ID before publication, then use a documented warehouse deduplication query or downstream process when interpreting unique events. Store arrival and publish timestamps separately if they matter to the analysis. Never delete a row solely because it shares a visitor name: names are neither reliable identifiers nor acceptable substitutes for an event-specific key.
Separate accepted messages from schema failures
A valid publish operation and a successful BigQuery write are different stages. A message can be accepted by Pub/Sub but rejected by the destination because a field is malformed, a required table mapping is incompatible, or a permissions issue occurs. Google recommends dead lettering for messages that cannot be delivered and documents an error-reason attribute for dead-lettered BigQuery subscription messages. Build a small failure-monitoring view that shows the type of incompatibility and the number of unresolved messages, without echoing private event payloads. Staff should investigate the table schema and source event contract before replaying anything. An empty dashboard may mean no traffic, a delivery failure, or a broken report.
Choose when a transform or Dataflow pipeline is necessary
BigQuery subscriptions are useful when event payloads need little transformation. Google notes that a Single Message Transform may be attached for lightweight modifications, while more complex windowing or aggregation is better suited to a separate Dataflow pipeline. Do not force enrichment, privacy redaction, and business reconciliation into an invisible SQL expression at the reporting stage. If events require joining ticket IDs to staff authorizations or comparing revisions, plan those controls as a distinct downstream process. Keep original event sources identifiable while controlling who can access them. The architectural choice should be made from transformation requirements, latency needs, permission boundaries, and the cost of maintaining another service.
Design a comparison that shows real delivery states
A useful diagram has three separate pictures: a publish event waiting for export, a BigQuery write that may be retried, and a reviewed analytics view that deduplicates by stable event ID. The third state must not be labeled a direct output guarantee of Pub/Sub. In the hypothetical museum, two identical event IDs in the raw table might be counted once in a curated attendance report. That is an analytic rule maintained by the reporting team, not proof that the warehouse contains exactly one physical row. Label raw ingestion, transformation, and human-approved reporting distinctly so reviewers can identify which step generated any apparent discrepancy.
Audit schema changes before modifying live feeds
Set up a staging topic and a test table containing fictional check-ins, optional fields, wrong data types, and old schema revisions. Confirm the compatibility rules documented for the selected schema mode, including nullable columns and additional fields. The documentation warns that deleting schema revisions can permanently affect delivery for messages validated against them. Instead of casually removing an old revision, prepare a backward-compatible change or an explicit migration window. Observe the subscription’s delivery and dead-letter behavior in staging. Log the configuration version used for every synthetic trial and check that an event with an invalid timestamp does not turn into a plausible but false date in the analytics report.
Give analysts a reconciliation checklist
Before publishing an attendance number, compare the event volume received, the number of unique event IDs, unresolved failed deliveries, and any corrections from the ticketing system. Use a query that distinguishes duplicate rows from actual multiple check-ins by the same visitor. Explain late-arriving events when reporting a daily snapshot. Restrict access to raw visitor activity and publish aggregates that do not reveal individuals. A green export subscription status is necessary for operations but not sufficient for a trustworthy public metric. If a dead-letter backlog or schema change remains unresolved, label the report as provisional instead of treating missing records as evidence that no one attended.
Measure pipeline health without invented success rates
Keep structured indicators for successful destination writes, dead-letter counts, schema version drift, and analytics lag. Compare these against a real operational baseline only after collecting legitimate measurements; this guide does not provide a benchmark. Document a restart process that avoids indiscriminate republishing, because replayed messages can appear as new physical rows. Verify that changing the destination table or service-account permissions has not opened a wider data-access path. For policy decisions, the museum should rely on a reviewed source-of-truth attendance register rather than a raw stream that is optimized for reliable delivery, not final legal or financial accounting.
How We Research
We reviewed the named official developer references Google Cloud: BigQuery subscriptions overview, Google Cloud: Create BigQuery subscriptions. We compared their documented request and delivery behavior, accepted configuration patterns, and explicit limitations with the proposed hypothetical museum workflow. This article is a source-based implementation guide: we did not execute live customer API calls, inspect private cloud accounts, run production tests, or measure latency, cost, or accuracy. Readers should verify current limits and deployment settings with the original documentation and run their own controlled staging tests before changing public systems.
Frequently Asked Questions
No. Google describes at-least-once delivery for this subscription type. Use stable event IDs and downstream deduplication for unique-event reporting.
Not for the straightforward export subscription path; Pub/Sub writes directly to the BigQuery table. More complex transformations may warrant Dataflow.
No. They are alternative schema configuration modes. Choose one and verify compatibility with your table.
Writes may fail and messages may be retried. Configure monitoring and a dead-letter topic to investigate undeliverable events.
Only after reconciliation with event IDs, late arrivals, delivery failures, and the institution’s approved attendance definitions.