Integrate data from Kdb+ (KX Systems) to Snowflake using Matillion

Creating an Identity in Kdb+

Kdb+ does not maintain a system-level concept of "users" or "roles" within the database itself (as you might find in traditional RDBMS like PostgreSQL or MySQL). Instead, authentication and authorization are managed via the underlying process, typically at the operating system (using OS-level users) or via the built-in authentication mechanisms of q (the Kdb+ language interpreter) and process configuration.

Below are instructions for configuring user access in Kdb+, primarily by setting usernames and passwords for connecting clients.

1. Setting Authentication via q Process Configuration

When launching a q process to run your Kdb+ database, you can set the

-u

Example: Start a q process with user authentication

bash
q mydb.q -p 5001 -u "priceadmin:pr1ceP@ssword"

priceadmin

pr1ceP@ssword

You can specify multiple users as a comma-separated list:

bash
q -p 5001 -u "alice:a1Pwd,bob:b0bPwd,admin:Adm!nPwd"

2. Controlling Access Inside the q Script

Alternatively, you can programmatically set or check credentials within your loading script:

``q
// Define a dictionary of authorized users and passwords
.USERS:

bob

// Verify authentication on connection system"secure 1"; // enable secure (authenticated) access

\ This automatically requires authentication from clients.

// Optionally, use the -u parameter programmatically system "u alice:a1Pwd" ```

3. Testing the Connection from a Client

A user connects using credentials as follows:

