Flexible Portable Data Design for Multicloud Workloads

Mapping current and future business requirements to the architecture is the practice of turning business goals, growth forecasts, and limits into a data design that can adopt new services without rework or downtime. The assessment weighs scalability, cost, and operational impact on Google Cloud, while the plan keeps storage and compute separable, adds new sources alongside old pipelines, and keeps reports working during change. A flexible design stores data and runs computation on separate layers that are colocated in the same zone and region for performance.

1.1Separation of Storage and Compute for Growth

Separation of storage and compute in BigQuery (opens in a new tab) is the foundation that lets a warehouse grow from terabytes to dozens of petabytes without resizing clusters. BigQuery stores data in Colossus, Google's distributed file system, in a compressed columnar format called Capacitor that is queried directly without decompression. Query execution runs on a multilevel tree based on Dremel and is connected by the Jupiter network with 1 petabit per second of bisection bandwidth. Compute is measured in slots, which are units of CPU and RAM managed by Google and allocated by a fair scheduler, with projects limited by default to 2,000 slots.

Because storage is allocated as data is consumed and slots are adjusted per workload, growth is handled by changing slot reservations rather than rebuilding clusters. In contrast, Amazon Redshift ties CPU, memory, and disk together in compute nodes, so growth requires classic resize over hours to days with full data copy or elastic resize in 10 to 15 minutes in read-only mode. Even RA3 nodes with managed storage cap at 64 TB per node. This difference means future volume, new workloads, and cost-model changes favor separated storage and compute.

1.2Evaluating Multicloud and Advanced Analytics Services

Evaluating multicloud and advanced analytics services means checking whether a new capability removes a silo or licensing limit without forcing data movement. BigQuery Omni runs compute locally to storage on Amazon Web Services (AWS) or Azure and queries data in Amazon Simple Storage Service (Amazon S3) or Azure Blob Storage where it rests. Teams analyze external data in place when cross-cloud work or data residency rules make copying impractical. For Oracle migrations, the Oracle Advanced Analytics option for R Enterprise and Data Mining requires separate licensing.

Google Cloud replaces that Oracle pattern with BigQuery ML (opens in a new tab) for in-warehouse linear regression, logistic regression, k-means clustering, and TensorFlow model imports, plus Vertex AI Workbench for development and Vertex AI Training for large-scale training and scoring. BigQuery datasets can be regional or multi-region, resource allocation follows query type and complexity, column statistics are gathered at load time, and query plan and timing diagnostics support tuning. Choose Omni when data must stay in another cloud, and choose BigQuery ML and Vertex AI when models must run on warehouse data without a separate mining server.

1.3Ordered Migration and Transfer Path

An ordered migration path moves the current warehouse to the future architecture while pipelines keep running. The recommended initial transfer keeps the schema unchanged so existing feeds, staff training, and automated tools continue to work, and proofs of concept can run in parallel with migration. Transfer options depend on source location and bandwidth, and that choice directly affects timeline and disruption. Data flows from the source system to staging storage, then to a Cloud Storage (opens in a new tab) bucket, then to BigQuery, then through validation.

1. Extract: Export tables from the source platform to vendor-neutral files in staging storage, preferring Avro, ORC, or Parquet where schema is embedded with data.
2. Upload: Copy files from staging storage to a Cloud Storage bucket, choosing the bucket location closest to the source and ideally matching the BigQuery dataset location.
3. Load: Load schema and data from Cloud Storage into BigQuery using BigQuery Data Transfer Service, Storage Write API, or batch load, with optional transformation during load.
4. Validate: Compare source and target with the Data Validation Tool, which runs aggregations such as count, sum, and average on selected columns.

Data flows from another cloud to Cloud Storage through Storage Transfer Service, from on-premises with good bandwidth through the gcloud CLI with multithreaded parallel uploads that resume after errors and encrypt in transit, and from bandwidth-constrained sites through offline Transfer Appliance. For recurring updates, scheduled transfers repeat the same path at intervals, while append-only tables partitioned by date can be backed up by copying required partitions before export to handle late updates such as 7-day returns.

1.4Flexible Processing Pipelines and Compatibility

Flexible processing pipelines use fully managed services that can be swapped or scaled as transformation needs change. Data flows from Cloud Storage or Pub/Sub handover queues into the processing service and then into BigQuery, so Pub/Sub queues or Cloud Storage buckets act as the boundary between transactional systems in private environments and analytics in Google Cloud. The handover networking pattern enables ingestion into Google Cloud, and the gated egress pattern is added when analytical results must flow back to transactional systems. Sensitive data is protected before broader use with Sensitive Data Protection de-identification such as masking, encryption, and tokenization for personally identifiable information (PII).

