Reference Data in KDB-X DB Service¶

This page explains what reference data is, how to define a reference table and link it to your timeseries data with a foreign key, and how to import and query it.

Clustered deployment

The examples on this page define tables through the table management API, used in single-node deployments. In a clustered deployment, define reference tables and their foreign keys in the assembly YAML instead — refer to Manage Tables (Clustered).

Reference data¶

Reference data is non-timeseries data that has some meaningful relation to other data. Reference data is typically small, static, and/or slowly changing.

A typical example of reference data would be organizational data, which may include postal codes, addresses, names, dates of birth, and more. Reference data are pieces of information that you want to reference when you query or analyze other data.

The examples on this page use an instruments table, holding the category and price precision of each currency pair, as reference data for the fxquote timeseries table.

Memory considerations¶

For reference data the table type determines the memory requirements of having the table. With a type of splayed or basic the table is stored on disk and memory mapped, and only recent records or updates are held in memory until they are written down at end-of-interval. If instead, for performance reasons, you want the table to be held fully in memory, set the type to splayed_mem.

Define a reference table¶

To define a reference table, define the schema and pick one or more columns as primary keys with primaryKeys. A primary key should uniquely identify the reference data. Refer to keyed tables for the ordering rules that apply to the key columns.

qPythonREST

// Define columns; the primary key column is listed first
instrumentsCols:(`name`type!("sym";"symbol");`name`type!("instrumentid";"long");`name`type!("category";"symbol");`name`type!("decimals";"long");`name`type!("pipdecimals";"long"))

// Create the reference table ('instruments'), keyed on 'sym'
session.createTable[`table`type`primaryKeys`columns!("instruments";"splayed";enlist "sym";instrumentsCols)]

session.create_table(
    table="instruments",
    type="splayed",
    primaryKeys=["sym"],
    columns=[
        {"name": "sym", "type": "symbol"},
        {"name": "instrumentid", "type": "long"},
        {"name": "category", "type": "symbol"},
        {"name": "decimals", "type": "long"},
        {"name": "pipdecimals", "type": "long"},
    ]
)

curl -s -X POST "http://localhost:8080/api/v0/tables/instruments" \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
    "type": "splayed",
    "primaryKeys": ["sym"],
    "columns": [
    {"name": "sym", "type": "symbol"},
    {"name": "instrumentid", "type": "long"},
    {"name": "category", "type": "symbol"},
    {"name": "decimals", "type": "long"},
    {"name": "pipdecimals", "type": "long"}
    ]
}'

Add a foreign key¶

In the corresponding timeseries table, set the foreign property of a column to the table and column name of a primary key column in the reference data. This indicates that the values of the column are a foreign key into a column in another table, and is given in the form table.column, where the table is another table in the same schema.

Both are checked when the table is created:

the column type must match the type of the column in the other table, or the request is rejected with foreign keys refer to columns of mismatching type
the referenced table must already exist, or the request is rejected with foreign keys refer to invalid table

So create the reference table first, then the table that points at it.

The fxquote table of the example create points its own sym column at the reference data by adding foreign, leaving the rest of the definition unchanged:

qPythonREST

`name`type`foreign`attrMem`attrDisk`attrOrd!("sym";"symbol";"instruments.sym";"grouped";"parted";"parted")

{"name": "sym", "type": "symbol", "foreign": "instruments.sym", "attrMem": "grouped", "attrDisk": "parted", "attrOrd": "parted"},

{"name": "sym", "type": "symbol", "foreign": "instruments.sym", "attrMem": "grouped", "attrDisk": "parted", "attrOrd": "parted"}

A describe of fxquote reports the foreign key back on the column.

A foreign key also constrains the order in which the two tables can be removed: a reference table cannot be dropped while another table still references it.

Import reference data¶

Reference data is imported like any other data, with either a file import or an API data import. Because the table is keyed, records with matching keys update the existing records rather than being appended, so a later import of the same key refreshes it.

qPythonREST

job:session.importFiles[`table`path!("instruments";"instruments.csv")]

job = session.import_files(table="instruments", path="instruments.csv")

curl -s -X POST "http://localhost:8080/api/v0/imports/files" \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{"table": "instruments", "path": "instruments.csv"}'

An unkeyed table can still be the target of a foreign key, and a query resolves the key either way. It accumulates a duplicate row on every re-import of the same key, however, and a query that resolves the foreign key then returns the value from one of those rows without reporting the ambiguity.

Query reference data¶

A query reads columns of the reference table by naming them in table.column form, and the service resolves the foreign key and joins the reference data for you. These reference columns can be used to select, group and filter.

The query below returns quotes with the category of each currency pair taken from instruments, keeping only those in the Major category:

qPythonREST

session.querySimple[([
    table:`fxquote;
    startTS:2026.03.02D00:00:00;
    endTS:2026.03.02D00:00:10;
    agg:("ts";"sym";"bid";"ask";"instruments.category");
    filter:enlist[("=";"instruments.category";"Major")];
    sortCols:enlist "ts"])]

session.query_simple(table='fxquote',
    startTS='2026.03.02D00:00:00',
    endTS='2026.03.02D00:00:10',
    agg=['ts', 'sym', 'bid', 'ask', 'instruments.category'],
    filter=[['=', 'instruments.category', 'Major']],
    sortCols=['ts'],
    return_as="pandas")

curl -X POST "http://localhost:8080/api/v0/query/simple" \
    -H "Content-Type: application/json" \
    -d '{"table":"fxquote",
        "startTS":"2026.03.02D00:00:00",
        "endTS":"2026.03.02D00:00:10",
        "agg":["ts","sym","bid","ask","instruments.category"],
        "filter":[["=","instruments.category","Major"]],
        "sortCols":["ts"]}'

The result carries the reference column under its dotted name:

{"ts":"2026-03-02T00:00:00.000000000","sym":"EURUSD","bid":1.16397,"ask":1.16399,"instruments.category":"Major"}

Not every query API resolves reference columns; refer to reference columns for the APIs that support them, and for the equivalent form in a q query.

Next steps¶

Refer to manage tables for table types, keyed tables, and column definitions.
Refer to import for the full set of import options.
Refer to query for the query APIs.