last_snapshot_version function

Returns the version of the most recently committed snapshot for the enclosing AUTO CDC ... FROM SNAPSHOT flow. The committed snapshot can come from a previous pipeline update or from an earlier iteration of the current update.

This function is available only inside the WITH VERSION (...) clause of an AUTO CDC ... FROM SNAPSHOT flow.

Syntax

last_snapshot_version()

Arguments

This function takes no arguments.

Returns

A single-column relation with the same column name and type as the enclosing flow's version query, containing 0 or 1 rows:

  • 1 row containing the most recently committed snapshot version.
  • 0 rows if the flow has not committed a snapshot since its initial creation or most recent full refresh.

After each snapshot commits successfully, last_snapshot_version() exposes that committed version to the next evaluation of the version query, including evaluations within the same pipeline update. This allows one update to select and process multiple versions in order.

Examples

The following flow uses last_snapshot_version() to find the next file to process after the last one that committed:

CREATE FLOW orders_cdc
  AS AUTO CDC INTO orders
  FROM SNAPSHOT (...)
  WITH VERSION (
    SELECT struct(modification_time, path) AS version
    FROM list_files('/Volumes/catalog/schema/landing/orders/')
    WHERE (
      NOT EXISTS (SELECT 1 FROM last_snapshot_version())
      OR struct(modification_time, path) > (SELECT version FROM last_snapshot_version())
    )
    ORDER BY modification_time, path
    LIMIT 1
  )
  KEYS (order_id);

Errors

last_snapshot_version() is available only inside the WITH VERSION (...) clause of an AUTO CDC ... FROM SNAPSHOT flow. Anywhere else, Spark resolves the name as an ordinary table-valued function. Because no such function is defined, it raises UNRESOLVABLE_TABLE_VALUED_FUNCTION (SQLSTATE 42883).

  • Last updated on