Compatibility is preserved in stages rather than cutover. Facade views sit between BigQuery tables and downstream processes, so dashboards first read views over the source schema, then a mix of source tables and new tables with nested and repeated fields or UNIONs, and finally only evolved tables while source tables retire. Changing to nested and repeated fields aligns the model with the Dremel representation, reduces joins compared to star and snowflake schemas, and improves filtering when combined with partitioning and clustering. SQL compatibility uses GoogleSQL with SQL:2011 support plus batch translation for bulk scripts and interactive translation for ad hoc queries.

1.5Translating Objectives into Inventory, Placement, and Cost Controls

Translating business objectives into technical requirements means converting latency, residency, availability, security, and cost goals into choices for ingestion, processing, storage, and access. The work starts with an inventory of pipelines and datasets, then maps each requirement to services and deployment patterns. The data platform divides into a data storage layer where bytes or records are saved and a data computation layer that loads data, processes it, and saves results to a target.

1. Build inventory: Catalog data infrastructure, pipelines scheduled for migration, and datasets read by those pipelines.
2. Catalog properties: Record storage platforms, computation platforms, versions, custom configurations, cluster sizes, and dependencies for each pipeline.
3. Train teams: Educate teams on Google Cloud services and deployment practices.
4. Build proof of concept: Validate data transfer, configuration, and output correctness on Google Cloud.
5. Calculate total cost of ownership (TCO): Compare time, budget, people, and bandwidth for online transfer versus future operating cost.
6. Choose first workloads: Prioritize movable datasets after filtering for regulatory, security, and transformation needs.

Placement controls performance and cost because data should be stored in the same zone where it is processed to reduce latency, outbound transfer cost, and I/O. Location and replication define the core tradeoff: regional buckets and regional Persistent Disks replicate synchronously across zones in one region, while dual-region or multi-region buckets store redundantly in at least two locations with synchronous metadata writes and asynchronous data replication. For dual-region buckets, turbo replication provides a recovery point objective (RPO) of 15 minutes. Reliability targets use RTO for time until service resumes and RPO for tolerable data loss, where chained availability multiplies as tier1 x tier2 x tierN and parallel availability follows 1 - (1 - A) x (1 - B).

Designing for data and application portability means building pipelines and storage that run across Google Cloud, AWS, Azure, and on-premises without refactoring logic and without violating location rules. Portability depends on open-source APIs, portable file formats, and standard handover points, while compliance depends on regionalized resources, location constraints, access perimeters, and customer-controlled encryption. Together they let teams query where data must stay and move only results or curated datasets.

2.1Portable Beam Pipelines on Cloud Dataflow

Portable Beam pipelines on Cloud Dataflow separate pipeline definition from pipeline execution for batch and streaming. A developer writes the pipeline once with the Apache Beam software development kit (SDK), which defines sources, transforms, and sinks, and then runs that program on the Dataflow managed service for serverless execution. Data flows from source systems through Beam transforms to the destination sink, with temporary files and pipeline state artifacts created along the way. If sources, sinks, or temporary files sit outside the job's region, data can move across regions during execution.

Portability comes from the Beam runner model, where the same Beam program can run on different runners. A primary pipeline can run on Dataflow while a backup runs on a local on-premises Spark cluster using the Apache Spark Runner, which supports disaster recovery and hybrid operation without rewriting transforms. Runner support varies by operation, so compatibility must be checked in the Beam Capability Matrix before relying on a second runner. Locality is controlled by three Dataflow parameters that separate control-plane metadata from data-plane compute: the regional endpoint controls where job metadata is stored, while worker region and worker zone control where workers run and data is processed.

2.2Portable Spark Workloads on Managed Service for Apache Spark

Managed Service for Apache Spark is the fully managed service for running open-source Apache Hadoop, Apache Spark, and Apache Flink jobs without managing a permanent cluster. Legacy Spark, Hadoop, or Flink code migrates directly because the service provides managed clusters with native APIs. Instead of operating one monolithic cluster, teams create many short-lived ephemeral clusters, run a job, and delete the cluster when processing completes. This model fits periodic extract, transform, and load (ETL) work, such as a retail batch job that reads point-of-sale files from Cloud Storage, runs a Spark ETL job orchestrated by Managed Service for Apache Airflow, and writes business metrics back to the data lake.

