The Multi-Cloud Data Architect Playbook: AWS, GCP and Azure

Multi-cloud architecture is valuable when it meets a real business, regulatory, resilience or platform requirement. It is not automatically more resilient or cheaper. Every additional provider brings identity, networking, governance, skills, data movement and support work. Start by deciding which outcomes matter, then place each workload where it can meet them with a clear owner and measurable service level.

1. Start with a workload-led architecture

For a financial-services example, a lender wants to reduce delinquency and improve collections efficiency. The data platform must combine account, payment, customer interaction and decision data; provide trusted metrics; support predictive models; protect personal information; and deliver information to BI, CRM and decisioning systems.

Map the path end to end: source systems and APIs → ingestion and CDC → raw storage → validated/refined data → curated models and features → analytics, ML and operational consumers. For each data product, document owner, freshness, completeness, access class, lineage, retention, recovery and cost target. Define a system of record so a dataset is not independently copied and redefined across all three clouds.

• AWS: S3, Glue, Lake Formation, DMS, DataSync, Kinesis/MSK, Athena, Redshift, EMR, Step Functions, IAM, KMS, CloudTrail and CloudWatch can cover storage, integration, analytics, control and operations.
• GCP: Cloud Storage, Pub/Sub, Dataflow, BigQuery, Composer, Vertex AI, IAM/VPC and Knowledge Catalog (formerly Dataplex Universal Catalog) cover ingestion, processing, analytics, ML and governance.
• Azure: ADLS Gen2, Data Factory, Databricks, Synapse, Microsoft Fabric/OneLake, Azure ML, Entra ID, VNet and Microsoft Purview cover integration, lakehouse/warehouse, ML, identity and governance.

Service names are not an architecture. Select based on workload, regional availability, source connectivity, data gravity, team skills, SLAs, cost model, governance and exit or portability requirements.

2. When to choose Athena, Dremio, Starburst or Snowflake analytics

• Amazon Athena: Choose serverless SQL over data in S3 for ad hoc exploration, lightweight data-lake analytics and workloads where scans and query frequency are controlled. Organize data in columnar formats, use query-aligned partitions, and measure bytes scanned. It is not a substitute for every low-latency, high-concurrency warehouse workload.
• Dremio: Consider it when a SQL lakehouse/query layer over open lake data and multiple sources, with Reflections to accelerate repeated query patterns, fits the requirement. Validate freshness, reflection refresh/storage cost, connectors and governance in the intended deployment.
• Starburst: Consider its Trino-based platform when federated SQL across multiple catalogs and sources is central and teams want to query data in place. Benchmark pushdown, network transfer, source concurrency, connector capabilities and failure behavior; federation does not remove source-system limits.
• Snowflake: Choose when managed SQL analytics, warehouse compute, governed sharing and existing team experience fit the workload. Its standard tables use automatic micro-partitioning; test pruning and query profiles before adding a clustering key, which may add maintenance cost.

Decision example: An analyst occasionally explores curated S3 Parquet files → Athena may be a simple fit. A BI estate needs fast repeat queries over Iceberg and federated sources → evaluate Dremio or Starburst with a real query benchmark. A large governed SQL warehouse with managed compute and sharing needs → evaluate Snowflake, Redshift or BigQuery against the same workload and total-cost model. A single platform need not serve every workload, but every second engine needs a clear business case.

3. Choose a data model that matches the questions

• Conceptual model: Business entities and their relationships, such as customer, account, payment and collection case.
• Logical model: Attributes, keys, cardinality and business rules independent of a specific database.
• Physical model: Platform-specific tables, data types, storage layout, partitions/clustering, distribution, indexes and access policies.
• Star schema: A central fact table at a declared grain joined to descriptive dimensions. Example: one row per payment transaction, joined to date, account, customer and channel dimensions. Often easy for BI users to understand.
• Snowflake schema: A normalized dimensional design where dimension hierarchies are split into related tables, such as product → category. It can reduce repeated attributes but adds joins and user complexity.
• Vault model: A historized integration model using hubs for business keys, links for relationships and satellites for descriptive history. Useful for traceability and multi-source change history; it usually needs downstream marts for simple reporting.

Pick by reporting needs, history requirements, source volatility and operating skills. Define the fact grain first. For changing customer segments, SCD Type 1 overwrites when history is not needed; SCD Type 2 adds an effective-dated version when historical “as-of” reporting matters. Keep source keys, surrogate keys, effective start/end timestamps and a current-row indicator consistent across the chosen platform.

4. Governance and security across clouds

Establish one set of enterprise policy outcomes—classification, least privilege, encryption, lineage, data quality, privacy, retention and audit—then implement them with provider-specific controls. GCP IAM/VPC and catalog controls, Azure Entra ID/RBAC/VNet/Purview/Fabric governance, and AWS IAM/KMS/Lake Formation/Glue Catalog each have distinct behavior. Do not assume a grant in one catalog secures direct storage access or follows data into another platform.

For the lender example, classify national IDs and phone numbers as restricted, minimize the fields copied into analytical zones, mask or tokenize where appropriate, and publish a de-identified feature product for broad analysis. Use separate human and workload identities, private connectivity where required, managed secrets, encryption in transit and at rest, audit logging, and regular access reviews. Maintain lineage from source to features, reports and model decisions.

Set quality checks for completeness, uniqueness, valid ranges, referential integrity, freshness and source-to-target reconciliation. Failed critical checks should quarantine or block publication, alert an owner and have a replay/remediation path. Catalogs, dashboards and AI copilots are useful only when ownership, policy and quality processes are operating.

5. Performance and cost tuning: partitioning, clustering and more

First identify the expensive queries, data scans, slow stages, source constraints and concurrency peaks. Tune to observed access patterns, then benchmark and compare before/after. Avoid copying techniques blindly between platforms because their physical storage models differ.

