Create Data Dependency (example for a Data Collection Trigger)

Data Dependencies are typically associated with a Data Collection Trigger in order to ensure that data is available and relevant in the source system. Unlike Schedule or Trigger Dependencies which verify that a particular process has been completed, this dependency involves creating SQL to look at the data for verification.

This article explains Creating from an existing Data Collection Trigger.  (see Create a Data Collection Trigger) and is identical to creating a Data Dependency from the Admin > Collection & Storage > Data Dependencies menu.

Note that creating Data Dependency is slightly different when creating from a Notification Schedule (see Create or Modify a Notification Schedule

Examples of Data Dependency Checks:

  1. Check that data in a Warehouse Customer Order table contains records post midnight of the prior day (indicating that all of the prior day's orders have been loaded).
  2. Verify that an ETL (Extract, Transform, Load) control table specifies that ETL processing has been completed since the last data collection cycle.
  3. Verify that volume in a data table is at least n% of the volume of data on a prior day as a data quality check before loading data.

Video Tutorial

1. Add a New Data Dependency: Data Collection Trigger Editor > Dependencies Tab

Add a New Data Dependency
  1. Click [+ New Data Dependency] on the Dependencies tab of the Data Collection Trigger Editor 
  2. In the Add Data Dependencypop-up:
    • Select the Add New Data Dependency option from the drop-down list

2. Define the Basics for the New Data Dependency

Define the basics for the New Data Dependency

After the new Data Dependency is saved, the Data Dependency Editor opens.

3. Info Tab

Enter Data Dependency Specifications

 

  1. Select Data Dependency Mode:
    1. If set to "Numeric",  the system expects the SQL statement to return a positive number, see Hint on right of statement box above
    2. If set to "Date",  the SQL hint will change to: "Specify a statement which will return a single column which contains a DateTime value. The dependency will be satisfied if the value returned falls after the DateTime on which the Dependency was last satisfied. You may optionally also specify the Table Name for the query."
  2. Specify the Data Source for the Data Dependency
  3. Provide a fetch command. The hint to the right will change based on the Data Dependency Mode selected above
  4. Configure Data Dependency Action:
    • If set to "Wait until satisfied": you can also specify how many time a Dependency is checked until it is satisfied. See Specify When and How Often to Perform the Data Dependency Check
    • If set to "Abort if not satisfied": the dependency will be checked as the first step of Data Collection Trigger  If the dependency is not satisfied, the associated Data Collection Trigger will abort with error when starting  
  5. [Validate] your fetch command
  6. If last_dependency_satisfied was included in the fetch command, provide a value for testing the data dependency logic
  7. If Validation is successful [Enable Dependency]

4. Optional: Specify When and How Often to Perform the Data Dependency Check

Below the statement specify when and how often to perform the Data Dependency check

Scroll to bottom of Info tab. The values shown above are the system defaults.

  1. Check Interval: specify how often the dependency check fetch command should be run
  2. Provide a time window during which checks should be performed
  3. Max. check time: specify a limit to time expended when checking the dependency. If exceeded, the Data Dependency fails.
  4. [Enable Dependency] at the top right corner of the page

5. Add Data Dependency to the Data Collection Trigger

This Data Dependency can now be added to the Data Collection Trigger

Note:

  • A given dependency can be shared among multiple Data Collection Triggers
  • Multiple dependencies can be specified for a Data Collection Trigger and all associated dependencies must be satisfied before the Data Collection Trigger starts

6. Set Data Dependency for Existing Trigger

Control when Data Dependency of Data Collection Trigger is Satisfied

Open  Dependencies tab

  1. Select the Edit icon to open popup
  2. If the Data Collection Trigger's interval value is set to "1" of any time unit, except Minute; e.g., 1 Hour, 1 Week, 1 Calendar Year, you can control whether an associated Data Dependency must be satisfied during current collection cycle by accessing the Dependencies tab of the Data Collection Trigger Editor and editing an existing dependency.