Integration with Google Cloud storage depends on two open Hadoop connectors. The Hadoop Cloud Storage Connector lets Spark read input files from and write output files to Cloud Storage, while the Hadoop BigQuery Connector is a Java library that lets Hadoop and Spark write directly to BigQuery through abstracted InputFormat and OutputFormat classes. Transformation logic stays in portable Spark code using Spark SQL for declarative queries, Scala, Java, Python, or R for custom functions, Cloud Data Fusion plugins from the CDAP library for visual pipelines, or BigQuery SQL after loading in an extract, load, and transform (ELT) pattern with a staging table.

2.3Open Formats, Connectors, and Shared Metadata

Open file formats preserve schema and performance when data moves between engines and clouds. Apache Avro, Apache Parquet, and Apache Optimized Row Columnar (ORC) embed schema with the data, which reduces transfer complexity compared with comma-separated values (CSV) where schema must be specified separately at load time. Parquet and ORC are columnar formats commonly used in Cloud Storage data lakes because they provide efficient storage and faster access for analytical workloads. Exporting warehouse tables to Avro, Parquet, or ORC in staging storage therefore creates a vendor-neutral intermediate that Spark, Beam, and BigQuery can all read.

Metadata portability depends on Dataproc Metastore, the managed Hive metastore that stores databases, tables, columns, and partitions for data in Cloud Storage. The processing pipeline depends on both sides of this split: Cloud Storage holds the columnar data files while Dataproc Metastore holds the table definitions that make those files queryable. Because both layers use open Hive and Hadoop interfaces, Spark jobs on-premises, on another cloud, or on Managed Service for Apache Spark can share the same table view without proprietary catalog code. A portable migration therefore extracts to files, copies to Cloud Storage with Storage Transfer Service or gcloud, then transforms with existing Beam or Spark logic or loads to staging and transforms with BigQuery SQL.

2.4Federated Analytics and Secure Hybrid Connectivity

Federated analytics with BigQuery Omni runs BigQuery compute locally to data stored outside Google Cloud. Query traffic flows from BigQuery Omni to Amazon S3 or Azure Blob Storage, and results return without requiring a full dataset copy into Google Cloud. This breaks down silos because teams can join datasets that remain resident in AWS or Azure using familiar BigQuery SQL. Choose BigQuery Omni when residency, egress cost, or volume makes moving raw data impractical and in-place querying meets performance needs.

Secure connectivity controls where ingestion enters Google Cloud and where results may leave. Data flows from transactional systems in a private environment or another cloud into Google Cloud through the handover networking pattern, which uses Pub/Sub queues or Cloud Storage buckets as the trust boundary. When results must flow back, the architecture combines handover for ingestion with gated egress for controlled return traffic. For sustained high-volume exchange, the service depends on Cross-Cloud Interconnect for dedicated bandwidth, with VPN tunnels for lower-volume links, high-availability (HA) VPN over Cloud Interconnect for encrypted dedicated links, and MACsec for Cross-Cloud Interconnect encryption. Initial one-time transfer is chosen by size and bandwidth, while Interconnect is reserved for long-term high traffic.

2.5Enforcing Residency, Sovereignty, and Key Control

Enforcing data residency (opens in a new tab) and sovereignty means keeping storage, compute, and backups inside approved geographic and legal boundaries. Residency is the physical location of data plus local rules for storage, encryption, and access, while sovereignty extends to legal control requiring that PII and government data remain in a specific country under its laws. BigQuery enforces residency through regional or multi-regional dataset locations, and Cloud Storage enforces it through single-region, dual-region, or multi-region bucket locations. Organization Policy Service applies location-restricted resources constraints across organization, folders, and projects to allow or deny specific multi-regions, regions, and zones.

Access boundaries add network and identity enforcement. Google Cloud Armor (opens in a new tab) filters traffic from clients to backends by IP and geography, while VPC Service Controls creates a context-based perimeter around APIs and projects so flows to outside identities are denied even with valid credentials. When the requirement is about who can read data rather than where bits sit, Cloud External Key Manager (Cloud EKM) keeps keys in a third-party system outside Google infrastructure for BigQuery and Compute Engine, while customer-supplied encryption keys (CSEK) are provided per operation for Cloud Storage and purged afterward. Without reachable keys, data cannot be decrypted or safely written, which makes recovery time objective (RTO) the critical risk. When single-region containment is mandatory, resilience comes from a second copy with another provider or on-premises in the same area using S3 interoperability, minIO or Ceph, Airflow with gcloud storage, or Transfer service for on-premises data.

Data staging, cataloging, profiling, and discovery is the governed path that moves raw data from ingestion to trusted consumption while making it findable and safe. In Google Cloud, Knowledge Catalog (opens in a new tab) provides centralized metadata management, Cloud Storage and lakehouse catalogs provide staged storage, and Sensitive Data Protection (opens in a new tab) provides continuous discovery and de-identification. Metadata flows from source systems into catalog entries, profiles and tags attach business and risk context, and search enforces permissions before returning results.

3.1Centralized Cataloging and Secure Discovery

Centralized metadata ingestion is how Knowledge Catalog automatically collects, organizes, and indexes metadata from supported Google Cloud sources. Metadata flows from source systems such as BigQuery, Cloud Storage, and Pub/Sub into catalog entries in Knowledge Catalog, so engineers do not manually register each asset. For sources outside Google Cloud, stewards create custom entries to bring external metadata into the same index. The catalog also unifies governance across BigLake and open-format tables like Iceberg and Delta, and it supports a data mesh where decentralized domain teams own data products while discovery remains centralized.

1. Ingest: Source systems publish technical metadata to Knowledge Catalog, and Knowledge Catalog stores the entry with its lineage and schema.
2. Enrich: Stewards attach business context to the entry through tags, aspects, and glossary terms.
3. Discover: Trusted users issue semantic search against Knowledge Catalog, and Knowledge Catalog returns only entries the caller is permitted to see.

Secure search separates finding data from reading data. Users call search in Knowledge Catalog, Knowledge Catalog validates the caller against entry-level and tag-level Identity and Access Management (IAM) permissions, and only then returns matching metadata. The control plane in Knowledge Catalog manages the index, lineage, quality checks, profiles, and model artifacts, while BigQuery, Cloud Storage, and Pub/Sub enforce data-plane reads. Data scientists therefore need catalog search permissions separate from permissions to read the underlying data.

3.2Business Enrichment with Tags, Aspects, and Policy Tags

Business enrichment adds business meaning to raw technical metadata to improve discoverability and management. Data Catalog tag templates provide schematized tags that stewards attach to entries, while aspects and aspect types provide structured enrichment that travels with the entry. A business glossary establishes a standardized vocabulary so teams use the same term for the same domain concept. Sensitive-data inspection results from BigQuery can be sent to Knowledge Catalog as aspects, linking classification directly to the discoverable entry.

3.3Governed Staging with Medallion Layers and Perimeters

Governed staging moves data through progressive, policy-controlled stages from raw ingestion to business-ready consumption. The bronze layer holds raw ingestion from sources such as Amazon S3 Parquet files, Cloud Storage event logs, and live AlloyDB for PostgreSQL data accessed through BigQuery connections. The silver layer holds cleansed and conformed data after large-scale joins, windowing, and behavioral aggregations, often executed by Managed Service for Apache Spark with Lightning Engine. The gold layer holds curated business aggregations for analytics and AI, and the documented best practice is to use BigQuery for this gold layer to maximize query performance and concurrency.

This medallion pattern maps to governed lake and zone concepts where raw, curated, and refined zones isolate untrusted landing data from trusted products. A separate landing zone and ingestion perimeter protect other workloads from incoming batch or streaming data while it is categorized and de-identified. A confidential or data perimeter then isolates sensitive or encrypted content, while a governance perimeter separates key management, catalog management, and logging from warehouse data itself. Movement between stages is therefore both a data-quality transition and a security-boundary crossing.

3.4Lakehouse Catalog Hierarchy and Open Tables

The Lakehouse runtime catalog is the top-level regional metadata service that centralizes discovery across Spark, Trino, and BigQuery. The Lakehouse runtime catalog hosts individual catalog instances and implements the open-source Apache Iceberg REST Catalog API to manage namespaces and tables. Organization follows the Project.Catalog.Namespace.Table naming structure, where a catalog is a logical metastore container backed by Cloud Storage warehouse locations and its region matches the bucket's region. A namespace groups tables like a database or BigQuery dataset and uses the same region as its parent catalog.

Key governance properties include credential vending where the catalog generates short-lived reduced-scope credentials instead of granting direct bucket access, namespace-level IAM boundaries, no support for nested namespaces, custom table paths nested under the parent namespace path, and random suffixes on table locations to prevent conflicts. Table choice determines where metadata and data live and which engines interoperate, with Lakehouse-managed Iceberg tables in Cloud Storage offering open compatibility with Spark, Trino, and BigQuery. Only Iceberg V2 tables, which are generally available, and V3 tables, which are in Preview, are supported, so V1 tables must be upgraded to V2 before use.

