---
title: "AWS Athena Data Source Template"
slug: "aws-athena-data-source-template"
updated: 2026-08-13T18:30:14Z
published: 2026-08-13T18:30:14Z
canonical: "docs.validatar.com/aws-athena-data-source-template"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.validatar.com/llms.txt
> Use this file to discover all available pages before exploring further.

# AWS Athena Data Source Template

## Overview

The AWS Athena data source template connects Validatar to a data lake: parquet (or CSV, ORC, JSON) files in Amazon S3, catalogued in AWS Glue, queried through Athena.

**Platform:** Amazon Athena (engine v3 / Trino) **Connection Category:** Database **Connection Type:** ODBC **Catalog:** AWS Glue (`AwsDataCatalog`)

An Athena table is not a table. It is a logical view over a set of objects in S3, and that distinction is the reason this template exists in the form it does. A generic SQL template pointed at Athena will tell you what the *schema* says. It will not tell you that last night's batch job ran twice and silently doubled a partition, that the table is now 4,000 objects averaging 90 KB each, or that Tuesday's load never arrived. Those are the failures that actually happen to sequentially loaded lakes, and they live in the physical layout, not the logical schema.

So alongside the standard ingestion and profiling, this template ships macros and profiles that read the physical layout directly.

## Requirements

Before creating a data source:

- **Amazon Athena ODBC 2.x driver** installed on whichever host runs the connection — your Data Agent host, or the Validatar Cloud runner. It registers as `Amazon Athena ODBC (x64)`. Validated against 2.2.0.1.
- **Outbound TCP port 444** open from that host. Athena streams query results on port 444, separately from the HTTPS API. This is the most common cause of a connection that tests successfully and then hangs on the first real query.
- **`athena:GetQueryResultsStream`** in the IAM policy. It has no matching public API call and is missing from most example policies, but the ODBC and JDBC drivers require it.
- **Catalog custom fields** from the `athena-lake-catalog-custom-fields` marketplace item, applied *before* first ingestion.

> **Note:** Ingestion columns that don't match an existing custom field reference key are discarded silently. If you ingest before applying the fields, the partition metadata simply won't appear and there is no error to tell you why.

Authentication is covered separately in [AWS Athena ODBC Authentication](/docs/aws-athena-odbc-authentication).

## What's Included

### Data type mappings

37 mappings covering both Trino type names (as `information_schema` reports them) and the Hive names that Glue tables are often defined with, so a column never falls through unmapped:

- **String:** `varchar`, `char`, `string`, `json`, `ipaddress`
- **Exact numeric:** `tinyint`, `smallint`, `int`, `integer`, `bigint`, `decimal`
- **Approximate numeric:** `real`, `float`, `double`
- **Date/time:** `date`, `time`, `timestamp`, and their `with time zone` variants
- **Boolean:** `boolean`, `bool`
- **Complex:** `array`, `map`, `row`, `struct`, `binary`, `varbinary`, interval types

### Metadata ingestion

Three scripts against `information_schema`, with one Athena-specific addition: **partition keys**.

Athena flags partition columns by setting `extra_info = 'partition key'` in `information_schema.columns`. Nothing else in the catalog exposes this, and it is the single most useful fact about a lake table — filtering on a partition key is what lets Athena skip files instead of scanning everything.

| Level | Standard output | Athena additions |
| --- | --- | --- |
| Schema | `Name` | — |
| Table | `Schema`, `Name`, `Type`, `ViewDefinition` | `is_partitioned`, `partition_keys`, `partition_key_count` |
| Column | `Schema`, `Table`, `Name`, `DataType`, `Sequence`, `IsNullable`, `Comment` | `is_partition_key`, `declared_type` |

Athena's `information_schema.columns` has no `numeric_precision`, `numeric_scale`, or `character_maximum_length` columns — unlike most databases. Precision, scale, and string length are therefore parsed out of the declared type string, so `decimal(18,2)` still yields precision 18 and scale 2, and `varchar(50)` still yields length 50. The full declared type is also preserved in `declared_type`, which is where you see nested shapes like `array(varchar)` or `row(a integer, b varchar)` that the flattened `DataType` cannot represent.

## Macros

Nine standard macros (`table`, `row_count`, `list_of_tables`, `column_metadata_for_a_table`, `distinct_values_in_a_column`, `row_count_grouped_by_column`, `standard_profile_of_a_column`, `key_value_list`, `list_of_tables_in_a_schema`) behave as they do on any SQL platform.

The remaining ten are Athena-specific. They are grouped below by the problem they solve rather than by what they query.

### "Did a batch get loaded twice?"

Re-running a failed batch job is the most common way a lake table silently gains duplicate rows. The duplicates are byte-identical, so a conventional duplicate-key test tells you *that* it happened but gives you nothing to act on.

**`duplicate_keys_across_files`** groups by a key column and returns only keys that appear in more than one underlying S3 object, along with the object paths. That turns "you have duplicates" into "these two files both contain order 30412" — which identifies the offending job run and the file to delete.

### "Is this table going to get expensive?"

Athena bills per byte scanned, and per-object overhead is real. A table loaded in small sequential micro-batches accumulates thousands of tiny parquet files, and query time degrades badly even though the total data volume is unremarkable.

