ExamFinch.com My Study

Study guide

4.2 Composite Keys, Cardinality & AutoNumber Memory Optimization

Practice this exam

QSDA Qlik Sense Data Architect Study Guide Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction: About the QSDA Exam

About the QSDA Exam: Current Facts, Objectives & How to Use This Guide

2 Chapter 1: Business Requirements, Data Granularity & Security

1.1 Business Requirements, KPI Formulation & Data Granularity 1.2 Dimensionality Analysis & Slowly Changing Dimensions (SCD) 1.3 Data Security Architecture & Section Access Implementation

3 Chapter 2: Data Connectivity & Source Integration

2.1 Relational Databases, ODBC/OLE DB & Cloud Data Warehouses 2.2 REST API Connectors, Pagination Strategies & Web Data 2.3 Data Connections, lib:// Pathing & Qlik Cloud Space Governance

4 Chapter 3: Dimensional Modeling & Schema Architecture

3.1 Dimensional Modeling: Star Schema vs. Snowflake in Qlik Sense 3.2 Multi-Fact Modeling: Fact Concatenation vs. Link Tables 3.3 Resolving Synthetic Keys ($Syn) and Circular References

5 Chapter 4: QIX Engine Architecture & Key Performance Optimization

4.1 The QIX In-Memory Associative Engine: Symbol Tables & Data Tables 4.2 Composite Keys, Cardinality & AutoNumber Memory Optimization 4.3 Field Scoping, QUALIFY / UNQUALIFY & Semantic Naming

6 Chapter 5: Load Scripting Fundamentals & Table Operations

5.1 Script Structure, Preceding Load & Resident Operations 5.2 Merging Tables: Joins, Keeps & Concatenation Semantics 5.3 Mapping Tables: ApplyMap vs. Lookup & Join Performance 5.4 Advanced Table Reshaping: Crosstable, Generic & Inter-Record Functions (Peek/Previous) 5.5 Script Documentation, Organization & Reusable Include Files

7 Chapter 6: QVD Architecture, Incremental Loads & Temporal Logic

6.1 QVD File Architecture: Optimized vs. Unoptimized Loads 6.2 Multi-Tier QVD Architecture: Extract, Transform & Presentation Layers 6.3 Incremental Load Patterns: Insert, Update & Delete Strategies 6.4 Master Calendars, Canonical Dates & Temporal Logic

8 Chapter 7: Advanced Transformations, Data Cleansing & Validation

7.1 IntervalMatch & Hierarchy Modeling (Hierarchy & HierarchyBelongsTo) 7.2 Data Cleansing, Null Handling & Script Control Flow 7.3 Data Model Validation, Source Reconciliation & Subset Ratios 7.4 Script Debugging, Trace Logging & Security Auditing

4.2 Composite Keys, Cardinality & AutoNumber Memory Optimization

Key Takeaways Explain This Topic AI

Composite keys are constructed by concatenating multiple dimensional attributes (FieldA & '|' & FieldB) to establish unique relationship linkages in multi-fact and link table architectures.

Delimiters (such as '|' or '~') are mandatory when concatenating string composite keys to prevent value collision where distinct attribute combinations accidentally produce identical concatenated strings.

String composite keys impose severe memory penalties by creating multi-gigabyte Symbol Tables of long text strings and widening Data Table bit-stuffed pointers across tens of millions of records.

The AutoNumber() function replaces arbitrary string composite keys with compact sequential integers (1, 2, 3...) at reload time, virtually eliminating symbol table memory and minimizing pointer bit-widths.

In incremental reload and multi-tier QVD pipelines, AutoNumber() integer stability must be preserved across reloads by persisting master key mapping QVDs or utilizing deterministic hashing functions.

Last updated: October 2026

Composite Keys, Cardinality & AutoNumber Memory Optimization

Quick Summary: In enterprise Qlik Sense architectures, linking tables with multi-attribute relationships—such as connecting multi-grain fact tables through a Link Table or resolving synthetic keys—requires creating composite keys . However, naive string concatenation ( FieldA & '|' & FieldB ) generates long, high-cardinality string keys that severely inflate Symbol Table RAM and widen Data Table bit pointers. The AutoNumber() function and its cryptographic hash variants ( AutoNumberHash128() , AutoNumberHash256() ) solve this bottleneck by replacing sprawling string keys with compact, 1-based sequential integers, sharply reducing symbol-table memory.

1. Composite Keys in Qlik Sense Data Models

