How to Define YAML for Incremental Data Updates in ETL Pipelines

Overview

Incremental data updates in Bold BI are achieved by combining:

1. Bold Data Hub pipelines (YAML configuration) for extracting only new or modified records.
2. Bold BI Incremental Refresh for efficiently updating datasets used by dashboards and reports.
   This article explains how to configure both components to support incremental loading while preventing duplicate records.

Prerequisites

Before configuring incremental loading, ensure the source table contains:

• A unique primary key column (for example, ticketid)
• A column that can identify newly added or modified records (for example, LastUpdatedOn)

Steps to Configure Incremental Updates

1. Create a Pipeline in Bold Data Hub

1. Navigate to Data Hub.
2. Click Add Pipeline.
3. Upload or configure the YAML connector file.
4. Save the pipeline.

2. Configure Incremental Extraction in YAML

Add the following metadata configuration for the table:

tables:
 - name: ozono
   replication_method: INCREMENTAL
   replication_key: LastUpdatedOn
   replication_value: '2023-01-01 00:00:00'
   primaryKeys:
     - device_id
     - LastUpdatedOn 

Configuration Details

3. Configure Target Load Behavior

To prevent duplicate records, configure the destination to use an appropriate load method.

load_method: upsert 

Why Use Upsert?

• New records are inserted.
• Existing records with matching primary keys are updated.
• Duplicate records are prevented when supported by the destination system.

4. Enable Incremental Refresh in Bold BI

After the pipeline creates the associated data source:

1. Open the generated data source.
2. Enable Incremental Refresh.
3. Select the last-modified or tracking column.
4. Optionally configure unique identifier columns.
5. Save the changes.

5. Schedule and Run the Pipeline

1. Configure a schedule in Bold Data Hub.
2. Run the pipeline manually or wait for the scheduled execution.
3. Monitor the execution logs for successful completion.

6. Validate the Results

After the pipeline completes:

• Insert a new record in the source table and run the pipeline again.
  Expected behavior:

1. New records should be added to the destination.
2. Existing records should not be duplicated.
3. Record counts should remain consistent across executions.

Important Concepts

Incremental Extraction

Incremental extraction is controlled by:

• replication_method
• replication_key
  The system automatically maintains a checkpoint value after each successful execution and uses it during subsequent runs.
  Important
  replication_key controls which records are extracted.
  It does not prevent duplicate records by itself.

Duplicate Prevention

• To avoid duplicate records:

YAML

metadata:
<table_name>:
  primary_key: ticketid

Always configure:

• A valid primary_key
• An upsert-based load strategy (when supported)
• A destination table with appropriate key constraints

Best Practices

• Define a primary key for every table.
• Use incremental extraction whenever possible.
• Configure an upsert load strategy to handle existing records.
• Ensure the replication key contains increasing or updated values.
• Validate record counts after each execution.
• Follow correct YAML syntax and indentation.

Expected Outcome

• After proper configuration: New records are extracted incrementally.
• Existing records are not duplicated.
• Pipeline execution remains efficient and scalable.
• Incremental refresh processes only required data.

Additional References

• Yaml (Bold Data Hub Connector)
• SQL Server data source refresh settings (Incremental update)
• Relationship between Bold Data Hub Pipeline and Associated Data Sources in Bold BI