ExamFinch.com My Study

Study guide

2.1 Relational Databases, ODBC/OLE DB & Cloud Data Warehouses

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

2.1 Relational Databases, ODBC/OLE DB & Cloud Data Warehouses

Key Takeaways Explain This Topic AI

Qlik Sense communicates with relational data stores using 64-bit ODBC, OLE DB, or native packaged cloud connectors via the unified LIB CONNECT TO statement.

Pushdown optimization requires placing filter predicates in the source SQL SELECT clause rather than the preceding LOAD statement to prevent full table scans and redundant network serialization.

Modern Cloud Data Warehouses (Snowflake, BigQuery, Redshift, Synapse, Databricks) leverage high-throughput APIs, cluster roles, and partition pruning to maximize ingestion performance.

Preceding Load enables real-time transformations and data hygiene directly as rows stream into memory, avoiding costly secondary Resident reloads.

Production enterprise connections require dedicated service accounts with least-privilege read access, parameterizing connection strings for seamless tier promotion.

Last updated: October 2026

Relational Databases, ODBC/OLE DB & Cloud Data Warehouses

Connecting Qlik Sense to enterprise databases and cloud data platforms is the first critical milestone in building an associative data model. As a Qlik Sense Data Architect, you must understand how the QIX In-Memory Engine interacts with external database drivers, how SQL queries are executed, and where data transformations should occur to maximize reload speed and cluster stability.

1. Qlik Sense Data Connectivity Architecture

The Qlik Associative Engine (QIX) does not connect directly to raw physical disk tables on external servers. Instead, it utilizes an abstraction layer governed by the Qlik Connector Framework . This framework provides a standardized communication protocol between the reload execution engine and external database drivers.

The Ingestion Pipeline

When a reload task runs, Qlik executes the following sequence:

Script Parsing & Connection Initiation : The QIX engine reads the LIB CONNECT TO 'ConnectionName'; statement and contacts the connector subsystem to instantiate a session using the encrypted credentials stored in the repository.

SQL Query Submission : The engine transmits the nested SQL SELECT query directly across the driver to the remote database engine.

Query Compilation & Execution : The source database compiles, optimizes, and executes the SQL statement against its own storage structures (tables, views, partitions).

Rowset Streaming : The database returns tabular recordsets in data packets over the network to the Qlik connector buffer.

Preceding Load Processing : As tuples arrive in RAM, the QIX engine applies in-flight transformations defined in any preceding LOAD statements before committing the field symbols and record vectors into memory tables.

Driver Categories: ODBC vs. OLE DB vs. Native Packaged Drivers

Historically, Windows-based architectures relied heavily on generic system drivers. Modern Qlik architectures—spanning Qlik Sense Enterprise on Windows (QSEoW) and Qlik Cloud—use a combination of standardized and optimized native drivers:

ODBC (Open Database Connectivity) : A standardized, C-based API defined by the SQL Access Group. ODBC drivers are configured either as System DSNs (Data Source Names) within the 64-bit Windows ODBC Administrator ( odbcad32.exe ) or as DSN-less connections where all connection parameters (driver name, host, port, credentials) are supplied in the connection string. ODBC is universally supported across Windows and Linux/containerized environments.

OLE DB (Object Linking and Embedding, Database) : A Microsoft Component Object Model (COM) specification designed for high-performance access to relational data stores (particularly Microsoft SQL Server and Oracle). While offering lower protocol overhead than legacy 32-bit ODBC drivers on Windows, OLE DB is platform-dependent (Windows only) and is largely deprecated in cloud-first environments.

Native / Packaged Cloud Connectors : The Qlik ODBC Connector Package and dedicated cloud data warehouse connectors (Snowflake, Google BigQuery, Amazon Redshift, Databricks, Azure Synapse). These packaged connectors are built directly into Qlik Sense, requiring no external driver installation. They bypass generic ODBC driver limitations by leveraging vendor-specific high-throughput APIs (such as Apache Arrow serialization and gRPC streaming) and native OAuth 2.0 flows.

Connection Types Compared

  • Dimension ODBC (System DSN / DSN-less) OLE DB Provider Native Cloud Connectors
  • Underlying Standard C/C++ API standard Microsoft COM interface Vendor REST / gRPC / Arrow APIs
  • Platform Availability QSEoW (Windows) & Linux/Cloud QSEoW (Windows only) QSEoW & Qlik Cloud

