Migrate Google BigQuery to an open lakehouse on AWS with Apache Iceberg

Enterprises more and more measure their readiness for AI by the readiness of their information, and that always means shifting to an open lakehouse the place information stays open and transportable. The organizations shifting quickest are those whose information offers them the pliability to decide on the engines and fashions that match every workload. A tightly coupled, single-engine cloud information warehouse begins to carry them again. A technology of analytics instruments was constructed inside vertically built-in programs, the place the storage format, the catalog, and the question engine have been fused into one vendor’s runtime. That design traded openness for comfort, and as generative AI, multi-engine analytics, and cross-team information sharing grow to be the norm, the price of that commerce is rising. More and more, enterprises are selecting the alternative: an open, modular structure the place governance is outlined as soon as and enforced in every single place. That is the muse for AI-ready information. It isn’t a brand new structure to undertake, however an open lakehouse that retains your information yours.

On this submit, we clarify why an open lakehouse structure on AWS is the pure subsequent step for organizations that wish to future-proof their information structure moderately than re-architect onto one other closed system. We then current the goal structure on AWS for migrating BigQuery workloads. Lastly, we offer a step-by-step migration information overlaying workload evaluation, schema and question conversion, information motion methods, and post-migration optimization that will help you transition from BigQuery to an open lakehouse on AWS with confidence.

Why organizations are shifting towards an open lakehouse

Open information, and the liberty to run engine of your selection. When information is held in a vendor’s proprietary format, it turns into troublesome to make use of with different engines, instruments, or AI fashions outdoors the seller’s atmosphere. Openness is what breaks it, however it is available in two levels which might be price distinguishing. On the base is interoperability, which suggests many engines can attain the identical information and it’s quick turning into desk stakes now that even proprietary warehouses federate to open codecs and question tables the place they sit. Past that’s portability: the liberty to take your information, your selection of engine, and your selection of AI mannequin elsewhere with out re-architecting. It’s the extra sturdy property an open lakehouse is constructed to present you. Apache Iceberg is an open desk format that removes these limitations: Iceberg appropriate engines can learn and write the identical tables concurrently at petabyte scale whereas sustaining ACID compliance. On AWS, Apache Iceberg is a first-class citizen. Amazon Redshift, Amazon Athena, Amazon EMR, and AWS Glue all function on one ruled copy of the info in Amazon Easy Storage Service (Amazon S3). As a result of storage is decoupled from compute, you match the engine to the workload similar to Athena for interactive SQL, Amazon Redshift for high-performance warehousing and enterprise intelligence (BI), Amazon EMR for large-scale transformation, and Amazon SageMaker AI for machine studying (ML) with out duplicating information or constructing extract, rework, and cargo (ETL) pipelines to shuffle it between programs. Interchangeability turns into a property of the structure moderately than one thing you engineer round.

Governance outlined as soon as, enforced throughout each engine. Openness doesn’t imply giving up management. On AWS, a single governance layer spans each engine that touches the info. The AWS Glue Information Catalog exposes an Apache Iceberg REST catalog endpoint that implements the Apache Iceberg REST Catalog open specification. AWS Lake Formation applies fine-grained entry controls on the database, desk, column, row, and cell stage, outlined as soon as and enforced persistently whether or not a knowledge engineer runs a Spark job on Amazon EMR, an analyst queries by Athena, or a knowledge scientist trains a mannequin in SageMaker AI. For discovery and sharing, you should use Amazon SageMaker Unified Studio to search out, perceive, and request entry to ruled information throughout the property. As a result of the catalog follows an open specification moderately than a single vendor’s runtime, governance travels with the info as a substitute of locking it in.

A basis that’s AI-ready and mannequin agnostic. The rationale all this issues now’s AI. Generative and agentic workloads are solely pretty much as good as the info they will attain, and so they carry out finest when information stays in open codecs you absolutely management. However openness can’t cease on the information format. The intelligence layer issues too: the AI mannequin, the semantic catalog, the enrichment layers, and the brokers. An open basis retains each layers open. A single ruled copy of open information sits in Amazon S3, and the identical tables that serve BI and interactive analytics additionally feed Amazon SageMaker AI for mannequin coaching and Amazon Bedrock and Amazon Bedrock AgentCore for generative and agentic work. You select the muse mannequin (FM) that most closely fits your wants, with no additional copies to maneuver or safe.

