Table relations and lookups may comprise the backbone or infrastructure of the data model, and as such they need to be created with care and thoughtfulness, keeping in mind that they are the pathways along which the data will flow.

Lookups that create circular references are one of the issues that may arise as data models become more complex. By definition, a circular reference is an instance where a field is somehow looking back to itself when attempting to populate its value. The following daisy chain of three fields that use lookup transformations is an example of a circular reference condition.

1. FieldA is getting its value from the FieldB.
2. FieldB is getting its value from the FieldC.
3. FieldC is getting its value from FieldA.

When attempting to a execute a table, newer versions of TimeXtender may be able to detect the circular reference and display an error similar to the following.

The above circular reference example is straightforward and easy to spot. However, this may be more challenging in a complex Prepare instance. 

A circular reference may more likely to occur for lookups created between dimension tables. A good practice to avoid this is to only create lookups on one side of the relation between two tables. Otherwise, if both tables have lookups that are pointing back to the other table, then the data cleansing process for each table may be waiting on the other table to complete before starting the data cleansing process for itself. 

Daisy chained lookups that result in a circular references are commonly found in lookups that are using multiple related tables.

1. Table A field gets a lookup value from a field in table B.
2. Table B field gets a lookup value from a field in table C.
3. Table C field gets a lookup value from a field in table A.

Views can also be a source of circular references, as in the following is an example.

1. The view is setup as an Ingest instance data source
2. The view incorporates fields from a Prepare instance table.
3. The Prepare instance table uses this same view for some of its mapped fields.

Table Inserts are also worth looking at when investigating the cause of a circular reference error. Check to see if the table that is being inserted includes any lookups, which may somehow point back to the original destination table.

Depending on the requirements of your data model, it may be difficult to avoid the scenarios outlined above. One workaround is to have the lookup get its value from the Raw table instead of the Valid table. Prepare instance tables may be comprised of different versions of the same data, as created during the execution of the table.

Usually, developers are working with the Valid Table that has completed its transformations and data cleansing processes. However, there is still the Raw Table that is also available, and a Prepare instance table field can be configured to get its value from the raw table instead.

When this is applied, the field only exists in the raw table and not in the valid table. Previewing the valid table will show this field as empty.  

A Conditional Lookup Field also includes the Use Raw Values check box, which allows for the lookup to use the raw value instead of the valid value. 

For more information on Raw and Valid tables, see: