# Mastering data loading in BigQuery using Dataform

> What truncate and load means, and when to append or use incremental tables with unique keys instead. Dataform examples for BigQuery.

Source: https://www.measurelab.co.uk/insights/blog/dataform-truncate-load-append-or-insert-incremental-tables-and-more/  
Author: Prasanna Venkatesan  
Published: 2025-02-07  
Updated: 2025-09-03

Efficient data loading is crucial for managing and updating tables in [Dataform](https://cloud.google.com/dataform?hl=en). Various strategies exist to handle different use cases, including truncate and load, appending data, and leveraging incremental tables with unique keys. This blog explores these primary methods and more:

#### Truncate and Load

In this method, all existing records in the target table are **deleted and replaced** with a fresh table. This approach works well when a full table refresh is necessary or if managing slowly changing data.

##### Implementation in Dataform:

- Set the table type to**table**.

##### Example:

```
config {
  type: "table"
}

SELECT
  id,
  name,
  timestamp
FROM
  ${ref("source_table")}
```

By defining the table type as table, Dataform ensures that each run recreates the table, effectively performing a truncate and load operation.

#### Append or Insert-Only Loads

This method appends new data to an existing table while preserving historical records. It is ideal for use cases where past data must remain unchanged, and only new records are added.

##### Implementation in Dataform:

- Set the table type to**incremental**.
- Define an **incremental condition** to capture only new records.

##### Example:

```
config {
  type: "incremental"
}

SELECT
  id,
  name,
  timestamp
FROM
  ${ref("source_table")}
WHERE
  timestamp = CURRENT_DATE() - 2
```

This ensures that only records from two days ago, based on CURRENT_DATE() - 2, are appended to the target table.

#### Incremental Loads with Unique Keys

This method ensures that only **one row per unique key** is retained and updates existing records if changes occur. This is useful for deduplicating or updating records efficiently.

#### Book your free Dataform consultation

##### Implementation in Dataform:

- Set the table type to**incremental**.
- Define an **uniqueKey** to update existing records with new data instead of inserting duplicates.

##### Example:

```
config {
  type: "incremental",
  uniqueKey: "id"
}

SELECT
  id,
  name,
  position,
  timestamp
FROM
  ${ref("source_table")}
WHERE
  timestamp = CURRENT_DATE() - 2
```

Dataform **automatically** handles the merge process—if a record with the same id already exists, it gets updated. If a new id appears, it is inserted as a new row.

#### Incremental Load with Rolling Delete

This method ensures that before inserting new data incrementally, the system deletes records from the last two days to accommodate any late-arriving updates. This is useful for ensuring data freshness while still maintaining an incremental approach.

##### Implementation in Dataform:

- Set the table type to**incremental**.
- Use a **pre-operation delete step** to remove data from the last two days before inserting new records.

##### Example:

```
config {
  type: "incremental"
}

pre_operations {
  DELETE 
  FROM 
    ${self()} 
  WHERE 
    timestamp >= CURRENT_DATE() - 2;
  }

  SELECT
    id,
    name,
    position,
    timestamp
  FROM
    ${ref("source_table")}
  WHERE
    timestamp >= CURRENT_DATE() - 2
```

This ensures that any late-arriving updates from the last two days are reflected correctly while keeping the rest of the data intact.

#### Choosing the Right Method in Dataform

Understanding these approaches in Dataform allows you to optimise your ETL/ELT workflows and effectively manage data changes for various use cases in [BigQuery](https://cloud.google.com/bigquery?utm_source=google&utm_medium=cpc&utm_campaign=emea-gb-all-en-dr-bkws-all-all-trial-%7Bmatchtype%7D-gcp-1707574&utm_content=text-ad-none-any-DEV_%7Bdevice%7D-CRE_%7Bcreative%7D-ADGP_%7B_dsadgroup%7D-KWID_%7B_dstrackerid%7D-%7Btargetid%7D-userloc_%7Bloc_physical_ms%7D&utm_term=KW_%7Bkeyword%7D-NET_%7Bnetwork%7D-PLAC_%7Bplacement%7D&%7B_dsmrktparam%7D%7Bignore%7D&%7B_dsmrktparam%7D&gclsrc=aw.ds&gad_source=1&gclid=Cj0KCQiA-5a9BhCBARIsACwMkJ6nsZ3gToA0rKv88tvq3mUEvy1hAtY2sDJXSfoORrYpkJRMw5DpFu8aArONEALw_wcB&gclsrc=aw.ds&hl=en) and other Data Warehouses.

Have you used any of these methods in Dataform? Reach out to let us know, and [contact us](https://www.measurelab.co.uk/contact/) if we can help you with anything Dataform/BigQuery related! 🚀