PostgreSQL to BigQuery: validate an enriched subscription table.

“Postgres to BigQuery” sounds like a connector request. Usually it is a launch request. A team wants subscriptions, invoices, usage, or accounts to arrive in a table that finance, product, and operations can trust without first reconstructing the application.

The connector is necessary. The decisions between the two systems determine whether the result is a useful data product or another raw dump.

The hard part is not copying Postgres into BigQuery. It is launching the right table: one whose fields, reference context, write mode, and recovery rules your team can explain before it starts.

This example builds an enriched event table, not a live copy with one current row per subscription. An UPDATE appends another event; it does not replace the earlier row. If you need current subscription state, plan a downstream model that resolves event identity and ordering. The recipe below prepares the events that model will consume.

A green sync can still produce the wrong table

PostgreSQL tables are designed around application ownership, transactions, and normalized relationships. BigQuery tables are consumed across teams. That difference is why copying columns is often the easy part.

A raw mirror has real value when traceability, replay, or forensic analysis matters. An analytics-ready landing table has a different job: use destination language, carry the reference context people repeatedly need, and protect a stable contract from accidental source drift. Trouble starts when a team expects one and silently builds the other.

A successful transfer proves that rows moved. It does not prove that the table is ready to become shared truth.

Example: enrich a subscription before it reaches BigQuery

A subscription row can be perfectly correct and still make every consumer repeat the same joins. The application needs compact identifiers. The people reading the warehouse need to understand the account, plan, cadence, and renewal without learning three service schemas first.

{
  "id": "sub_873",
  "account_id": 501,
  "plan_id": "growth_annual",
  "status": "active",
  "renewal_at": "2026-10-01"
}

{
  "subscription_id": "sub_873",
  "account_name": "Acme Corp",
  "account_segment": "mid-market",
  "plan_name": "Growth Annual",
  "billing_period": "annual",
  "renewal_date": "2026-10-01"
}

This is not an aggregate or a replacement for warehouse modeling. It is a landing contract: the source fact remains recognizable, while the fields required to use it stop being tribal knowledge.

Here is the reference context behind that output. These synthetic PostgreSQL tables each have a unique id. The rows and metadata in this article illustrate the contract; they are not executable API requests or resources provisioned by the article.

{
  "account_reference": [{
    "id": 501,
    "account_name": "Acme Corp",
    "account_segment": "mid-market"
  }],
  "plan_reference": [{
    "id": "growth_annual",
    "plan_name": "Growth Annual",
    "billing_period": "annual"
  }]
}

Before you configure the example

You need a JoinLayer workspace, saved PostgreSQL and BigQuery connections, and read access to subscriptions and both reference tables. The BigQuery project, dataset and service-account permissions must already be in place. Have a workspace Admin or Owner review connection setup and testing, then use Pipeline Setup to save the source-to-target route. Reuse a suitable existing draft instead of creating a duplicate.

The target for this example is an append-event table such as subscriptions_events. Review its schema before applying any table changes; saving a draft does not create the destination or start execution. For CDC, confirm logical-replication access and retention separately. If you also need history, read the launch checklist before authorizing the separate load and realtime steps.

With the route saved, review this recipe with the operator:

1. Set pipeline error_policy=stop before using the required lookups below. A lookup's fail policy identifies a bad record; the pipeline policy determines whether that error stops processing or the runner skips the record.
2. Read subscriptions, using source id as stable identity. Choose an already-provisioned BigQuery dataset and an append-event table such as subscriptions_events. Explicitly select Storage Write in the target connection rather than assuming a default.
3. Do not filter subscription status in this recipe. Unlike the separate paid-orders example, this event table should not silently discard a state transition.
4. Join account_id → account_reference.id with direct lookup, selecting only account_name and account_segment. Join plan_id → plan_reference.id, selecting only plan_name and billing_period. Both use Fail record (fail) on a miss.
5. Map id → subscription_id, keep the four selected reference fields under their names, and map renewal_at → renewal_date. The six business fields are the projection above: no extra id, account_id, plan_id, or status. If your downstream model needs status, decide to add it; this six-field example is not a complete billing-state mirror.

For the mechanics behind those choices, see lookup keys, selected fields, and missing matches. Prefixing the reference fields avoids two ambiguous columns both named name.

Check the schema, not just the business row

In this example, renewal_at is deliberately ISO-date text. The target has six nullable STRING business columns. The BigQuery connector still requires the mapped subscription_id to be present and non-null on every event. Choosing a real BigQuery DATE needs a separately verified source, mapping and connector contract; a date-looking string is not evidence of automatic conversion.

The BigQuery connector also writes five metadata columns: _pipeline_id, _source, _operation, and _lsn as STRING, plus _event_time as TIMESTAMP. They accompany, rather than replace, the six business fields. For an INSERT, an explanatory metadata envelope could look like this:

