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.

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.

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.

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.
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.