Create an Incremental Postgres Read Component

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

Prerequisites

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

Structure your Postgres Incremental Read Component following this pattern:

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

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

   Your incremental column should be a timestamp or datetime 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

   When using merge materialization, specify deletion_column to detect and remove rows that have been soft-deleted.

read_postgres_incremental.yaml

component:
  read:
    postgres:
      table:
        name: my_table
        schema: public
    connection: my-postgres-connection
    strategy:
      incremental:
        merge:
          unique_key: id  # Column or set of columns used as a unique identifier for records.
          deletion_column: deleted_at  # Column name used for soft-deleting records, if applicable.
      on_schema_change: append_new_columns

🎉 Congratulations! You just created an Incremental Postgres Read Component in Ascend.