Chapter 8: Interactive Querying and Federated Analytics

Serverless Query Model

Most data warehouses follow a "load before you query" model: stand up a cluster, ingest data, then run SQL. Amazon Athena inverts that contract. The data already lives in Amazon S3; you point Athena at it and run SQL immediately. There are no nodes to size, no maintenance windows, and no idle capacity to amortize.

The clearest analogy is a public library with a fleet of on-call researchers. You arrive with a question, hand it to the front desk (the Athena API), and behind the scenes a researcher pulls exactly the books your question requires. When the answer comes back, the researcher disappears. You pay only for the books they had to open.

Architecturally, Athena is a managed deployment of the Trino distributed SQL engine (renamed from PrestoSQL in 2020). When you submit a query, Athena allocates worker capacity from a shared, multi-tenant pool, plans the query, scans S3 objects in parallel, and streams results back. Metadata — table definitions, column types, partition locations — comes from the AWS Glue Data Catalog, so any table registered for Glue ETL or Lake Formation governance is immediately queryable from Athena.

Because the engine is serverless, "always-on availability" is a structural property: there is no cluster that can be paused, downsized, or fail. The same SQL that worked yesterday at 2 a.m. will work today at 2 p.m., even if no one queried in between.

Athena Engine Versions and the Trino Lineage

Athena exposes its underlying engine through versioned releases. Engine version 2 was based on PrestoDB; engine version 3 (the current default for new workgroups) is based on Trino and ships with Apache Iceberg native support, improved geospatial functions, EXPLAIN ANALYZE, and a cost-based optimizer that consumes Glue table statistics.

Trino's distinguishing features are its massively parallel execution, its connector architecture (which we exploit for federation later), and its memory-resident intermediate results. Unlike Spark, which writes shuffle data to disk, Trino keeps query state in memory across worker nodes — making interactive sub-second responses possible on terabyte-scale data, but causing Trino to fall over on multi-hour ETL jobs that exceed cluster memory. Trino is the tool for interactive analytics; Spark is the tool for batch transformation.

Pricing Per Terabyte Scanned

Athena bills $5.00 per terabyte of data scanned in S3 (US regions, on-demand pricing), with a 10 MB minimum charge per query. There is no charge for query planning, no cluster-hour charge, and no data-stored charge (S3 storage is billed separately).

This pricing model inverts the optimization mindset most engineers bring from warehouses. In Redshift or Snowflake, you optimize for wall-clock time — a faster query frees up cluster capacity. In Athena, you optimize for bytes read from S3. A query that scans 10 GB in 45 seconds costs the same as one that scans 10 GB in 5 seconds.

Concrete example: a daily report scans a 1 TB CSV clickstream table. At $5/TB, that costs $5/day or roughly $1,825/year. Convert to Snappy-compressed Parquet partitioned by event_date, and a single-day query scans about 800 MB. The annual cost drops below $1.50.

Figure 8.1: Athena Serverless Trino Execution Model

Once you internalize "minimize bytes scanned," the optimization toolkit organizes itself naturally. There are three layers to attack: what you read (partitioning), how you read it (columnar formats), and what you compute on it once read (CTAS for materialization).

Partition Projection

Partitioning arranges files in S3 under a directory hierarchy that encodes a column value in the path. The Hive convention uses key=value segments:

s3://my-lake/sales/year=2024/month=11/day=07/sales-001.parquet
s3://my-lake/sales/year=2024/month=11/day=07/sales-002.parquet
s3://my-lake/sales/year=2024/month=11/day=08/sales-001.parquet

When you submit SELECT * FROM sales WHERE year=2024 AND month=11 AND day=07, Athena consults the Glue Catalog for partition locations, identifies that only one prefix matches, and instructs Trino workers to read only that prefix. On a table with 365 day-partitions, a single-day query touches roughly 1/365th of the data — a 99.7% reduction in bytes scanned.

The traditional Hive approach requires MSCK REPAIR TABLE or a Glue crawler whenever new partitions appear. Partition projection takes a different path: instead of materializing every partition in the catalog, you declare the partition pattern in table properties, and Athena synthesizes the partition list on the fly from the WHERE clause.

CREATE EXTERNAL TABLE sales (
  sale_id BIGINT,
  amount DECIMAL(10,2)
)
PARTITIONED BY (year INT, month INT, day INT)
STORED AS PARQUET
LOCATION 's3://my-lake/sales/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.year.type' = 'integer',
  'projection.year.range' = '2020,2030',
  'projection.month.type' = 'integer',
  'projection.month.range' = '1,12',
  'projection.day.type' = 'integer',
  'projection.day.range' = '1,31',
  'storage.location.template' = 's3://my-lake/sales/year=${year}/month=${month}/day=${day}/'
);

