AS-OF Queries

Overview

AS-OF queries read a historical snapshot of an Iceberg table as it existed at a given point in time. Krishiv preprocesses the FOR SYSTEM_TIME AS OF clause and resolves it to the appropriate snapshot before handing the rewritten query to DataFusion.

Syntax

SELECT *
FROM <table> FOR SYSTEM_TIME AS OF TIMESTAMP '<iso-timestamp>'

-- or using a binding expression
SELECT *
FROM <table> FOR SYSTEM_TIME AS OF <timestamp_expr>

Example

-- Read the orders table as it was at a specific point in time
SELECT customer_id, SUM(amount) AS total
FROM orders FOR SYSTEM_TIME AS OF TIMESTAMP '2024-01-15 12:00:00'
GROUP BY customer_id;

Notes

• Only supported on Iceberg tables registered under a KrishivCatalog catalog.
• The snapshot closest to and not after the given timestamp is selected.
• Multiple AS-OF refs in the same query are resolved independently.
• Requires Iceberg catalog configured with the iceberg feature.