The governed query flow preserves policy checks before storage reads. A user submits SQL to Spark, Trino, or BigQuery, the engine requests table metadata from the Lakehouse runtime catalog, the catalog validates IAM and fine-grained policies, the catalog returns metadata and optionally a short-lived token, the engine reads data files directly from Cloud Storage, and the engine processes and returns results. Because retrieval goes directly from engine to Cloud Storage after the check, storage permissions, catalog IAM, and token scope must align.

3.5Continuous Profiling, Classification, and Column Security

Continuous discovery with Sensitive Data Protection automatically finds, classifies, and reports on new assets as they appear at organization, folder, or project level. When a new project uploads a dataset, the service generates data profiles without manual inspection jobs. Each profile contains sensitivity level, data risk level, infoType coverage, and location, with reports broken down by risk, infoType, and location. Profiles can be exported to Security Command Center, Google Security Operations, BigQuery for history, and Knowledge Catalog as aspects.

Classification is driven by infoTypes and record transformations that identify sensitive patterns in structured and unstructured data. For structured data, record transformations match specific fields such as credit card numbers and PINs in a de-identification template applied by Dataflow. For unstructured data, detectors such as PHONE_NUMBER, US_SOCIAL_SECURITY_NUMBER, and DATE_OF_BIRTH scan free-form content. A column with a high free-text score and multiple infoTypes signals hidden PII in notes fields, which requires an on-demand inspection job on the BigQuery table or Cloud Storage bucket to pinpoint values before de-identification. Before restricting profiled tables, the Sensitive Data Protection service agent must hold read permission or profiling returns an error.

A BigQuery taxonomy is a hierarchical classification scheme whose policy tags enforce column-level security. Access is granted through the Fine-Grained Reader role on the tag, and untagged columns remain available to anyone with warehouse access. In the example hierarchy, 3_Confidential for credit card numbers grants access to 2_Private for first names and 1_Sensitive for credit limits, 2_Private grants 2_Private and 1_Sensitive but not 3_Confidential, and 1_Sensitive grants only 1_Sensitive. Policy tags continue to block reads even after re-identification, so re-identification in the confidential perimeter does not bypass authorization.

3.6De-identification, Lifecycle, and Risk Remediation

De-identification preserves analytical utility while hiding raw identifiers through masking, tokenization with re-identifiable surrogates, pseudonymization, generalization such as bucketing, and character masking. The pipeline flows from Cloud Storage or Pub/Sub through Dataflow, where Sensitive Data Protection tokenizes data tagged as confidential, then into separate BigQuery warehouses for non-confidential and confidential data. Cryptographic control depends on Cloud Key Management Service (Cloud KMS) with Cloud HSM managing customer-managed encryption keys (CMEKs), where each CMEK resides in the same location as its resource and rotates by default every 30 days. At query time, BigQuery remote functions and GoogleSQL AEAD functions de-identify or re-identify individual values without copying full tables.

Remediation starts from data risk and sensitivity in each profile and follows three outcomes: expected and protected data is published for monitoring, unexpected or unprotected data is locked down or de-identified, and unclear findings trigger deeper inspection. For BigQuery assets flagged with high data risk, meaning sensitive data without additional protections, controls include applying a policy tag to restrict reads, creating a de-identified or masked copy for analysts, enabling automatic tagging and setting risk to Low only after verification, or removing the column or table if unneeded. Integrity across stages also depends on VPC Service Controls perimeters, firewall deny-by-default rules, Admin Activity and Data Access audit logs, Cloud Storage lifecycle rules for minimization, and operational practices such as session templates, custom Spark batch names for logging, and diagnostic bundles with retained driver and executor logs.

Key Facts

• BigQuery separates storage in Colossus and Capacitor from compute in Dremel slots managed by Google, with a default limit of 2,000 slots per project, so storage and query capacity scale independently.
• Initial warehouse migration follows Extract to vendor-neutral files, Upload to Cloud Storage, Load to BigQuery, and Validate with the Data Validation Tool.
• The Dataflow regional endpoint controls where job metadata is stored, while worker region and worker zone control where workers run and data is processed.
• BigQuery Omni runs BigQuery compute locally to Amazon S3 or Azure Blob Storage so federated queries return results without copying full datasets into Google Cloud.
• Knowledge Catalog enforces entry-level and tag-level IAM checks before returning search results, while BigQuery policy tags restrict who can read confidential columns even after re-identification.