3.3 Resolving Synthetic Keys ($Syn) and Circular References

3.3 Resolving Synthetic Keys ($Syn) and Circular References

Quick Summary: In Qlik Sense, associative relationships are formed automatically whenever two tables contain fields with identical names. While this associative mechanism is Qlik's greatest strength, unintended field name collisions create two critical data modeling defects: Synthetic Keys ($Syn) and Circular References (Loops). Synthetic keys force the engine to build costly compound key tables in memory, while circular references create ambiguous associative paths that cause tables to become loosely coupled. A qualified Qlik Data Architect must know how to diagnose these anomalies using the Data Model Viewer and resolve them using proven scripting patterns.

---

Synthetic Keys ($Syn): Origin and Internal Mechanics

A Synthetic Key occurs when two or more tables share two or more fields with the exact same name. For example, if both Orders and OrderDetails contain OrderID and ProductID, Qlik recognizes multiple shared columns.

The QIX Engine's Automatic Response

Rather than guessing which field should serve as the primary associative link, or performing an arbitrary join, Qlik's in-memory engine constructs an internal synthetic structure:

1. It creates an auxiliary table named $Syn1Table (or $Syn2Table, etc.).
2. It generates a synthetic key field named $Syn1 in both participating tables and in the $Syn1Table.
3. The $Syn1Table contains the $Syn1 key field alongside the original shared fields (OrderID and ProductID).
4. Each row in $Syn1Table represents a unique composite combination of the shared fields encountered in the source tables.

   [Orders]                  [OrderDetails]
      \                           /
       \                         /
      [$Syn1]                 [$Syn1]
         \                       /
          -----> [$Syn1Table] <---
                 • $Syn1
                 • OrderID
                 • ProductID

Why Synthetic Keys Are Harmful

While Qlik Sense can function with a single, simple synthetic key over small datasets without crashing, synthetic keys are considered an architectural defect on enterprise projects and the QSDA exam for several critical reasons:

1. Severe Memory Overhead: Each synthetic key requires its own Symbol Table and Data Table. As datasets grow to millions of rows, the permutations of compound keys generate massive symbol tables, bloating RAM utilization.
2. Cascading Synthetic Keys: If multiple tables share overlapping subsets of fields (e.g., Table A shares fields 1 and 2 with Table B, and fields 2 and 3 with Table C), Qlik creates cascading synthetic keys ($Syn1, $Syn2, $Syn3). These cascading structures cause exponential calculation degradation.
3. CPU Thrashing During Calculations: When a user selects a value in a visualization, the QIX engine must dereference pointers through the synthetic key table to reach associated tables, increasing query latency and CPU cache misses.
4. Unintended Aggregation Results: Compound associations through synthetic tables can mask data quality issues (such as mismatched grains or cartesian duplicates), producing mathematically invalid aggregations.

---

Circular References (Loops): Origin and Impact

A Circular Reference occurs when the associative links between three or more tables form a closed topological loop. In a circular model, there is more than one associative path between any two tables.

For example, consider the following classic loop:

          [Customers]
          /         \
   CustomerID     CustomerID
        /             \
    [Orders]       [Invoices]
        \             /
      OrderID      OrderID
          \         /
        [Shipments]

The Associative Ambiguity Problem

In the diagram above, if a user selects a customer in [Customers], the engine can filter [Shipments] through two distinct paths:

1. Customers $\rightarrow$ Orders $\rightarrow$ Shipments
2. Customers $\rightarrow$ Invoices $\rightarrow$ Shipments

If an order has shipped but has not yet been invoiced, Path 1 and Path 2 yield conflicting associative states for the same shipment record. The engine cannot know which business rule governs the associative truth.

The Engine's Reaction: Loosely Coupled Tables

To prevent infinite recursion and execution deadlocks during state calculation, Qlik Sense automatically detects circular loops during script execution. It breaks the loop by designating one or more of the participating tables as Loosely Coupled Tables.

• In the Data Model Viewer, loosely coupled relationships are flagged with red or dashed connecting lines.
• During script reload, the execution log warns that a loop (circular reference) was found and that one or more tables were set as loosely coupled to cut it.

User Impact of Loosely Coupled Tables

When a table is loosely coupled, normal associative filtering across the severed path is disabled:

• Selections made on Customers will not propagate through the loosely coupled table to downstream visualizations.
• Associative color-coding (green, white, gray) fails to reflect true relationships.
• Chart calculations that span tables across the loop produce inconsistent or incomplete totals, destroying dashboard reliability.

---

The Comprehensive Resolution Toolkit: 6 Architectural Strategies

A data architect must master the six proven methods for eliminating synthetic keys and circular references:

1. Field Renaming via the AS Keyword

The simplest and most common resolution. Many synthetic keys occur because source tables contain fields with coincidentally identical names that actually represent different business concepts (e.g., Date, Status, Description, Type, ID).

// Before (Creates $Syn on Date and Status)
Orders:
LOAD OrderID, CustomerID, Date, Status FROM Orders.qvd (qvd);

Shipments:
LOAD ShipmentID, CustomerID, Date, Status FROM Shipments.qvd (qvd);

// After (Clean resolution using AS aliasing)
Orders:
LOAD 
    OrderID,
    CustomerID as %CustomerID,
    Date as OrderDate,
    Status as OrderStatus
FROM Orders.qvd (qvd);

Shipments:
LOAD 
    ShipmentID,
    CustomerID as %CustomerID,
    Date as ShipDate,
    Status as ShipmentStatus
FROM Shipments.qvd (qvd);

2. QUALIFY and UNQUALIFY Directives

When loading source tables with dozens of generic column names, manually renaming every field using AS can be tedious and prone to omissions. The QUALIFY directive instructs Qlik to automatically prefix every field name with the table name (e.g., Orders.Status).

The architect uses UNQUALIFY to specify the genuine foreign key fields that should associate:

// Qualify all incoming fields by default
QUALIFY *;

// Unqualify only the true relational business keys
UNQUALIFY %CustomerID, %DateKey;

Orders:
LOAD 
    CustomerID as %CustomerID,
    OrderDate as %DateKey,
    OrderID,
    Status,
    Amount