An analogy: traditional Hive partitioning is a library where every book is individually catalogued by hand. Partition projection is a library where the librarian knows the shelving rule and computes where any book lives without consulting an index card.

Columnar Formats and Compression

Once partitioning has narrowed the file set, the next question is how each file is laid out internally. Row-based formats (CSV, JSON, Avro) store all columns of one row together; column-based formats (Parquet, ORC) store all values of one column together.

Parquet also embeds min/max statistics per row group (~128 MB chunks). When your WHERE clause says amount > 1000, Trino can skip entire row groups whose maximum amount is below 1000, without reading any actual values. This is predicate pushdown at the file level, stacked on top of partition pruning.

The recommended file size is 128–256 MB per Parquet object, with internal row groups of 64–128 MB. Files smaller than 128 MB hurt parallelism — each worker pays a fixed S3 GET overhead per file, and with thousands of tiny files that overhead dominates wall-clock time (the "tail latency" problem). Files larger than 1 GB underutilize the worker fleet because individual workers cannot subdivide a file beyond row-group boundaries.

Compression: Snappy is the Parquet default (fast, modest ratio). ZSTD is 10–30% better at slightly higher CPU cost. GZIP is single-threaded per block — avoid for analytical Parquet.

CTAS and INSERT INTO Patterns

CTAS (CREATE TABLE AS SELECT) is the Swiss Army knife of Athena optimization. A CTAS reads from a source, applies any transformation (projection, filter, partitioning, formatting, bucketing), and writes a new table in a single operation:

CREATE TABLE optimized_sales
WITH (
  format = 'PARQUET',
  parquet_compression = 'SNAPPY',
  partitioned_by = ARRAY['year', 'region'],
  bucketed_by = ARRAY['customer_id'],
  bucket_count = 32,
  external_location = 's3://my-lake/curated/sales/'
) AS
SELECT
  sale_id,
  amount,
  customer_id,
  year(sale_date) AS year,
  region
FROM raw_sales
WHERE sale_date >= DATE '2020-01-01';

Bucketing hashes a chosen column into a fixed number of files per partition. When two bucketed tables are joined on the bucket column, Trino performs a co-located join — bucket 7 of sales joins only against bucket 7 of customers — dramatically reducing shuffle traffic.

CTAS has two important constraints: the destination S3 location must be empty (Athena refuses to overwrite), and a single CTAS produces at most 100 partitions. For larger datasets you use INSERT INTO ... SELECT to append additional partitions:

INSERT INTO optimized_sales
SELECT sale_id, amount, customer_id, year(sale_date) AS year, region
FROM raw_sales
WHERE sale_date >= DATE '2024-01-01' AND sale_date < DATE '2024-02-01';

A common production pattern combines CTAS for backfills with scheduled INSERT INTO for daily increments. Many teams have replaced this with Apache Iceberg tables (Athena engine 3+), which provide atomic commits, hidden partitioning, and time travel.

Figure 8.2: Hive Partition Lookup vs Partition Projection

Athena Federated Query Connectors

Up to this point, every example has assumed data lives in S3. In practice, organizations have customer profiles in DynamoDB, transactional records in RDS, financial metrics in Snowflake, and event logs in S3 — and the analytical question that matters often spans all of them. Athena Federated Query is the bridge.

Federated Query introduces a catalog that points not at the Glue Data Catalog but at an AWS Lambda function. That Lambda function is the data source connector — the runtime intermediary between Athena and the external system.

SQL Query
  -> Athena Trino engine
  -> Federated Query Handler
  -> Lambda Data Source Connector (per source)
  -> Native API call (DynamoDB GetItem, RDS SQL, Redshift, etc.)
  -> Apache Arrow result blocks
  -> Athena query plan continues (joins, aggregates)
  -> Final result to user

Every connector implements four required handler interfaces:

The Apache Arrow detail is more than a technicality. Arrow is a columnar in-memory format that allows Trino, Lambda, and external systems to share data without serialization/deserialization overhead. When the connector returns Arrow blocks (up to 64 MB each), Trino consumes them directly, treating remote data with the same primitives it uses for native S3 Parquet.

Querying RDS, DynamoDB, and External Sources

