Full loading

Full loading is the act of fully reloading the data of your tables. All existing data will be removed and replaced by whatever the source produced on this run. Resources that are not selected while performing a full load will not replace any data in the destination.

Performing a full load

To perform a full load on one or more of your resources, choose the write_disposition='replace' for this resource:

p = dlt.pipeline(destination="bigquery", dataset_name="github")
issues = []
reactions = ["%2B1", "-1", "smile", "tada", "thinking_face", "heart", "rocket", "eyes"]
headers: dict[str, str] = {}
repo_name = "dlt-hub/dlt"
for reaction in reactions:
    for page_no in range(1, 3):
      page = requests.get(f"https://api.github.com/repos/{repo_name}/issues?state=all&sort=reactions-{reaction}&per_page=100&page={page_no}", headers=headers)
      print(f"Got page for {reaction} page {page_no}, requests left", page.headers["x-ratelimit-remaining"])
      issues.extend(page.json())
p.run(issues, write_disposition="replace", primary_key="id", table_name="issues")

caution

All tables that belong to a replace resource are truncated on each load, including nested tables and tables created by dispatching to many tables or as table variants.

Note that a table does not need to receive any data to get truncated.

Replace with incremental loading

The shorthand and the dictionary form of the replace write disposition handle the resource state differently:

• The shorthand write_disposition="replace" resets the resource state on each run. An incremental on such a resource starts from its initial_value again, and dlt reloads the table from scratch.
• The dictionary form write_disposition={"disposition": "replace"} keeps the resource state by default. The incremental continues from the last saved cursor and extracts only the new or changed records. The table holds only these records, which is useful when a downstream tool merges them.

The exclude_resource_state option of the dictionary form defaults to True, so these two resources behave the same:

@dlt.resource(write_disposition={"disposition": "replace"})
def clients(updated_at=dlt.sources.incremental("updated_at")):
    ...


@dlt.resource(write_disposition={"disposition": "replace", "exclude_resource_state": True})
def clients_explicit(updated_at=dlt.sources.incremental("updated_at")):
    ...

Set exclude_resource_state to False to reset the state like the shorthand does. A run that yields no records truncates the table, so the table can be empty. For an explicit full refresh, use the refresh option instead.

Choosing the correct replace strategy for your full load

dlt implements three different strategies for doing a full load on your table: truncate-and-insert, insert-from-staging, and staging-optimized. The exact behavior of these strategies can also vary between the available destinations.

You can select a strategy with a setting in your config.toml file. If you do not select a strategy, dlt will default to truncate-and-insert.

[destination]
# Set the optimized replace strategy
replace_strategy = "staging-optimized"

The truncate-and-insert strategy

The truncate-and-insert replace strategy is the default and the fastest of all three strategies. If you load data with this setting, then the destination tables will be truncated at the beginning of the load, and the new data will be inserted consecutively but not within the same transaction. The downside of this strategy is that your tables will have no data for a while until the load is completed. You may end up with new data in some tables and no data in other tables if the load fails during the run. Such an incomplete load may be detected by checking if the _dlt_loads table contains a load id from _dlt_load_id of the replaced tables. If you prefer to have no data downtime, please use one of the other strategies.

The insert-from-staging strategy

The insert-from-staging strategy is the slowest of all three strategies. It will load all new data into staging tables away from your final destination tables and will then truncate and insert the new data in one transaction. It also maintains a consistent state between nested and root tables at all times. Use this strategy if you have the requirement for consistent destination datasets with zero downtime and the optimized strategy does not work for you. This strategy behaves the same way across all destinations.

The staging-optimized strategy

The staging-optimized strategy has all the upsides of the insert-from-staging but implements certain optimizations for faster loading on some destinations. This comes at the cost of destination tables being dropped and recreated in some cases, which means that any views or other constraints you have placed on those tables will be dropped with the table. If you have a setup where you need to retain your destination tables, do not use the staging-optimized strategy. If you do not care about tables being dropped but need the upsides of the insert-from-staging with some performance (and cost) saving opportunities, you should use this strategy. The staging-optimized strategy behaves differently across destinations:

• Postgres: After loading the new data into the staging tables, the destination tables will be dropped and replaced by the staging tables. No data needs to be moved, so this strategy is almost as fast as truncate-and-insert.
• BigQuery: After loading the new data into the staging tables, the destination tables will be dropped and recreated with a clone command from the staging tables. This is a low-cost and fast way to create a second independent table from the data of another. Learn more about table cloning on BigQuery.
• Snowflake: After loading the new data into the staging tables, the destination tables will be dropped and recreated with a clone command from the staging tables. This is a low-cost and fast way to create a second independent table from the data of another. Learn more about table cloning on Snowflake.

For all other destinations, please look at their respective documentation pages to see if and how the staging-optimized strategy is implemented. If it is not implemented, dlt will fall back to the insert-from-staging strategy.