SQL Assertions for Monotonic and Sequence Columns

SQL assertions for monotonic and sequence columns enforce ordering guarantees at the data layer rather than leaving them to application code, and writing one correctly requires knowing the exact contract it tests. A column is monotonic if its values never reverse direction. Ascending means each value is greater than or equal to the one before it; strictly ascending tightens that to require each value be greater, with no ties allowed. Descending and strictly descending work the same way in the other direction. A constant column, one where every value is identical, counts as monotonic too: nothing ever decreases, so the definition holds by default. Amazon Kinesis Data Analytics's SQL Reference lays out all five of these modes, ascending, strictly ascending, descending, strictly descending, and constant, and it treats the constant case as monotonic rather than as some edge case needing separate handling.

A sequence column is a narrower idea. It's a column whose values come from a generator, an auto-increment field, an identity column, a sequence object, and the expectation is that those values increase monotonically. But monotonicity and gap-free continuity are two separate properties, and an engineer writing an assertion has to pick which one actually applies. A sequence can climb forever without ever producing a duplicate or a reversal while still skipping numbers: 1, 3, 7 is monotonic and full of holes at the same time.

That separation matters most with primary keys. A primary key generated by auto-increment typically only needs to be unique and non-null, not continuous. Asserting gap-free continuity on a column like that tests the wrong thing: it conflates identity assignment with ordering integrity, and it will fail on a healthy table the first time a transaction gets declined.

Event stream positions sit on the other side of that line. A stream position is usually expected to stay monotonic and, depending on the system, gap-free within a given stream, and an assertion on that column has to address ordering and concurrency directly rather than borrowing the looser contract that primary keys get away with. Mechanically, a monotonicity assertion works by tracking a running maximum across the column's history and checking that each new value never falls below it. That's equivalent to asking whether any row, anywhere in the sequence, reverses the order established by everything that came before it. Keeping the sequence contract and the monotonicity contract distinct in your head is what lets the rest of this piece talk about gap detection and ordering checks as two different jobs.

The declarative-vs-runtime split and what each approach actually guarantees

Declarative constraints and runtime assertions look similar on the surface, since both claim to enforce ordering, but they guarantee fundamentally different things. A declarative constraint stops a bad write before it lands. A runtime assertion takes the engineer's word for it and moves on without checking. A declarative constraint catches a violation at the moment it happens, while a runtime assertion only discovers it later, if ever.

Under the declarative model, a column is declared monotonic up front, and an index manager or constraint checker evaluates every incoming write against the running maximum before the write is allowed to commit. A write that would break the ordering gets refused. Some engines can even carry this guarantee forward automatically: if an expression is built entirely out of arguments already known to be monotonic, the engine can deduce that the result is monotonic too, without the engineer re-declaring it at every step.

Runtime assertions work differently. Amazon Kinesis Data Analytics's MONOTONIC() function wraps an expression and declares it increasing, and the server accepts that declaration without checking whether it's true. A MONOTONIC() declaration is a performance contract. It tells the engine how to process the stream efficiently, and it says nothing about whether the stream is actually ordered the way the declaration claims.

Most OLTP databases and a good share of pipeline frameworks don't offer a clean declarative path for this kind of ordering constraint. Engineers working in those environments usually reach for CHECK constraints or triggers to approximate what a native declarative constraint would give them. Those substitutes carry their own exposure under concurrent writes. The same gap between when a value is computed and when it's actually visible to other transactions that makes runtime assertions risky is what breaks timestamp-based ordering under load.

Timestamp Columns as Ordering Columns Under Concurrent Inserts

A received_at or NOW() timestamp column feels like a natural way to track insert order, but it isn't reliable under concurrent inserts, because the moment a transaction starts and the moment it commits are two different points in time that don't have to land in the same order. Any assertion or FIFO query built on that column ends up trusting a clock that two racing transactions can read in reverse.

The failure mechanism is straightforward once you see it. A FIFO claim built on that ordering doesn't occasionally pick the wrong row. It picks the wrong row reliably, every time two inserts race closely enough for their timestamps to land out of commit order.

The fix is to stop using a timestamp for ordering and introduce a dedicated sequence column, commonly called seq, backed by a database-native generator: SERIAL on Postgres, AUTO_INCREMENT PRIMARY KEY on MySQL, IDENTITY on SQL Server. That value gets assigned once per row at INSERT time, in the order INSERT statements are actually issued, rather than by a clock that two concurrent transactions can read in whatever order the scheduler happens to run them. This solves the commit-order failure. It still only guarantees insert order, not the order in which some downstream event occurred, a narrower guarantee that needs to be understood as such.

The fix only works if it's applied everywhere the ordering matters. Every index supporting these queries needs updating too, with seq added as the trailing column so it actually matches the new ORDER BY; without that, the query plan may fall back to a slow sort or silently use a stale index.

Testing this fix means deliberately breaking the assumption it depends on. Seed received_at values out of step with actual insertion order, then assert that the row inserted first, identified by seq rather than by timestamp, is the one a FIFO claim actually returns. To confirm the test is meaningful rather than coincidentally passing, revert the ORDER BY to received_at and confirm the test fails on the database dialects in question, for the reason just described, not for some unrelated reason.

Writing the SQL assertion for a monotonic column: the core pattern and its variants

