Why your Dataform incremental table keeps adding duplicates

Your Dataform workflow finishes successfully. The tables refresh on schedule. Everything looks fine.

But when you check the data, you notice something strange: the event count keeps growing, even when the source data hasn’t changed.

What’s going on?

One possible cause is a missing value in the merge key: the field Dataform uses to recognise whether a record already exists.

This can be particularly relevant when working with GA4 exports, where user and session identifiers are not always available. If your incremental table revisits recent data, a missing value in the key can cause the same event to be inserted again.

Let’s look at why this happens and how to prevent it.

First, what is an incremental table?

An incremental table allows you to process a smaller amount of data on each run, rather than rebuilding the whole table every time.

For example, instead of processing all your GA4 history every day, you might choose to refresh only the most recent few days.

This can save time and reduce the amount of data processed. It can also help you capture events that arrive late or have been updated since the previous run.

The period you choose to revisit is often called a lookback window.

For example, a three-day lookback might work like this:

Run date	Data processed
Thursday	Tuesday to Thursday
Friday	Wednesday to Friday

Wednesday and Thursday are processed more than once. That overlap is intentional: it gives the pipeline another opportunity to pick up late-arriving data.

The important question is: when Dataform sees those records again, does it recognise them?

The date filter and the key do different jobs

It helps to think of the date filter and the merge key as answering two different questions:

• The date filter: Which records should we process?
• The merge key: Does this record already exist in the table?

The date filter brings recent records back into the pipeline. The merge key helps Dataform decide whether to update an existing row or insert a new one.

In Dataform, you can specify the matching column using uniqueKey:

config {
  type: "incremental",
  uniqueKey: ["event_key"]
}

With a matching key, an existing row can be updated. If no matching row is found, a new row is inserted.

Without a uniqueKey, the usual incremental behaviour is to append the rows returned by the query. If the query selects the same days again, those rows can be added again too.

Start by defining what one row represents

Before choosing a key, ask yourself:

What should one row in this table represent?

For example:

• If each row represents an event, the key needs to distinguish events.
• If each row represents a session, the key needs to distinguish sessions.
• If each row represents a daily summary, the key needs to include the date and relevant grouping fields.

This level of detail is often called the table’s grain. A key that works for a session table may not work for an event table, because a session can contain many events.

Why GA4 identifiers can be missing

When working with GA4 exports, it is tempting to assume every event will have a usable user or session identifier. In practice, that is not always the case.

Consent settings are one possible reason. With advanced consent mode, Google tags can send measurements without analytics cookies when analytics storage is denied. This can affect the identifiers available for linking events to users or sessions. 

For example, if your key uses user_pseudo_id or ga_session_id, check what happens when one of those fields is missing.

A missing identifier doesn’t automatically tell you why it is missing. Consent is one possible explanation, but it is worth investigating your implementation and checking the exported privacy_info.analytics_storage field.

It is also worth remembering that an event can still be useful for event reporting, even when it cannot be confidently linked to a user or session. Removing every event with a missing identifier may mean losing useful data.

How can a missing value create a duplicate?

Imagine a simplified merge key containing these fields:

Field	Incoming event	Existing event
Event timestamp	10:15:30.123456	10:15:30.123456
Event name	page_view	page_view
User identifier Want help with Dataform? We build and review Dataform pipelines in BigQuery: tested models, assertions and documentation your team can maintain. Explore BigQuery and Dataform consultancy	NULL	NULL

At first glance, these look like the same event. The timestamp and event name match, and both user identifiers are missing.

However, in SQL, comparing NULL with NULL using = does not return TRUE. As a result, an equality-based merge may not recognise the incoming record as a match. 

Here is what can happen:

1. The first run inserts the event.
2. The next run reads the same event again because it falls within the lookback window.
3. The merge cannot match the missing identifier.
4. The event is inserted as a new row.

After several runs, the table may contain several copies of the same event.

The surprising part is that the source data does not need to contain duplicates. They can target row.

Building a key that handles missing values

One way to address this is to create a key that handles missing values consistently.

The aim is for the same event to produce the same key each time it is processed, even when one of the selected fields is missing.

For example, you could represent the chosen fields in a structured format that includes missing values, then turn that representation into a compact key. There is a SQL example at the end of this article if you want to see how this works in BigQuery.

But handling nulls is only part of the solution.

A non-null key is not necessarily a unique key

Imagine replacing every missing user identifier with "unknown".

That may avoid a null value, but it doesn’t mean the resulting key will always be unique. If two different events share all the other fields used in the key, they could end up with the same key.

Similarly, hashing a set of fields does not make the underlying information more complete. A hash simply turns the values you provide into another representation.

A good key needs to be:

• Consistent: The same record produces the same key on every run.
• Detailed enough: Different records produce different keys at the intended table grain.
• Checked: The incoming data returns at most one row per key before it is merged.

Be careful with random keys. If a new random value is generated on every run, the same event will appear to be a new event each time.

Also, a key that allows an event to be processed consistently does not recover a missing user or session identifier. Avoid grouping all events with a missing session ID into one artificial session.

Does uniqueKey remove duplicates for me?

No. Despite its name, uniqueKey should not be treated as a database uniqueness constraint or a source deduplication step.

You still need to check the incoming query. If it returns multiple rows with the same key, a merge may insert duplicates or fail when multiple source rows match an existing target row.

The key tells Dataform how to match rows; it does not guarantee that your source query has produced one row per key.

Keep your lookback window in mind

A reliable key is important, but it is only useful for records that your incremental query actually processes.

Suppose an event happened on Monday but arrived on Wednesday. If your lookback window only covers Tuesday and Wednesday, that Monday event may be missed.

Choose a window that reflects how late data can arrive or change in your source. If older data is corrected outside that window, you may need a targeted backfill.

An advanced note on updatePartitionFilter

If you use updatePartitionFilter, remember that it limits which existing rows in the target table are considered during the merge. This can reduce the amount of data scanned, but the filter needs to work alongside your source lookback window. 

For example, if your source query reprocesses the last seven days, but the target filter only considers the last three days, an existing row from five days ago may be excluded from the match. Dataform could then insert the incoming row again, even if its key is correct.

How to check whether the fix works

Once you have updated your key and incremental logic, test the table before relying on the results.

1. Run it twice

Run the incremental process, then run it again with unchanged source data.

The second run should not add extra copies or change the expected results. If it does, investigate whether the merge is matching existing records correctly.

2. Check for missing and duplicate keys

Check both the incoming query result and the finished table.

For a table designed to contain one row per key, the following query should return no rows:

SELECT
  event_key,
  COUNT() AS row_count
FROM project.dataset.table
GROUP BY event_key
HAVING event_key IS NULL OR COUNT() > 1;

Replace the table and key names with your own. To check the incoming data, run an equivalent check against the query result that feeds the merge.

3. Test late arrivals and changes

Check that your lookback window captures late-arriving events and that an updated source record is reflected in the target table as expected.

Then compare reporting totals with the underlying records. A pipeline can finish successfully while still producing incorrect results.

4. Repair any existing duplicates

Fixing the incremental logic helps prevent the problem from continuing, but it does not automatically remove duplicates already in the table.

You may need to rebuild the affected history and refresh downstream reports. Before doing so, confirm that the source data needed for the rebuild is still available.

The takeaway

Incremental tables can make pipelines more efficient, but they depend on being able to recognise records that have already been processed.

When counts keep growing unexpectedly, check:

1. Whether your lookback window is selecting the same records again.
2. Whether your table has a suitable merge key.
3. Whether any part of that key can be missing.
4. Whether the incoming query returns one row per key.
5. Whether the target filter includes the existing rows that need to be matched.

The goal is simple: when a record is revisited, the table should recognise it and update it when needed, without quietly counting it again.

---

Optional: a null-safe key example in BigQuery

The following example shows one way to create a non-null key from a set of fields, including a missing user identifier:

WITH example_event AS (
  SELECT
    'website_a' AS source_id,
    1700000000123456 AS event_timestamp,
    'page_view' AS event_name,
    CAST(NULL AS STRING) AS user_pseudo_id
)
SELECT
  *,
  TO_HEX(
    SHA256(
      TO_JSON_STRING(
        STRUCT(
          source_id AS source_id,
          event_timestamp AS event_timestamp,
          event_name AS event_name,
          user_pseudo_id AS user_pseudo_id
        )
      )
    )
  ) AS event_key
FROM example_event;

In this example:

• STRUCT keeps the selected fields together and named.
• TO_JSON_STRING converts them into text, including the missing value.
• SHA256 and TO_HEX turn that text into a compact key.

The same values will produce the same key when the expression stays consistent.

This is an illustration of null handling, not a universal GA4 event key. The four fields shown are not guaranteed to distinguish every real event. In production, choose and validate fields that identify the intended row across your data sources and event batches. Keep the field names, types and order consistent across runs.

Reference:

• https://docs.cloud.google.com/dataform/docs/create-tables
• https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement
• https://developers.google.com/tag-platform/security/concepts/consent-mode
• https://support.google.com/analytics/answer/7029846
• https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#to_json_string
• https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/hash_functions