Goal structure: Open lakehouse on AWS

The next structure diagram represents the really helpful goal state for organizations migrating BigQuery workloads to AWS and is very nicely suited to clients wanting to construct a unified, open information basis. It’s constructed on Apache Iceberg because the foundational layer, with purpose-built consumption engines chosen per use case: Amazon Athena for interactive analytics, Amazon Redshift for BI reporting and dashboarding, and Amazon SageMaker AI for ML and agentic workloads, all on one ruled copy of the info that stays open, interoperable, and transportable.

The structure follows the stream of knowledge from left to proper: supply programs are ingested into an open lakehouse on Amazon S3, refined by medallion layers, and served to a various set of customers similar to BI instruments, AI brokers, and ML programs. It rests on 4 foundational pillars: open storage, purpose-built analytics, unified governance, and an built-in expertise.

Determine 1: Open lakehouse goal structure on AWS, from supply ingestion by the Amazon S3 Apache Iceberg storage layer to analytics, BI, and ML customers

Ingestion

That is the place all of it begins. You could carry information from quite a lot of sources similar to databases, software program as a service (SaaS) purposes, streaming programs, on-premises programs, IoT units into your information lake.

• Bulk migration: For the preliminary BigQuery migration and different giant batch transfers, use BigQuery’s EXPORT DATA assertion to export tables to Google Cloud Storage (GCS) in Parquet format. Then use AWS DataSync to maneuver Parquet information from Google Cloud Storage to Amazon S3. For extra info, see Step 4.
• Zero-ETL: For SaaS and operational sources similar to Google Analytics, Salesforce, SAP, ServiceNow, and HubSpot, Zero-ETL integrations land information straight within the lakehouse with out constructing or sustaining customized pipelines.
• Change information seize (CDC): For operational information shops, Debezium connectors working on Amazon MSK Join stream row-level adjustments into the lakehouse to maintain it repeatedly present.
• Streaming: For real-time sources, Amazon Kinesis Information Streams and Amazon Managed Streaming for Apache Kafka (Amazon MSK) present streaming storage that feeds straight into the uncooked layer.

Storage layer: Amazon S3 with Apache Iceberg

Amazon S3 serves because the central storage layer, offering nearly limitless scalability, 99.999999999% (11 nines) of knowledge sturdiness, and a number of storage lessons, so you possibly can match price to the entry sample. Information is saved in Apache Iceberg desk format, which brings ACID transactions, schema evolution, time journey, and partition evolution to your information lake. Amazon S3 Tables, a functionality of Amazon S3, offers a completely managed Apache Iceberg expertise with automated compaction, snapshot administration, and optimization eradicating the undifferentiated heavy lifting of desk upkeep.

Information is progressively refined by a medallion structure, with every layer saved as Apache Iceberg tables on S3:

• Bronze/Uncooked: Ingested information lands right here as-is, in Apache Iceberg format both on absolutely managed Amazon S3 Tables or as self-managed Apache Iceberg tables on Amazon S3.
• Silver/Trusted: Silver layer serves as single supply of fact. AWS Glue and Amazon EMR Spark jobs rework, clear, and conform the uncooked information into trusted, query-ready Apache Iceberg tables. Amazon Athena offers serverless, interactive analytics straight in opposition to this layer for interactive exploration and information discovery.
• Gold/Serving: Amazon Redshift Serverless serves curated, business-ready information for dashboarding and reporting. Amazon Redshift is a first-class Apache Iceberg engine able to managing the complete Apache Iceberg desk lifecycle (see Utilizing Apache Iceberg tables with Amazon Redshift). Information may also be natively saved into Amazon Redshift Managed Storage for workloads that require elevated efficiency for dashboarding and reporting use circumstances.

Governance: Unified throughout each engine

A unified basis depends upon governance that’s outlined as soon as and enforced in every single place. The AWS Glue Information Catalog serves because the central metadata repository, and AWS Lake Formation offers fine-grained entry management on the database, desk, column, row, and cell stage, enforced persistently whether or not information is accessed by Athena, Amazon Redshift, EMR, or SageMaker AI. AWS Glue Information High quality, a functionality of AWS Glue, validates information because it strikes by the layers and Amazon SageMaker Unified Studio offers a single built-in improvement atmosphere for information discovery, SQL analytics, visible ETL, and synthetic intelligence and machine studying (AI/ML) improvement all ruled by the identical Lake Formation permissions.

