4.1 The QIX In-Memory Associative Engine: Symbol Tables & Data Tables

The QIX In-Memory Associative Engine: Symbol Tables & Data Tables

Quick Summary: The QIX (Qlik Index) engine is an in-memory, columnar, associative analytical database. Unlike traditional relational database management systems (RDBMS) that store records as contiguous row blocks or disk-bound pages, QIX completely decouples data into two internal memory structures: Symbol Tables (distinct value dictionaries) and Data Tables (bit-stuffed index pointer arrays). Understanding how QIX allocates bits, indexes distinct values, and evaluates dual-typed data is fundamental to designing scalable enterprise models that handle tens or hundreds of millions of records within compact memory footprints.

---

1. Internal Columnar Architecture: Decoupled Storage

To achieve sub-second response times across large associative datasets, the QIX engine utilizes dictionary-encoded columnar compression. When a data architect executes a LOAD or SELECT statement in a Qlik script, the engine does not append raw text records to a traditional table grid. Instead, it shreds the incoming records column by column, isolating distinct values from their occurrence locations.

Incoming Stream:             Symbol Table (Dictionary)       Data Table (Bit Array)
Row 1: ["East",  100]    ──►  Field: Region                 Offset  Pointer
Row 2: ["West",  250]         Index 0: "East"                Row 1:   [0, 0]
Row 3: ["East",  100]         Index 1: "West"                Row 2:   [1, 1]
Row 4: ["North", 250]         Index 2: "North"               Row 3:   [0, 0]
                                                            Row 4:   [2, 1]

The Two Decoupled Storage Tiers

1. Symbol Tables (The Distinct Dictionary)

For every field present in the data model, QIX allocates a dedicated Symbol Table. The Symbol Table contains:

• Every unique, distinct literal value that appears within that field across the entire application, stored exactly once.
• Both the textual display string and the binary/numeric representation of each distinct value.
• An internal zero-based numeric index (symbol token) assigned sequentially or by sort order to each distinct entry.
• Hash lookup tables that allow the engine to map incoming values to symbol tokens during reload and query evaluation.

2. Data Tables (Bit-Stuffed Pointer Arrays)

The physical tables visible in the data model viewer (such as fact tables and dimension tables) do not store raw strings, decimal amounts, or customer names. Instead, a Data Table is a lightweight matrix of bit-stuffed integer pointers. Each cell in a data table column contains only the integer offset that points to the corresponding memory address in that field's Symbol Table.

When a user clicks a filter pane or interacts with a visualization, the associative engine evaluates selections across bit arrays rather than performing expensive string comparisons or scanning relational table scans. It evaluates state vectors (Selected, Possible, Alternative, Excluded) by intersecting bit arrays, delivering instantaneous associative responses.

---

2. Bit-Stuffed Pointer Mechanics & Logarithmic Bit Widths

In standard computer architectures, integer storage is typically constrained to byte boundaries: an 8-bit byte, a 16-bit short integer, a 32-bit integer, or a 64-bit long integer. If an RDBMS column holds an integer ranging from 1 to 3, it still consumes a minimum of 1 full byte (8 bits) or 4 bytes (32 bits) per row.

The QIX engine completely bypasses byte boundaries through bit-stuffing. In a QIX Data Table, a field's pointer array allocates only the exact minimum number of bits required to address the distinct cardinality of that field.

The Pointer Bit-Width Formula

The memory width ($W$) in bits allocated for every row in a data table column is determined strictly by the column's distinct cardinality ($N$) according to the ceiling base-2 logarithm:

$W = \text{ceil}(\log_2(N))$

Where:

• $N$ = Number of distinct unique values in the field (Symbol Table row count).
• $W$ = Number of bits consumed by each record pointer in the Data Table.

Distinct Cardinality (N)        Required Bits per Row (W)
1 to 2 unique values       ──►   1 bit  (indexes 0 to 1)
3 to 4 unique values       ──►   2 bits (indexes 0 to 3)
5 to 8 unique values       ──►   3 bits (indexes 0 to 7)
9 to 16 unique values      ──►   4 bits (indexes 0 to 15)
17 to 32 unique values     ──►   5 bits (indexes 0 to 31)
33 to 64 unique values     ──►   6 bits (indexes 0 to 63)
65 to 128 unique values    ──►   7 bits (indexes 0 to 127)
...                             ...
1,000,000 unique values    ──►  20 bits (indexes up to 1,048,576)
16,777,216 unique values   ──►  24 bits (indexes up to 16,777,216)
33,554,432 unique values   ──►  25 bits (indexes up to 33,554,432)

Real-World Bit Allocation Example

Consider an enterprise transactional table containing 20,000,000 rows:

1. Field A: OrderStatus
   • Distinct values: "Pending", "Processing", "Shipped", "Delivered" ($N = 4$).
   • Bits required per row: $\text{ceil}(\log_2(4)) = 2$ bits.
   • Total Data Table memory: $20,000,000 \times 2\text{ bits} = 40,000,000\text{ bits} \approx 5.0\text{ MB}$.
2. Field B: TransactionTrackingGUID
   • Distinct values: 20,000,000 unique alphanumeric UUIDs ($N = 20,000,000$).
   • Bits required per row: $\text{ceil}(\log_2(20,000,000)) = 25$ bits ($2^{25} = 33,554,432$).
   • Total Data Table memory: $20,000,000 \times 25\text{ bits} = 500,000,000\text{ bits} \approx 62.5\text{ MB}$ (just for pointers, excluding the massive Symbol Table).

Notice that both fields exist in the exact same 20-million-row table, yet TransactionTrackingGUID consumes 12.5 times more memory in the data table pointer array alone than OrderStatus, entirely because of distinct cardinality.

---

3. Mathematical Foundations: Cardinality vs. Row Count

A foundational premise of Qlik Sense architecture is that field cardinality—not raw row count—governs application RAM consumption and calculation velocity.

The total memory footprint for any field in QIX is the sum of its Symbol Table memory and its Data Table pointer memory:

$\text{Memory}(\text{Field}) = \text{Memory}(\text{Symbol Table}) + \text{Memory}(\text{Data Table})$

$\text{Memory}(\text{Symbol Table}) = N \times (\text{Len}_{\text{AvgText}} + \text{Size}_{\text{Numeric}} + \text{Overhead}_{\text{SymbolEntry}})$

$\text{Memory}(\text{Data Table}) = R \times \text{ceil}(\log_2(N)) \text{ bits}$

Where:

• $N$ = Distinct cardinality (unique values).
• $R$ = Total row count in the table.
• $\text{Len}_{\text{AvgText}}$ = Byte length of the string representation in UTF-8.
• $\text{Size}_{\text{Numeric}}$ = 8 bytes for 64-bit IEEE float (if numeric/dual).
• $\text{Overhead}_{\text{SymbolEntry}}$ = Engine dictionary pointers, hash table slots, and tree metadata (typically 24 to 40 bytes per distinct value in RAM).

The Cardinality Paradox: 100 Million Rows vs. 5 Million Rows

To see why cardinality dominates row count, examine two contrasting tables:

Despite having 20 times fewer rows, Scenario 2 consumes 22 times more RAM than Scenario 1. When junior developers ask why a 5-million-row model crashes the Qlik Sense Engine while a 100-million-row model runs smoothly, the answer is almost always high-cardinality symbol table bloat.

---

4. Dual Representation of Values: Dual(text, number)

In Qlik Sense, values are not strictly classified into rigid primitive types like SQL VARCHAR, INT, or DATETIME. Instead, a value loaded into the QIX engine can carry two components (a dual value):

1. A String Representation (Display Value): Used whenever the value is rendered in list boxes, chart labels, table cells, or exported to Excel.
2. A Numeric Representation (Binary Value): A 64-bit IEEE double-precision floating-point number used whenever the value is aggregated, sorted, or evaluated in mathematical expressions.

Numbers, dates, timestamps, and values created with Dual() carry both parts; a pure text value (such as a status word loaded from a text column) has only the string part.

        ┌──────────────────────────────────────────────┐
        │            QIX Dual Value Object             │
        ├──────────────────────┬───────────────────────┤
        │ String Part (Text)   │ Numeric Part (Number) │
        │ "October 15, 2026"   │ 46310                 │
        │ "$1,250.00"         │ 1250                  │
        │ "Approved"           │ 1                     │
        └──────────────────────┴───────────────────────┘

The Dual() Function Syntax

Architects can explicitly define dual values during the load script using the Dual() function:

// Syntax: Dual(text_representation, numeric_representation)
LOAD
    OrderID,
    Dual(StatusText, StatusCode) as OrderStatus,
    Dual(MonthName, MonthNumber) as FiscalMonth
FROM [lib://SourceData/Orders.qvd] (qvd);

Why Dual Representations Drive Performance

When a front-end chart evaluates an aggregation or a sorting algorithm, the QIX engine checks whether the underlying field possesses valid numeric representations:

• Numeric Execution Path (Optimal): If the field has a valid numeric representation, calculations (Sum(), Avg(), Min(), Max()) and sorting operations execute directly against the 64-bit binary float array inside CPU hardware registers. The engine avoids touching the symbol table string heap entirely.
• String Execution Path (Degraded): If a field is loaded as a pure string (e.g., using Text() or unparsed text flags), any comparison (If(Status = 'Active', ...)), string sorting, or set analysis requires resolving symbol pointers, dereferencing string memory pointers, and executing character-by-character string comparisons (strcmp). This consumes significantly more CPU clock cycles and causes cache misses.

Production Script Pattern: Dual Status Flags

// Inefficient: Pure string flags force string evaluations in charts
LOAD
    TransactionID,
    If(ReturnAmount > 0, 'Returned', 'Kept') as ReturnStatus
FROM [lib://QVD/Transactions.qvd] (qvd);

// Enterprise Pattern: Dual flag enables numeric speed with text display
LOAD
    TransactionID,
    If(ReturnAmount > 0, 
        Dual('Returned', 1), 
        Dual('Kept', 0)
    ) as ReturnStatus
FROM [lib://QVD/Transactions.qvd] (qvd);

In the visualization layer, a KPI object calculating the return count can now use the simple numeric aggregation Sum(ReturnStatus) instead of a set expression over text values such as Count({<ReturnStatus={'Returned'}>} TransactionID).

---

5. Memory Footprint Estimation & Cardinality Reduction Techniques

Because field cardinality determines symbol table size and pointer bit-widths, enterprise data architects routinely apply cardinality reduction transformations during QVD extraction and transformation layers.

High-Cardinality Culprits

1. Timestamps with Sub-Second or Minute Precision: A timestamp field formatted as YYYY-MM-DD hh:mm:ss contains up to 86,400 distinct values per day. Over 5 years of transactional history, this produces over 157 million potential distinct values.
2. Free-Form Text Fields & Audit Notes: Comments, address lines, and error messages where almost every row contains a unique string literal.
3. System Identifiers and GUIDs: Alphanumeric hash strings (e.g., 'a9f1b2c3-4d5e-6f7a-8b9c-0d1e2f3a4b5c') loaded into fact tables solely for audit tracking without any analytical purpose.

Cardinality Reduction Strategy: Timestamp Deconstruction

Consider a retail fact table containing 50,000,000 sales transactions recorded over 3 years:

Anti-Pattern: Retaining Full Timestamp

LOAD
    TransactionID,
    TransactionTimestamp, // 2026-10-15 14:23:41.128
    Amount
FROM [lib://RawSource/Sales.qvd] (qvd);

• Distinct cardinality ($N$): $\approx 45,000,000$ unique timestamps.
• Symbol Table size: $45\text{M} \times (24\text{ text bytes} + 8\text{ num bytes} + 32\text{ overhead}) \approx \mathbf{2.88\text{ GB}}$.
• Data Table pointer width ($W$): $\text{ceil}(\log_2(45,000,000)) = \mathbf{26\text{ bits}}$.
• Data Table pointer RAM: $50\text{M} \times 26\text{ bits} \approx \mathbf{162.5\text{ MB}}$.
• Total memory: $\approx 3.04\text{ GB}$ for a single field!

Enterprise Optimized Pattern: Decomposing into Date and Hour

LOAD
    TransactionID,
    // Truncate to pure integer date: 3 years = 1,096 distinct values
    Floor(TransactionTimestamp) as TransactionDate,
    // Extract discrete integer hour: exactly 24 distinct values (0-23)
    Hour(TransactionTimestamp) as TransactionHour,
    Amount
FROM [lib://RawSource/Sales.qvd] (qvd);

Let's evaluate the optimized memory footprint:

1. TransactionDate Field:
   • Distinct cardinality ($N$): $1,096$ unique dates.
   • Symbol Table size: $1,096 \times (10 + 8 + 32\text{ bytes}) \approx \mathbf{54.8\text{ KB}}$.
   • Data Table pointer width ($W$): $\text{ceil}(\log_2(1,096)) = \mathbf{11\text{ bits}}$.
   • Data Table pointer RAM: $50\text{M} \times 11\text{ bits} \approx \mathbf{68.75\text{ MB}}$.
2. TransactionHour Field:
   • Distinct cardinality ($N$): $24$ unique hours.
   • Symbol Table size: $24 \times (2 + 8 + 32\text{ bytes}) \approx \mathbf{1.0\text{ KB}}$.
   • Data Table pointer width ($W$): $\text{ceil}(\log_2(24)) = \mathbf{5\text{ bits}}$.
   • Data Table pointer RAM: $50\text{M} \times 5\text{ bits} \approx \mathbf{31.25\text{ MB}}$.

The Optimization Result:

• Original Timestamp Memory: $3,040\text{ MB}$
• Decomposed Date + Hour Memory: $68.8\text{ MB} + 31.3\text{ MB} = \mathbf{100.1\text{ MB}}$
• Net Memory Reduction: 96.7% RAM savings!
• Business Impact: End users retain full daily and intraday hourly trend analysis capability, while dashboard reload and query speeds increase by orders of magnitude.

---

6. Field Data Types and Bit-Pointer Widths Comparison Table

The following reference matrix outlines how distinct data types and cardinality profiles behave inside the QIX memory architecture:

---

7. Common Exam Pitfalls & Traps