q
// In q session or script
h:hopen `:priceadmin:pr1ceP@ssword@localhost:5001

Or in SQL/KDB+ (using q-sql):

select from tableName where ...

4. Managing Authorization

Kdb+ does not natively provide SQL-like GRANT/REVOKE access control per table or schema. Custom application logic may be introduced to enforce authorization rules based on the identity passed in the connection.

5. Summary Table

-u

.z.pw

Note: Always protect credentials, avoid hardcoding in production code, and consider secure networking (TLS/SSL) for sensitive deployments. For further hardening, consider integrating OS-level controls (e.g., firewalling), or using reverse proxies for authentication.

Installing the JDBC driver

At the time of writing, the Kdb+ JDBC driver is not bundled with Matillion Data Productivity Cloud due to licensing and redistribution restrictions. As such, you must manually acquire and add the driver to your Matillion environment before you can connect to a Kdb+ database.

To obtain and configure the driver for use in Matillion Data Productivity Cloud, please follow the steps below:

1. Download the JDBC Driver
   Navigate to the KX documentation at https://code.kx.com/q/interfaces/jdbc/ to download the Kdb+ JDBC driver.

2. When selecting a driver, choose a Type 4 JDBC driver, as this type is platform-independent and communicates directly with the Kdb+ database via the network.

3. Prepare the Driver File
   Save the downloaded JAR file (the Java archive containing the JDBC driver) in an accessible location on your local machine, ready for upload.

4. Upload the Driver to Matillion Data Productivity Cloud
   The Matillion platform enables agents to utilize external JDBC drivers by uploading them via the Matillion user interface.

5. For step-by-step instructions on how to upload an external JDBC driver, refer to the official Matillion documentation here: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/.

6. Be sure to associate the uploaded driver with your running agent, in accordance with Matillion’s guidelines.

7. Configure and Use the Driver
   Once the driver is installed, you can configure database connections within Matillion Data Productivity Cloud to utilize the newly added Kdb+ JDBC driver.

8. Detailed usage instructions, including how to set up and configure a Database Query component to use your Kdb+ JDBC connection, can be found at: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/.

If you encounter any issues related to driver compatibility, version mismatches, or connection configuration, please consult both KX and Matillion documentation for troubleshooting suggestions and best practices.

Checking network connectivity

To ensure successful integration between Matillion Data Productivity Cloud and your Kdb+ database, you must verify that the Kdb+ database allows incoming network connections according to your chosen deployment configuration:

• Full SaaS agent configuration: The Kdb+ database must allow incoming connections from the Matillion Data Productivity Cloud's managed infrastructure. Ensure that your Kdb+ server allows inbound connections from the IP addresses listed at: Matillion SaaS Allowed IP Addresses.

• Hybrid SaaS deployment: The Kdb+ database should be configured to accept connections from your own virtual private cloud (VPC) where the Hybrid SaaS Agent is deployed. For assistance in verifying network paths and connectivity, refer to the utilities at: Matillion Network Access Check Utilities.

Additionally, if the Kdb+ database is referenced using a DNS hostname rather than an IP address, ensure that the relevant Matillion agent (Full SaaS or Hybrid SaaS) is able to resolve this DNS address to the correct IP endpoint. Proper name resolution is essential for establishing a connection.

Querying Data from a Kdb+ Database

This guide explains how to query data from a Kdb+ database, using SQL-like syntax, and discusses patterns for loading data—including initial and incremental loads—while considering datatype conversion with Snowflake.

1. Querying Kdb+ with SQL SELECT Statements

Kdb+ natively uses the q language for querying, but it also supports an SQL-like syntax for SELECT statements. Below are examples:

Selecting all rows from a table

SELECT * FROM trades

Selecting specific columns

SELECT sym, price, size FROM trades

Filtering with WHERE clause

SELECT * FROM trades WHERE sym = 'AAPL' AND price > 150.0

Aggregation and Grouping

SELECT sym, SUM(size) AS total_size FROM trades GROUP BY sym

Note: These SQL queries are translated to q queries under the hood in Kdb+. Not all SQL features are supported, so consult the q SQL reference for details.

2. Datatype Conversion Between Kdb+ and Snowflake

When migrating or querying data between Kdb+ and Snowflake, be aware that datatypes may not map 1:1. Common considerations include:

• Timestamp/Date/Time: Kdb+ supports nanosecond precision, whereas Snowflake typically supports microsecond or millisecond precision.
• Symbol (Kdb+): Typically maps to a

  VARCHAR

  or

  STRING

  in Snowflake.
• Floats and Doubles: Kdb+

  float

  and

  real

  may map to

  FLOAT

  ,

  DOUBLE

  or

  NUMBER

  datatypes in Snowflake.
• Boolean: Kdb+

  boolean

  maps to Snowflake

  BOOLEAN

  .

You may need to CAST or explicitly convert datatypes during data transfer operations.

3. Loading Patterns: Initial Load and Incremental Load

A robust data replication strategy involves:

• A once-off initial load to migrate the full dataset.
• Subsequent incremental loads to transfer only changed or new data.

You can use the same Database Query component or process for both, modifying only the filtering logic.

3.1. Initial Load

• Purpose: Import the entire table.
• Query pattern: No WHERE/filter clause.

Example:

SELECT * FROM trades

Usage: - Run this once to capture the full state of the table. - Suitable for new integrations or when rebuilding a target table.

3.2. Incremental Load

• Purpose: Import only new or updated rows, minimizing data transfer.
• Query pattern: WHERE clause filters for recently changed records, usually based on a column such as timestamp or an incrementing ID.

Example:

SELECT * FROM trades WHERE trade_time > 2024.06.13D12:00:00

Replace

2024.06.13D12:00:00

Best Practice: - Track the checkpoint in your ETL process (e.g., Matillion, Airflow). - Reference the Incremental Load Data Replication Strategy for deeper guidance.

Database Query Component Configuration

• Use the same component or connection for both initial and incremental loads.
• For initial loads: omit filter/WHERE clause.
• For incremental loads: add the appropriate filter condition.

This process ensures data consistency and optimal ETL performance in modern data pipelines.

Data Integration Architecture

Loading data into Snowflake in advance of integration is a practical way to divide and conquer the data integration challenge by breaking it into two distinct steps: loading and then transforming the data. This core principle of the ELT (Extract, Load, Transform) architecture simplifies the overall process and enables organizations to address each stage independently, improving manageability and flexibility. Once data is loaded, integration requires comprehensive data transformations, which are best accomplished using purpose-built data transformation pipelines. These pipelines allow detailed, repeatable, and reliable modification and merging of data, ensuring consistent results. Furthermore, a significant advantage of the ELT approach is that all data transformation and integration tasks occur within the target Snowflake database environment. This architecture leverages the scalability and performance of Snowflake, allowing for fast, on-demand transformations without having to provision or pay for separate data processing infrastructure outside of Snowflake.