9.10.1.2 Creating Persist

The Persist widget enables you to write data to database tables so that it can be used in other pipelines.

It is used to map columns of the source table to a destination table. The Persist widget helps you to map attributes from the input datasets to the target table, which will be stored.

To create a persist, follow these steps:

  1. Navigate to the Pipeline Designer page.
  2. Drag and drop the Persist widget from the widgets pane to the designer pane.
  3. Hover on the Persist widget and click Edit. A dialog box is displayed.
  4. Provide the details as described in the following table.

    Table 9-18 Fields and Description - Creating Persist

    Field Description
    Save As Enter the name for the Persist widget.
    Source Datasets Displays the list of datasets that are linked to the persist widget.
    Target Table Select the target table to which you want to map the columns in the source dataset tables.
    Type Select the type of mapping that you want to implement for the columns in the target table.

    The following options are available:
    • Full Load: This option enables you to truncate the existing data in the target table and load it with new data from the source datasets.
    • SCD: This option represents a slowly changing dimension. This option is used to map data from source datasets to the target table with both current and historical data stored in the target table. You can select the following options:
      • Surrogate: Values of this type are typically generated by incremental keys. For example, Sequence IDs.
      • Unique: Use this type for values which are unique across the dataset. For example, Customer Identifiers.
      • Type 2: Use this type for values that may be changed or added to. For example, Customer Names. Values of this type compare both current and historical data to provide the latest record as active. Historical values will be marked inactive.
      • Direct: Use this type for values that should consider only the current data for this record. For example, Data Origin.
      • Incremental: This option is used to map data from the source dataset to a target table incrementally. Incremental mapping adds new entries in addition to the existing data.
    • Merge: This option is used to map data from the source dataset to the target table such that both current and historical data are stored, and incremental data is also stored.
    • Delta: This option allows to identify the delta records of the source by comparing with the available records in the target.
    Join Available only if you have connected multiple datasets. For more information on Joining datasets, see Creating Join section.
    Hints Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the specific criteria. Select the Type of SQL Operation from the drop-down list and provide a hint in the Hints field.
  5. In the Map pane, follow these steps:
    1. Select the Source Dataset from the drop-down list on the left-hand side.
      The columns in the table that are associated with the selected source dataset are listed on the left-hand side.

      Note:

      The source dataset table is referred to as Source Entity, and the columns in the Source Entity are referred to as Source Column.
    2. Select the Target Table on the right-hand side. The columns in the target table are listed on the right-hand side.

      Note:

      The target dataset table is referred to as the Target Entity. The columns in the Target Entity are referred to as the Target Column
      • To Automap, click the Link icon. Source and target columns are auto-mapped based on Column Names and Data Types.
      • To map source and target columns manually, select a source column and target column, and then click Expand.

        Note:

        You must select columns of the same data type.

        The source column is mapped to the target column. The mapping details are displayed in the table on the right-hand side.

      • To add a condition to the target column, click Add + and use the Expression Builder to create the condition. The result is displayed in the target column on the right pane.
      • You can also import source and target columns from an Excel sheet. Click Choose File and select the Excel sheet.
      • You can also export the mapped source and target columns to Excel using Export.
  6. Click Save to save the changes. The persist is created.
    You can perform certain common tasks in all the widgets, such as Edit, Delete, filter, etc. For more information, see the Common Tasks section.