Extra Template Features in Prophecy Dialect¶

Prophecy dialect provides a number of additional template types and helper functions that allow for more advanced and dynamic templates.

Helper Functions¶

The following functions are available to templates in Prophecy dialect:

get_df_name(ds_meta_id_or_name: str): Returns the correct dataframe name in the current template context, either by SAS dataset meta ID or the physical table name.

The following functions expose dataset & schema configuration to templates:

get_table_name(schema_name: str | None, ds_name: str): Returns the converted dataset name for the given schema and dataset name.
get_table_schema_or_path(schema_name: str | None, ds_name: str): Returns the converted schema or path for the given schema and dataset name.
get_table_cloud_storage_format(schema_name: str | None, ds_name: str): Returns the converted cloud storage format for the given schema and dataset name.
get_table_type(schema_name: str | None, ds_name: str): Returns the converted dataset type for the given schema and dataset name.
get_table_db_provider(schema_name: str | None, ds_name: str): Returns the converted database provider for the given schema and dataset name.
get_table_db_file_format(schema_name: str | None, ds_name: str): Returns the converted database file format for the given schema and dataset name.
get_table_unity_catalog(schema_name: str | None, ds_name: str): Returns the converted unity catalog for the given schema and dataset name.

Example Template¶

Here is an example of how the get_df_name can be used when replacing a user written transform:

{{ get_df_name('W123456') }} = spark.sql("select * from {{ get_df_name('SOURCE_TABLE') }}")

And this is an example of the other function as used in Alchemist pre-filled inter model templates (see below):

datasets:
- dataset_def_id: 1_A12345678.BX123456_A12345678.BF123456_1
  dataset_type: Database
  format: catalogTable
  location: '{{ get_table_schema_or_path("LIB_REF", "SOURCE_TABLE") }}'
  df_schema:
  - name: COLUMN
    type: string
  write_mode: overwrite
  merge_source_alias: null
  merge_target_alias: null
  merge_condition: null
  when_matched_update_action: null
  when_matched_delete_action: null
  when_not_matched_action: null
  table_name: '{{ get_table_name("LIB_REF", "SOURCE_TABLE") }}'
  provider: '{{ get_table_db_provider("LIB_REF", "SOURCE_TABLE") }}'
  unity_catalog: '{{ get_table_unity_catalog("LIB_REF", "SOURCE_TABLE")
    }}'

Output Types¶

sas_di_tr_script - This template should only be used against SAS Data Integration Tranforms, thus make sure that only SASMetaTranformBase models are matched in the template config. When this output type is used, a Script gem is created, which is automatically linked to inputs & outputs of the original Transform. The resulting string generated from the template becomes the body of the script.
sas_di_tr_sql - same as sas_di_tr_script, but the resulting string is used as the SQL body of the SQLStatement gem.
sas_di_tr_inter_model - this is the most powerful template type, which allows a user to fully customize or amend what is generated for a given SAS DI Transform. During regular conversion Alchemist will generate pre-filled template of this type for every converted transform and save them as conversion assets. From there, they can be used to change anything from a single expression to using entirely different GEMs. The easiest way to learn how to use them is to explore the existing templates in the conversion assets folder.
sas_di_tr_custom - this is a template type that can be used to generate a custom GEM, which is not part of the standard Alchemist conversion. The template must contain the specific Custom GEM configuration for conversion, such as the mapping of SAS DI Transform parameters to Custom GEM properties. For now, it has only one configuration option, custom_gem_name, which should match one specified in the Custom GEM configuration. Here is an example of the yaml template file content:
```
custom_gem_name: newGemType
```

Source & Target Filters for Task-Like Source Nodes¶

When using a template type that replaces an entire task-like node (e.g. SAS DI Transform), such as sas_di_tr_script or sas_di_tr_sql, it is possible to force Alchemist to skip some or all of the source and target ports on the resulting GEM.

For example this is handy when you want to implement a custom logic to load a source table, and thus do not needthe standard Source GEM preceding the generated Script GEM.

To do so, you can use the following template config:

converter:
# ...
  template_configs:
    - template: full_program
      output_type: "sas_di_tr_script"
      match_ids:
        - "some transform id"
      source_filter:
        # include: [] - or inlcude only specific ones
        exclude: ["source dataset id"]
      target_filter:
        exclude_all: true

The source_filter and target_filter fields are optional. The syntax and exclusion/inclusion logic is the same as other filters, e.g. Node Filter.