Partitions

Partitioning a table does two things: it reduces what Athena has to read, and it lets you keep in the data lake a history that no longer exists in the database.

Without configuration here, every table is copied straight through — which works well until the table grows.

Why partition

A table partitioned by year and month becomes directories in S3:

catalog/{cluster}/vendas.pagamentos/ano=2024/mes=03/*.parquet

When the query says WHERE ano = 2024 AND mes = 3, Athena reads only that directory. Without partitions, it reads the whole table and you pay for it.

Configure

Under Partições (Partitions), create a configuration by choosing the cluster, the database and the table. Then define the partition columns: an expression that computes the value and the name of the resulting column.

year(CAST(payment_date AS TIMESTAMP))   como   ano
month(CAST(payment_date AS TIMESTAMP))  como   mes

Dates arrive as text

The RDS export delivers date columns as text. Every date expression needs an explicit CAST, as in the example.

Order matters: from broadest to narrowest. Year before month, month before day.

How many levels to use

Each distinct combination of values becomes a directory, and too many directories make writing slower and can exhaust the job’s memory.

Data Pump works out on its own how many fit, from the available memory and the width of the table — a 40-column table supports far fewer directories than a 3-column one. When it goes past the limit, it writes in batches instead of failing.

Even so, it is worth choosing deliberately: partitioning by day on a table with years of history generates thousands of directories, and queries rarely need that granularity. Year and month are usually enough.

Sorting

The sort field exists, but it is expensive: the whole table has to be sorted before the first row is written. Parquet already stores the minimum and maximum of each block, so Athena filters well without it. Use it only if you know why.

Replace the direct copy

By default, a partitioned table appears twice in the data lake: the whole copy and the partitioned version, side by side. That is deliberate — it lets you compare the two during a migration.

When you want only the partitioned one, turn on substituir a cópia direta (replace the direct copy).

A combination that makes the table disappear

With “replace the direct copy” on and the partition disabled, the table stops being written entirely: the copy skips it and the partitioner does not run. The console warns you before saving, but the case is worth knowing.

Long-term history

Turn on manter histórico de longo prazo (keep long-term history) and the table starts being written to an area that is not erased at every run.

That is what lets you purge old data from Postgres without losing it: the month that leaves the database stays queryable in the data lake. catalog/ reflects the database today; longterm/ accumulates.

At each run, only the partitions present in that export are rewritten. The ones that no longer come from the database stay intact.

Update rows that changed

History on its own works well for a transactional table, where a row is born and never changes. But if a row already ingested is corrected in the database, the old version stays in the data lake — and it may be in a partition that no longer comes in the export.

Turn on atualizar linhas que mudaram (update rows that changed) and provide:

• Key column — identifies the row, so you know which one to replace
• How to recognize what changed — a condition that selects, in the export, the rows altered since the last ingestion (e.g. updated_at > current_date - 7)

Data Pump locates the files that contain those rows and swaps the old version for the new one.

Before each replacement, the previous state is saved to longterm/overrides/{export}/. It is the only record of what was swapped: if a replacement goes wrong, that is what you rebuild from. Reprocessing the same export does not overwrite that record.

Repeating the same ingestion

Running the same export twice is safe. Writing erases the partition before rewriting it, and the replacement matches by key column — the result is the same as running it once.