Customers: One lakehouse, many workloads

As a result of information sits in open Apache Iceberg format underneath unified governance, a broad set of customers can entry a single supply of fact with out information motion:

• BI instruments: Amazon Fast Suite, Looker, Energy BI, and Tableau join for dashboarding and reporting.
• AI brokers: Agentic tooling similar to MCP-based shoppers, Kiro, and Claude, together with Amazon Bedrock AgentCore, can cause over ruled lakehouse information.
• ML/SageMaker: Amazon SageMaker AI trains fashions and serves inference straight in opposition to lakehouse information.
• Amazon SageMaker Unified Studio offers a single built-in improvement atmosphere (IDE) for information exploration, SQL analytics, visible ETL, and AI/ML improvement. For organizations accustomed to a single-pane expertise, Unified Studio addresses the priority of managing a number of companies by offering one place to find information, run queries, construct dashboards, and develop ML fashions, all ruled by the identical Lake Formation permissions.

Information interoperability

The Apache Iceberg REST catalog endpoint of the AWS Glue Information Catalog, a part of Amazon SageMaker Lakehouse, permits interoperability with third-party engines. Companions similar to Snowflake and Databricks can learn and write the identical Apache Iceberg tables by that endpoint, so that you select one of the best software for every job with out duplicating information.

Cross-cutting layers: Orchestration, safety, and observability

Two layers span the whole lakehouse moderately than sitting at any single stage. An orchestration layer coordinates how information strikes by the medallion layers: Amazon Managed Workflows for Apache Airflow (Amazon MWAA), AWS Step Features, and Amazon EventBridge schedule and sequence ingestion, transformation, and cargo jobs throughout Bronze, Silver, and Gold.

A safety and observability layer applies throughout each engine and layer. AWS Id and Entry Administration (IAM) and AWS Key Administration Service (AWS KMS) present authentication, authorization, and encryption, whereas Amazon CloudWatch, AWS CloudTrail, and AWS Price Explorer present monitoring, audit logging, and value visibility throughout the lakehouse.

Step-by-step migration information

Migrating from BigQuery to an open lakehouse on AWS follows a structured, phased strategy. Every part builds confidence incrementally, so you possibly can validate worth earlier than committing additional.

Step 1: Assess and uncover

Start by evaluating your present BigQuery atmosphere:

• Stock workload: Catalog all datasets, tables, views, saved procedures, scheduled queries, and downstream customers (BI instruments, purposes, ML pipelines). Identify the customers that the majority usually want a call: Looker Studio, Linked Sheets, scheduled queries, and any manufacturing service studying BigQuery straight.
• Classify question complexity: Analyze your SQL workloads to establish customary ANSI SQL (which migrates straight), BigQuery-specific syntax (similar to STRUCT, ARRAY, UNNEST, and JavaScript UDFs), and procedural logic in saved procedures.
• Map information dependencies: Doc which instruments and purposes learn from and write to BigQuery. Establish upstream information sources (streaming, batch, CDC) and downstream customers (dashboards, experiences, ML fashions).
• Assess information volumes: File lively storage, long-term storage, information progress charges, and every dataset’s storage billing mannequin (logical or bodily) with its measured bodily bytes. The compressed bodily dimension generally is a small fraction of the logical dimension and adjustments the storage price estimate materially. This informs your S3 storage class technique and value modeling.
• Consider governance: Doc present entry controls, row-level safety insurance policies, and audit necessities. Map these to Lake Formation capabilities.

The output of this part is a migration readiness report with per-table effort scoring, question complexity classification, and a really helpful migration sample.

AWS account groups and companions have metadata-driven evaluation accelerators. They learn solely BigQuery metadata (schemas, INFORMATION_SCHEMA job historical past, view definitions, and routine signatures), not uncooked information. From that metadata, they produce a price comparability with the projected financial savings, per-table migration effort scoring, a complexity classification for each view, routine, and noticed question, and a map of each software studying from or writing to BigQuery with how each reconnects on AWS.

