Why your Dataform incremental table keeps adding duplicates
Your Dataform incremental table keeps adding duplicates? A NULL in the merge key could be why. Here’s how it happens with GA4 and how to stop it.
Elisa Liu3 Nov 2026
Blog
Katie Kaczmarek13 July 20263 min read

Say you want to find the entry page and exit page for every session in your events table. The natural query writes itself:
SELECT
CONCAT(ga_session_id, '-', user_pseudo_id) AS session_key,
event_timestamp,
page_path,
FIRST_VALUE(page_path) OVER (
PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
ORDER BY event_timestamp
) AS entry_page,
LAST_VALUE(page_path) OVER (
PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
ORDER BY event_timestamp
) AS exit_page
FROM base
Run this and entry_page works perfectly. exit_page returns the wrong result for every row except the last one in the session. No error, no warning, just quietly incorrect data.
Both functions use a window defined by PARTITION BY and ORDER BY. What most people do not realise is that ORDER BY also sets a default frame for what those functions consider their window.
The default frame is: from the start of the partition to the current row.
For FIRST_VALUE this does not matter. The first row of the window is always the start of the partition, regardless of how large the frame is. It returns the right value every time.
For LAST_VALUE it matters a lot. With the default frame, the last row of the window is the current row — not the end of the partition. So LAST_VALUE returns the value on the row you are currently on, which is the same as just writing page_path. It is useless, and it looks correct until you check the numbers.
Tell LAST_VALUE to look all the way to the end of the partition:
LAST_VALUE(page_path) OVER (
PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
ORDER BY event_timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS exit_page
Now the frame runs from the first row to the last row of the partition, and LAST_VALUE correctly returns the final value.
This works, but ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is long and easy to get wrong. There is a cleaner option.
FIRST_VALUE always works correctly with the default frame. So instead of fighting LAST_VALUE, reverse the sort order and use FIRST_VALUE:
SELECT
CONCAT(ga_session_id, '-', user_pseudo_id) AS session_key,
event_timestamp,
page_path,
FIRST_VALUE(page_path) OVER (
PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
ORDER BY event_timestamp ASC
) AS entry_page,
FIRST_VALUE(page_path) OVER (
PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
ORDER BY event_timestamp DESC
) AS exit_page
FROM base
ORDER BY event_timestamp DESC puts the last event at the top of the window. FIRST_VALUE picks it up correctly. No frame clause needed, and the intent is immediately readable.
This is the version most people land on once they have understood the problem.
FIRST_VALUE and LAST_VALUE are not symmetric. FIRST_VALUE works correctly with the default window frame. LAST_VALUE does not. The asymmetry is not a bug, it is how window frames are defined in SQL, but it is not something most people are taught explicitly, which is why it keeps catching people out.
Any time you reach for LAST_VALUE, either write the full frame clause or ask yourself whether reversing the sort and using FIRST_VALUE gets you there more cleanly. The choice is yours.
Join marketing, customer and product data with tested models and documentation your team can use. Start with the sources needed for your question.
Bring data to connect, a question to investigate or something to fix or build. We can agree a focused scope directly.
Your Dataform incremental table keeps adding duplicates? A NULL in the merge key could be why. Here’s how it happens with GA4 and how to stop it.
Elisa Liu3 Nov 2026
TimesFM spots unusual days in BigQuery; ARIMA_PLUS explains why. We tested both on a year of GA360 data to turn traffic drops into clear answers.
Elisa Liu3 Nov 2026
A year ago, I wrote about getting a BigQuery warehouse ready for AI ( Easy ways to prepare your BigQuery warehouse for AI) , but didn't look properly into Knowledge Catalog (known as Dataplex at the time). We decided to go back and look through Google's Knowledge Catalog properly: what's actually in there, how it works and what it relates to. What Knowledge Catalog is Knowledge Catalog is Google's metadata layer for BigQuery data (and a few other sources). It sits alongside your tables rather
Katie Kaczmarek15 Sept 2026