Introduction to Recipes and Staging Tables (2.7)

Structure of a Recipe

Any recipe consists of some or all of the following steps, in order:

1. Staging activities. For each staging activity, you select a data source and then the schemas of interest from that data source.

   When the recipe is run, the data for each schema is loaded into one or more generated staging tables. The system automatically uses the extraction strategy specified in the schema. In the recipe, you choose which fields to copy.

2. Transformation activities, which transform fields in the staging tables. You choose how to transform the value, and you specify whether to replace the existing value or save the transformed value into a new field that you specify (in the same table as the original value).

3. Validation activities, which examine the values in the staging table or tables. For each fields that you choose to validate, you specify the validation function.

   The results of the validation checks are written to a validation report file. Validation errors can halt the recipe or can simply issue warnings, as you choose; in case of any validation error, the system generates a workflow task for the assigned role (specified when the recipe is scheduled).

4. Reconciliation activities, which compare values in different staging tables from the same run of the recipe. You specify how to match up records and you specify which fields to compare. Then for each comparison, you choose a comparison option.

   The results are written to a reconciliation report file. Reconciliation errors can halt the recipe or can simply issue warnings, as you choose; in case of any reconciliation error, the system generates a workflow task for the assigned role (specified when the recipe is scheduled).

5. Data promotion activities. For each data promotion activity, you define a set of steps, each of which uses SQL to update a final table, based on the contents of the staging table or tables. This final table can be in the native database or can be in an external database.

A recipe can also include custom steps at each stage in the processing.

Staging Tables

As a preliminary step, a recipe involves loading data into generated staging tables. The names of these tables are generated, and the table name format is as follows:

Staging_recipeshort_stagingshort_vversion.schemaname

Where recipeshort is the Recipe Short Name, stagingshort is the short name of the staging activity, version is the version of the recipe, and schemaname is the name of the schema as seen in the Data Catalog. (Note that Staging is a default part of this name but is configurable.)

Any staging table includes the following fields:

• Fields maintained and used internally by the system: %IRISRowID and %BatchId. The %BatchId field contains the ID of the batch in which the given record was updated; you use this to identify the records to use in the data promotion activity.

• A set of fields whose names match the field names from the data that is loaded into that table; these fields are automatically added as needed.

• Any additional fields defined within the transformation activities. These field names start with the prefix %T_.

As you create a recipe, you may find it helpful to view the structure of the staging table, particularly when you define the promotion activities.

See Also

• Schema Evolution

• Defining Recipes

• Editing and Managing Recipes

• Scheduling and Running Tasks

• Viewing Run History (includes information on viewing dropped records, validation reports, and reconciliation reports)