Once insert order is tracked by an actual sequence column rather than a clock, you can write the assertion that checks it by comparing each row against the one before it. The standard tool for that comparison is a window function: LAG() pulls the previous row's value into the current row so the two can be compared directly, and the assertion flags any row where that comparison fails.

A skeleton version of the check looks like this:

The operator in the WHERE clause is doing the real work. `seq < prev_seq` flags a violation of plain ascending order, while `seq <= prev_seq` flags a violation of strictly ascending order, since it also catches ties. Which one belongs in the assertion depends on what kind of column is being checked. Using the wrong one in either direction breaks the assertion: strict ascending on a timestamp column throws false positives on every legitimate tie, while plain ascending on an identity column lets a genuine duplicate slip through unflagged.

The first row in any ordered set returns NULL from LAG(), since it has no predecessor to compare against, so the assertion needs to exclude that row rather than let a NULL comparison produce an unexpected result. The `WHERE prev_seq IS NOT NULL` clause above handles that, though a COALESCE around the comparison works just as well.

Partitioning matters as much as the comparison operator. If a table holds interleaved sequences from multiple sources, say, one sequence per stream, per trading symbol, or per customer, running the LAG() check against a single global ORDER BY treats all of those interleaved sequences as one combined sequence. That produces false positives: a row from a different partition landing in between two rows of the sequence being checked looks exactly like a reversal, even though nothing is actually wrong. Adding `PARTITION BY stream_id` (or whatever the relevant grouping key is) inside the OVER() clause fixes this, since LAG() then only looks back within the same partition.

To use this as a working assertion inside a pipeline, wrap the comparison in a CTE as shown above and treat any non-zero row count from the final SELECT as the failure signal. The Amazon Kinesis Data Analytics documentation frames a closely related goal: it describes asserting that an orderId column stays ascending, or that no orderId ever falls more than a defined number of rows from sorted order, and it also notes that declared sort keys weren't supported in the version of the service it documents. That second detail is useful precisely because it shows how much flexibility the tolerance parameter carries: the assertion doesn't have to be all-or-nothing, and a bounded tolerance for near-monotonic columns, where values occasionally arrive a few positions out of order but never further than that, can be a legitimate design choice rather than a compromise.

## Writing the SQL Assertion for Sequence Gaps: the LEAD()-Based Pattern

Checking that a column never reverses direction and checking that it never skips a value are two different assertions, even though they get grouped together in casual conversation about sequence integrity. The right assertion depends entirely on which of those two properties the column is actually supposed to have.

Where the LAG()-based pattern looks backward to catch reversals, gap detection looks forward using LEAD() to catch missing values:

The logic is symmetric with the monotonicity check but flipped in direction: LEAD() pulls in the next row's value instead of the previous one, and the assertion flags any row where that next value exceeds the current value by more than 1. The last row in the sequence returns NULL from LEAD(), since it has no successor to compare against, and that row needs to be excluded the same way the first row was excluded in the LAG() version.

What this assertion is really testing is an absence, and absences are harder to catch than reversals because nothing fires on its own when a value simply never arrives. The LEAD() pattern is valuable specifically because it turns that silent absence into a concrete, measurable condition, a gap size greater than 1, that a query can detect and flag.

Whether a gap found this way is a problem depends entirely on what the column represents. On financial transaction sequences, where every number issued has to be accounted for by regulation or audit policy, a gap is a finding that needs an explanation on record, not just a log entry somewhere.

Timing matters here too. An event-driven approach that tracks the last-seen value per partition and compares each new arrival against it as it lands closes that window and gets close to real-time detection, at the cost of more infrastructure to maintain than a scheduled batch query.

## The failure modes that correct SQL syntax does not prevent

A LAG() or LEAD() query with flawless syntax can still miss the violation it was written to catch, and the reason is almost never the SQL itself. The scope, the timing, or the assumptions baked into the assertion don't match the actual conditions the data is running under.

Backfills are the clearest example. Either the assertion needs to be suspended for the duration of the backfill, or the backfilled rows need to be tagged and excluded from the check, and skipping that step means the assertion will reliably fire on data that was never actually wrong.

Concurrent writes create a second failure mode, and it traces straight back to the timestamp issue described earlier. A LAG() check written with perfectly correct syntax will still validate the wrong ordering if the column it orders by isn't actually monotonic under concurrency, the way received_at isn't. The assertion is only as trustworthy as the column feeding it, and no amount of correct window-function syntax fixes a column that was the wrong choice to begin with.

Partitioning mistakes cause failures in both directions at once. That produces false positives, where a brand-new stream starting its own count from a low value looks like a reversal of the global order, and false negatives, where a genuine reversal inside one partition gets hidden by the overall ordering of the combined table.

Runtime assertions carry a failure mode that's quieter than any of these: it doesn't produce an error. A MONOTONIC() declaration that turns out to be wrong doesn't just fail silently, it actively misleads the engine into dropping aggregate state it believes it will never need again, which can produce results that are wrong without any signal that something went bad. Amazon Kinesis Data Analytics's own documentation is direct about this: if the expression declared monotonic through MONOTONIC() is, in fact, not monotonic, the resulting behavior of the service is unspecified. That's the sharpest version of trusting a declaration without verifying it, raised earlier. A declarative constraint refuses the bad write and tells you immediately. A runtime assertion that's wrong just keeps running, keeps optimizing based on a false premise, and leaves you to discover the problem only when someone notices the output doesn't add up.