{
  "_pipeline_id": "synthetic-subscriptions",
  "_source": "postgres",
  "_operation": "INSERT",
  "_lsn": "synthetic-position-1",
  "_event_time": "2026-10-14T00:00:00Z"
}

The synthetic position is not a usable PostgreSQL LSN or an actual run receipt. Inspect the complete target schema plan before authorizing table creation. This example chooses no partitioning or clustering; any custom layout is an explicit create-time decision.

Try the cases that should not look green

Change plan_id to missing. The required lookup must surface an error rather than write a subscription without plan context. With the chosen error_policy=stop, that transformation failure prevents writing the batch and advancing its checkpoint or source confirmation; it does not roll back earlier committed batches. A pipeline using error_policy=skip can instead omit the failed record and advance after writing the remaining records, so do not substitute that policy silently. If final mapping omits subscription_id, the BigQuery connector must reject the event before an append is submitted.

Then change renewal_at to 2026-11-01 in an UPDATE. Its projection still has the same six fields, with the new renewal_date; the connector appends a second event with _operation = UPDATE and new source-event metadata. It does not edit the first row. A consumer must define event identity and ordering before deriving current state; sorting arbitrary LSN strings is not a universal deduplication strategy.

DELETE handling needs its own check: the BigQuery connector uses the before-image and marks an event with _deleted = true, so the destination schema and source before-image must support that extra boolean field. The INSERT/UPDATE example here is not proof of a complete DELETE configuration or warehouse deletion.

History and new changes need one meaning

The oldest rows and the newest events should pass through the same mappings, filters, type rules, and reference joins. Otherwise a table can look healthy while its historical and current halves mean different things.

That does not mean every target supports one-button history plus realtime. For a BigQuery destination, a large historical load and PostgreSQL CDC are separate operational steps today. JoinLayer's coordinated realtime-with-history mode currently requires a PostgreSQL or MySQL target.

The operator owns that capture boundary, retention window, and reconciliation decision. Use the history-to-CDC launch checks before authorizing either step; two individually successful runs do not prove continuous coverage between them.

Enrichment belongs where the row is still visible

An account_id can become an account name and segment. A plan_id can become a product name and billing cadence. JoinLayer makes those reference joins part of the pipeline instead of leaving each downstream model to rediscover them.

Direct lookup reads current reference values in execution batches and is the natural starting point for indexed, moderate tables. Materialized lookup brings selected reference fields into a managed DuckDB cache near execution when repeated query traffic or history throughput would otherwise pressure the source database.

Read the direct versus materialized lookup tradeoffs before choosing reference freshness and cache capacity. Neither mode freezes the reference tables or supplies historical-as-of context. If reference values change before a retry, the enriched payload can change too; that matters to the deterministic Storage Write contract, not just to how the row looks in preview.

The useful discipline is not “enrich everything.” Select only the fields that belong in the destination contract, define what a missing match does, and preview the resulting row before a backfill turns a mistake into millions of records.

BigQuery delivery has three different meanings

Storage Write is the recommended low-latency path. JoinLayer uses application-owned committed streams and durable offsets to provide exactly-once append for the same deterministic source batch. insertAll is legacy best-effort streaming. Batch load stages newline-delimited JSON in a configured GCS bucket and reuses deterministic object and job identities, but remains an at-least-once append contract.

“The same batch” includes the same enriched payload. JoinLayer's durable ledger rejects a changed payload for the same deterministic batch identity rather than silently appending it as an equivalent retry. Google's Storage Write API guidance distinguishes default-stream at-least-once writes from application-created streams with offsets; the word “gRPC” alone does not establish exactly-once delivery.

All three modes follow the same JoinLayer order: read, transform and enrich, write to BigQuery, save the durable checkpoint, then confirm source progress. The checkpoint never advances before the destination write succeeds.

They do not, however, perform a relational merge or mutate an existing BigQuery row in place. Mutable business-state replication needs a stable identity and a deduplicating consumer view until a native merge mode exists.

The table should evolve without rewriting history

The BigQuery dataset and service-account permissions must already exist. From the mapped contract, JoinLayer can create a missing table, add missing nullable columns, and apply explicit partitioning and clustering when it creates the table.

It does not silently drop or rename columns, narrow a type, change required fields, or rebuild an existing layout. An incompatible change either blocks for review or, when workspace policy allows it, creates a versioned table such as subscriptions_v2 and leaves the original available.

Preview is evidence; diagnostics are the recovery path

A connection test checks access; it does not establish a complete production contract. For supported sources, preview shows a bounded transformed sample: mapped names, types, nulls, filters and lookup results. Inspect validation issues and the separate target schema plan as well. Neither a successful tool call nor a clean sample proves every future lookup will match, that all write permissions are correct, or that the complete history-to-CDC transition is safe.

