MONOTONICALLY_INCREASING_ID

Generate a 64-bit integer id that is unique and strictly increasing within a single query execution.

Syntax

MONOTONICALLY_INCREASING_ID()

Description

## Overview Returns a 64-bit integer that is unique and strictly increasing within a single query execution. Ids are assigned per partition: within a partition they are contiguous, but across partitions the ids jump in large blocks so there are visible gaps. Use this function when you need a synthetic per-query row id (for debugging, temporary deduplication, or stable projection ordering), not when you need a persistent primary key. The assignment is non-deterministic across query executions: running the same query twice produces different id values. ## Behavior - Unique within a single query execution. - Monotonically increasing in partition-scan order, but with gaps between partitions. - Returns a BIGINT. - Not deterministic across executions; do not persist as a stable key. - Never returns NULL. - Side effect free. ## Compatibility - Standard analytical SQL primitive for per-query id assignment.

Examples

-- Assign row ids in a projection
SELECT MONOTONICALLY_INCREASING_ID() AS row_id, *
FROM retail.catalog.products;

-- Generate ids inside a CTE so they can be referenced later
WITH numbered AS (
  SELECT MONOTONICALLY_INCREASING_ID() AS rn, name
  FROM retail.catalog.products
)
SELECT rn, name FROM numbered WHERE rn < 100;

-- Combine with metadata columns to diagnose data layout
SELECT MONOTONICALLY_INCREASING_ID() AS id, INPUT_FILE_NAME() AS file
FROM obs.catalog.events;

-- Don't use as a surrogate primary key across runs; ids are per-query only
SELECT MONOTONICALLY_INCREASING_ID() AS tmp_id, *
FROM retail.catalog.products
LIMIT 10;

Pitfalls

• MONOTONICALLY_INCREASING_ID is unique but NOT contiguous. Ids jump in large blocks between partitions. If you need 1..N dense numbering, use ROW_NUMBER_GENERATE or the ROW_NUMBER() window function.
• The assignment is non-deterministic across query executions. Persisting these ids as primary keys means the same row gets a different id next time; use UUID or a sequence for stable keys.
• Sort order of the ids reflects scan order, not logical order. A query without ORDER BY may produce different id sequences across runs even when the underlying data is unchanged.
• Comparisons between ids across queries are meaningless. Treat ids as opaque integers within a single query only.

See Also

• row_number_generate
• generate_series
• input_file_name
• spark_partition_id
• uuid