Attain out to your AWS account crew for an preliminary evaluation of shifting BigQuery workload to an open lakehouse on AWS.

Step 2: Design goal structure

Based mostly in your evaluation findings, design the target-state structure:

• Storage design: Outline your S3 bucket construction with a multi-layer format (uncooked, cleaned, and aggregated) aligned with information lifecycle finest practices. For Apache Iceberg storage, we suggest Amazon S3 Tables for absolutely managed Apache Iceberg with automated desk upkeep and optimization and general-purpose S3 buckets with self-managed Apache Iceberg for circumstances that want granular management over desk format and compaction.
• Partitioning technique: Design Iceberg partition schemes aligned to precise question entry patterns. Use Iceberg’s native partitioning transforms (bucketing, time slicing) for optimum question pruning.
• Engine choice: Select the engine from the workload you measured in Step 1. Amazon Athena matches estates that scan a average quantity per day and tolerate a couple of seconds of latency whereas Amazon Redshift matches estates with sustained excessive concurrency, sub-second dashboard targets, or a day by day scanned quantity the place heat compute is cheaper than paying per terabyte scanned. For big-scale information transformations, place Amazon EMR (together with Amazon EMR Serverless) for complicated, customized open supply frameworks like Spark and Hive, or AWS Glue for serverless, integration-heavy ETL pipelines.
• Governance mannequin: Design your AWS Lake Formation permission mannequin to implement granular database, desk, column, and row-level entry insurance policies. Confirm the structure plans for multi-tenant entry patterns if relevant.
• Ingestion structure: Design information ingestion pipelines. Choices embrace Zero-ETL integrations for operational information sources, Amazon Information Firehose for streaming information, and AWS Glue for batch ETL.

Step 3: Convert and validate (POC)

Decide a proof-of-concept slice, a handful of consultant tables and the dashboards that learn them, and convert BigQuery SQL, saved procedures, and information pipelines to their AWS equivalents.

• Schema conversion: A BigQuery dataset turns into an AWS Glue database when Athena is the engine, or an Amazon Redshift schema when Amazon Redshift is. The BigQuery mission maps to your AWS account with its top-level namespace being the AWS Glue Information Catalog for Athena or to an Amazon Redshift database.
• SQL conversion: Convert BigQuery SQL dialect to ANSI SQL or engine-specific syntax. Widespread conversions embrace:
  • BigQuery STRUCT to Iceberg struct and ARRAY to Iceberg record, and BigQuery JSON maps to a string column queried with JSON perform when Athena is the engine and information is saved in Apache Iceberg format. For Amazon Redshift, the advice is to construct the gold layer which is optimized for reporting use circumstances the place information is flattened out for high-performance queries. If it is advisable retailer nested information inside Amazon Redshift, use the SUPER information sort.
  • On Amazon Redshift, the goal depends upon the place the info lands: for information saved natively in Amazon Redshift, BigQuery JSON maps to the SUPER sort (queried with PartiQL), and STRUCT/ARRAY are represented inside SUPER.
  • BigQuery MERGE statements to Athena or Amazon Redshift MERGE INTO. Select copy-on-write for tables up to date in bulk and merge-on-read for steadily up to date ones.
  • BigQuery JavaScript UDFs to a SQL expression the place doable, in any other case an AWS Lambda-backed UDF on Athena (USING EXTERNAL FUNCTION) or Amazon Redshift.
  • BigQuery scheduled queries to Amazon EventBridge Scheduler working Athena queries, Amazon Redshift scheduled queries in Question Editor v2, or dbt for something that may be a mannequin. Chains of schedules grow to be AWS Step Features.
• Pipeline conversion: Rebuild ETL pipelines utilizing AWS Glue, dbt (with dbt-athena or dbt-redshift adapters), or Apache Spark on EMR.
• Validation: Run transformed workloads in opposition to the slice. Evaluate row counts and checksums per desk, question outcomes for the dashboards on the slice, and run occasions in opposition to the agreed goal, between BigQuery and AWS, to confirm correctness.

BigQuery provides two pricing fashions for working queries. On-demand pricing is a scan-based mannequin that expenses $6.25 per terabyte of knowledge scanned, whereas capacity-based pricing is a compute-based mannequin that expenses for the compute capability used to run queries. Amazon Redshift can be compute-based, so if you happen to at present run BigQuery on demand, its scan-based pricing will not be straight similar to Amazon Redshift’s compute-based pricing. As a result of the 2 fashions differ, benchmark a consultant mixture of queries on the goal engine moderately than a single question sort.