A composite key (or compound key) is a surrogate identifier constructed by combining two or more distinct data fields into a single linkage field. In relational database design, composite primary and foreign keys are resolved through multi-column join clauses ( ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 ). However, the QIX associative engine operates under a strict relational law: tables associate if and only if they share an identically named field .

When two tables share multiple identical field names, Qlik does not execute a multi-column join; instead, it automatically spawns a Synthetic Key Table ( $Syn ) . To prevent synthetic keys while preserving the multi-attribute relationship, the data architect must combine the shared attributes into a single composite key field.

The Mandatory Delimiter Rule

When concatenating fields to form a composite key, architects must always include an explicit delimiter that cannot appear within the underlying data values: // DANGEROUS ANTI-PATTERN: No delimiter causes accidental key collision

CustomerID & ProductID as %CompositeKey

// Examples of Collision:

  • // CustomerID = '12' and ProductID = '345' --> '12345'
  • // CustomerID = '123' and ProductID = '45' --> '12345' (COLLISION! Corrupts associative link)
  • // ENTERPRISE BEST PRACTICE: Explicit pipe or tilde delimiter guarantees uniqueness
  • CustomerID & '|' & ProductID as %CompositeKey

// Result:

  • // '12|345' <> '123|45' (Distinct keys preserved)
  • 2. The Severe Memory Penalty of Concatenated String Keys

While string concatenation ( FieldA & '|' & FieldB & '|' & FieldC ) solves relational ambiguity, it creates a massive performance and memory bottleneck in large enterprise datasets.

The Anatomy of String Key Bloat

Consider a supply chain data model containing 25,000,000 shipment fact rows and a shared Link Table connecting Shipments, Orders, and Invoices across three attributes: CompanyCode (4 chars), PlantID (8 chars), and DocumentNumber (18 chars). String Composite Key Construction:

  • CompanyCode & '|' & PlantID & '|' & DocumentNumber as %ShipmentLinkKey
  • Example Key Value: "US10|PLANT-EAST-04|DOC-2026-9874102948"
  • Average String Length: 36 characters (36 bytes in UTF-8)

Assume there are 18,000,000 distinct combinations across the dataset:

Symbol Table Footprint :

Unique symbols ( N N N ): 18 , 000 , 000 18,000,000 18 , 000 , 000 .

String data: 18 , 000 , 000 × 36 bytes ≈ 648 MB 18,000,000 \times 36\text{ bytes} \approx 648\text{ MB} 18 , 000 , 000 × 36 bytes ≈ 648 MB .

Symbol Table overhead (pointers, hash slots, tree nodes): 18 , 000 , 000 × 32 bytes ≈ 576 MB 18,000,000 \times 32\text{ bytes} \approx 576\text{ MB} 18 , 000 , 000 × 32 bytes ≈ 576 MB .

Total Symbol Table RAM: ≈ 1.22 GB \approx 1.22\text{ GB} ≈ 1.22 GB just to store the dictionary of string keys!

Data Table Pointer Array Footprint :

Distinct cardinality ( N N N ): 18 , 000 , 000 18,000,000 18 , 000 , 000 .

Pointer bit-width ( W W W ): ceil ( log ⁡ 2 ( 18 , 000 , 000 ) ) = 25 bits \text{ceil}(\log_2(18,000,000)) = \mathbf{25\text{ bits}} ceil ( lo g 2 ​ ( 18 , 000 , 000 )) = 25 bits per cell ( 2 25 = 33 , 554 , 432 2^{25} = 33,554,432 2 25 = 33 , 554 , 432 ).

Fact Table Pointers: 25 , 000 , 000 × 25 bits ≈ 78.1 MB 25,000,000 \times 25\text{ bits} \approx 78.1\text{ MB} 25 , 000 , 000 × 25 bits ≈ 78.1 MB .

Link Table Pointers: 18 , 000 , 000 × 25 bits ≈ 56.3 MB 18,000,000 \times 25\text{ bits} \approx 56.3\text{ MB} 18 , 000 , 000 × 25 bits ≈ 56.3 MB .

Total Pointer RAM: ≈ 134.4 MB \approx 134.4\text{ MB} ≈ 134.4 MB .

Associative Join Hash Tables :

When a user filters by plant or company, the QIX engine must evaluate associative relationships across this 36-byte string key. String hashing, cache misses, and memory bus transfers significantly degrade CPU responsiveness.

3. The AutoNumber() Function: Engine Mechanics & Compression

The AutoNumber() function is the primary tool for optimizing composite keys in Qlik Sense. It evaluates an arbitrary string expression and replaces it with a compact, 1-based sequential integer ( 1 , 2 , 3 , … 1, 2, 3, \dots 1 , 2 , 3 , … ) on the fly during script execution.

Syntax AutoNumber(Expression [, AutoNumberId])

Where:

Expression : The string or concatenated expression to convert into an integer.

AutoNumberId (Optional): A distinct integer or string identifier representing an isolated lookup namespace. If omitted, the default namespace is used.

How AutoNumber() Operates Internally

During reload, Qlik maintains a dynamic in-memory hash table for each AutoNumberId namespace:

When AutoNumber() encounters a value for the first time (e.g., 'US10|PLANT-EAST-04|DOC-001' ), it inserts the string into its temporary lookup table, assigns it the next integer in the sequence ( 1 ), and returns 1 .

When it encounters a previously seen string value, it immediately retrieves the existing assigned integer ( 1 ) and returns it.

At the conclusion of the reload script, the temporary hash table is completely purged from RAM. Only the resulting integer keys remain in the data model. Raw Concatenated String AutoNumber() Integer Key

  • 'US10|PLANT-01|DOC-901' ──► 1
  • 'US10|PLANT-02|DOC-902' ──► 2
  • 'US10|PLANT-01|DOC-901' (Seen) ──► 1 (Reused)
  • 'CA20|PLANT-05|DOC-903' ──► 3
  • The Memory Revolution of Integer Keys

Replacing the 36-byte string composite key with AutoNumber() transforms the memory architecture:

Symbol Table Transformation : Instead of storing 18 million 36-byte string literals, the Symbol Table stores a dense sequence of 32-bit integers ( 1 1 1 to 18 , 000 , 000 18,000,000 18 , 000 , 000 ). The QIX engine applies specialized bit-packing optimizations for dense sequential integers, shrinking symbol table overhead from 1.22 GB 1.22\text{ GB} 1.22 GB to under 75 MB 75\text{ MB} 75 MB .

Faster Comparisons : Compact integer keys are cheaper to store and compare than long string keys, which helps both reload time and selection response.

4. Cryptographic Hashing: AutoNumberHash128() and AutoNumberHash256()

Qlik also provides hash-based functions that accept the key fields as separate arguments, so you do not have to build a delimited string yourself.

The Hashing Function Family

Function Return Type Scope & Stability Primary Use Case

AutoNumber(Expr) Sequential Integer ( 1 , 2 , 3 … 1, 2, 3\dots 1 , 2 , 3 … ) Session-only (Reload sequence) Fast compression within a single script run

Hash128(A, B, ...) 128-bit hash returned as a 22-character string Deterministic across reloads and apps Cross-app linkage, incremental loads, QVD storage

Hash256(A, B, ...) 256-bit hash returned as a 43-character string Deterministic across reloads and apps Ultra-high cardinality ( > 10 9 > 10^9 > 1 0 9 rows) collision prevention

AutoNumberHash128(A, B, ...) Sequential Integer ( 1 , 2 , 3 … 1, 2, 3\dots 1 , 2 , 3 … ) Session-only (Reload sequence) Maximum speed: hashes parameters in C++ and assigns integer

AutoNumberHash256(A, B, ...) Sequential Integer ( 1 , 2 , 3 … 1, 2, 3\dots 1 , 2 , 3 … ) Session-only (Reload sequence) Maximum speed on extreme multi-attribute composite keys

  • Passing Fields Directly with AutoNumberHash128()
  • Instead of writing: // Builds a delimited string first, then numbers it
  • AutoNumber(CompanyCode & '|' & PlantID & '|' & DocumentNumber) as %Key
  • Architects write: // Passes the key fields directly; no delimiter needed
  • AutoNumberHash128(CompanyCode, PlantID, DocumentNumber) as %Key

Qlik documents AutoNumberHash128() as calculating a 128-bit hash of the combined input values and returning a unique integer for each distinct hash value. The result is the same kind of compact sequential key as AutoNumber() , and like AutoNumber() it is only stable within one reload.

The AutoNumber Statement

Qlik also provides an AutoNumber script statement that converts whole fields after they have been loaded: // Load keys as readable strings or hashes; the QVD reads can stay optimized

Fact_Sales:

LOAD %SalesKey, SalesAmount FROM [lib://QVD/Fact_Sales.qvd] (qvd);

Dim_Store:

LOAD %SalesKey, StoreName FROM [lib://QVD/Dim_Store.qvd] (qvd);

  • // After the final LOAD: replace every value of matching fields with an integer
  • AutoNumber '%*Key';

Qlik notes that the AutoNumber() function inside a LOAD limits optimized QVD loads, whereas loading the QVD first and then running the statement keeps the load optimized. Place the statement after the last LOAD that touches those fields, quote field names when you use wildcards, and remember that each field gets its own key index unless you add Using namespace .

5. Cross-Reload Key Stability in Incremental Load Pipelines

Caution

The Critical Danger of AutoNumber(): AutoNumber() integers are non-deterministic across reloads. The integer assigned to a given key value is determined strictly by the chronological order in which rows are read into memory during that specific reload run .

Consider this failure scenario:

On Monday, AutoNumber('Customer-A') is the first row loaded, receiving integer key 1 in Fact_Sales.qvd .

On Tuesday, an incremental reload runs. Because of new incoming transactions, AutoNumber('Customer-Z') is processed first, receiving integer key 1 . Meanwhile, Customer-A is loaded fifth, receiving integer key 5 .

The historical QVD stores Customer-A with key 1 , while the newly loaded record stores Customer-A with key 5 . The associative model is now catastrophically corrupted!

Enterprise Solution: Persistent Key Mapping QVD

To safely deploy AutoNumber() across multi-tier QVD architectures and incremental load pipelines, architects must ensure key stability. Two primary enterprise patterns exist:

Pattern A: Deterministic Hash128() in QVDs, AutoNumber() in Data Mart

In this recommended pattern, incremental ETL layers store deterministic 128-bit hashes in QVD files. The presentation layer app loads all QVDs and applies AutoNumber() in a single pass: Extract Tier (Incremental) ──► Stores Hash128(Company, Plant, Doc) as %HashKey in QVD

  • (Stable across any reload, any day)
  • │
  • ▼
  • Data Mart Model ──► LOAD AutoNumber(%HashKey) as %IntegerKey
  • (Runs across all data at once; keys match perfectly!)
  • Pattern B: Persistent Key Registry (Master Key QVD)

If the architect requires integer keys stored directly within persistent QVD files, a Master Key Registry must be maintained: // Step 1: Load the existing Master Key Registry from disk

KeyRegistry:

  • LOAD
  • NaturalKey,
  • IntegerKey
  • FROM [lib://Governance/KeyRegistry.qvd] (qvd);
  • // Determine the current maximum integer assigned
  • Let vMaxKey = Peek('IntegerKey', -1, 'KeyRegistry');
  • If Len('$(vMaxKey)') = 0 Then
  • Let vMaxKey = 0;
  • End If

// Step 2: Extract distinct new natural keys from today's incremental batch

NewKeys:

  • NoConcatenate LOAD DISTINCT
  • NaturalKey
  • FROM [lib://Extract/TodayIncrementalTransactions.qvd] (qvd)
  • WHERE NOT Exists(NaturalKey);
  • // Step 3: Assign new sequential integers starting from vMaxKey + 1
  • Concatenate (KeyRegistry)
  • LOAD
  • NaturalKey,

RecNo() + $(vMaxKey) as IntegerKey // RecNo() restarts at 1 for this resident source; RowNo() would continue counting the concatenated registry

RESIDENT NewKeys;

DROP TABLE NewKeys;

  • // Step 4: Save the updated Master Key Registry
  • STORE KeyRegistry INTO [lib://Governance/KeyRegistry.qvd] (qvd);

// Step 5: Map new integer keys into transactional facts using ApplyMap

Map_NaturalToInteger:

MAPPING LOAD NaturalKey, IntegerKey RESIDENT KeyRegistry;

Fact_Sales:

  • LOAD
  • TransactionID,
  • ApplyMap('Map_NaturalToInteger', NaturalKey) as %IntegerKey,
  • SalesAmount
  • FROM [lib://Extract/TodayIncrementalTransactions.qvd] (qvd);
  • DROP TABLE KeyRegistry;
  • 6. Enterprise Scenario: Global Logistics Multi-Fact Link Table
  • Business Problem

GlobalFreight Inc. operates a worldwide logistics platform processing 30,000,000 shipments, 25,000,000 freight invoices, and 12,000,000 customs declaration filings annually. Management requires an interactive executive dashboard where analysts can select any vessel, port of origin, or customs broker and seamlessly cross-analyze freight billing variance against transit milestone delays.

A novice developer constructed a Link Table using a raw concatenated string composite key: VesselIMO & '|' & OriginPortCode & '|' & DestPortCode & '|' & CarrierID as %ShipmentLinkKey

Performance Symptoms:

Server RAM consumption exceeded 42 GB on reload.

Interacting with port filter panes produced a 4.5-second UI freeze as the engine resolved string pointer intersections.

The data model viewer showed the Symbol Table for %ShipmentLinkKey consuming 2.1 GB alone.

Architectural Remediation:

Replaced the 4-attribute string concatenation with AutoNumberHash128(VesselIMO, OriginPortCode, DestPortCode, CarrierID) in all three fact tables and the Link Table, all in the same reload so the integers match.

Converted the Link Table from storing raw strings to pure dense sequential integers.

Illustrative Results (this scenario):

  • Performance Metric Before (String Composite Key) After ( AutoNumberHash128 ) Improvement
  • Symbol Table RAM 2 , 140 MB 2,140\text{ MB} 2 , 140 MB 48 MB 48\text{ MB} 48 MB 97.8% reduction
  • Data Table Pointer RAM 285 MB 285\text{ MB} 285 MB 160 MB 160\text{ MB} 160 MB 43.9% reduction
  • Total Application RAM 42.6 GB 42.6\text{ GB} 42.6 GB 14.2 GB 14.2\text{ GB} 14.2 GB 66.7% overall savings

Sheet Filter Response Time 4 , 500 ms 4,500\text{ ms} 4 , 500 ms (laggy) 380 ms 380\text{ ms} 380 ms (instant) 11.8x faster

Reload Duration 18 min 40 sec 18\text{ min } 40\text{ sec} 18 min 40 sec 11 min 15 sec 11\text{ min } 15\text{ sec} 11 min 15 sec 40% faster

7. String Key vs. AutoNumber Memory Comparison Table

The following matrix contrasts the architectural properties of raw string keys versus AutoNumber() integer keys across enterprise dimensions:

Architectural Vector Raw String Composite Key ( A & '~' & B ) AutoNumber() Sequential Integer Key AutoNumberHash128(A, B) Key

Underlying Data Type Alphanumeric Text (UTF-8 String) Dense 32-bit Integer Dense 32-bit Integer

Symbol Table Size Proportional to string length × \times × cardinality (Gigabytes) Minimal (Underlying values are dense integers, Megabytes) Minimal (Underlying values are dense integers, Megabytes)

Pointer Array Width Identical bit-depth ( W = ceil ( log ⁡ 2 ( N ) ) W = \text{ceil}(\log_2(N)) W = ceil ( lo g 2 ​ ( N )) ) Identical bit-depth ( W = ceil ( log ⁡ 2 ( N ) ) W = \text{ceil}(\log_2(N)) W = ceil ( lo g 2 ​ ( N )) ) Identical bit-depth ( W = ceil ( log ⁡ 2 ( N ) ) W = \text{ceil}(\log_2(N)) W = ceil ( lo g 2 ​ ( N )) )

Concatenation in Script Required (delimited string) Required (delimited string) Not needed (fields passed as separate arguments)

Hardware CPU Cache Efficiency Low (heap memory dereferencing, cache misses) High (L1/L2 cache register optimization) High (L1/L2 cache register optimization)

Deterministic Across Reloads Yes (string literal is identical every time) No (dependent on row read sequence) No (dependent on row read sequence)

Recommended Use Case Small datasets ( < 100 , 000 < 100,000 < 100 , 000 rows) Single-load data marts, static reloads High-volume multi-attribute link tables

8. Common Exam Pitfalls & Traps

Warning

Pitfall 1: Omitting Delimiters During String Key Concatenation Concatenating keys without a delimiter ( FieldA & FieldB ). Inevitably, distinct values such as 'A1' & '23' and 'A12' & '3' merge into the identical string 'A123' , causing accidental data loss and corrupted associations.

Warning

Pitfall 2: Using AutoNumber() Directly in Incremental Reload Scripts Executing AutoNumber() on daily incremental batches stored into historical QVDs. Because AutoNumber() assigns integers based on reload order, Monday's key 1 will not match Tuesday's key 1 , permanently scrambling historical relational linkages.

Warning

Pitfall 3: Confusing Hash128() with AutoNumberHash128() Expecting Hash128() to return an integer. Hash128() returns a 22-character string; it is deterministic across reloads, but it remains a string and consumes symbol-table memory. AutoNumberHash128() returns a compact 1-based integer instead.

Warning

Pitfall 4: Neglecting the AutoNumberId Parameter Using AutoNumber() for unrelated keys without the second argument ( AutoNumberId ). Both keys then draw from one shared counter, so neither %CustomerOrderKey nor %VendorProductKey gets its own compact 1-to-N sequence, and identical strings from the two domains receive the same integer. Qlik provides the ID argument to create separate counter instances when AutoNumber() is used on different keys.