Partitioning Cost Time-Series as Parquet for Athena

Once historical database cost lives as millions of daily rows, a row store buckles under range scans, so this page writes those records to date-partitioned Parquet on S3 and queries them in Athena where a single day’s WHERE dt = '...' reads one partition instead of the whole history.

Back to: Batch Processing for Historical Metrics

Athena bills by bytes scanned, and Parquet’s columnar layout plus Hive-style date partitioning is what keeps that number small: a query for last week’s spend touches seven dt= prefixes and reads only the columns it projects. The layout that makes this work is a strict s3://bucket/cost/dt=YYYY-MM-DD/ prefix, one Parquet file (or a few) per day, written with an explicit Arrow schema so column types never drift between days. This is the query-optimized destination for records produced by an idempotent historical backfill, and every record it writes should already have passed strict schema validation for billing data so the Parquet types below hold for real. The work splits into writing typed, compressed, partitioned files and then registering those partitions so Athena can prune to them.

Prerequisites

Before writing any Parquet, confirm the following are in place.

  • AWS IAM: the writer needs to put objects under the cost prefix and, for the registration step, to run Glue/Athena partition operations. Scope it tightly — this least-privilege posture is part of broader access control for cost data.

    {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "WriteParquet",
      "Effect": "Allow",
      "Action": ["s3:PutObject", "s3:GetObject", "s3:ListBucket"],
      "Resource": [
        "arn:aws:s3:::finops-cost-lake",
        "arn:aws:s3:::finops-cost-lake/cost/*"
      ]
    },
    {
      "Sid": "RegisterPartitions",
      "Effect": "Allow",
      "Action": [
        "glue:BatchCreatePartition",
        "glue:GetTable",
        "athena:StartQueryExecution",
        "athena:GetQueryExecution"
      ],
      "Resource": "*"
    }
  ]
}

  • Python: 3.10 or newer.

  • Libraries: install pyarrow (bundles the Parquet writer and S3 filesystem) and boto3.

    pip install "pyarrow>=15.0" "boto3>=1.34"

Step-by-Step Implementation

The pipeline pins an explicit Arrow schema, writes each day’s records as a Snappy-compressed Parquet object under a dt= prefix, registers the new partitions with Glue, and then queries the table in Athena with a predicate that prunes to the requested days. Build it in four steps.

Step 1 — Pin an explicit Arrow schema

Never let pyarrow infer types from a Python list — a day where every amount happens to be a whole number infers int64, and the next day’s decimals silently break the column across partitions. Declare the schema once and reuse it for every write so amount is always decimal128, timestamps are always UTC, and the partition column is a clean string.

import pyarrow as pa

# Money as decimal128 avoids float drift; dt is the partition key, kept out of the file body.
COST_SCHEMA = pa.schema([
    ("tenant_id",   pa.string()),
    ("resource_id", pa.string()),
    ("service",     pa.string()),
    ("amount",      pa.decimal128(18, 6)),
    ("currency",    pa.string()),
    ("usage_start", pa.timestamp("us", tz="UTC")),
])


def to_table(records: list[dict]) -> pa.Table:
    """Build an Arrow table under the fixed schema, failing loudly on type drift."""
    return pa.Table.from_pylist(records, schema=COST_SCHEMA)


# Expected: to_table([...]) -> pa.Table with 6 typed columns; a str amount raises
#           pa.lib.ArrowInvalid rather than silently coercing.

Keeping dt out of the file body and only in the S3 path is deliberate: a Hive partition column is stored in the prefix, not the data, so you never pay to scan it and never store it redundantly across every row.

Step 2 — Write one day as Snappy-compressed partitioned Parquet

Each day becomes a Parquet object at cost/dt=YYYY-MM-DD/<file>.parquet. Snappy is the right default — it is splittable and cheap to decompress, so Athena parallelizes reads across the file, and it costs far less CPU than gzip for a marginal size difference on already-columnar data.

import pyarrow.parquet as pq
from pyarrow import fs

BUCKET = "finops-cost-lake"
PREFIX = "cost"


def write_day(records: list[dict], day: str) -> str:
    """Write one day's records to s3://BUCKET/cost/dt=<day>/data.parquet."""
    table = to_table(records)
    s3 = fs.S3FileSystem()  # picks up the ambient IAM role / credential chain
    path = f"{BUCKET}/{PREFIX}/dt={day}/data.parquet"
    pq.write_table(
        table,
        path,
        filesystem=s3,
        compression="snappy",
        use_dictionary=["tenant_id", "service", "currency"],  # low-cardinality -> dict encode
        version="2.6",
    )
    return f"s3://{path}"


