Querying the Lake: Athena, Synapse Serverless and BigQuery Omni

The modern enterprise data stack has definitively shifted away from tightly coupled, proprietary analytical appliances toward decoupled storage and compute architectures. While object stores such as Amazon S3, Azure Data Lake Storage (ADLS) Gen2, and Google Cloud Storage (GCS) provide virtually unlimited, cost-effective capacity, accessing that data without managing persistent compute clusters presents a complex architectural challenge. Serverless query engines have emerged as the standard solution for querying lake environments on demand, transforming passive object storage into responsive analytical foundations.

For organizations navigating the convergence of data lakes & lakehouse architectures, selecting and optimizing the engine that directly queries object storage dictates both cost curves and downstream query latency. Three prominent engines dominate this landscape across the major hyperscalers: Amazon Athena, Azure Synapse Serverless SQL pools, and Google Cloud’s cross-cloud engine, BigQuery Omni. Each approaches distributed execution, catalog integration, and multi-cloud federation with distinct technical trade-offs.

---

Amazon Athena: Trino-Powered Serverless Analytics on AWS

Amazon Athena is a serverless, interactive query service built on open-source Trino (historically Presto) that allows engineers to query object storage directly using standard ANSI SQL. By utilizing the AWS Glue Data Catalog as its central metastore, Athena bypasses the need for persistent infrastructure while abstracting away cluster orchestration, scaling, and fault tolerance.

Architectural Mechanics and File Formats

Athena executes queries by breaking down SQL operations into distributed stages across isolated compute nodes managed entirely by AWS. In Athena engine version 3, the engine provides significant performance improvements and deeper support for open table formats, most notably Apache Iceberg, alongside Delta Lake and Apache Hudi.

To maximize throughput and minimize costs, query planning heavily relies on metadata filtering and partition pruning:

• Columnar Pruning: By reading columnar formats like Apache Parquet and ORC, Athena uses projection pushdown to fetch only the byte ranges corresponding to referenced columns.
• Partition Projection: Rather than repeatedly scanning the AWS Glue Data Catalog for highly partitioned datasets (which can bottleneck on AWS Glue API throttling), Athena can calculate partition metadata programmatically through partition projection rules, cutting query planning time from minutes to milliseconds.
• Iceberg Native Acceleration: With Apache Iceberg, Athena reads table-level metadata files (manifest lists and manifest files) to determine file splits, skipping entire data files based on column-level min/max statistics without reading the actual data blocks.

Cost and Performance Profile

Athena operates primarily on a consumption model of $5.00 per terabyte (TB) of data scanned, rounded up to the nearest megabyte, with a 10 MB minimum per query. Athena also offers Provisioned Capacity (measured in Data Processing Units, or DPUs) for teams requiring dedicated concurrency and predictable sub-second query routing.

---

Azure Synapse Serverless SQL: Native T-SQL Over ADLS Gen2

Azure Synapse Serverless SQL pools represent Microsoft’s dedicated engine for ad-hoc exploration, data virtualization, and transformation layers over ADLS Gen2. Unlike engines that rely on open-source Presto variants, Synapse Serverless uses a customized, distributed execution version of the relational SQL Server engine (DQP—Distributed Query Processing).

Direct Query Execution via OPENROWSET

Synapse Serverless allows developers to use familiar T-SQL syntax without defining permanent tables in a metastore upfront. Using the OPENROWSET function, data engineers can query raw CSV, JSON, Parquet, and Delta formats directly in-place:

SELECT 
    r.filepath(1) AS ProcessingYear,
    r.filepath(2) AS ProcessingMonth,
    COUNT_BIG(*) AS RecordCount,
    SUM(TotalAmount) AS AggregateRevenue
FROM OPENROWSET(
    BULK 'https://datalakeprod.dfs.core.windows.net/curated/sales/year=*/month=*/*.parquet',
    FORMAT = 'PARQUET'
) AS r
GROUP BY r.filepath(1), r.filepath(2);

Delta Lake Integration and Automatic Statistics

Synapse Serverless provides first-class support for Delta Lake, reading the _delta_log transaction ledger directly to resolve ACID states, time-travel queries, and schema evolution.

A critical operational differentiator is automatic statistics generation. As queries execute over Parquet and Delta datasets, the engine automatically creates and updates column-level statistics behind the scenes. The distributed query optimizer leverages these statistics to build accurate cardinality estimates and construct optimal distributed hash or broadcast join trees.

