Exploring Data Relations

Overview

This guide describes how to use the Explore table references stage hook in a Row Level Processing (RLP) task.

The stage hook automatically analyzes existing table relationships, identifies missing Data Relation Rules, and provides recommendations for completing the coverage of an Application Model.

The script generates:

detailed diagnostic output,
recommended Data Relation Rules,
a summary of all recommendations,
a CSV block that can be used for import into the Application Model Version.

This approach is especially useful when building or extending an Application Model based on existing database foreign key relationships.

Prerequisites

Before starting, ensure that the following prerequisites are met:

A database connection with sufficient read permissions.
An Application Model and Version that should be extended with Data Relation Rules.

Steps

Step 1: Configure the RLP Task

Configure the RLP Task with the required Environment and start table.

Make sure that:

the Environment uses the intended Application Model Version,
the start table is defined,
Data Preview is enabled,
the Explore table references stage hook is added after Stage 2.

The complete hook used in this guide, including a detailed description, can be found here:

Explore table references

Step 2: Run the RLP Task

Execute the RLP Task.

The stage hook analyzes the tables currently included in the extraction, starting with the defined start table.

During execution, the script:

examines foreign key relationships for included and external tables,
checks for missing or incomplete relationships,
prints detailed queries and sample data for each relation,
outputs recommendations for missing Data Relation Rules,
collects a summary of all recommendations,
creates a CSV block for import.

Step 3: Interpret the Output

After execution, the script creates two main output sections.

Detailed Diagnostic Output

For each table and foreign key, the script displays:

the executed queries,
found sample data,
detected relationships,
missing relationships.

If a missing relationship is found, the script prints a recommendation.

Example:

Recommended Data Relation Rule:

Base: production.employees (emp_no) => Reference: production.salaries (emp_no)

This helps identify which table and key associations should be added to the Application Model.

Summary of Recommendations

At the end of the output, the script prints a summary.

Example:

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)

Below the summary, the script prints a CSV block.

This CSV block can be copied into a file, saved as CSV, and imported into the Data Relation Rule list of the Application Model Version.

Step 4: Add the Recommended Data Relation Rules

Review the recommendations from the output.

Add the required Data Relation Rules to the Application Model Version.

Use the CSV block if you want to import multiple recommendations at once.

Step 5: Repeat the Exploration

The hook is intended to be used iteratively.

A typical process is:

Start with the intended start table, for example employees.
Run the RLP Task.
Review the recommended Data Relation Rules.
Add the required rules to the Application Model Version.
Run the task again.
Review newly discovered relations.
Repeat until no further recommendations appear or until all required data is included.

With each run, newly related tables are included in the analysis. The script can then discover further relations that are one level deeper in the data model.

Step 6: Review an Example Output

Suppose the start table is employees from the demo database.

The first run may show links from employees to directly related tables.

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, the summary may look like this:

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)

Step 7: Visualize the Table Relationships

The following diagram visualizes the typical progression of discovered relations 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"

Benefits

Using the Explore table references stage hook provides several benefits:

Missing Data Relation Rules can be identified automatically.
Existing database foreign keys can be used as the basis for Application Model improvements.
Detailed diagnostic output makes the recommendations traceable.
The summary provides a clear overview of missing relations.
The CSV block simplifies bulk import into the Application Model Version.
The iterative approach helps build a complete and consistent relational model step by step.

Conclusion

The Explore table references stage hook helps analyze table relationships and identify missing Data Relation Rules in an RLP task.

By running the hook iteratively, you can gradually improve the Application Model until all required relationships are covered.

This makes extraction modeling more transparent, traceable, and easier to maintain.