# Expected:
#   write_day(rows, "2026-04-01")
#   -> 's3://finops-cost-lake/cost/dt=2026-04-01/data.parquet'

Dictionary encoding on the low-cardinality string columns (tenant_id, service, currency) shrinks the file substantially and lets Athena apply predicate pushdown on those columns within a partition — a second pruning layer beneath the date partition itself.

The path from raw records to a pruned Athena scan is shown below.

Parquet partitioning flow: schema-cast records to daily Parquet objects under dt= prefixes, register in Glue, prune in AthenaNormalized cost records are cast to a fixed Arrow schema and written as Snappy Parquet objects, one per day, under cost/dt=DATE prefixes on S3. Those prefixes are registered as partitions in the Glue Data Catalog with MSCK REPAIR TABLE or ALTER TABLE ADD PARTITION. An Athena query with a WHERE dt predicate prunes to matching partitions and scans only those objects.castwriteregisterprune on dtCost recordsvalidated dictsArrow tablefixed COST_SCHEMAS3: cost/dt=…/data.parquetdt=2026-04-01snappydt=2026-04-02snappydt=2026-04-03snappyGlue Data Catalogpartition registryAthena queryWHERE dt BETWEEN … scans matched partitions

Step 3 — Register partitions so Athena can find them

Writing a new dt= prefix does not make Athena aware of it — the Glue Data Catalog must be told the partition exists. For a full re-scan use MSCK REPAIR TABLE, which discovers every dt= prefix under the table location; for incremental daily loads, a targeted ALTER TABLE ADD PARTITION is far cheaper because it registers exactly the one new day instead of relisting the whole bucket.

import time
import boto3

athena = boto3.client("athena")
DATABASE = "finops"
TABLE = "cost"
OUTPUT = "s3://finops-cost-lake/athena-results/"


def _run(sql: str) -> None:
    """Execute a statement and block until it reaches a terminal state."""
    qid = athena.start_query_execution(
        QueryString=sql,
        QueryExecutionContext={"Database": DATABASE},
        ResultConfiguration={"OutputLocation": OUTPUT},
    )["QueryExecutionId"]
    while True:
        state = athena.get_query_execution(QueryExecutionId=qid)["QueryExecution"]["Status"]["State"]
        if state in ("SUCCEEDED", "FAILED", "CANCELLED"):
            if state != "SUCCEEDED":
                raise RuntimeError(f"Athena query {qid} ended {state}")
            return
        time.sleep(1)


def add_partition(day: str) -> None:
    """Register a single new day's partition (cheap, incremental)."""
    location = f"s3://{BUCKET}/{PREFIX}/dt={day}/"
    _run(
        f"ALTER TABLE {TABLE} ADD IF NOT EXISTS "
        f"PARTITION (dt = '{day}') LOCATION '{location}'"
    )


def repair_all() -> None:
    """Full partition discovery — use after a bulk backfill of many days."""
    _run(f"MSCK REPAIR TABLE {TABLE}")

The one-time DDL that creates the external table declares dt as the partition column and points at the table root:

CREATE EXTERNAL TABLE IF NOT EXISTS finops.cost (
    tenant_id   string,
    resource_id string,
    service     string,
    amount      decimal(18,6),
    currency    string,
    usage_start timestamp
)
PARTITIONED BY (dt string)
STORED AS PARQUET
LOCATION 's3://finops-cost-lake/cost/'
TBLPROPERTIES ('parquet.compression' = 'SNAPPY');

Step 4 — Query with partition pruning

A predicate on the partition column is what turns a full-history table into a cheap scan. Filter on dt and Athena reads only the matching prefixes; project just the columns you need and it reads only those column chunks.

def spend_by_tenant(start: str, end: str) -> str:
    """Sum spend per tenant across a date range, pruning to those partitions."""
    sql = f"""
        SELECT tenant_id, SUM(amount) AS spend
        FROM {TABLE}
        WHERE dt BETWEEN '{start}' AND '{end}'   -- partition pruning happens here
        GROUP BY tenant_id
        ORDER BY spend DESC
    """
    qid = athena.start_query_execution(
        QueryString=sql,
        QueryExecutionContext={"Database": DATABASE},
        ResultConfiguration={"OutputLocation": OUTPUT},
    )["QueryExecutionId"]
    return qid

Expected result and, more importantly, the bytes-scanned figure that proves pruning worked:

tenant_id   spend
---------   ---------
acme        4821.402100
globex      1190.884500
initech      317.559000

Data scanned: 2.14 MB   (one week of partitions, not the full year)

Verification

