Overview of Parameterization

Overview

In some cases, you may need to be able to execute a recipe across multiple instances of identical datasets. For example, if your source dataset is refreshed each week under a parallel directory with a different timestamp, you can create a variable to replace the parts of the file path that change with each refresh. This variable can be modified as needed at job runtime. In the Designer Cloud Powered by Trifacta platform, parameterization enables you to manage executions of the same recipe steps across serialized datasets the paths to which can be managed via variable. 

Example

Suppose you have imported data from a file system source, which has the following source path to weekly transaction logs:

<file_system>:///source/transactions/2018/01/29/transactions.csv

In the above, you can infer a date pattern in the form of 2018/01/29, which suggests that there may be a pattern of paths to transaction files.  Based on the pattern, it'd be useful to be able to do the following:

• Import data from parallel paths for other weeks' data.
• Sample across all of the available datasets.
• Execute jobs based on runtime variables that you set for other transaction sets fitting the pattern.

• Pass in parameterized values through API to operationalize the execution of jobs across weeks of transaction data.

In this case, you would want to parameterize the date values in the path, such that the dynamic path would look like the following:

<file_system>:///source/transactions/YYYY/MM/DD/transactions.csv

The above example implements a Datetime parameter on the path values, creating a dataset with parameters. 

Parameter Types

You can use the following types of parameters to create datasets with parameters:

• Datetime parameters: Apply parameters to date and time values appearing in source paths.
  
  • When specifying a Datetime parameter, you must also specify a range, which limits the range of the Datetime values.
• Variables: Define variable names and default values for a dataset with parameters. Modify these values at runtime to parameterize execution.
• Pattern parameters: 
  • Wildcards: Apply wildcards to replace path values.
  • Regular Expressions: You can apply regular expressions to specify your dataset matches. Please see the limitations section below for more information.

For more information, see Create Dataset with Parameters.

Mismatched Schemas

Designer Cloud Enterprise Edition expects that all datasets imported using a single parameter have schemas that match exactly. The schema for the entire dataset is taken from the first dataset that matches for import.

If schemas do not match:

• When the first dataset contains extra columns at the end, the subsequent datasets that match should import without issues.
• If the subsequent datasets contain extra columns at the end, the datasets may import. Depending on the situation, there may be issues.
• If the subsequent datasets have additional or missing columns in the middle of the dataset, results of the import are unpredictable.
  • If there are extra columns in the middle of the dataset, you may see extra data in the final column, in which the spill-over data has not been split.
• Ideally, you should fix these issues in the source of the data. But if you cannot, you can try the following:

Tips:

• After import of a dataset with parameters, perform a full scan random sample. When the new sample is selected:
  • Check the last column of your imported to see if you have multiple columns of data. See if you can perform split the columns yourself.
    
  • Scan the column histograms to see if there are columns where the number of mismatches or anomalous or outlier values has suddenly increased. This could be a sign of mismatches in the schemas. 
• Edit the dataset with parameters. Review the parameter definition. Click Update to re-infer the data types of the schemas. This step may address some issues.
• You can use the union tool to import the oldest and most recent sources in your dataset with parameters. If you see variations in the schema, you can look to modify the sources to match.
  • If your sources have variation in structure, you should remove the structure from the imported dataset and create your own initial parsing steps to account for the variations. See Overview of Parameterization.

Limitations

• You cannot create datasets with parameters from uploaded data.
  • You cannot create datasets with parameters from Microsoft Excel files.
• You cannot create dataset with parameters from multiple file types.
  • File extensions can be parameterized. Mixing of file types (e.g. TXT and CSV) only works if they are processed in an identical manner, which is rare.
  • You cannot create parameters across text and binary file types.
• You cannot apply parameters to write or publishing operations.
• For regular expression patterns, the following reference types are not supported due to the length of time to evaluate:

  • Backreferences. The following example matches on axa, bxb, and cxc yet generates an error:

    ([a-c])x\1

  • Lookahead assertions: The following example matches on a, but only when it is part of an ab parttern. It generates an error:
    
    

    a(?=b)

Creating Dataset with Parameters

From file system

When browsing for data on your default storage layer, you can choose to parameterize elements of the path. Through the Import Data page, you can select elements of the path, apply one of the supported parameter types and then create the dataset with parameters.

For more information, see Create Dataset with Parameters.

From relational source

If you are creating a dataset from a relational source, you can apply parameters to the custom SQL that pulls the data from the source. 

For more information, see Create Dataset with SQL.

Matching parameters

When a dataset with parameters is imported for use, all matching source files or tables are automatically unioned together. 

The initial sample that is loaded in the Transformer page is drawn from the first matching source file or table. If the initial sample is larger than the first file, rows may be pulled from other source objects.

Managing Datasets with Parameters

Datasets with parameters in your flows

After you have imported a dataset with parameters into your flow:

• You can review any parameters that have been applied to the dataset through the Parameterization in Flow view. 
• When the dataset with parameters is selected, you can use the right panel to review and edit the parameters that are applied to it. 
• You can change the default value applied to the parameter through the Parameters panel in Flow View.

For more information, see Flow View Page.

Sampling from datasets with parameters

When a dataset with parameters is first loaded into the Transformer page, the initial sample is loaded from the first found match in the range of matching datasets. 

For more information, see Overview of Sampling.

Scheduling for datasets with parameters

Schedules can been applied to a dataset with parameters. 

For more information, see Flow View Page.

Sharing for datasets with parameters

For more information, see Overview of Sharing.

Housekeeping

Since the Designer Cloud Powered by Trifacta platform never touches the source data, after a source that is matched for a dataset with parameters has been executed, you should consider removing it from the source system or adjusting any applicable ranges on the matching parameters. Otherwise, outdated data may continue to factor into operations on the dataset with parameters.

Runtime Parameter Overrides

When you choose to run a job on a dataset with parameters from the user interface, any variables are specified using their default values. 

Through the Run Job page, you can specify different values to apply to variables for the job. 

For more information, see Run Job Page.

In the Job Results page, click View Parameters to view the parameter names and values that were used as part of the job, including the list of matching datasets. See Job Results Page.

Operationalization with Parameters

CLI

Through the CLI, you can execute jobs against datasets with parameters. When you download the CLI bundle from the application, the path in the sources file references the first matching dataset among all of the matches. You cannot apply any variable overrides at job runtime.

For more information, see CLI for Jobs.

Configuration

Disable

By default, parameterization is enabled. For more information on disabling this feature, see Miscellaneous Configuration.