Driver Configuration Windows ODBC Admin or Connection Wizard Data Link Properties Wizard Built-in Qlik Connector UI / Space Admin

Throughput & Speed Moderate to High (fetch-size dependent) High (optimized for SQL Server) Maximum (streaming vector buffers)

Authentication Support Basic (User/Password), Windows Kerberos Windows Integrated (NTLM), Basic Basic, OAuth 2.0, Key-Pair, IAM Roles

Recommended Use Case Legacy on-prem relational systems, niche DBs On-premises SQL Server instances Modern Cloud Data Warehouses (Snowflake, Databricks)

Important

Qlik Sense Enterprise runs exclusively as a 64-bit architecture . When setting up System DSNs on Windows Server, always configure the 64-bit ODBC Administrator ( C:\Windows\System32\odbcad32.exe ), never the 32-bit version ( C:\Windows\SysWOW64\odbcad32.exe ). Mismatched bitness is one of the most frequent reload failure causes in enterprise deployments.

2. Modern Cloud Data Warehouse (CDW) Integration

Modern cloud data warehouses separate compute from storage, allowing near-infinite scaling. However, improper Qlik integration patterns can trigger massive cloud billing costs or sluggish reload performance.

Snowflake Integration

Snowflake stores structured and semi-structured data in micro-partitions. Key architectural patterns for Qlik integration include:

Virtual Warehouse Sizing : Dedicated virtual warehouses (e.g., QLIK_RELOAD_WH ) should be provisioned for Qlik reload tasks with auto-suspend enabled (e.g., after 5 minutes of inactivity). This isolates BI ingestion workloads from operational reporting workloads.

Role-Based Context Execution : Snowflake requires setting execution context. You can pre-set default roles on the user account or issue session commands prior to querying: LIB CONNECT TO 'Snowflake_Production_DW';

  • // Execute pre-load commands to establish context if required
  • SQL ALTER SESSION SET TIMEZONE = 'UTC';

Arrow Optimization : Modern Qlik Snowflake connectors automatically pull compressed columnar chunks serialized in Apache Arrow format, bypassing traditional row-by-row ODBC serialization.

Authentication : While username/password is supported, enterprise security mandates Key-Pair (RSA 2048-bit) authentication or OAuth 2.0 integrated with Okta or Microsoft Entra ID.

Google BigQuery Integration

Google BigQuery is a serverless, multi-cloud data warehouse designed for business agility.

BigQuery Storage Read API : The Qlik BigQuery connector supports the High-Throughput Storage Read API. Rather than pulling data through standard paginated HTTP endpoints, it establishes parallel gRPC streams that read directly from underlying capacitor storage.

Partition & Cluster Filtering : BigQuery charges for data scanned. You must always pass partition filters (such as WHERE _PARTITIONDATE >= '2026-01-01' or clustering keys) inside the SQL SELECT block. Filtering in Qlik script after scanning the whole table wastes substantial cloud spend.

Billing Project Configuration : The connection must specify a valid Billing Project ID separate from the data project if cross-project billing structures are enforced.

Amazon Redshift Integration

Amazon Redshift is an MPP (Massively Parallel Processing) architecture organized into clusters with Leader and Compute nodes.

Distribution and Sort Keys : Understand the underlying schema's DISTKEY and SORTKEY . Structuring queries that match the sort key allows Redshift to skip disk blocks using zone maps, drastically cutting query response times.

IAM Authentication : Qlik supports IAM database authentication, retrieving temporary credentials via AWS STS (Security Token Service), eliminating static database passwords.

Redshift Spectrum : Qlik can query external tables defined over Amazon S3 data lakes (Parquet/ORC) through Redshift external schemas with identical SQL syntax.

Microsoft Azure Synapse Analytics & Databricks

Azure Synapse : Supports Dedicated SQL Pools (MPP engine) and Serverless SQL Pools (querying data lake Parquet files). Ensure dedicated pools are resumed before scheduled reload tasks.

Databricks Lakehouse : Connect to Delta Lake tables via Databricks SQL Warehouses using the native Databricks connector. Leverage Delta Lake data skipping and Z-Order clustering by passing precise query predicates.

3. Script Syntax and Execution Mechanics

Data loading from relational sources requires mastering three primary statements: LIB CONNECT TO , LOAD ... SQL SELECT , and SQL EXECUTE .

Connecting to a Governed Connection

