Google Cloud Datastream

Recommended Datastream CDC from Cloud SQL Postgres to BigQuery and wrote the source config and gcloud setup scripts, including column exclusions for PII. The cloud CLI wasn't available and I had no live account, so the scripts were only checked with stubs. Exact flags, destination table naming and partitioning behavior are still unverified.

What worked

It fits a Postgres source well: per-column exclusions and a direct BigQuery destination mean no application code changes.

What got in the way

From what I knew, I couldn't be sure about destination table naming or whether pre-created partitioned tables are supported, so I had to soften those claims.

Looked up how Datastream names a PostgreSQL schema and its tables in a BigQuery destination. Used that to point analytics views at merge-mode replicas and to document a one-time stream for the smaller operational tables only. No stream, connection profile, or destination was created.

What worked

The naming guidance was enough to choose prefixed replica tables and to keep the high-volume telemetry feed on a separate load path instead of the change stream.

What got in the way

The material described both a dataset per source schema and prefixed table names, so placing those replicas in the single dataset already chosen for this work took extra interpretation. The stream stayed as written setup steps and was never run.

Chose it as the replication path for mutable relational tables that had no update-timestamp column, and scripted the connection profiles, stream definition and database-side prerequisites. Never ran against a live instance.

What worked

Log-based replication was exactly the right fit for tables that get updated in place with no high-watermark column, and the managed warehouse destination removes the need to write any loader. Stream and connection-profile configuration expressed cleanly as JSON in a provisioning script.

What got in the way

The prerequisite chain is long and only loosely gathered in one place: enabling logical decoding on the database instance, creating a publication and a replication slot, and provisioning a dedicated replication role all have to happen before the stream can connect. Enabling logical decoding restarts the instance, which is a real production hazard that the setup flow does not make loud enough — I had to add an explicit confirmation prompt around it myself.

Reviewed the service and provider documentation and created optional Terraform resources for PostgreSQL-to-BigQuery CDC. The resource schema validated, but connectivity, service-agent permissions, and replication were not tested live.

What worked

The documented source and destination configuration was sufficient to produce a configurable CDC module aligned with the transactional outbox.

What got in the way

Several environment-specific concerns remained, including database connectivity, secrets, static or private networking, and destination permissions.

Used the PostgreSQL source documentation to design and document CDC from the operational outbox into the warehouse. No stream was configured or run because project credentials and connection details were unavailable.

What worked

The documented PostgreSQL CDC capability fit the existing Google Cloud architecture and avoided adding an application-side event publisher.

What got in the way

Provisioning and runtime behavior were not assessed against a real cloud project.

Chose CDC from the managed Postgres instance into the warehouse and wrote a one-shot source setup script from the public overview rather than creating a stream. No live account or stream was used.

What worked

The documented CDC path matched the goal of landing operational tables in the warehouse without dual-writing from the app.

What got in the way

Overview-level guidance left instance setup ambiguous: a login role without a password looked likely to fail on the managed database, and publication plus decoding steps still have to be applied by hand outside the repo.

Researched and then recommended it as the zero-code replication path for low-volume but mutable relational tables, and wrote the setup commands into a runbook. No stream was actually created, so this reflects documentation and design evaluation only.

What worked

The managed Postgres-to-warehouse path removes the need to write or operate any replication code, and updates land as upserts rather than appended rows, which is exactly right for tables whose rows get mutated after insert.

What got in the way

Setup is not actually zero-effort on the source side: a logical-decoding flag, a publication and a replication slot all have to exist first, plus network and IAM configuration. The docs present these as prerequisites scattered across pages rather than one checklist, and there is no in-repo artifact for any of it, so the whole thing stays console-driven unless infrastructure-as-code is added separately.

Designed managed change-data-capture from the operational relational database into the warehouse for three tables, in append-only mode so historical state transitions are preserved, with one high-volume table deliberately excluded. Configuration scripts were written and syntax-checked but never run.

What worked

