Querying Latest Available Partition


I am building an ETL pipeline using primarily state machines, Athena, and the Glue catalog. In general things work in the following way:

  1. A table, partitioned by "version", exists in the Glue Catalog. The table represents the output destination of some ETL process.
  2. A step function (managed by some other process) executes "INSERT INTO" athena queries. The step function supplies a "version" that is used as part of the "INSERT INTO" query so that new data can be appended into the table defined in (1). The table contains all "versions" - it's a historical table that grows over time.

My question is: What is a good way of exposing a view/table that allows someone (or something) to query only the latest "version" partition for a given historically partitioned table?

I've looked into other table types AWS offers, including Governed tables and Iceberg tables. Each seems to have some incompatibility with our existing or planned future architecture:

  1. Governed tables do not support writes via athena insert queries. Only Glue ETL/Spark seems to be supported at the moment.
  2. Iceberg tables do not support Lake Formation data filters (which we'd like to use in the future to control data access)
  3. Iceberg tables also seem to have poor performance. Anecdotally, it can take several seconds to insert a very small handful of rows to a given iceberg table. I'd worry about future performance when we want to insert a million rows.

Any guidance would be appreciated!

1 Answer

I believe CREATING a VIEW with the latest version and exposing that view to the user would be appropraite.

CREATE OR REPLACE  VIEW latest_version_only AS 
select * from "datalake"."table1" 
where version_no = (SELECT MAX(version_no) FROM "datalake"."table1");
profile pictureAWS
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions