When automating a flow based on a relational data source, you often need to selectively process certain input tables or parts of tables. Source dataset parameterization in Trifacta allows you to dynamically choose which data to pass into your flow.
Customers often use source parameterization on relational sources in the following scenarios:
Filter: Dynamically filter a table given a certain criteria.
Automate: Create a workflow based on data that is refreshed on a scheduled basis.
All parameters are evaluated at the time when you run a job. Consequently, parameters are often combined with scheduling to create complex workflows.
How to create a parameterized dataset from a relational database
1. Navigate to the Import Data page. You can access this page by clicking on the Import Data button on the Trifacta homepage, the Import Data button on the Library page, or the Add Datasets button from within a flow.
3. Once you have created your custom SQL dataset, you can directly edit the custom SQL query to include parameters. Select the parts of the custom query that you want to parameterize. After highlighting the path, you will see a pop-up appear over your selection. This pop-up allows you to choose which type of parameter you want to create.
Trifacta supports two types of parameters for relational sources:
Variables: Pass in a specific string
Date/time parameters: Pass in a dynamic date or range of dates
Click on the icon that represents the type of parameter you want to create, and complete the fields in the parameter configuration window.
Crate a variable parameter
Select the Add Variable icon:
From the drop-down, you can name your parameter and configure a default value. We recommend choosing a unique name for each of your parameters. For variable type parameters, the default value is a string. You can override this string at run-time.
IMPORTANT: We recommend not selecting the complete string, including quotes. If you do this, you will need to include quotes in any of your variable overrides in order to create a valid SQL statement.
Once you have configured your variable settings, click Save.
Create a date/time parameter
Select the Add Timestamp Parameter icon:
From the pop-up box, enter the format for your date/time variable. This should match the format in your database. Next, configure the date range that will be evaluated at job run time:
IMPORTANT: Make sure you pay attention to the timezone. If you configure an incorrect timezone in this variable, Trifacta will not be able to identify the correct records.
Once you have configured your variable settings, click Save.
More Information
For product help and specific questions, check out the Trifacta Community
For an end-to-end example of source parameterization, reference the blog post here.