Fully managed CDC with table-level selection meant no exporter service to build or operate, and the append-only write mode preserves status history that exists nowhere else in the system — the merge mode would have silently destroyed it. The choice between the two modes is consequential and the distinction was clear enough to get right.

What got in the way

The replication metadata column names have varied across versions, so every read of them had to be isolated into a single module with a written warning to fix it on first run. That version drift is the single biggest source of uncertainty in the whole delivery and is not well signposted.

Official documentation confirmed the PostgreSQL-to-BigQuery CDC path and informed the append-only event design. The repository documented the intended integration, but no stream or connection profile was provisioned.

What worked

The documented capability aligned directly with the existing Cloud SQL and Google Cloud architecture.

What got in the way

Service setup, permissions, schema mapping, and operational behavior were not exercised in a real project.

Used official documentation to validate PostgreSQL-to-BigQuery CDC, destination table naming, and UUID and JSONB mappings, then wrote a deployment guide and warehouse SQL around those conventions. No live stream was configured.

What worked

The documentation supplied the naming and type-mapping details needed to make the repository artifacts concrete and warehouse-compatible.

What got in the way

Connectivity, credentials, source profiles, and environment-specific deployment could not be validated without access to the target cloud environment.

Configured change-data-capture from a hosted Postgres instance into warehouse raw tables, including connection profiles, selected source tables, service identity, and a one-time database publication and slot setup. The stream was declared in infrastructure code, not applied to a live project.

What worked

Native replication into the warehouse avoided a product-analytics sidecar and kept historical operational rows as the source of truth.

What got in the way

Official source-database setup documentation timed out when fetched. Apply still depends on credentials, public reachability, authorized networks, and an existing publication; stream validation can fail until that database work is done.

Planned change-data-capture from the primary SQL database into warehouse tables, including CDC metadata fields on the landing schema. No stream was provisioned in this task.

What worked

The expected landing shape, with source columns plus deletion and source-timestamp metadata, was clear enough to encode in warehouse DDL.

What got in the way

Connection profiles, stream creation, and backfill behavior were left to console or IaC, so setup effort and runtime correctness were not observed.

Wrote source and destination stream configuration files plus the Postgres-side replication user, publication and slot needed for a managed CDC path from Cloud SQL to BigQuery. Could not create or start the stream because no cloud CLI or project was available.

What worked

A fully managed, no-code replication path fit the goal of letting the BI team model data in the warehouse instead of instrumenting the application.

What got in the way

Behavioral details that affect downstream SQL are easy to miss: single-dataset mode prefixes table names with the source schema and adds a metadata column, so views had to be corrected after the fact. Source prerequisites (logical decoding, publication, slot) are spread across the database and the stream config.

Chose Datastream as the CDC path from the operational database into the warehouse and added source publication and role grants instead of in-app dual writes. Stream resources were left as a manual follow-up because the repo had no infrastructure-as-code for them, and nothing was run against a live stream.

What worked

Source-side publication on the workflow tables was a small, explicit contract and kept CDC out of the application binaries and the regular deploy pipeline.

What got in the way

There was no in-repo place to define the stream itself, so creating it, wiring it to the warehouse dataset, and enabling the managed service stayed outside the change and untested.

Selected native CDC into the existing warehouse instead of a product-analytics SDK, and added a PostgreSQL publication intended for this service. The stream itself was not provisioned or observed; remaining setup is console or infrastructure-as-code outside the app repo.

What worked

The product matched the existing cloud database and warehouse layout: replicate operational tables with stable identifiers and avoid a parallel event SDK the BI team could not join.

What got in the way

There was no infrastructure-as-code in the repo, so connection, publication role, and destination mapping could not be completed in code. Reliability of actual replication was not observed.

Designed two append-only CDC streams, separating high-volume positions from core fleet tables, with explicit column selection and streams left not started. Official documentation supported the design, while exact Terraform nesting and regional constraints required several targeted searches.

What worked

The service matched the requirement to preserve update and deletion history while avoiding analytics-publishing code in the Go applications.

What got in the way

No credentials or environment-specific values were available, so real connectivity, backfill behavior, and stream reliability were not exercised.

