Athena data freshness check without scanning S3

Connect Athena

Add an Athena connector with access key, secret key, region, S3 output location, database, and optionally a workgroup and a custom data catalog. Leave the catalog blank to use the default AwsDataCatalog. The fields are listed on the Athena connector page in the docs, and the test flow is in the connectors overview.

Two Athena-specific fields trip people up. The S3 output location (s3://bucket/prefix/) is where Athena writes each result, and arcodash reads the result back from there. If the workgroup defines its own result configuration, that is what Athena uses, so make the two agree. The workgroup is optional, but a dedicated one for arcodash is the best cost tool you have, as the cost section below explains.

Create an IAM user just for this, with a key pair, and keep its policy narrow. The pieces it needs are: permission to start and read Athena queries (athena:StartQueryExecution, athena:GetQueryExecution, athena:GetQueryResults), read access to the Glue catalog entries for your database (glue:GetDatabase, glue:GetTable, glue:GetPartitions and their plural forms), read on the data bucket, and read plus write on the results bucket. arcodash runs read queries, so it needs no Glue write actions and no write access to the data bucket. Check the exact action list against the Athena IAM access docs for your setup, since Lake Formation or KMS encryption add requirements.

Network access: arcodash calls the public Athena API in your region, so there is nothing to allowlist on your side. A result keeps at most 10,000 rows (see Limits).

Keep checks cheap

Athena charges for data scanned. AWS’s pricing page uses $5 per TB in its examples, and failed or cancelled queries are billed for what they read. A scheduled check runs many times a day, so cost per run matters more than it does for an ad hoc query.

Three habits keep it near zero:

1. Filter on the partition column. A freshness check does not need all of history, only the last day or two.
2. Name the columns. On Parquet and ORC, Athena reads only the columns a query references. max(loaded_at) reads one column; SELECT * reads all of them.
3. Cap the workgroup. A workgroup can enforce a per-query data scanned limit, from 10 MB up. A query that exceeds it is cancelled (and still billed for what it read), so a mistake in a check cannot scan a petabyte. Put arcodash in its own workgroup with a limit and a CloudWatch metric.

If your table is partitioned by day, projection saves the catalog round trip. Partition projection computes partition values from table properties instead of calling the Glue catalog:

CREATE EXTERNAL TABLE analytics.events (
  event_id string,
  status int,
  loaded_at timestamp
)
PARTITIONED BY (dt string)
STORED AS PARQUET
LOCATION 's3://my-data-bucket/events/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.dt.type' = 'date',
  'projection.dt.format' = 'yyyy-MM-dd',
  'projection.dt.range' = '2024-01-01,NOW',
  'projection.dt.interval' = '1',
  'projection.dt.interval.unit' = 'DAYS'
)

Projection has a monitoring consequence. Athena projects every date in the range whether or not data exists for it, and it ignores partition metadata in Glue for that table. So SHOW PARTITIONS and max(dt) tell you nothing about whether data arrived. Measure freshness from a timestamp in the rows.

A freshness check

A check is a scheduled query that returns one numeric row; the monitor compares one column of it with a threshold (data quality monitors has the details).

SELECT coalesce(
         date_diff('second', max(loaded_at), cast(current_timestamp AS timestamp)),
         2147483647
       ) AS seconds_since_last_row
FROM analytics.events
WHERE dt >= cast(current_date - interval '2' day AS varchar)

Monitor seconds_since_last_row with > and 3600. The partition filter bounds both cost and the lag you can measure: if nothing landed in two days, max() is NULL, and coalesce turns that into a very large number so the monitor triggers instead of showing unknown. The same idea with a Postgres table is in data freshness.

dt here is a string in yyyy-MM-dd form, which is why the filter casts a date to varchar. Match whatever format your partition values use.

A volume check

SELECT count(*) AS rows_today
FROM analytics.events
WHERE dt = cast(current_date AS varchar)

Monitor rows_today with <. This one is cheap because it reads only today’s partition and no data columns at all. Note that early in the day the count is small by nature, so a floor that works at 6pm will fire at 1am. A baseline monitor handles that shape better, and row count anomaly detection in SQL explains the logic.

A metric alert

Server error rate over today’s requests:

SELECT coalesce(
         round(100.0 * count_if(status >= 500) / nullif(count(*), 0), 2),
         0
       ) AS error_pct
FROM analytics.requests
WHERE dt = cast(current_date AS varchar)

Monitor error_pct with > and 2, and route it with notification destinations.

Gotchas that matter for monitoring

• UTC dates. Projected date partitions are generated in UTC at query time, so “today” in a partition filter is the UTC day. A team in another time zone sees a day that rolls over mid-afternoon.
• Small files. Many tiny files slow every query. "$path" returns the S3 file behind each row, which makes the problem visible (see the tile below).
• S3 latency. Results go through S3 on every run, so expect seconds, not milliseconds. Cancelled queries can leave partial results in the output prefix, so give that bucket a lifecycle rule.
• Reserved words. Column names that collide with Athena reserved words need double quotes in a SELECT.

To generate freshness, row count, null and duplicate checks for an Athena table, use the Athena data check generator.

What a dashboard looks like

Daily volume for the last two weeks:

SELECT dt, count(*) AS events
FROM analytics.events
WHERE dt >= cast(current_date - interval '14' day AS varchar)
GROUP BY dt
ORDER BY dt

Files per partition, to catch a small-files problem early:

SELECT dt, count(DISTINCT "$path") AS files, count(*) AS rows
FROM analytics.events
WHERE dt >= cast(current_date - interval '7' day AS varchar)
GROUP BY dt
ORDER BY dt

Status mix for the day, as a bar chart:

SELECT status, count(*) AS requests
FROM analytics.requests
WHERE dt = cast(current_date AS varchar)
GROUP BY status
ORDER BY requests DESC

Schedule the queries that feed monitors with scheduled refresh; each run is a billable Athena query, so pick intervals with that in mind. Suggest checks with AI can draft checks from your schema, using your own AI key. arcodash is hosted only, with 14 days free and no card.

BigQuery is the closest alternative if your data is on Google Cloud.

docs

Amazon Athena setup in the docs

Connection fields, an example configuration and caveats.