**`small_file_report`** returns file count, total and average size, min/max, the number of files under 128 MB, and the percentage that represents. It is the input to a compaction decision, and it is worth trending: a small-file percentage that climbs week over week means the ingestion cadence is outrunning compaction.

**`table_files`** is the underlying inventory — one row per object with size, S3 last-modified, and the number of rows that object contributes.

### "Did every load arrive?"

**`row_count_by_partition_key`** returns row count, file count, and last-modified per partition value. On a date-partitioned table this is a completeness check you can read at a glance: a missing date is a missing row in the result.

**`load_history`** groups by the S3 last-modified date of each file, giving files, rows, and megabytes per load date, plus first and last file timestamps.

> **Note:** `load_history` reads `$file_modified_time`, which is when the object was *written to S3* — not a logical load date. For a genuinely incremental table these coincide and the macro reads as a load timeline. For backfilled or bulk-loaded data every file shares one write time and the macro collapses to a single row. Use `row_count_by_partition_key` instead when the partition key carries the logical date.

**`files_added_since`** takes a timestamp and returns only objects modified after it — the basis for a freshness test asserting that something landed since the last run.

### "What is actually in the lake?"

**`partition_list`** reads the `$partitions` metadata table, returning the partitions registered in Glue. It reads the catalog rather than the data, so it scans nothing and costs nothing.

**`partition_row_counts`** returns rows, files, and megabytes per partition. Skew here is what makes one partition slow; a partition with zero rows is usually a load that failed after registering.

**`flatten_array_column`** applies `UNNEST` to an array column. Parquet routinely carries `array`, `map`, and `struct` columns, and without flattening there is no way to test anything inside them.

**`show_create_table`** returns the full DDL. Store it as a baseline and assert it doesn't change — the cheapest schema-drift tripwire available.

## Data Profiles

The standard profile set is implemented in Trino SQL, with three deliberate choices that matter on a pay-per-scan engine:

- **`try_cast` for all numeric coercion.** A plain `cast` on a string column that contains one bad value fails the whole query. `try_cast` yields NULL instead, so `min_numeric`, `numeric_count`, and friends work on loosely typed lake columns.
- **`approx_percentile` for median and quartiles.** The exact form requires a full sort.
- **`count_if`** instead of `sum(case when ...)`.

Percentage profiles (`null_percent`, `distinct_percent`, `blank_percent`, `numeric_percent`, `zero_percent`, `negative_percent`) are calculated from already-collected profiles rather than re-querying. On Athena that is not a style preference — every re-scan is billed.

### Athena-specific profiles

Eight profiles with no equivalent on other platforms, because no other supported platform exposes its physical storage to SQL. These are profiles rather than custom fields so that they carry history — a small-file count climbing over six weeks is exactly the trend worth charting.

| Profile | Level | What it tells you |
| --- | --- | --- |
| `file_count` | Table | Number of S3 objects behind the table |
| `total_data_mb` | Table | Physical size on S3 |
| `avg_file_mb` | Table | Average object size — the compaction signal |
| `small_file_count` | Table | Objects under 128 MB |
| `newest_file_modified` | Table | Freshness — when data last landed |
| `oldest_file_modified` | Table | Age of the oldest object |
| `partition_count` | Table | Partitions registered in Glue |
| `approx_distinct_count` | Column | Cardinality at roughly 2.3% error and a fraction of the scan cost of `count(distinct)` |

> **Note:** `partition_count` ships disabled. It reads the `<table>$partitions` metadata table, which exists only for partitioned tables, so enabling it globally would break profiling of unpartitioned ones. Enable it per profile set on tables you know are partitioned.

## Installation

1. Install the Amazon Athena ODBC 2.x driver on the host that will run the connection, and confirm outbound port 444 is open.
2. Import `athena-lake-catalog-custom-fields` under **Settings → Catalog Custom Fields → Import**.
3. Import `athena-template.xml` under **Settings → Data Source Templates → Import**.
4. Create the data source, choosing the authentication approach that matches where the connection runs — see [AWS Athena ODBC Authentication](/docs/aws-athena-odbc-authentication).
5. Run technical metadata ingestion.
6. Optionally import the `athena-validation` test pack and repoint its dataset filters at one of your own partitioned tables.

## Verification

Verified end to end against a live AWS account: 23 of 23 elements passed, covering the connection, table and column ingestion, all 19 macros, and the lake profile set.

The fixture was a partitioned parquet table of 13,575 rows across 25 S3 objects and 12 partitions, built with deliberate defects — a two-day gap in the partition sequence, one double-loaded batch, and uniformly small files — so that the detection macros were confirmed to *fire*, not merely to execute. `duplicate_keys_across_files` returned the 827 double-loaded keys; `partition_list` and `row_count_by_partition_key` both returned 12 partitions against 14 calendar days.

## Known Limitations

- **Views do not expose hidden columns.** Athena disallows `$path`, `$file_size`, `$file_modified_time`, and `$partition` on views. Every physical-layout macro and profile therefore works on tables only. Point them at the underlying table.
- **`load_history` reflects S3 write time,** not logical load date — see the note above.
- **`partition_count` and `partition_row_counts` require a partitioned table.** They error on unpartitioned ones.
- **Iceberg and Delta tables** expose a different metadata surface; this template targets Hive-style external tables. Structural ingestion works, but the physical-layout macros are untested against them.