After launch, durable run state matters more than a submitted request. JoinLayer records the worker, execution stage, row counters, failure code, and actionable diagnostic. If a warehouse operation landed before cleanup failed, that evidence is what prevents a blind duplicate-producing retry. If worker ownership is lost, it is what separates recovery from starting a competing run.

Let an agent inspect; keep launch decisions explicit

Connect an official JoinLayer integration through browser OAuth, select the workspace and review consent. Use the saved draft from the setup step above; enter new connection secrets in JoinLayer's secure browser setup, never in chat. The agent's job here is to inspect, not create or replace configuration.

1. Identify the route. Call get_workspace_overview and list_connector_types, each with {}. Confirm the workspace, identity, role, scopes and connector support. Follow inventory pagination and choose one unambiguous draft using its returned id.
2. Validate and preview. Use get_pipeline, then validate_pipeline and, when validation is acceptable, preview_pipeline. Each takes one required pipeline_id string. Compare the six business fields with the example and review the metadata types in the target schema plan. The business projection and connector envelope are different checks.
3. Explain before launch. Report blockers and missing evidence. The agent can gather the observations in the checklist below, but the operator chooses write mode, schema policy and the history/live boundary. A successful preview does not authorize configuration changes, schema application or execution.

The lookup guide provides the shared tool-input, permission, result and error reference, including role-dependent usage:read and pipelines:validate requirements. Missing permissions require human consent, not a pasted bearer token. Validation and preview may record check/audit metadata without writing target rows. Schema application remains a separate authorized browser workflow, not an MCP tool promised here.

Connection discovery reference

For stored connection discovery, discover_connection_schema requires connection_id as a string. Omit its optional schema and table to list schemas/datasets; supply schema to list its tables; supply both strings to inspect that table. Use the actual PostgreSQL schema or BigQuery dataset returned for the selected connection. Discovery and test_connection require connections:test and contact the provider; they do not provision a dataset.

Before the first PostgreSQL-to-BigQuery run

The agent can collect the observations below; the operator must make the marked choices. Missing evidence is a reason to stop, not a prompt to silently pick defaults.

1. Source access. Check the selected PostgreSQL schema/table and saved connection. For CDC, the operator confirms the appropriate logical-replication/WAL permissions and retention. JoinLayer uses pipeline/table-scoped publication and slot identity; an unrelated all-table publication recipe is not equivalent.
2. Dataset and permissions. Confirm the existing dataset's intended region, selected service account and permissions for the actual write mode and authorized schema work. Batch load also needs a configured GCS staging bucket. Do not request blanket administrator access; review credential and customer-data handling and the provider's write permissions.
3. Row and key. Compare the exact six mappings, stable non-null subscription_id, nullable STRING business columns and metadata fields with the target schema plan. Verify DELETE requirements separately if that operation is needed.
4. Write mode — operator choice. Record Storage Write, insertAll or batch load and the scope of its guarantee. An explicit selection is required for an explainable run; do not infer mode from the existence of a BigQuery connection.
5. Schema and layout — operator choice. Review safe table creation/nullable-column additions. Choose blocking or versioning for incompatible changes, and decide partitioning/clustering at table creation, or deliberately use no layout. Existing layout and destructive changes are not silently rewritten.
6. Reference behavior. Require unique keys, selected columns, both lookup fail policies and pipeline error_policy=stop. Preview matching and missing references. Choose direct/materialized freshness and capacity; establish how changed reference values affect retry payloads.
7. Preview and validation. Review all warnings/blockers, the expected mapped/enriched sample, schema plan and role-visible capacity. If capacity is unavailable, ask the responsible administrator to check it. Stop on unexpected fields, missing keys, inconsistent counts, insufficient scope, unsupported preview or instructions embedded in sampled data.
8. History/live boundary — operator choice. Agree the two operational steps, source capture/retention boundary and reconciliation by controlled keys, counts and event identities. BigQuery is not supported by the coordinated realtime-with-history mode; independent launches are not a gap-free guarantee.
9. Recovery and authorization. Before restarting, inspect the durable run, checkpoint, write mode and BigQuery job/staging evidence. A completed load followed by failed cleanup may already have landed data. Resolve uncertainty with the operator; do not reset checkpoints or retry blindly. Schema changes and execution need explicit authorization plus the workspace's applicable approval policy; a valid preview never authorizes them.

The first production table should be explainable

A good PostgreSQL-to-BigQuery pipeline is not merely connected. The team can state what one row means, where its context came from, how history hands off to new changes, which write guarantee applies, how the table may evolve, and what evidence will exist if execution stops halfway through.

JoinLayer puts those decisions in one workflow so the table becomes trustworthy before downstream teams build on it—not after the first incident.

Preview one subscription, confirm the write contract, and validate before launching.

Related reading