A superb follow is to run three tiers of queries and examine whole price and efficiency in opposition to your present BigQuery baseline: light-weight queries for easy lookups and aggregations, medium queries for average joins and transformations, and heavyweight queries for complicated joins, window features, and enormous scans. To construct an correct, workload-representative whole price of possession (TCO), multiply every question tier by how usually it runs, add storage prices, and produce a month-to-month determine you possibly can examine on to your present month-to-month BigQuery invoice.

Step 4: Migrate information

Execute bulk information migration from BigQuery to Amazon S3 with Apache Iceberg:

1. Use BigQuery’s EXPORT DATA assertion to export tables to Google Cloud Storage (GCS) in Parquet format. For big datasets, partition exports by date or key ranges to parallelize the switch.
2. Use AWS DataSync to maneuver Parquet information from Google Cloud Storage to Amazon S3. DataSync reads Google Cloud Storage straight by its S3-compatible XML API and writes the objects to your S3 bucket, with scheduling and incremental transfers in-built. For many transfers you deploy a DataSync agent, both as a digital machine in Google Cloud (closest to the supply bucket) or as an Amazon Elastic Compute Cloud (Amazon EC2) occasion in your digital personal cloud (VPC). DataSync Enhanced mode transfers between Google Cloud Storage and Amazon S3 run with out an agent and provide greater efficiency and scalability.
3. Create the Apache Iceberg tables in Athena or Amazon S3 Tables and cargo them from the exported Parquet with CREATE TABLE AS SELECT or INSERT INTO, or register the present information with Spark’s add_files process on Amazon EMR or AWS Glue.
4. Throughout the transition interval, implement incremental information synchronization to maintain AWS information present whereas BigQuery stays lively. Use BigQuery’s change historical past features (APPENDS and CHANGES) for scheduled delta exports, change information seize (CDC) patterns, or Amazon AppFlow.
5. Apply for Google’s information switch price waiver earlier than the majority export begins: Google waives egress charges for purchasers leaving Google Cloud who request it by help and full the export inside the agreed window.

Step 5: Cutover and go-live

Execute manufacturing cutover with minimal disruption:

• Parallel run: Run workloads on each BigQuery and AWS concurrently for a validation interval. Evaluate outputs to verify information consistency and question correctness.
• Redirect customers: Each client takes one among three paths:
  • Re-point: On this case, the software stays the identical, solely its connection adjustments from BigQuery to Athena or Amazon Redshift (dbt, Fivetran, Tableau, Energy BI, Looker). That is normally a JDBC/ODBC or connector replace. Athena and Amazon Redshift each use the AWS Glue Iceberg REST catalog (IRC) endpoint to find the bodily Parquet information on Amazon S3. For extra info, see Querying Apache Iceberg tables in Athena and Querying Apache Iceberg tables in Amazon Redshift.
  • Rebuild: On this case, the workload has no direct AWS connection and is re-implemented on an AWS counterpart (BigQuery scheduled queries → Amazon Athena Saved Queries orchestrated by AWS Step Features, or managed by Amazon EventBridge to run an ETL pipeline on a schedule, Dataform → dbt (utilizing the Athena or Amazon Redshift connector), BigQuery ML → SageMaker AI or Amazon Redshift ML), Google Analytics 4 and Linked Sheets → Amazon AppFlow native connectors ingest the info (Google → AWS) into Amazon S3 or Amazon Redshift on a schedule. From S3, an AWS Glue or Athena step lands it in Apache Iceberg.
  • Relay: On this case, the software stays on Google Cloud for now and reads AWS information synced again to it (Linked Sheets, GA4 uncooked export).

Looker Studio, Linked Sheets, and scheduled queries are the customers that the majority usually want an specific resolution on which path to take.

• Validate end-to-end: Affirm that each one dashboards, experiences, scheduled jobs, and ML pipelines produce right outcomes.
• Decommission: After profitable validation, decommission BigQuery assets and terminate cross-cloud information synchronization.

Step 6: Optimize and increase

