Create an Incremental MySQL Read Component

This guide walks you through building an Incremental MySQL Read Component that ingests only new or updated records by leveraging Ascend's Incremental strategy.

Prerequisites

• Ascend Flow
• MySQL 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 MYSQL Component

Structure your MySQL Incremental Read Component following this pattern:

1. Reference your MySQL connection: Specify which MySQL connection to read from
2. Add the mysql key - specify the table name you want to read from (in this example, the aircraft table)
3. Add the replication key - specify the incremental replication strategy using the updated_at column to track changes

tip

Your incremental column should be a timestamp or a datetime column that keeps track of when a record was last updated. Ascend will use this column to read only the data that was modified since the last refresh. The new data is then merged into the existing dataset based on the materialization mode set in the next step.

4. Add the materialization key - specify the materialization strategy you want to use. We will use merge materialization here with the following options:

• mode: The mode in which you want to merge. There are two options:
  • latest: This will merge the data without retaining any history, so you will always see the latest state of your table. This is the mode we have chosen in our example.
  • history: This will merge in new and updated records while also retaining previous versions of your records. For reach version of a record, Ascend will store an _ASCEND_START_AT and _ASCEND_END_AT timestamp which tells you the time frame within which that version of the record was valid. This allows you to time travel with your data and view versions of the data set at certain points in time.
• unique_key: The column that uniquely identifies each record in the table. This is used to determine which records are new or updated. In our example, we are using the aircraft_id to uniquely identify records.
• deletion_col: Ascend will detect deletions from the source table via the deletion_col you specify. The deletion_col should represent a soft-delete timestamp for when the record should be considered deleted.

read_mysql_incremental.yaml

component:
  read:
    connection: mysql_db
    mysql:
      table:
        name: aircraft
    strategy:
      replication:
        incremental:
          column_name: updated_at
      incremental:
        merge:
          unique_key: aircraft_id
          deletion_column: deleted_at

🎉 Congratulations! You just created your first Incremental MySQL Read Component in Ascend.