Confirm the layout prunes and the types survived the round trip before you build reports on it.

  1. Prove pruning with EXPLAIN and bytes scanned. Run the query for one day and check Data scanned in the Athena console or via the API — it must be a fraction of the full table size.

    qid = spend_by_tenant("2026-04-01", "2026-04-01")
stats = athena.get_query_execution(QueryExecutionId=qid)["QueryExecution"]["Statistics"]
assert stats["DataScannedInBytes"] < 5_000_000, "pruning failed — scanning too much"

  2. Round-trip one file’s types. Read a written object back and assert amount is still decimal, not float:

    import pyarrow.parquet as pq
from pyarrow import fs
tbl = pq.read_table("finops-cost-lake/cost/dt=2026-04-01/data.parquet", filesystem=fs.S3FileSystem())
assert pa.types.is_decimal(tbl.schema.field("amount").type), "amount drifted off decimal"

  3. Confirm the partition is registered. SHOW PARTITIONS finops.cost must list the day you just wrote; a missing entry means the ADD PARTITION step was skipped and Athena will silently return no rows for it.

Gotchas & Edge Cases

  • A written prefix is invisible until it is registered. Athena queries the Glue catalog, not S3 directly, so a dt= prefix with data but no partition entry returns zero rows with no error. Always pair write_day with add_partition, or run MSCK REPAIR TABLE after a bulk load — the same silent-gap failure that catches every first Athena pipeline.
  • MSCK REPAIR TABLE gets expensive at scale. It relists the entire table location every run, which is slow and costly once you have hundreds of partitions. Use it once after a backfill, then switch to incremental ALTER TABLE ADD PARTITION for daily loads.
  • Type inference will bite you across days. Letting pyarrow infer types per file means a day of whole-dollar amounts becomes int64 and the next day’s decimals fail the merge. Pinning COST_SCHEMA once, and validating upstream via strict schema validation for billing data, is what keeps every partition’s Parquet mutually compatible.
  • Many tiny files kill scan performance. One object per day is fine; thousands of few-KB objects per day are not — Athena spends more time opening files than reading them. If a day produces many small writes, compact them into one file per partition before registering, which the batch processing tier is the natural place to do.
  • A predicate on a non-partition column does not prune. WHERE service = 'RDS' scans every partition and filters inside; only WHERE dt = ... prunes prefixes. Structure the partition key around your most common range filter — date — and rely on dictionary-encoded columns plus Parquet pushdown for the rest.
  • Overwriting a partition needs a clean prefix. Re-writing a day without deleting the old object leaves both files under the prefix, and Athena reads both — doubling that day’s numbers. Delete the dt= prefix’s objects before re-writing, or write a single deterministically-named data.parquet so the put overwrites in place.

Frequently Asked Questions

Why Parquet and Athena instead of loading everything into Postgres?

They solve different jobs. Postgres is ideal for the transactional, upsert-heavy idempotent backfill and for serving live dashboards. Once history grows to hundreds of millions of rows that are read in wide date ranges and rarely updated, columnar Parquet on S3 queried by Athena scans far less data per dollar and scales storage independently of compute. Many teams run both: Postgres for the hot ledger, Parquet for the cold analytical history.

Should I partition by day, month, or by tenant as well?

Partition by the dimension you filter on most, which for cost time-series is almost always the date. Daily partitions suit typical week-and-month range queries; if most queries span whole months, month partitions produce fewer, larger files and less catalog overhead. Avoid partitioning by high-cardinality tenant — it explodes the partition count and creates tiny files. Keep tenant as a dictionary-encoded column and let Parquet pushdown handle it.

Why decimal128 for amount instead of double?

Money in floating point accumulates rounding error, and a chargeback total that fails to reconcile to the invoice by a cent erodes trust in the whole ledger. decimal128(18, 6) stores the exact value, sums exactly in Athena, and matches the numeric type you would use in Postgres — so figures agree whichever store you query.

How do I handle a day that gets restated after I have written it?

Overwrite the whole partition. Delete every object under that dt= prefix and re-write the day from the corrected source, keeping the deterministic data.parquet filename so no stale file lingers. Because the partition is already registered, no catalog change is needed — the next query reads the new file. This mirrors the upsert semantics of the row-store backfill, expressed at the file level.

Does Snappy compression hurt query performance?

No — it helps. Snappy is splittable and decompresses fast, so Athena reads compressed bytes off S3 (fewer bytes billed) and parallelizes decompression across the file. Gzip compresses slightly smaller but is not splittable at the block level in the same way and costs more CPU, so Snappy is the standard choice for Athena-facing Parquet.

Back to: Batch Processing for Historical Metrics