Put up-migration, optimize your lakehouse for efficiency and value:

• Question optimization: On general-purpose buckets with self-managed Apache Iceberg, tune file dimension targets, compaction frequency, and kind orders to the precise question patterns whereas Amazon S3 Tables handles compaction and snapshot expiry routinely. Allow Athena question end result reuse and Amazon Redshift materialized views for steadily accessed datasets.
• Price optimization: Use S3 Clever-Tiering for occasionally accessed information, Athena workgroups and Amazon Redshift utilization limits to regulate prices, and AWS Price Explorer to observe.
• Allow new capabilities: With information in open Apache Iceberg format on Amazon S3, you possibly can tackle use circumstances that have been beforehand troublesome or unattainable:
  • Prepare ML fashions straight on lakehouse information utilizing SageMaker AI.
  • Construct generative AI purposes with Amazon Bedrock Information Bases.
  • Implement real-time analytics with Amazon Information Firehose or Amazon Kinesis Information Streams writing Apache Iceberg tables to Amazon S3 Tables, or with Amazon Redshift streaming ingestion.
  • Share Apache Iceberg tables with companion engines similar to Snowflake and Databricks by the Apache Iceberg REST catalog endpoint.

Buyer success: Yggdrasil Gaming

Yggdrasil Gaming, a world iGaming studio, migrated from BigQuery to an Apache Iceberg-based lakehouse on AWS, working with GOStack, an AWS Companion. The corporate had been working dual-cloud infrastructure throughout AWS and Google Cloud, creating vital operational overhead that restricted their capability to implement superior analytics and AI/ML initiatives.

The migration adopted a phased strategy: establishing the lakehouse basis on S3 with Apache Iceberg and AWS Glue Information Catalog, implementing real-time information ingestion utilizing Debezium for change information seize, migrating processing pipelines to AWS Lambda and Amazon EMR, modernizing the transformation layer with dbt and Amazon Athena, and enabling governance by AWS Lake Formation.

The outcomes have been vital:

• 60 % discount in information processing prices.
• 75 % enchancment in analytics latency (from 2 hours to half-hour).
• Alleviated multi-cloud complexity by consolidating on AWS.
• Enabled AI/ML use circumstances together with participant habits modeling and fraud detection.

For the complete story, learn Constructing a contemporary lakehouse structure: Yggdrasil Gaming’s journey from BigQuery to AWS on the AWS Massive Information Weblog.

Buyer success: International expertise chief carried out zero-downtime GCP exit at petabyte scale

A worldwide expertise chief within the community safety house migrated from Google BigQuery to AWS to cut back prices and allow streaming ingestion for community safety analytics. Working with Quantiphi, an AWS Companion, the corporate migrated 10 core customer-facing purposes throughout 3 areas with zero enterprise downtime for greater than 1,000 clients.

Highlights of the migration:

• Migrated over 1,000 BigQuery tasks to Amazon Athena with Apache Iceberg on Amazon S3 (organising over 5,000 Apache Iceberg tables).
• Key drivers: price discount (30 % financial savings in cloud operational prices), streaming ingestion for safety analytics, and regulatory alignment.
• Zero enterprise downtime throughout a petabyte-scale migration spanning three AWS Areas, with minute-level information refresh assembly a 99.9 % service stage settlement (SLA).
• A full GCP exit consolidating totally on AWS by Quantiphi’s Qatapult modernization and migration platform.

For the complete story, learn Engineering Excellence: How a Tech Chief Migrated to AWS with Zero Enterprise Downtime (Quantiphi case research).

Conclusion

Migrating from BigQuery to an open lakehouse structure on AWS with Apache Iceberg is an architectural change and a strategic funding in information openness, multi-engine flexibility, and AI readiness. By storing information in open codecs on Amazon S3, governing it centrally by AWS Lake Formation, and accessing it with purpose-built engines, you construct a knowledge basis that adapts to your evolving wants with out vendor lock-in.

The phased migration strategy (assess, design, convert, migrate, cutover, and optimize) means you possibly can validate worth at every step and preserve enterprise continuity all through the transition. Organizations like Yggdrasil Gaming have demonstrated that this migration may be accomplished in months, not years, with measurable price financial savings and efficiency enhancements.

To get began, discover the next assets:

Concerning the authors