Like Athena, Azure Synapse Serverless charges $5.00 per TB of data processed. Data processed includes not just the raw bytes scanned from storage, but also the data shuttled across compute nodes during shuffles and intermediate hash joins, making query design and partition pruning critical for cost containment.

---

BigQuery Omni: Multi-Cloud Execution Without Data Egress

When data lakes are split across clouds, centralizing analytics typically requires copying petabytes of data into a single cloud—incurring massive network egress fees and operational overhead. Google Cloud address this friction directly with BigQuery Omni.

BigQuery Omni extends Google’s distributed query engine (Dremel) to execute natively inside AWS (S3) and Microsoft Azure (ADLS Gen2).

The Control Plane vs. Data Plane Split

BigQuery Omni achieves low latency and avoids egress charges by decoupling the control plane from the data plane:

1. Control Plane (Google Cloud): Manages user authentication, query parsing, parsing optimizations, and execution planning via the BigQuery UI, API, or Google Cloud SDK.
2. Data Plane (Target Cloud): Runs as an Anthos-managed cluster within Google’s managed tenant network inside AWS or Azure. When a user issues a query against an external connection pointing to an S3 bucket or ADLS container, the compute runs entirely inside that foreign cloud's region.

       [BigQuery Control Plane (GCP)]
                     |
       (Query Plan Dispatched - No Data Transfer)
                     v
   [BigQuery Omni Data Plane (AWS / Azure Region)]
                     |
         (Local Low-Latency Reads)
                     v
       [Object Storage (S3 / ADLS Gen2)]

Only the aggregated, final query results are returned to the user or serialized back to the local cloud storage. Raw data never crosses cloud boundaries, mitigating both egress costs and cross-cloud latency penalties.

Table Formats and Federated Queries

BigQuery Omni supports standard Parquet, ORC, CSV, and JSON data formats, along with emerging support for Iceberg tables defined through external catalogs. By combining Omni with cross-cloud join capabilities, organizations can execute queries that join local BigQuery native storage with external lake tables located on AWS S3 in a single declarative SQL statement.

---

Architectural Comparison: Athena vs. Synapse Serverless vs. BigQuery Omni

The following matrix compares the core characteristics of each serverless query engine when deployed against modern object storage:

---

Best Practices for Cost Optimization and Performance

Regardless of the engine selected, operating efficiently over data lakes & lakehouse layers requires disciplined file organization and query structure:

1. Optimize File Sizing and Layout

Querying millions of tiny files (sub-128 MB) introduces substantial metadata overhead and excessive HTTP GET requests. Conversely, multi-gigabyte files limit the degree of parallelism distributed engines can achieve. Aim for file sizes between 128 MB and 512 MB. Implement regular compaction jobs to merge small files generated by streaming or micro-batch pipelines.

2. Standardize on Open Table Formats

Moving from raw directory-based partitioning to structured formats like Apache Iceberg or Delta Lake eliminates the need for expensive file listing calls (LIST operations on S3/ADLS). Metadata-driven file pruning allows Athena, Synapse, and BigQuery to skip vast quantities of irrelevant files using manifest-level statistics, directly lowering the cost per query under consumption-based billing models.

3. Implement Rigid Partition Pruning

Avoid running unconstrained SELECT * queries over high-cardinality datasets. Ensure analysts and data pipelines always filter on partition columns (e.g., date, region, business unit) at the top level of the SQL syntax. For complex schemas in Athena, leverage partition projection to avoid Glue Catalog API limits.

---

How Izan Technologies Helps

Designing, optimizing, and securing modern lakehouse architectures requires deep, hands-on engineering across cloud boundaries. Izan Technologies partners with enterprise teams to build high-performance analytics platforms, unified governance models, and automated data pipelines across AWS, Azure, Databricks, and Snowflake.

When you partner with Izan Technologies, you get production-grade engineering tailored to your operational constraints—and your organization retains 100% of the intellectual property (IP) created during the engagement. Whether you need to optimize query performance on terabyte-scale data lakes or implement multi-cloud analytics frameworks:

• Try our Smart AI DataStudio to accelerate your data engineering and analysis workflows.
• Or talk to an expert to design an optimized, cost-efficient lakehouse infrastructure.