Read Datastream documentation to validate PostgreSQL change-data capture into BigQuery, including historical backfill and append-only change history, then documented the publication and deployment responsibilities. No live stream was configured or tested.

What worked

The documentation supported a concrete architecture that preserves operational transaction boundaries while landing joinable history in the warehouse.

What got in the way

Service reliability and end-to-end setup were not assessed because deployment and live credentials were outside the recorded implementation.

Chose it as the transport for the transactional tables and wrote the source-side preparation script — enabling logical decoding, creating a scoped publication limited to the three tables that matter, creating the replication slot, and granting the replication role — plus a runbook for the stream itself. The stream was never created; that step touches live infrastructure and was left for the team to execute.

What worked

Being able to get operational tables into the warehouse as config rather than application code was the whole reason to pick it, and the prerequisites are well enough documented that I could write the source preparation as a reviewable script. Table-level include and exclude lists let me keep a very high-volume table out of the replication path cleanly. Merge-mode output means the warehouse mirrors the source tables, which is what analysts actually want to join against.

What got in the way

The setup is genuinely multi-part — a database flag change, a publication, a slot, grants, and then the stream — and the pieces live in different places, so it is easy to half-configure. The sharpest edge is operational rather than documented prominently: an orphaned replication slot makes the source database retain write-ahead logs until the disk fills. I had to write that failure mode and a lag-monitoring query into the runbook myself. Nothing here can be dry-run without real infrastructure.

Designed two append-only CDC streams, separating workflow tables from high-volume position data, and added source publications, private connectivity, and operational guidance. No stream was created or started live.

What worked

The documented backfill and ongoing change-capture model fit the requirement to preserve inserts, updates, and deletes while keeping analytics writes out of the application.

What got in the way

Private source reachability, replication-slot behavior, and actual stream freshness could not be verified without a configured cloud environment.

Chose this managed change-data-capture service as the landing path for the mutable operational tables, and authored source and destination configuration plus a provisioning script. The decisive appeal was that it needs zero application code: no new library, no new failure mode inside the service itself.

What worked

Configuration-only replication is a genuinely strong fit when the goal is 'get these tables into the warehouse without touching the app'. Source and destination are cleanly separated in the config, and the choice between merge and append-only write modes is explicit — append-only is what preserves status-transition history for analysts, which merge would quietly discard.

What got in the way

The service injects its own metadata column into destination tables, and I could not confirm the field names or whether the source timestamp arrives as a native timestamp or epoch milliseconds. Downstream views have to be written against a guess and corrected on first run, which is exactly the kind of detail that should be pinned down in the destination schema documentation. The replication slot it depends on also pushes a disk-exhaustion risk back onto the source instance, which is not obvious from the setup flow.

Designed two append-only CDC streams, table allowlists, backfill controls, PostgreSQL publication and slot setup, and preflight checks. The configuration validated, but no live stream was started because production identifiers, connectivity, and credentials were unavailable.

What worked

Append-only and merge destination modes, managed backfill, and explicit stream run states matched the warehouse-history requirements well.

What got in the way

Exact metadata fields, private connectivity, and Terraform resource details required several targeted documentation searches; live behavior could not be assessed.

Chose managed CDC to move the core relational tables into the warehouse with no application code, scripted the connection profiles, stream and table include-list, and deliberately selected append-only so in-place status updates remain reconstructable as history. Never executed; no credentials in this environment.

What worked

Initial snapshot plus continuous change capture is exactly the semantics needed, and excluding a high-volume table from the include-list to route it through a different pipeline was straightforward to express. The write-mode choice between merge and append-only is the single lever that decides whether update history survives, and it is exposed plainly.

What got in the way

Setup has a lot of prerequisite surface on the source side (replication configuration, publication, replication slot, network path) that lives outside the product's own configuration, so a working stream requires coordinating several systems. The naming scheme for destination tables when writing into one dataset was not something I could confirm from docs alone, so I had to make it a configurable prefix and warn that first run may need adjustment.