All external data connections in Qlik Sense are referenced using the LIB CONNECT TO statement, which binds the reload session to a centrally managed connection object: // Connect to an enterprise data connection defined in QMC or Qlik Cloud

  • LIB CONNECT TO 'Snowflake_Enterprise_Analytics';
  • Preceding Load vs. Direct SQL SELECT

When loading data from a database, Qlik permits two scripting styles:

Direct SQL SELECT : Raw columns are selected from the database. The QIX engine ingests them as-is without inline Qlik transformations.

Preceding Load : A LOAD statement is placed directly above the SQL SELECT statement. The SQL SELECT executes first on the external database; the resulting rows stream directly into the preceding LOAD statement where Qlik functions manipulate the fields before committing them to RAM. // Demonstrating Preceding Load with in-flight transformation

LIB CONNECT TO 'Postgres_ERP_Warehouse';

Orders:

  • LOAD
  • OrderID,
  • CustomerID,
  • // In-flight Qlik transformation: format dates and build canonical periods
  • Date(Date#(OrderDateString, 'YYYY-MM-DD'), 'MM/DD/YYYY') as OrderDate,
  • Year(Date#(OrderDateString, 'YYYY-MM-DD')) as OrderYear,
  • Month(Date#(OrderDateString, 'YYYY-MM-DD')) as OrderMonth,
  • // Numeric arithmetic and flag construction
  • Quantity,
  • UnitPrice,
  • Quantity * UnitPrice as GrossAmount,
  • If(OrderStatus = 'CMP', Dual('Completed', 1),
  • If(OrderStatus = 'PND', Dual('Pending', 2),
  • Dual('Cancelled', 3))) as OrderStatusDual
  • ;
  • // Database-side query execution: pushdown projection and selection
  • SQL SELECT
  • order_id AS OrderID,
  • customer_id AS CustomerID,
  • order_date AS OrderDateString,
  • order_status AS OrderStatus,
  • quantity AS Quantity,
  • unit_price AS UnitPrice
  • FROM enterprise_schema.orders
  • WHERE order_date >= '2026-01-01'
  • AND is_deleted = FALSE;
  • Why Preceding Load is Best Practice

Avoids Resident Reloads : Without Preceding Load, developers load the raw SQL table, then execute LOAD ... RESIDENT RawTable; followed by DROP TABLE RawTable; . This resident reload pattern forces the QIX engine to duplicate memory structures and scan internal tables twice, doubling reload time and RAM consumption.

Separation of Concerns : Database-specific SQL dialect handles row filtering and physical table joins; Qlik script functions ( Date() , ApplyMap() , Dual() , Capitalize() ) handle business presentation logic in memory.

Pipeline Composability : Multiple preceding loads can be stacked on top of a single SQL query, evaluating from bottom to top sequentially.

SQL EXECUTE vs. SQL SELECT

SQL SELECT is strictly reserved for queries that return tabular data sets into Qlik internal tables.

SQL EXECUTE is used to trigger stored procedures, execute DDL/DML maintenance statements, or set database session variables where no tabular return set is mapped directly into an associative table: // Executing a database stored procedure for pre-reload ETL processing

LIB CONNECT TO 'Oracle_Core_Financials';

SQL EXECUTE pkg_financial_staging.refresh_daily_balances('2026-10-15');

Warning

SQL EXECUTE behavior depends heavily on the specific database driver. Some drivers do not return result sets to Qlik from executed stored procedures. If a stored procedure outputs a tabular cursor that you must ingest into the associative model, wrap the procedure execution in a database view or table function and query it with SQL SELECT .

4. Query Optimization and Pushdown Techniques

The architectural boundary between the source database and the Qlik engine is where performance is won or lost. The golden rule of data architecture is Pushdown Optimization : execute filtering, joining, and heavy pre-aggregation at the database source whenever the source engine can perform it faster and reduce network transmission.

Source Filtering vs. Script Filtering: The Pushdown Principle

Consider a transaction table containing 250 million rows, where the business only requires the current year's data (5 million rows): // ANTI-PATTERN: Client-side filtering in LOAD statement

Orders_Slow:

  • LOAD
  • OrderID,
  • OrderDate,
  • Amount
  • WHERE Year(OrderDate) = 2026; // Qlik evaluates this after reading rows
  • SQL SELECT
  • OrderID,
  • OrderDate,
  • Amount

FROM sales.transactions; // Fetches all 250,000,000 rows across the network!

In the anti-pattern above, the database performs a full table scan of 250 million rows, serializes every row across the network driver, and transmits gigabytes of data. The QIX engine discards 245 million rows as they arrive. This saturates network bandwidth and causes reload timeouts. // BEST PRACTICE: Database pushdown filtering via SQL WHERE

Orders_Fast:

  • LOAD
  • OrderID,
  • Date(OrderDate) as OrderDate,
  • Amount;
  • SQL SELECT
  • OrderID,
  • OrderDate,
  • Amount
  • FROM sales.transactions
  • WHERE OrderDate >= '2026-01-01' // Database prunes partitions/indexes; transmits 5M rows
  • AND OrderDate < '2027-01-01';

In the best-practice pattern, the database engine uses partition pruning and B-tree indexes to read only the target 5 million rows from storage, transmitting a compact dataset that reloads in seconds.

When is Qlik-Side Filtering Appropriate?

Client-side filtering in the LOAD statement is only justified when:

Filtering against a table already loaded into Qlik memory using WHERE EXISTS(KeyField) .

Evaluating proprietary Qlik functions that have no equivalent in the source database SQL dialect.

Ingesting from flat files (CSV, Excel) that do not possess an underlying database query engine.

Pre-Aggregation vs. Raw Grain Modeling

A critical decision for the Data Architect is determining the ingestion grain:

Raw Grain Extraction : Loading every transaction line item into Qlik. Provides complete drill-down capability to the individual receipt or invoice. However, for billion-row datasets, it increases memory footprint and reload times.

Source Pre-Aggregation : Using SQL GROUP BY to aggregate data at the day, store, or customer level before loading into Qlik. // Ingesting pre-aggregated monthly summary for executive KPI dash

MonthlySalesSummary:

  • LOAD
  • DepartmentID,
  • FiscalMonthDate,
  • TotalSales,
  • TotalTransactions,
  • UniqueCustomers;
  • SQL SELECT
  • dept_id AS DepartmentID,
  • DATE_TRUNC('month', txn_date) AS FiscalMonthDate,
  • SUM(sale_amount) AS TotalSales,
  • COUNT(txn_id) AS TotalTransactions,
  • COUNT(DISTINCT customer_id) AS UniqueCustomers
  • FROM transactions_dw.sales
  • WHERE txn_date >= '2025-01-01'
  • GROUP BY
  • dept_id,
  • DATE_TRUNC('month', txn_date);
  • Network Buffer, Concurrency, and Timeout Tuning

Fetch Size & Packet Size : In ODBC/OLE DB driver configurations, adjust the row fetch buffer (e.g., FetchSize=50000 ). Low fetch sizes force thousands of network round-trips for small row packets; excessively high fetch sizes exhaust driver heap memory.

Query Timeouts : Production connection strings should set an explicit query timeout (e.g., QueryTimeout=3600; ) to prevent runaway locked queries from stalling reload queues indefinitely.

Connection Concurrency : Running multiple parallel apps connecting to the same operational database (OLTP) can exhaust database connection pools. Schedule batch reloads during off-peak hours or extract from read-replicas.

5. Security and Credential Management

Governing database access within Qlik Sense requires enforcing zero-trust credential hygiene across environments.

Service Accounts vs. Individual User Accounts

Never configure production data connections using personal enterprise directory credentials (e.g., developer email accounts). Personal credentials present significant enterprise risks:

They expire during mandatory corporate password rotation cycles, abruptly failing mission-critical reload tasks.

When a developer changes departments or leaves the organization, their account is deactivated, severing all associated data pipelines.

Individual credentials lack audit separation between interactive user activity and automated service workloads.

Always provision Dedicated Service Accounts (e.g., svc_qlik_reporting ) with service principal names (SPNs) or machine identities.

Principle of Least Privilege

The database service account used by Qlik must be strictly granted read-only permissions ( SELECT ) limited to specific reporting schemas or views: -- Snowflake / PostgreSQL Least Privilege Example

  • CREATE ROLE qlik_readonly_role;
  • GRANT USAGE ON WAREHOUSE qlik_etl_wh TO ROLE qlik_readonly_role;
  • GRANT USAGE ON DATABASE analytics_dw TO ROLE qlik_readonly_role;
  • GRANT USAGE ON SCHEMA analytics_dw.reporting TO ROLE qlik_readonly_role;
  • - Grant SELECT ONLY on curated reporting views, never base transactional tables
  • GRANT SELECT ON ALL VIEWS IN SCHEMA analytics_dw.reporting TO ROLE qlik_readonly_role;
  • GRANT ROLE qlik_readonly_role TO USER svc_qlik_bi;

By restricting the account to curated views, the database administrator can implement column masking, row-level filtering, and query governance directly at the source engine before data ever traverses the wire.

6. Enterprise Production Scenario

Context : A multinational logistics provider operates an on-premises Oracle ERP database tracking 180 million shipping events per year. The business intelligence team reports that the daily Qlik Sense reload task takes 3 hours and 45 minutes , frequently failing due to network connection timeouts during peak morning hours.

Investigation : The Lead Data Architect inspects the load script and discovers the following implementation: // Flawed initial implementation

LIB CONNECT TO 'Oracle_Logistics_Prod';

ShipmentEvents:

  • LOAD
  • TrackingID,
  • Date(ShipDate) as ShipmentDate,
  • DestinationCountry,
  • FreightCost
  • WHERE Year(ShipDate) >= 2025;
  • SQL SELECT * FROM logistics.shipment_events;

Diagnosis :

SQL SELECT * forces Oracle to read and serialize all 120 columns across 500 million lifetime rows, including heavy unindexed VARCHAR2(4000) comments and internal audit columns never used in the Qlik application.

The date filter WHERE Year(ShipDate) >= 2025 is placed in the preceding LOAD statement. Oracle scans the entire historical table and transmits all historical records across the enterprise WAN before Qlik discards 70% of the rows.

The reload runs against the primary OLTP transaction node rather than the dedicated Oracle Active Data Guard read-replica.

Remediation : The architect implements a fully optimized data ingestion pipeline: // Re-architected high-performance ingestion script

SET vReloadStart = '2025-01-01 00:00:00';

  • // 1. Connect to the dedicated read-replica using a secured service account
  • LIB CONNECT TO 'Oracle_Logistics_ReadReplica';

// 2. Preceding load executes in-flight date typing and business logic in memory

ShipmentEvents:

  • LOAD
  • TrackingID,
  • Date(Floor(ShipTimestamp), 'YYYY-MM-DD') as ShipmentDate,
  • DestinationCountry,
  • Round(FreightCost, 0.01) as FreightCost;
  • // 3. Explicit projection of only required columns and SQL pushdown filtering
  • SQL SELECT
  • tracking_id AS TrackingID,
  • ship_timestamp AS ShipTimestamp,
  • dest_country_code AS DestinationCountry,
  • freight_cost_usd AS FreightCost
  • FROM logistics.shipment_events
  • WHERE ship_timestamp >= TO_TIMESTAMP('$(vReloadStart)', 'YYYY-MM-DD HH24:MI:SS')
  • AND is_test_record = 0;
  • // 4. Immediately persist clean data to Tier 1 QVD and free RAM
  • STORE ShipmentEvents INTO [lib://QVD_Extract_Tier/ShipmentEvents.qvd] (qvd);
  • DROP TABLE ShipmentEvents;

Result : Reload time drops from 3 hours 45 minutes to 11 minutes and 20 seconds . Network traffic across the enterprise backbone decreases by 94%, and query contention on the primary operational database is completely eliminated.

7. Common Exam Pitfalls

Placing SQL Dialect in Preceding LOAD : Writing database-specific functions (such as TO_DATE() , DATEADD() , NVL() , or DECODE() ) in the Qlik LOAD clause causes a script syntax error. SQL functions belong exclusively in the SQL SELECT block; Qlik functions (such as Date#() , AddYears() , Alt() , or Pick() ) belong in the preceding LOAD block.

Bypassing Pushdown with Complex Expressions : If you place a Qlik variable or complex expression in a SQL WHERE clause without dollar-sign expansion ( $(vDate) ), the database receives the literal text vDate rather than the evaluated value, throwing a database syntax error.

Using SELECT * in Production Scripts : Loading with SELECT * creates catastrophic technical debt. If a database administrator adds a high-cardinality GUID column or an uncompressed BLOB/JSON field to the source table, subsequent scheduled reloads will silently ingest those columns, consuming gigabytes of server RAM and degrading associative calculation speeds.

Confusing DSN Architectures on 64-bit Servers : Creating an ODBC connection under the 32-bit ODBC Administrator on a QSEoW server results in an error stating "The specified DSN contains an architecture mismatch between the Driver and Application" when Qlik Sense tries to connect.