Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

PIT joined with INNER JOIN (Business Rule)

Applies to
Platform: Microsoft SQL Server · Snowflake · Databricks · Databricks dbt · Fabric Lakehouse · Fabric Warehouse · Oracle
Architecture: Data Mart

This business rule warns when a Mart model object reads a PIT and joins a satellite with an inner join in the same dataflow set. The inner join filters out the unknown-member row of the satellite, so rows without satellite data at a point in time can drop out of the result. The rule raises warning ID-21001, and generation continues.

Message

The client shows a warning with this text on the model object. <model object name> and <type> stand for its name and type.

Model object '<model object name>' of type '<type>' uses a PIT and
joins a Satellite using Inner Join, which could lead to unexpected
results because the singleton of the Satellite is filtered out.
Consider using a Current View, Business Current View, History View or
Business History View instead for a more robust and performant
implementation; or use an outer join for the Satellite.

On the model object card, point to the warning icon to see this message with the rule ID: Warning ID-21001. The Generate Artifacts page lists the message without the ID.

Available since Generator 2.0.

Cause

A dataflow set of a Mart Composite, Mart Dimension or Mart Fact has a PIT or Business PIT as source and joins a satellite source with the join operator Inner Join.

Fix

Choose one of the two ways the message names:

  • In the dataflow set, change the join operator of the satellite to Left Outer Join.
  • Read a Current View, Business Current View, History View or Business History View instead of the PIT and its satellites.

Check the result

The warning icon is gone from the model object.

Example

In a Vault and Mart project on Microsoft SQL Server, the Mart Composite prep_bk_nullable reads the PIT br_pit_customer with Left Outer Join and the Satellite CUSTOMER with Inner Join. The client shows:

Model object 'prep_bk_nullable' of type 'Mart Composite' uses a PIT
and joins a Satellite using Inner Join, which could lead to unexpected
results because the singleton of the Satellite is filtered out.
Consider using a Current View, Business Current View, History View or
Business History View instead for a more robust and performant
implementation; or use an outer join for the Satellite.

Related articles