• S3/Athena: Use Parquet or ORC and compression when suitable; partition by common bounded filters such as date or region; avoid tiny files and excessive partition cardinality. Filter on partition columns and measure scanned bytes. Partition projection or Glue partition indexes may help specific high-partition workloads.
• BigQuery: Partition on a useful date/timestamp or integer-range column when filters prune substantial data; cluster on commonly filtered/aggregated columns. Too many small partitions increase metadata overhead. Clustering can reduce scanned blocks but exact bytes are not always estimable before execution.
• Redshift: Use automatic table optimization or choose sort/distribution design from measured query patterns. Sort keys can help range filters; distribution can reduce join redistribution but skew can make one node a bottleneck. Redshift does not use user-managed intra-table partitions like S3/Athena.
• Snowflake: Micro-partitioning and metadata pruning are automatic. Consider an explicit clustering key only on large tables with measured poor pruning and recurring query benefit; account for reclustering cost.
• Databricks/lakehouse: Compact small files, use supported table layout and clustering options based on query patterns, and inspect Spark shuffle, skew and spill metrics. Do not over-partition by high-cardinality keys.

Cost levers include incremental loads, selective columns, pruning, compute right-sizing and auto-stop where available, storage lifecycle, workload isolation, query caching/materialization where justified, and reducing cross-cloud egress. Include engineering, operations, licensing, retrieval, backup, restore and support costs in ROI; storage alone is not total cost.

6. Cloud migration and delivery roadmap

1. Discover: Inventory apps, data, integrations, owners, volumes, SLAs, security, compliance and dependency links. Replace assumptions with measured facts.
2. Decide: Choose retain, retire, rehost, replatform, refactor or replace per workload. Explain why data lives in a given cloud and which capabilities must be shared.
3. Foundation: Establish landing zones, identity, network, logging, policy, key management, environments, IaC and support ownership before production waves.
4. Pilot: Migrate a bounded low-risk data product end to end. Test security, data reconciliation, performance, recovery, user acceptance and operational handover.
5. Wave: Group workloads by dependency and business window. Use CDC/incremental synchronization when appropriate; rehearse final sync, cutover, validation and rollback.
6. Optimize: Review cost, performance, incidents, data quality and outcomes after each wave. Retire old platforms only after owners approve retention and recovery.

7. Scope creep: keep decisions fair and visible

Agree a baseline scope, deliverables, acceptance criteria, dependencies, assumptions and change process at kickoff. When a new request appears, assess value, effort, data/security impact, dependencies, risk and schedule. Present choices: swap it for an existing item, add capacity/funding, or move the date. Record the decision and update the roadmap, estimate and acceptance criteria. Keep an explicitly prioritized backlog for useful work outside the committed release.

Example: a request to add another CRM source may affect connectors, PII controls, reconciliation and report testing. Show those impacts and ask the sponsor to prioritize it against the existing release goals. Avoid saying “yes” before the engineering and governance impact is known.

8. Negotiating a late delivery with the client

When the team misses a roadmap milestone, communicate early with evidence rather than waiting for the status meeting. Confirm the facts, identify the critical-path blocker and its business impact, and distinguish completed work from remaining uncertainty. Avoid blaming individuals; focus on decisions, dependencies, capacity and recovery.

Bring a recovery brief with three options: (A) keep date and reduce scope to the highest-value acceptance criteria; (B) keep scope and move the date with a revised dependency-based plan; or (C) add approved capacity, explaining onboarding time, cost and risk. State the recommended option, actions needed from the client, owners and next checkpoint. Re-estimate after the blocker is understood; do not offer a new date based only on optimism.

For a collections launch, for example, if source reconciliation is not signed off, explain which reports or decisions are at risk. Offer a limited pilot with validated data and delayed expansion, or a revised full cutover after reconciliation. Agree rollback and acceptance in writing.

9. Common multi-cloud challenges and mitigations

• Data egress and duplicated copies: Keep compute near data, filter/compress transfers, track egress by product and establish a source of truth.
• Identity and policy mismatch: Define common policy intents, map provider-specific roles and test end-to-end user journeys.
• Network complexity: Standardize address plans, DNS, private connectivity, routing and egress review; test throughput and failover.
• Different service limits and SLAs: Validate quotas, regional availability and failure modes; define end-to-end platform SLAs rather than adding component marketing targets.
• Skills fragmentation: Establish a platform team/CCoE, training plans, supported patterns and on-call ownership.
• Governance gaps in federation: Test source-level authorization and query pushdown; federation is not permission propagation unless explicitly supported.
• Scope and stakeholder misalignment: Use decision logs, change control, visible dependencies, demos and business acceptance criteria.

10. Architect’s practical checklist

• Can we name the business outcome and baseline?
• Is every workload placed in a cloud for a documented reason?
• Are source, owner, grain, quality, freshness, privacy and retention defined for every critical data product?
• Have we tested security, lineage, data reconciliation, recovery and realistic workload performance?
• Do we know the cost per workload or business outcome, including egress and operations?
• Are scope changes, schedule risks, decisions, client dependencies and rollback options explicit?
• Can the team operate and support the platform after the architect leaves?

A strong multi-cloud design is not the one with the most services. It is the smallest governed set of capabilities that meets business outcomes, can be operated by the team and has evidence for its security, performance, cost and recovery assumptions.

Official references

• Athena data optimization · Redshift table design · Redshift distribution styles
• Dremio Reflections and acceleration · Starburst documentation · Snowflake micro-partitions and clustering
• BigQuery partitioning · BigQuery clustering · Google Cloud landing zones
• Azure landing zone design · Microsoft Fabric overview · Purview with Fabric

Prepared for Khojdo.in.