Exploring and Recommending Data Relation Rules with the Table Reference Stage Hook

Overview

This guide describes how to use the Explore table references stage hook in an RLP task to automatically analyze existing table relationships, identify missing data relation rules, and provide actionable recommendations for completing your application model’s coverage. The script generates detailed diagnostics and a summary for easy review and import into your modeling environment.

Prerequisites

Before starting, ensure the following prerequisites are met:

  • A valid connection to your source database.

  • Sufficient browse/read permissions on the database connection.

  • An Application Model and Version with a defined start table.

  • An Environment set up using your Application Model.

  • A RLP Task that uses the Environment as its source environment.

  • The Data Preview feature activated in your RLP Task.

  • The Explore table references stage hook added after Stage 2 of your RLP workflow. The complete hook can be downloaded here.

Workflow Steps

  1. Configure your RLP Task as described above, ensuring the stage hook is placed correctly.

  2. Run the RLP task. The script will analyze the tables currently included in your extraction (starting with the defined start table).

  3. With each execution, the script:

    1. Examines foreign key relationships for both included and external tables.

    2. Checks for missing or incomplete relationships.

    3. Prints out detailed queries and live sample data for each relation.

    4. Outputs a clear recommendation for each missing data relation rule, if found.

    5. Collects a summary and a CSV block for easy copy-paste import at the end of each run.

Output Interpretation

You will see two output sections after script execution:

Detailed Diagnostic Output

For each table and foreign key, the script displays queries run, found sample data, and wherever a missing relationship is identified, a:

Recommended Data Relation Rule
Base: production.employees (emp_no) => Reference: production.salaries (emp_no)

This lets you track exactly which key and table associations need to be established for a consistent application model.

Summary of Recommendations

At the end, the script prints a summary in a format such as: Summary of Recommended Data Relation Rules:

[employees -> dept_emp] -- Base: production.employees (emp_no) => Reference: production.dept_emp (emp_no)
...

Below this summary, the script will print a copy-paste ready CSV block for bulk importing all recommendations to the data relation rule list of the application model version. Paste the list into a file, save it as CSV, and import it into your rule list.

Iterative Exploration

The hook is best used iteratively:

Start with your intended start table (e.g., employees in a demo HR schema). Run the task—the initial run shows which directly related tables and keys are missing in your extraction.

Add the recommended data relation rules.

Run again. The script will include these newly related tables and now recommend rules for keys/tables further out in your model. Repeat until no further recommendations appear or until you decide, that all necessary data is included; your application model then fully covers the discovered relational structure.

Example Output

Suppose you start with the employees table from the demo database. The output after the first run will show the links from employees to directly related tables, for example:

Processing incoming external foreign key: salaries_emp_no_fkey
... (connection, query, and data preview logs) ...
Recommended Data Relation Rule:
Base: production.employees (emp_no) => Reference: production.salaries (emp_no)

At the end:

Summary of Recommended Data Relation Rules:

[employees -> dept_emp] -- Base: production.employees (emp_no) => Reference: production.dept_emp (emp_no)
[employees -> dept_manager] -- Base: production.employees (emp_no) => Reference: production.dept_manager (emp_no)
[employees -> salaries] -- Base: production.employees (emp_no) => Reference: production.salaries (emp_no)
[employees -> titles] -- Base: production.employees (emp_no) => Reference: production.titles (emp_no)

Visualizing the Table Relationships

The following diagram visualizes the typical progression of discovered relations (e.g., in the HR demo dataset):

erDiagram employees ||--o{ dept_emp : "emp_no" employees ||--o{ dept_manager : "emp_no" employees ||--o{ salaries : "emp_no" employees ||--o{ titles : "emp_no" dept_emp }o--|| departments : "dept_no" dept_manager }o--|| departments : "dept_no"

Best Practices

Review both the detailed outputs (for traceability) and the summary (for oversight). Use the CSV block at the end for fast integration with your modeling/import tools. Re-run after each adoption of recommended rules until no further relations are suggested.

Conclusion

With each execution, the stage hook enables structured, transparent exploration of all necessary data relation rules for complete and consistent extraction modeling. The iterative approach helps you quickly converge on a complete relational mapping tailored to your real database structure.