Each connector maps Athena's query operations to the source system's native primitives. The mapping matters because it determines what gets pushed down (good — less data moves) versus pulled up to Trino for filtering (bad — more data moves).

DynamoDB is the trickiest because it is not a relational engine. WHERE user_id = 'u-123' becomes a DynamoDB GetItem — single-digit-millisecond latency. Partition-key equality becomes a Query. But WHERE last_login > '2024-01-01' degenerates into a full-table Scan — slow and expensive. Treat DynamoDB like an OLTP key-value store; treating it like a warehouse is a path to surprise bills.

-- Efficient: pushes down to GetItem
SELECT user_id, email, last_login
FROM lambda_dynamo.production.user_profiles
WHERE user_id = 'user_12345';

-- Expensive: degenerates to a full Scan
SELECT user_id, email
FROM lambda_dynamo.production.user_profiles
WHERE last_login > TIMESTAMP '2024-01-01 00:00:00';

RDS connectors are more conventional — the Lambda receives the WHERE clause and column list and constructs a real SQL query against the database. Pitfalls are operational: Lambda must share the VPC with RDS, credentials live in Secrets Manager, and concurrent queries can saturate connection limits. Route federated reads to a read replica when possible.

SELECT
    c.customer_id,
    c.name,
    c.tier,
    s.total_revenue_2024
FROM lambda_rds.prod.customers c
JOIN s3_lake.curated.revenue_summary s
    ON c.customer_id = s.customer_id
WHERE c.created_date > DATE '2024-01-01'
  AND c.tier IN ('gold', 'platinum');

Connectors to Snowflake and BigQuery

The connector model extends naturally to non-AWS warehouses. AWS publishes a Snowflake connector (JDBC) and a BigQuery connector (BigQuery client libraries). Both push down filters and projections; both bill on two axes (Athena scanned bytes plus the source warehouse's own pricing).

Latency: pure S3 queries return in 2–5 s; federated DynamoDB in 3–8 s; RDS or warehouse queries in 5–15 s; cross-source joins in 10–30 s. Lambda's 15-minute hard limit caps long-running reads, and the 64 MB Arrow block size can fragment very large result sets. Federation is excellent for joining moderate volumes; it is not a replacement for nightly terabyte ETL. The right pattern is exploration via federation, then materialization via CTAS for production.

When Athena vs Redshift vs EMR

The fundamental difference is whether your workload is interactive or persistent.

• Athena — ad-hoc analysis. Data scientist exploring a new dataset, SRE searching CloudTrail logs, analyst answering a one-time CFO question. No idle cluster.
• Redshift — persistent warehouse. Nightly executive dashboards, customer-facing analytics, predictable concurrency, sub-second SLAs. RA3 separates storage from compute.
• EMR — non-SQL big-data processing. Spark for ML feature engineering, Flink for streaming, Hive for legacy batch. Heavy ETL that produces curated tables Athena and Redshift query.

Three workshops in a factory: Athena is the bench for quick repairs as customers walk in; Redshift is the assembly line running the same product day after day; EMR is the heavy machine shop fabricating custom components.

Cost models compared

Ten ad-hoc Athena queries/day at 5 GB each ≈ $1/year. The same workload on a small Redshift provisioned cluster costs roughly $1,750/month even when idle. Conversely, 200 high-concurrency BI queries per minute on a 10 TB warehouse: Redshift wins; Athena would bill aggressively and likely throttle.

Figure 8.4: Decision Flow for Athena vs Redshift vs EMR

Hybrid Usage Patterns

In practice, mature platforms use all three engines, each for the workload it is optimized for, unified by the Glue Data Catalog and Lake Formation governance.

Raw landing (S3, JSON/CSV)
        |
        | EMR Spark (heavy transformation)
        v
Curated lake (S3, Parquet/Iceberg, Glue Catalog)
        |
        +--------+--------+
        |                 |
        v                 v
   Athena (ad-hoc)   Redshift Spectrum + RA3 (BI marts)
        |                 |
        v                 v
    Data scientists    BI dashboards

The Glue Data Catalog is the glue (literally): a table registered in Glue is queryable from Athena, accessible via Redshift Spectrum, and visible to EMR Spark — without redefinition. Lake Formation layers row- and column-level access control over the catalog so that a marketing analyst querying via Athena and a data engineer querying via Spark see the same governed view.

The data lakehouse pattern captures this in a single phrase: warehouse-quality structure (ACID, schema evolution, time travel) on lake-economics storage (S3, no duplication, multi-engine access). Athena engine v3's native Iceberg support makes this practical without third-party tooling.