Google Cloud Platform Services and Architecture Questions

Direct answer

Treat this as four separate problems that have to land in a specific order: move the bulk historical data, translate the schema and existing ETL (extract-transform-load) semantics, stand up a parallel streaming path so near-real-time ingestion never has a gap, and validate query-for-query before anyone is allowed to cut traffic over. Don't try to do a single big-bang migration of 200TB; run the bulk historical load and the new streaming path in parallel with the existing on-prem warehouse for a defined overlap window, and only cut over once independent validation shows the two systems agree.

Structured elaboration

1. Storage transfer (the bulk 200TB). For a one-time bulk move of this size, the deciding factor is available network bandwidth, not preference: if your link to Google Cloud can move 200TB in an acceptable window, use Storage Transfer Service to land the historical extract in Cloud Storage; if bandwidth is the constraint (a slow or metered on-prem link), a physical transfer appliance is the standard fallback for moving hundreds of terabytes without waiting weeks on a wire. Either way, land in Cloud Storage first, not directly into BigQuery, so you have a durable, re-loadable staging copy if a later load step fails partway through.

2. Schema mapping. Use BigQuery's migration tooling to auto-translate the source warehouse's SQL dialect (if it's Teradata, Oracle, Redshift, or similar) and assess the existing schema and ETL jobs for what maps cleanly versus what needs manual rework, rather than hand-translating hundreds of tables and jobs. Expect friction on anything that leaned on the old warehouse's procedural extensions (stored procedures, proprietary functions) since those don't have a mechanical translation and need to be re-implemented, typically in Dataflow or scheduled queries.

3. Streaming versus batch ingestion, chosen per source, not once for everything. For the near-real-time requirement, identify which upstream sources are transactional databases with a CDC (change data capture) capability (Datastream supports MySQL, PostgreSQL, AlloyDB, SQL Server, and Oracle as sources) and stream those directly into BigQuery with low latency, preserving insert/update/delete semantics rather than re-running full extracts. Sources that only produce periodic batch files (nightly exports, flat files) stay batch, loaded via Storage Transfer plus scheduled BigQuery load jobs; forcing everything into a streaming shape when the source itself is batch just adds operational complexity for no latency benefit.

4. Validate before cutover, don't assume the migration worked. Run the new BigQuery pipeline and the legacy warehouse side by side for an overlap window (long enough to cover your slowest reporting cycle, commonly at least one full month-end close), and reconcile row counts, key aggregates (sums, counts by dimension), and checksums between the two systems for every migrated table, not just a sample. Google's open-source Data Validation Tool is built for exactly this comparison and is worth using instead of hand-rolled reconciliation queries, since it standardizes how the aggregates are computed on both sides. Only cut reporting traffic over once a defined tolerance (for example, financial aggregates matching exactly, non-financial aggregates within an agreed rounding tolerance) holds for the full overlap window, not just on day one.

flowchart TD
    A[On-prem warehouse: 200TB] --> B[Storage Transfer Service or Transfer Appliance]
    B --> C[Cloud Storage staging]
    C --> D[BigQuery load: historical backfill]
    E[Transactional sources w/ CDC] --> F[Datastream]
    F --> G[BigQuery: near-real-time tables]
    H[Batch-only sources] --> I[Scheduled load jobs]
    I --> G
    D --> J[Validation: row counts, aggregates, checksums]
    G --> J
    J -->|matches within tolerance| K[Cutover]

Worked example

Say the 200TB splits into 150TB of pure historical fact tables (loaded once) and 50TB of actively-changing dimension and transaction tables that need the near-real-time path. The historical 150TB, at a sustained transfer rate of, say, 10 Gbps (gigabits per second) dedicated to the migration, works out to:

That's the network-time floor under ideal, uninterrupted throughput; real-world transfer jobs run well below link capacity due to retries and concurrency limits, so treat this as a lower bound when setting the overlap-window schedule, not a promise, and validate the actual sustained throughput of your specific link before committing to a cutover date.

Trade-offs & pitfalls

• Skipping the overlap-and-validate window to hit a deadline is the single most common way these migrations produce a quiet, delayed incident: a subtly wrong join or a timezone mismatch in the schema translation doesn't show up until the first month-end report after cutover, when the old system is already decommissioned and there's nothing to reconcile against.
• CDC-based streaming preserves insert/update/delete semantics, but only if the source database's replication log retains enough history to avoid gaps during any pause in the pipeline; monitor replication lag as a first-class signal, not an afterthought.
• Auto-translated SQL is a starting point, not a finished migration; anything relying on the source warehouse's specific optimizer behavior or non-standard functions needs a human to verify the translated query still returns the same result, not just that it runs without error.

Direct answer

Don't export or copy anything to the external analyst. Publish a narrow, governed surface, an authorized view over the raw table (never the table itself), tightened further with row-level security so the analyst only sees rows scoped to them, and distribute access to that view through BigQuery sharing (Analytics Hub): the analyst subscribes to a listing and gets a read-only, no-copy "linked dataset" in their own project that always reflects the live data and can be revoked at any time. Dataset-level IAM (identity and access management) then only needs to grant the sharing mechanism (or, if going simpler, the analyst's identity directly) roles/bigquery.dataViewer on the dataset that holds the authorized view, never on the dataset holding the raw table, so a misconfiguration on the shared side can't leak the base data.