FROM [lib://Data/Orders.qvd] (qvd);

Customers:
LOAD 
    CustomerID as %CustomerID,
    CustomerName,
    Status,
    Region
FROM [lib://Data/Customers.qvd] (qvd);

Result: Orders.Status and Customers.Status remain separate fields, preventing a synthetic key, while %CustomerID associates cleanly.

3. Composite Keys via AutoNumber()

When multiple fields represent a genuine composite primary/foreign key relationship (e.g., a store order identified by StoreCode and DailyReceiptNumber), combining them into a single surrogate key resolves the synthetic key.

// Create a single composite surrogate key in both tables
Orders:
LOAD 
    AutoNumber(StoreCode & '|' & DailyReceiptNumber) as %Key_StoreReceipt,
    OrderAmount,
    OrderTime
FROM [lib://Data/Orders.qvd] (qvd);

OrderDetails:
LOAD 
    AutoNumber(StoreCode & '|' & DailyReceiptNumber) as %Key_StoreReceipt,
    LineItemNumber,
    ProductID,
    UnitPrice
FROM [lib://Data/OrderDetails.qvd] (qvd);

Note: The individual fields (StoreCode, DailyReceiptNumber) must be omitted from one of the tables (or qualified) so they do not continue to associate alongside the composite key.

4. Link Table Creation

When circular loops arise from multiple fact tables sharing multiple dimension tables, extracting the shared dimension keys into a central Link Table breaks the circular loop while preserving cross-dimensional filtering (as detailed in Section 3.2).

5. Table Concatenation

If two tables have similar structures or represent transactions that can be logically unified (e.g., CurrentOrders and ArchivedOrders), concatenating them using CONCATENATE into a single table completely eliminates the parallel associative paths that cause loops.

6. Dropping Redundant Fields

If auxiliary fields were extracted during development or debugging and are no longer required for analysis, dropping them eliminates unnecessary associations:

DROP FIELD ModifiedDate, RowVersion FROM Orders;

---

Data Model Viewer Diagnostics: Verifying Model Integrity

The Data Model Viewer (opened from the app's data preparation menu or from the data load editor) is the architect's primary diagnostic console. When inspecting a model, evaluate the following visual cues and statistical metrics:

1. Visual Topology Inspection

• Table Nodes: Tables should display a clean, organized Star or Link Table layout.
• Synthetic Tables ($Syn): Synthetic key tables appear with an explicit $SynXTable label and distinctive styling. Any synthetic table in the viewer indicates an unresolved design defect.
• Connector Lines: Normal associations are rendered with solid lines. Loosely coupled tables resulting from circular references are flagged with red or dashed warning connectors.

2. Deep Dive: Key Diagnostic Metrics

Clicking on any field in a table node displays detailed field metrics in the preview pane:

Metric A: Information Density

• Definition: The percentage of rows in the table that contain a valid, non-null value for that field.
• Formula: (Total Non-Null Cell Count) / (Total Table Row Count)
• Diagnostic Rule: Primary and foreign key fields should generally have an Information Density of 100%. A key field with an Information Density below 100% indicates missing or orphaned keys, which causes rows to drop out of associations.

Metric B: Subset Ratio

• Definition: The percentage of distinct values of this field present in the selected table relative to the total distinct values of this field across the entire data model.
• Formula: (Distinct Field Values in THIS Table) / (Total Distinct Field Values in ALL Tables)

---

Step-by-Step Before-and-After Load Script Transformation

Consider an enterprise sales model loading Customers, Orders, and Invoices. In the unoptimized script below, coincidentally named fields create a synthetic key table and a circular reference:

The Problematic Script (Defective Model)

// DEFECTIVE SCRIPT: Creates $Syn on Date/ZipCode and a Circular Loop
Customers:
LOAD 
    CustomerID,
    CustomerName,
    ZipCode,
    Date // Registration Date
FROM [lib://Data/Customers.qvd] (qvd);

Orders:
LOAD 
    OrderID,
    CustomerID,
    Date, // Order Date
    ZipCode, // Shipping Zip
    OrderAmount
FROM [lib://Data/Orders.qvd] (qvd);

Invoices:
LOAD 
    InvoiceID,
    OrderID,
    CustomerID,
    Date, // Invoice Date
    InvoiceAmount
FROM [lib://Data/Invoices.qvd] (qvd);

Diagnostic Failure Analysis:

1. Customers and Orders share CustomerID, ZipCode, and Date $\rightarrow$ Generates $Syn1Table.
2. Orders and Invoices share OrderID, CustomerID, and Date $\rightarrow$ Generates $Syn2Table.
3. The three tables form a closed triangular loop between CustomerID, OrderID, and $Syn tables, resulting in loosely coupled tables.

The Refactored Script (Clean Star Architecture)

// OPTIMIZED SCRIPT: Fully resolved Star Model
Customers:
LOAD 
    CustomerID as %CustomerID,
    CustomerName,
    ZipCode as CustomerZipCode,
    Date as CustomerRegistrationDate
FROM [lib://Data/Customers.qvd] (qvd);

Orders:
LOAD 
    OrderID as %OrderID,
    CustomerID as %CustomerID,
    Date as %DateKey,
    ZipCode as ShippingZipCode,
    OrderAmount
FROM [lib://Data/Orders.qvd] (qvd);

Invoices:
LOAD 
    InvoiceID,
    OrderID as %OrderID,
    Date as InvoiceDate,
    InvoiceAmount
FROM [lib://Data/Invoices.qvd] (qvd);

Architectural Resolution Summary:

• ZipCode and Date are aliased using AS to reflect their specific business context (CustomerZipCode, ShippingZipCode, InvoiceDate).
• Orders acts as the primary transaction fact linking to Customers via %CustomerID and to the calendar via %DateKey.
• Invoices links directly to Orders via %OrderID without redundant CustomerID or Date links, breaking the circular loop entirely.

---

Data Model Viewer Diagnostic Checklist

Before promoting any Qlik Sense data model to user acceptance testing (UAT) or production, execute this six-point architectural checklist:

1. Zero Synthetic Tables: Confirm that no $Syn tables or $Syn fields exist anywhere in the Data Model Viewer.
2. Zero Circular References: Verify that no loosely coupled tables exist and that no warning banners appear in the script execution log.
3. 100% Dimension Subset Ratio Audit: Check the primary key of each dimension table. If the subset ratio is less than 100%, investigate missing master records in the source data.
4. 100% Key Information Density: Verify that all link keys (%Key_*) have an Information Density of 100% (zero null keys).
5. AutoNumber Optimization: Ensure that all multi-field composite keys are wrapped in AutoNumber() to minimize Symbol Table memory.
6. Single-Hop Topology: Verify that fact tables connect directly to dimensions in a clean Star Schema or structured Link Table architecture.