A running total jumps across equal timestamps

Illustrative learning scenario; not a claimed client engagement. A fictional warehouse ledger has two credits with the same event time. A running total ordered only by that time gives both rows the same cumulative amount. The intended report needs a separate step for each event, not a timestamp-level total.

Symptoms

Choose an explicit ROWS frame and deterministic ordering for event-by-event totals.

Diagnosis

1. Inspect duplicate timestamps inside each account partition before changing the query.
2. Compare the default SUM frame with an explicit ROWS frame on a two-event tie.
3. Identify a stable, meaningful event sequence. A unique key creates reproducibility but may not represent business chronology.

Runnable minimal example

Snowflake SQL; read-only CTE fixture. Not executed in a Snowflake account; expected output is documentation-reviewed.

WITH ledger AS (
  SELECT column1::INTEGER event_id,
         column2::INTEGER account_id,
         column3::TIMESTAMP_NTZ event_time,
         column4::NUMBER(12,2) amount
  FROM VALUES
    (1, 7, '2026-10-31 09:00:00', 10.00),
    (2, 7, '2026-10-31 09:00:00', 20.00),
    (3, 7, '2026-10-31 10:00:00', -5.00)
)
SELECT event_id,
  SUM(amount) OVER (PARTITION BY account_id ORDER BY event_time)
    timestamp_total,
  SUM(amount) OVER (
    PARTITION BY account_id ORDER BY event_time, event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) event_total
FROM ledger
ORDER BY event_time, event_id;

Expected output — not executed

EVENT_ID | TIMESTAMP_TOTAL | EVENT_TOTAL
1        | 30.00           | 10.00
2        | 30.00           | 30.00
3        | 25.00           | 25.00

Fix and boundaries

For event-level accumulation, specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and order by a complete stable sequence. In this fixture event_id is the agreed within-timestamp sequence. If the business requirement is totals per timestamp, retaining peer-group behavior or aggregating to timestamp grain is valid.

Why it works

For ordered SUM, the implicit RANGE frame includes all peers at the current ordering value. Both 09:00 events therefore see 30. The explicit ROWS frame advances through individual rows using event_id to resolve the tie. The final ORDER BY controls display order; it does not define either window calculation.

Prevention

Document output grain and frame semantics in analytic queries. Include equal timestamps, multiple accounts and negative adjustments in tests. Check that a proposed tie-breaker remains stable across replay and has agreed business meaning.

Practice exercise

Add account 8 with a credit of 40 at 09:00. Verify that its totals stay separate. Then remove event_id from the ROWS ordering and explain why the two account-7 intermediate totals are no longer reliably assigned.

Documentation

• Snowflake window syntax and frames
• Snowflake SUM