Structured elaboration

Authorized views: control what exists to be seen. Create a view (in a dataset dedicated to sharing, separate from where the raw tables live) that selects only the columns the analyst is entitled to, with any sensitive columns dropped, hashed, or aggregated out entirely. Grant that view "authorized" status against the source dataset so it can read the underlying table without the caller needing direct access to the table. The base table effectively becomes invisible from the analyst's side: they only ever see the shape the view defines.

Row-level security: control which rows, within that shape. A CREATE ROW ACCESS POLICY on the underlying table (or the view, depending on how it's layered) filters rows using a predicate tied to the querying identity, for example matching a partner_id column against SESSION_USER(), so if this analyst represents Partner A, they only ever see Partner A's rows even though the view's column definition is shared across every partner.

Dataset-level IAM: control the blast radius of a mistake. Put the authorized view in its own dataset and grant access at that dataset only. If someone later mis-scopes a permission, the worst case is exposing the already-restricted view surface, not the raw table. Never grant the external analyst (or the service account acting for them) any role on the dataset holding the raw table.

BigQuery sharing (Analytics Hub) ties it together for distribution. Create a private data exchange, publish a listing backed by the authorized view (not the raw table), and grant the external organization's identity access to subscribe. When they subscribe, BigQuery creates a read-only, symbolic "linked dataset" in their project: no data is copied, updates to the source are visible immediately, and you can revoke the listing at any time to cut off access instantly. This avoids the two riskiest alternatives: emailing a service-account key with broad project access, or periodically exporting a CSV that immediately becomes a stale, uncontrolled copy sitting somewhere outside your governance.

flowchart LR
    A[Raw table] -->|row access policy| B[Authorized view]
    B --> C[Dedicated sharing dataset]
    C --> D[Data exchange listing]
    D -->|subscribe, no copy| E[External analyst: linked dataset]

Worked example

Say you run a marketplace and want to give a specific ad-analytics partner visibility into ad performance, but only for their own campaigns, and only impressions/clicks/spend, never the underlying user-level click stream. The authorized view selects campaign_id, date, impressions, clicks, spend from the raw event table, deliberately excluding any user or device identifier. A row access policy filters on campaign_id IN (SELECT campaign_id FROM partner_campaigns WHERE partner_email = SESSION_USER()). That view lives in a shared_reporting dataset, granted roles/bigquery.dataViewer only to the data exchange's service identity, listed on a private exchange, and the partner subscribes once. If the partnership ends, revoking the listing (a single action) removes their access immediately, versus having to track down and invalidate a CSV or export that may already have been copied elsewhere.

Trade-offs & pitfalls

• Sharing the raw table through Analytics Hub instead of an authorized view is a real and easy mistake: the mechanism only controls distribution, not what's inside what you distribute. It will happily hand over everything in the underlying table if that's what you point the listing at.
• Row access policies are evaluated per query, so a row policy predicate that's expensive to compute (a subquery against a large lookup table) can add real latency to every query the external analyst runs; keep the predicate cheap or backed by a small, indexed-equivalent lookup table.
• Because a linked dataset always reflects live data, a bug that writes bad rows into the base table is visible to the external partner immediately too; there's no built-in delay or approval step between "data lands" and "partner can query it," so data-quality checks need to run upstream of the raw table, not downstream of the sharing layer.
• Query costs on the linked dataset are attributed to whichever project runs the query (the subscriber's, by default), which is usually what you want, but confirm this explicitly with the partner up front so nobody is surprised by who gets billed for their own heavy queries.

Direct answer

For a global transactional application needing low latency and strong consistency, Spanner's multi-region configuration is the right recommendation over a hand-built cross-region Cloud SQL topology, because Cloud SQL fundamentally has one authoritative primary. A cross-region Cloud SQL setup can give you regional read replicas for low-latency reads, but every write still goes through a single primary in one region, meaning every write from anywhere in the world pays that primary region's round-trip latency, and a regional outage of the primary is a real, manual failover event, not an automatic one. Spanner's multi-region configuration distributes both reads and a quorum-based write path across regions natively, giving strong (in fact, stronger than typical "strong consistency": external consistency) guarantees globally with automatic failover, at the cost of higher baseline write latency than a single-region deployment and real cost and design complexity.

Structured elaboration

Spanner multi-region configuration. A multi-region configuration designates a default leader region (housing the leader replicas for each shard), a second read-write region, and a witness region that holds a voting replica without serving reads, plus optional additional read-only regions for low-latency local reads elsewhere. Every write forms a quorum: one of the replicas in the leader region plus two of the four remaining voting replicas across the other regions must agree before a write commits, which is what gives Spanner external consistency (a guarantee stronger than standard strong consistency, meaning transaction order is preserved in a way consistent with real time globally, not just per-object). Multi-region configurations offer higher availability than a regional one (99.999% versus 99.99%), precisely because losing any single region still leaves enough voting replicas to form a quorum.

Cross-region Cloud SQL topology. The realistic version of "cross-region Cloud SQL" is one primary in a home region with cross-region read replicas near other user populations, so reads local to those replicas are fast, but every write anywhere still round-trips to the single primary's region. A regional outage of the primary requires promoting a replica to primary, which is an operator-initiated (or scripted) failover with a real recovery time, not the automatic, sub-second quorum failover Spanner's model provides. There's no native Cloud SQL feature that gives you a globally-distributed write path with strong consistency; building an approximation (application-level sharding by geography, conflict resolution logic for cross-region writes) is a significant, ongoing engineering investment that's reinventing a narrower, less battle-tested version of what Spanner provides as a managed service.

Failover and replication compared directly:

Recommendation and justification. For a genuinely global, write-heavy transactional application needing strong consistency (the stated requirement), recommend Spanner's multi-region configuration: it's the only one of the two that actually gives strongly-consistent writes with low latency from more than one region, because Cloud SQL's single-primary architecture makes "low latency writes from everywhere" and "one authoritative primary" mutually exclusive. If the actual requirement, on closer inspection, turns out to be "low-latency reads globally, but writes can tolerate one region's latency" (a materially different and more common requirement than it first sounds), a cross-region Cloud SQL topology with regional read replicas is simpler, cheaper, and sufficient, so it's worth explicitly confirming which requirement is real before committing to Spanner's added cost and complexity.

Worked example

A global ride-hailing dispatch system needs a driver's location update and a rider's booking confirmation to be consistent worldwide (a driver can't be double-booked across two regions' views of the same fleet), with drivers and riders spread across North America, Europe, and Asia. On Spanner, a multi-region configuration spanning those three regions gives every region low-latency local reads and a consistent write quorum, so a booking confirmed in Europe is immediately, correctly reflected if queried from Asia. On cross-region Cloud SQL with a single primary in, say, us-central1, every booking write from Europe or Asia pays a full round trip to the US regardless of how many local read replicas exist, and a us-central1 regional outage means dispatch is down globally until a replica is manually promoted, both of which directly violate the stated low-latency and strong-consistency requirements. The identical argument holds for a global financial-services application processing payments across the same three regions: a payment authorization is the same kind of write that must be immediately, consistently visible everywhere (to prevent the same funds being authorized twice from two regions), so the recommendation and the reasoning behind it don't change just because the domain moves from ride-hailing to payments.

Trade-offs & pitfalls

• Recommending Spanner without confirming the write requirement genuinely needs global strong consistency (rather than just global low-latency reads) means paying for capability the application doesn't use; verify which property the business actually needs before defaulting to the more powerful option.
• Spanner's multi-region write latency is real and higher than single-region Spanner or a well-placed Cloud SQL primary serving nearby users; a workload that's latency-sensitive on writes specifically (not just consistency-sensitive) needs to be tested against that added latency, not assumed away.
• A cross-region Cloud SQL topology can look deceptively similar to Spanner on paper (multiple regions, replicas), which is exactly what makes it easy to mis-recommend for a workload that actually needs strongly-consistent global writes; the failover behavior and write-path latency are where the architectures diverge in practice, not just theory.

Direct answer

For a latency-SLA'd (service-level agreement) inference workload, the deciding factor isn't raw throughput, it's who is responsible for keeping capacity warm and how much control the team needs over batching. Vertex AI's managed prediction service is the right default when the model fits a supported serving path, since it owns the autoscaling and warm-capacity problem. Reach for GKE (Google Kubernetes Engine) with GPU node pools when the serving stack needs custom batching logic, a specific inference server, or tighter cost control at high sustained volume. Compute Engine with GPUs is the fallback for the narrowest, most idiosyncratic serving requirements that don't fit either managed option.

Decision framework

• Vertex AI online prediction (Google's managed model-serving product; Vertex AI was rebranded into Google Cloud's Gemini Enterprise Agent Platform in 2026, but this answer keeps calling it Vertex AI since that is how the question names it and how the capability is still widely referenced): handles autoscaling, load balancing, and model versioning. Best when the model deploys through a supported framework container, or a well-behaved custom container implementing its prediction protocol, and the team wants to spend engineering time on the model, not the serving infrastructure. The trade-off is less control over exactly how batching and instance warm-up behave.
• GKE with GPU node pools: the right layer when the serving stack needs something Vertex AI doesn't expose directly, a specific inference server (for example NVIDIA Triton) doing custom dynamic batching, GPU time-sharing across several models per GPU, or integration with other services already running in the cluster. This buys control at the cost of owning node pool sizing, GPU driver management, and autoscaler tuning.
• Compute Engine with GPUs: appropriate when the workload doesn't fit a container-and-autoscaler shape at all, an unusual GPU memory management pattern, or a fixed, fully predictable load where manual capacity planning gets the lowest steady-state cost. It offers the most control and the least built-in elasticity.
• What to watch for regardless of platform: autoscaling behavior (GPU capacity in a region can be constrained, so a scale-up triggered by an SLA breach may not get new capacity as fast as CPU-only scaling; on GKE a new node has to provision and join the cluster before a pod can even schedule); cold starts (any path that scales down aggressively reintroduces the delay of loading model weights and initializing the runtime, so keep a minimum warm replica count regardless of platform); and batching (batching requests together improves throughput per dollar but adds latency to any individual request waiting for the batch window to fill, and the batch wait time has to be tuned to stay well under the SLA's budget, not just tuned for throughput).

Worked example

A model with a tight 200ms p99 latency SLA (the 99th-percentile response time: 99% of requests must finish under 200ms, so it's driven by the slowest requests, not the average) and moderate, somewhat bursty traffic is deployed behind Vertex AI online prediction with a minimum of 2 replicas rather than 0, avoiding the cold-start cost (model load plus runtime initialization, often measured in seconds, an order of magnitude too slow for a 200ms budget) on the first request after a quiet period, and letting the managed autoscaler add replicas as traffic grows beyond what those 2 can handle within the SLA. If profiling later shows requests would benefit from batching multiple inputs per GPU pass, and the default serving path doesn't expose that control, that's the concrete trigger to move to GKE with a Triton deployment where the batch window, say a 10ms max wait, can be tuned explicitly and verified to stay well inside the 200ms budget, since the wait time adds directly to the slowest request in that batch.

Trade-offs and pitfalls

• Assuming a managed service abstracts away cold starts entirely is a common mistake: a minimum replica count still has to be configured deliberately, since the default behavior for an idle deployment may scale down further than a strict SLA can tolerate.
• Choosing GKE for its control without actually using it, running a generic serving container with no custom batching or GPU sharing, pays the operational cost of Kubernetes and GPU node-pool management for no benefit; only move once there's a specific capability the managed option doesn't provide.
• Batching for throughput and a strict per-request latency SLA are in direct tension; tuning batch size purely to maximize GPU utilization, without checking the effect on the SLA's percentile rather than the average, is a frequent source of incidents.
• GPU capacity is more likely to be genuinely regionally constrained than general compute; a scaling plan that assumes GPUs are always available on demand should be validated, or hedged with a secondary region or reserved capacity, before the SLA depends on it.

Direct answer

BigQuery bills storage and compute (query processing) separately, which means the size of a dataset and the cost of querying it are two independent numbers, not one. For compute, on-demand pricing charges per amount of data scanned by a query (with a modest free monthly allowance), while BigQuery Editions offers slot-based capacity pricing (the current model, having replaced what was previously called flat-rate) for predictable, high-volume workloads. Partitioning and clustering both reduce the amount of data a query actually has to read, which lowers both cost and latency, but they do it differently: partitioning physically splits a table by a column (usually date), and clustering sorts data within a table (or partition) by up to four columns to let a query skip irrelevant blocks.

Structured elaboration

Storage and compute, separated

• Storage cost is driven purely by how much data sits in the table and for how long, independent of whether anyone ever queries it, with cheaper long-term storage rates kicking in automatically for data that hasn't been modified recently.
• Compute cost is driven purely by processing queries, and scales up or down independently: a table can be enormous and cheap to store while a query against it is expensive to run, or vice versa, and each can be optimized separately.

On-demand vs. capacity-based (Editions) pricing

• On-demand: pay per amount of data scanned by each query (with the first amount each month free), no upfront commitment, and the simplest model to start with since cost tracks usage directly.
• BigQuery Editions (Standard, Enterprise, Enterprise Plus): buy compute capacity in slots (units of query-processing power), either on-demand-style or via a commitment for a discount, and every query draws from that capacity pool instead of being billed per scan; this is the current name and shape of what used to be called flat-rate pricing, and it's the better fit once query volume is high and predictable enough that a capacity commitment costs less than paying per-scan for the same total work.
• Choosing between them is a volume-and-predictability question, not a maturity question: a small, spiky workload is usually cheaper on-demand; a large, steady one is usually cheaper on a slot commitment.

Partitioning

• Physically divides a table into segments based on a column, most commonly a date or timestamp, or an integer range.
• A query that filters on the partitioning column only reads the matching partitions, not the whole table, which is why partitioning a large, frequently date-filtered table is often the single biggest cost and speed lever available.

Clustering

• Sorts the data within a table (or within each partition of a partitioned table) by up to four chosen columns, so that rows with similar values in those columns are stored together.
• A query filtering or aggregating on a clustered column can skip blocks that don't contain matching values, without needing a full separate physical split the way partitioning has one; it's the right tool for high-cardinality columns (a customer ID or a product SKU, for instance) that wouldn't make sense as a partition column, since partitioning by something with millions of distinct values would create millions of tiny, inefficient partitions.
• Partitioning and clustering are complementary, not competing: a common, effective pattern is to partition a large table by date and cluster it by a high-cardinality column like customer ID, so a query for one customer's activity in one month reads only that partition's relevant blocks.

Worked example

Explaining this to a data team new to BigQuery: think of the whole table as a library. Partitioning by date is like the library being organized into one room per year, so a search for "everything from March 2026" only requires searching that one year's room, not the entire building. Clustering by customer ID is like every book within a year's room being shelved alphabetically by author, so a search for "everything by this one author, in this one year" skips straight to the right shelf inside the room instead of scanning every book in it. A 500 GB events table, partitioned by event date and clustered by customer ID, answers "this customer's events last Tuesday" by reading a tiny fraction of the 500 GB (just the shelf, in the room, for that day), instead of scanning the whole library every time, which is the direct mechanism by which both features cut cost and latency together.

Trade-offs & pitfalls

Partitioning by a column that queries rarely filter on buys nothing, since the whole point is that a filter on the partition column is what lets BigQuery skip partitions; a table partitioned by date but queried mostly by customer ID, with no clustering, still scans every partition every time. Over-partitioning (partitioning by a high-cardinality column instead of clustering by it) creates a large number of very small partitions, which adds per-partition overhead without the intended benefit. On the pricing side, the most common early mistake is staying on on-demand well past the point where query volume has grown enough that a slot commitment under BigQuery Editions would clearly cost less for the same total work, simply because nobody revisited the original, correct-at-the-time choice.