Qlik Sense Synthetic Keys & Circular References: How to Identify and Fix Data Model Problems

This is Article 9 of the Qlik Sense Data Modeling Course.

What Can You Learn About Synthetic Keys & Circular References in 18 Minutes?

After this guide, you will be able to:

1. Identify synthetic keys and resolve them in 5 minutes through deliberate field renaming
2. Diagnose circular references and eliminate them through targeted architectural changes
3. Implement preventive naming conventions that prevent 90% of all data modeling problems

Time investment: 18 min reading + 2 hrs hands-on
Prerequisite: Basic knowledge of Table Relationships and simple data models
Quick Win: Identify synthetic keys in 3 minutes and understand why they are problematic

How Can I Quickly Identify Synthetic Keys?

The problem: Your data model shows mysterious $Syn tables and performance becomes inexplicably slow.

Solution in 3 steps:

1. Open the Data Model Viewer for diagnosing issues (data model icon in the app)
2. Look for tables named “$Syn1”, “$Syn2”, etc.
3. Check which fields are causing these synthetic keys

// Typical situation that creates Synthetic Keys
Customers:
LOAD
    CustomerID,
    CustomerName,
    Region,
    CountryCode
FROM [lib://Data/customers.csv];

Orders:
LOAD
    OrderID,
    CustomerID,
    OrderDate,
    Region,        // Problem: Shared fields CustomerID AND Region
    CountryCode    // Problem: Three shared fields = Synthetic Key
FROM [lib://Data/orders.csv];

Explanation:

• Synthetic Key Cause: More than one field with the same name between tables → Qlik automatically creates a $Syn table
• Performance Impact: Every expression must go through the Synthetic Table → 2-5x slower calculations
• Model Complexity: Instead of 2 tables, 3-4 tables are created → hard to understand

Performance: 1M Rows without Synthetic: Expressions 2-3s → With Synthetic: 8-15s

Checkpoint: Do you see $Syn tables in the data model? → Problem identified! No $Syn tables? → Everything is optimal or there are circular references. See Troubleshooting.

For a systematic solution, see Resolving Synthetic Keys.

Which Solution Fits Your Problem?

First identify the problem pattern, then choose the appropriate solution strategy:

How Do I Resolve Synthetic Keys & Circular References in Qlik Sense?

Do you have $Syn tables in your data model?

• YES → Problem: Synthetic Keys → Method 1: Resolve Synthetic Keys
• NO → Can you navigate from any table to any other via multiple paths?
  • YES → Problem: Circular References → Method 2: Resolve Circular References
  • NO → Model is correct → Method 3: Implement Prevention

Legend:

• Time Required: Average effort for typical business data models (10-20 tables)
• Complexity: Technical difficulty and required architectural knowledge
• Frequency: Share of real Qlik projects with this problem pattern

How Can You Resolve Synthetic Keys in Qlik Sense?

The Problem

Two or more tables share multiple fields with identical names. Qlik interprets this as a multi-field key and automatically creates Synthetic Keys ($Syn1, $Syn2). This leads to performance problems and incomprehensible data models.

The Solution

Deliberate field renaming: Only true association fields (keys) keep identical names; all other fields are renamed to clarify their semantic role.

// Problematic code (creates Synthetic Keys)
Customers:
LOAD
    CustomerID,
    CustomerName,
    Region,        // Problem: Region in both tables
    CountryCode,   // Problem: CountryCode in both tables
    CreatedDate    // Problem: CreatedDate in both tables
FROM [lib://Data/customers.csv];

Orders:
LOAD
    OrderID,
    CustomerID,    // OK: Association field
    OrderDate,
    Region,        // Problem: Creates Synthetic Key
    CountryCode,   // Problem: Creates Synthetic Key
    CreatedDate    // Problem: Creates Synthetic Key
FROM [lib://Data/orders.csv];

// Correct solution (avoids Synthetic Keys)
Customers:
LOAD
    CustomerID,                    // Key: Keep identical
    CustomerName,
    Region as CustomerRegion,      // Renamed: Clarifies semantics
    CountryCode as CustomerCountry,
    CreatedDate as CustomerCreatedDate
FROM [lib://Data/customers.csv];

Orders:
LOAD
    OrderID,
    CustomerID,                    // Key: Keep identical for association
    OrderDate,
    Region as OrderRegion,         // Renamed: Different semantic meaning
    CountryCode as ShipToCountry,  // Renamed: More precise description
    CreatedDate as OrderCreatedDate
FROM [lib://Data/orders.csv];

Strategy explained:

• Key fields unchanged: CustomerID stays CustomerID for automatic association
• Semantic renaming: Region in Orders might be the shipping region, in Customers the customer region
• Prefix strategy: Table name as prefix (Customer*, Order*) for instant identification

Performance context:

• 2M Orders + 50k Customers with Synthetic: Chart calculation 12-18s
• Same data without Synthetic: Chart calculation 3-5s
• Memory reduction: 25-40% less RAM by eliminating $Syn tables

Best Practices

• Key field identification: Deliberately document which fields are association fields (1:1, 1:N, M:N). Only these keep identical names. Business rule: A field is a key if it’s used for JOINs or lookup purposes.
• Semantic prefixes: Use table names as prefixes (Customer*, Order*, Product*). Alternative: Functional prefixes (Billing*, Shipping*, Reporting*). Consistency matters more than the specific convention.
• Documentation in script: Comment on renames: // CustomerRegion: Customer's home region vs OrderRegion: Shipping region. Essential for successor developers to understand the model.
• Validation after changes: Check the data model: No more $Syn tables? Row counts before/after identical? Test charts work as expected? Systematic validation prevents undiscovered side effects.

Checkpoint: Why should only key fields have identical names, but not all others?

→ Next step: For very complex models with many tables, field renaming alone isn’t enough. Resolving Circular References shows architecture-based solutions.

How Do I Resolve Circular References in Qlik Sense?

The Problem

Multiple paths between tables arise from complex business relationships. Example: Customer → Orders → Product and Customer → Territory → Product. Qlik cannot decide which path to use for calculations, leading to incorrect aggregations.

The Solution

Architectural decoupling through deliberate path interruption: Use either denormalization or link tables to create unambiguous relationships.

// Problematic structure (Circular Reference)
Customers:
LOAD CustomerID, CustomerName, TerritoryID FROM customers.csv;

Territory:
LOAD TerritoryID, TerritoryName, RegionID FROM territory.csv;

Orders:
LOAD OrderID, CustomerID, ProductID, TerritoryID FROM orders.csv;  // Circular: Customer→Territory AND Orders→Territory

Products:
LOAD ProductID, ProductName, RegionID FROM products.csv;  // Circular: Territory→Region AND Products→Region

// Solution 1: Denormalization (simplest solution)
Customers:
LOAD
    CustomerID,
    CustomerName,
    TerritoryID as CustomerTerritoryID,  // Renaming breaks the path
    ApplyMap('TerritoryMap', TerritoryID, 'Unknown') as CustomerTerritory
FROM customers.csv;

// Create Territory Map
TerritoryMap:
MAPPING LOAD TerritoryID, TerritoryName FROM territory.csv;

Orders:
LOAD OrderID, CustomerID, ProductID, TerritoryID as OrderTerritoryID FROM orders.csv;

Products:
LOAD ProductID, ProductName, RegionID as ProductRegionID FROM products.csv;

// Solution 2: Link table for complex M:N relationships
Customer_Territory_Link:
LOAD
    CustomerID & '|' & TerritoryID as Customer_Territory_Key,
    CustomerID,
    TerritoryID,
    ValidFrom,
    ValidTo
FROM customer_territory_assignments.csv;

Strategy explained:

• Denormalization: Redundant data inclusion prevents complex joins. Performance advantage through fewer tables.
• Path interruption: Deliberate field renaming destroys automatic associations where not desired.
• Link tables: For true M:N relationships with additional attributes (time validity, weighting).

Performance context:

• 5 tables with Circular Reference: Expressions 15-25s, unpredictable results
• 3 tables after denormalization: Expressions 4-8s, unambiguous calculations
• Trade-off: More data redundancy vs. better performance and comprehensibility

Best Practices

• Architecture Decision Documentation: Deliberately document why each path was interrupted. Business rule: Which relationship is primary, which is secondary? Essential for successor developers and future extensions.
• Star Schema as target architecture: Denormalization naturally leads to Star Schema (1 Fact, multiple Dimensions). Plan deliberately in this direction instead of ad-hoc fixes. More maintainable and performance-optimal long-term.
• Staged Refactoring: Resolve circular references step by step, not all at once. Test after each step that calculations are still correct. Have a rollback strategy for each refactoring step.
• Business Rule Validation: After architecture changes: Validate with business users that aggregations still meet expectations. Circular references often lead to subtly wrong numbers that are discovered late.

Checkpoint: When should you choose denormalization vs. link tables?

→ Next step: Prevention is better than reactive problem-solving. Implementing Naming Conventions prevents 90% of all modeling problems.

How Do Preventive Naming Conventions Help with Synthetic Keys & Circular References in Qlik Sense?

The Problem

Reactive problem-solving is time-consuming and error-prone. Teams develop different modeling approaches, leading to inconsistent and hard-to-maintain data models.

The Solution

Proactive naming conventions and modeling standards that automatically create correct associations and prevent problematic patterns.

// Naming Convention Template
// Format: [TableName]_[FieldType]_[BusinessEntity]

// 1. Key fields: Always with "ID" suffix, table reference in the name
Customers:
LOAD
    CustomerID,           // Standard Key Format
    CustomerName,         // Business Attribute
    CustomerRegion,       // Prefix = Table ownership
    CustomerCreatedDate   // Prefix + descriptive name
FROM customers.csv;

Orders:
LOAD
    OrderID,              // Own Key
    CustomerID,           // Foreign Key - identical for association
    OrderDate,            // Prefix for uniqueness
    OrderRegion,          // Different from CustomerRegion - no Synthetic Key
    OrderAmount
FROM orders.csv;

// 2. Fact vs Dimension Pattern
Fact_Sales:               // Fact Tables with "Fact_" prefix
LOAD
    SalesID,
    CustomerID,           // FK to Dimension
    ProductID,            // FK to Dimension
    SalesDate,
    SalesAmount,          // Measure in Fact
    SalesQuantity         // Measure in Fact
FROM sales.csv;

Dim_Customers:            // Dimension Tables with "Dim_" prefix
LOAD
    CustomerID,           // PK
    CustomerName,         // Attribute
    CustomerSegment,      // Attribute
    CustomerRegion        // Attribute
FROM customers.csv;

// 3. Avoid Generic Names
LOAD
    ID as CustomerID,                    // Specific instead of generic
    Name as CustomerName,               // Add context
    Type as CustomerType,               // Precise naming
    Status as CustomerStatus,           // Unambiguous assignment
    Date as CustomerRegistrationDate    // Full semantic clarity
FROM customers.csv;

Strategy explained:

• Prefix system: Table name as prefix prevents accidental name collisions
• Suffix standards: ID = Key, Date = Date, Amount = Monetary value, Count = Count
• Fact/Dim separation: Clear distinction between transactional and dimensional data

Performance context:

• Team efficiency: 50-70% less time for data model debugging
• Onboarding: New developers understand the model in 30 min instead of 3 hrs
• Maintenance: Structural changes in 15 min instead of 2 hrs (no unexpected side effects)

Best Practices

• Template-Driven Development: Create template apps with correct naming patterns. New projects copy the template instead of from-scratch development. Includes standard tables (Calendar, Lookup Tables) with correct names.
• Code Review Integration: Naming convention checklist for all pull requests. Automated checks where possible (prefix validation, reserved words check). Cultural change: Conventions are not optional but a quality standard.
• Living Documentation: Conventions document with real examples from current apps. Regular updates when new patterns emerge. FAQ section for common edge cases and their approved solutions.
• Tooling Support: Include files with standard mappings and lookup tables. Code snippets for IDE/text editors. Validation scripts that check models for convention compliance.

Checkpoint: Why are prefixes better than suffixes for table ownership?

→ Next step: With solid naming conventions, you can build clean star schemas. Star Schema Implementation shows optimal data model architecture.

How Do All Solution Approaches for Synthetic Keys & Circular References Compare?

This evaluation is based on typical enterprise projects (10-30 tables, 2-5 developers, 2-3 year project duration).

Legend:

• Problem Resolution: How effectively the specific problem is addressed
• Implementation: Initial time investment to reach a working solution
• Maintenance: Long-term effort for upkeep and adjustments
• Team Scalability: How well the approach works with multiple developers
• Performance: Impact on Qlik app performance and user experience

Interpretation: Reactive fixes are tempting due to quick implementation but lead to technical debt. Preventive conventions have high initial investment but pay off exponentially in team work and long-term maintenance.

How Do I Troubleshoot Synthetic Keys & Circular References in Qlik Sense?

Alphabetical index:

• Associations disappeared after field renaming
• Circular reference persists despite renaming
• Performance worse after synthetic key resolution
• Star schema not automatically recognized
• Synthetic keys return after reload
• Wrong aggregations despite correct model

How Do I Fix Synthetic Keys Returning After a Reload?

Symptom: $Syn tables reappear even though fields were renamed

Most common causes:

1. New field in source data → Solution: Check for new columns in CSV/Database, adjust renames accordingly
2. Another table loads the same field name → Solution: Systematically check all LOAD statements, not just the two problematic ones
3. Include file or subroutine overrides the rename → Solution: Check include files for field aliases

Debug steps:

1. Enable field value tracing: SET TraceValueFields = 1;
2. Search the load script for all occurrences of the problematic field name
3. Check recent changes in source systems (new columns, renamed fields)

Code fix:

// Debug: Track all fields
TRACE "=== BEFORE LOAD ===";
FOR i = 1 to NoOfTables()
    TRACE "Table: $(TableName($(i)-1))";
    FOR j = 1 to NoOfFields(TableName($(i)-1))
        TRACE "  Field: $(FieldName($(j), TableName($(i)-1)))";
    NEXT j
NEXT i

How Do I Fix Disappeared Associations After Field Renaming?

Symptom: Tables are isolated, no automatic associations visible anymore

Most common causes:

1. Association field was accidentally renamed → Solution: Key fields must remain identical
2. Data type mismatch after renaming → Solution: Check data types of key fields

Debug steps:

1. Check the data model: Are key fields present in both tables?
2. Test with sample data: TRACE Distinct(CustomerID); in both tables
3. Check data types: String vs Number can prevent association

Code fix:

// Ensure consistent data types
LOAD
    Text(CustomerID) as CustomerID,  // Force string type
    CustomerName
FROM customers.csv;

LOAD
    Text(CustomerID) as CustomerID,  // Force same type
    OrderDate
FROM orders.csv;

How Can I Improve Performance After Synthetic Key Resolution?

Symptom: Charts and expressions are slower despite eliminated $Syn tables

Most common causes:

1. Denormalization leads to larger tables → Solution: QVD optimization and memory management
2. More fields from renaming → Solution: Check whether all renamed fields are actually needed

Debug steps:

1. Compare memory usage before/after with DocumentSize()
2. Profiling: Which charts have become slowest?
3. Row count comparison: Have tables exploded due to denormalization?

Code fix:

// Reduce redundant fields
LOAD
    CustomerID,
    CustomerName,
    // CustomerRegion,    // Drop if not used in expressions
    // CustomerCountry    // Drop if derivable from other fields
FROM customers.csv;

See also: QVD Performance Optimization for memory-efficient solutions. For more background on how synthetic keys work, read the synthetic keys explained on Qlik Community.

What Are the Next Steps for Resolving Synthetic Keys & Circular References?

You can now create clean, performant data models without Synthetic Keys and Circular References. For production-ready apps, deepen your knowledge in these areas:

1. Optimal model architecture: Naming conventions are the first step toward systematic modeling. Star Schema Design shows you how to build enterprise-grade data models.

2. Performance optimization: Clean models are the foundation for fast apps. QVD Optimization maximizes load performance and memory efficiency of your models.

3. Enterprise standards: For larger teams, you need systematic governance. Data Governance Framework establishes standards and quality assurance for complex projects.