Create an Incremental Microsoft SQL Server Read Component

This guide walks you through building an Incremental Microsoft SQL Server Read Component that ingests only new or updated records.

Prerequisites

• Ascend Flow
• Microsoft SQL Server Connection

Create a new Component

Begin from your workspace Super Graph view. Follow these steps to create your component:

Using the Component Form

1. Double-click the Flow where you want to create your component
2. Right-click anywhere in the Flow Graph
3. Hover over Create Component, then over Read in the expanded menu, and click From Scratch
4. Complete the form with these details:
   • Select your Flow
   • Enter a descriptive Component Name like read_sales
   • Select YAML as your file type

Using the Files Panel

1. Open the files panel in the top left corner
2. Navigate to and select your desired Flow
3. Right-click on the components directory and choose New file
4. Name your file with a descriptive name like read_sales.yaml and press enter

Create your Incremental Microsoft SQL Server Component

Structure your Microsoft SQL Server Incremental Read Component following this pattern:

1. Reference your Microsoft SQL Server connection: Specify which connection to read from.
2. Add the mssql key – specify the table you want to read from. In this example, we use the my_table table in the my_schema schema.
3. Add the replication key – specify the incremental replication strategy using a column to track changes:

   strategy:
     replication:
       incremental:
         column_name: last_modified  # Column used for tracking incremental updates to the data.
   tip

   Your incremental column should be a timestamp or a numeric version column that updates whenever a row is inserted or modified. Ascend uses this column to fetch only new or changed records since the last run.

4. Add the incremental key – specify the merge materialization strategy:

   strategy:
     incremental:
       merge:
         unique_key: id  # Column that uniquely identifies each record.
         deletion_column: deleted_at  # (Optional) Soft-delete timestamp column.
   note

   If you need to detect soft deletes, set deletion_column to your table’s delete indicator column.

read_mssql_incremental.yaml

component:
  read:
    connection: my-mssql-connection
    mssql:
      table:
        name: my_table
        schema: my_schema
    strategy:
      replication:
        incremental:
          column_name: last_modified  # Column used for tracking incremental updates to the data.
      incremental:
        merge:
          unique_key: unique_id_column  # Unique key for identifying records.

🎉 Congratulations! You just created an Incremental Microsoft SQL Server Read Component in Ascend.