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 Kaczmarek10 August 20264 min read

If you have ever opened a GA4 export in BigQuery for the first time and found yourself looking at a column called event_params that seemed to contain an entire table inside each row, this post is for you.
Arrays are not trying to make your life harder. Once you understand why they exist and what to do with them, they stop being intimidating.
GA4 tracks events. Each event can have many parameters. A purchase event might have a transaction ID, a value, a currency and a list of items, each item with its own name, price and quantity. Rather than create hundreds of columns to cover every possible combination, GA4 stores related values together in arrays.
This means event_params is not a broken column. It is a structured collection of key-value pairs sitting inside each row. The same applies to items for purchase data and user_properties for user-level attributes.
The core operation for working with arrays is UNNEST. It expands an array column into individual rows so you can filter and select from it like a normal table.
The most common pattern in GA4 data is a correlated subquery, pulling a single parameter value out inline:
SELECT
event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
FROM `project.dataset.events`
If you need to filter on a parameter or work with multiple values from the same array, the comma join is cleaner:
SELECT
event_name,
ep.value.string_value AS page_location
FROM `project.dataset.events`,
UNNEST(event_params) AS ep
WHERE ep.key = 'page_location'
One thing worth knowing: the comma syntax is a cross join. If a row has an empty array, that row is dropped from the results. If you need to keep rows with empty arrays, use LEFT JOIN UNNEST instead.
Join marketing, customer and product data with tested models and documentation your team can use. Start with the sources needed for your question.
When you click an array column in the BigQuery UI it often auto-fills something like:
ecomm_events[SAFE_OFFSET(0)].transaction_id
SAFE_OFFSET(0) picks the element at position zero — the first item in the array. It returns NULL rather than erroring if the array is empty, which is why it looks safe. The problem is that it only ever looks at that one position. If the value you want is not at position zero, you get NULL and no indication that anything went wrong.
For picking a specific position when you are certain the structure is consistent, SAFE_OFFSET is fine. For searching across all elements in an array, use UNNEST with a filter:
SELECT ds.*
FROM `project.dataset.ga4_daily_snapshot` AS ds,
UNNEST(ds.ecomm_events) AS ee
WHERE ee.transaction_id = 'abc123'
BigQuery recently added ARRAY_FIRST() and ARRAY_LAST(). If you have an ordered array and want the first or last element, you no longer need to reach for SAFE_OFFSET:
-- Old way
session_attribution[SAFE_OFFSET(0)]
session_attribution[SAFE_OFFSET(ARRAY_LENGTH(session_attribution) - 1)]
-- New way
ARRAY_FIRST(session_attribution)
ARRAY_LAST(session_attribution)
These make the most sense when your array already has a meaningful order. For example, attribution events sorted by timestamp. First and last then map directly to first touch and last touch:
SELECT
session_id,
ARRAY_FIRST(attribution_events) AS first_touch,
ARRAY_LAST(attribution_events) AS last_touch
FROM sessions
One thing to watch: unlike SAFE_OFFSET, there is no SAFE_ARRAY_FIRST() or SAFE_ARRAY_LAST(). If the array is empty, these functions error rather than returning NULL. If your data can have empty arrays, check with ARRAY_LENGTH first or wrap in a CASE.
The functions above are for reading arrays. ARRAY_AGG is for building them, aggregating individual rows into an array, usually with an order:
SELECT
session_id,
ARRAY_AGG(page_path ORDER BY event_timestamp ASC) AS page_sequence
FROM events
GROUP BY session_id
This gives you one row per session with every page visited in order, a pattern that comes up constantly in session analysis.
BigQuery is actively adding more array functions. ARRAY_FILTER for keeping only elements that meet a condition and ARRAY_TRANSFORM for applying a function to every element are in the pipeline. Worth keeping an eye on the BigQuery release notes, these are the kind of updates that quietly save you from writing a lot of verbose UNNEST logic.
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