Configuration

The components of a configuration file are:

db

Connection details for the target database. Example:

db:
  backend: mariadb
  hostname: 127.0.0.1
  port: 3306
  username: root
  password: admin

backend defaults to mariadb when omitted. Supported backend names are:

• mariadb - the default and currently complete production backend.
• xtdb - supported XTDB adapter. Install with the xtdb optional dependency before creating an XTDB engine. The optional dependency includes the binary psycopg wrapper for pgwire and the ADBC FlightSQL driver for Arrow-native bulk ingest.
• mssql - currently reserved for a future MS SQL adapter.

XTDB uses two ports when both backend paths are enabled:

• port defaults to 5432 for the pgwire/SQLAlchemy path.
• adbc_port defaults to 9832 for the ADBC/FlightSQL path.

XTDB writes are chunked by max_rows_per_insert, which defaults to 10000. This bounds the size of each final insert/ingest transaction after staging, deduplication, transforms, foreign-key handling, and unchanged-row filtering have already run. Lower this value for smaller XTDB nodes or raise it only after load testing retained/replay workloads.

ingestion

Dataset execution is bounded and preserves ordering for datasets that write to the same tables. Independent datasets can run concurrently:

ingestion:
  max_workers: 2
  queue_size: 2

Both values default to 1. Increase max_workers only after confirming the database pool and target database can sustain the additional concurrent writes.

table_configs

A collection of tables that will be managed by this configuration. Example:

table_configs:
- schema: test
  name: simple_table
  columns:
  - id
  - another_id
  - [ col_double, col_varchar ]
  primary_keys:
  - id
  foreign_keys:
  - name: another_id
    references:
      table: another_table
      column: id
  is_temporal: true
  delta_config:
    drop_unchanged_rows: true
    on_duplicate_key: take_last
    prefill_nulls_with_default: false
    row_finality: dropout

The main section defines usual properties of the table, including the name of the table, the column names, plus any primary and foreign keys.

A flag is_temporal indicates whether the table uses system versioning. Uploading of temporal data is done in two parts. First the data is staged to a temporary table, allowing some computations to be done before modifying the target table. Specifically:

• rows can be dropped from the staged table if they have not changed, meaning the as-of date of the associated data would not be modified.
• sometimes within a single batch of data, multiple versions of the same row may be present. The primary_key defines how rows are tested for ‘equality’ and the on_duplicate_key option controls how duplicates are handled.
• the row_finality option controls what happens when a row is no longer present in the data-batch.

column_definitions

A set of typed column definitions referenced by table_configs and datasets section of the files.

column_definitions:
- { name: rating, data_type: INT, header: Rating, nullable: false, default_value: 0 }
- {
    name: price_usd,
    data_type: 'DECIMAL(10,4)',
    transforms: {
        try_to_usd: [ 'Price', 'Year' ]
    }
  }
- { name: month, data_type: VARCHAR(2), header: Month }
- { name: year, data_type: INT, header: Year }

Each column definition defines a column to be created in the staging table. This is either a mapping from a column in the source data file (called header when the name is different), or a derived column according to some user-defined transformation. An example of how to define a transformation is the price_usd column above, with full details given in the example notebook.

datasets

This is a set of data sources and pipelines, describing where to look for new data, and how to process it into the defined tables. Example:

datasets:
- name: turkey_food_prices
  delta_table_schema: test
  scrape_limit: ~
  search_paths:
  - root_path: ../_testdata_dataset_data
    file_include: ['turkey_food_prices.csv']
    is_enabled: true
    timestamp:
        source_tz: Europe/London
        method: mtime
   - ...

  pipeline:
  - table: unit_info
    columns:
    - id<um_id!
    - name<um_name!

  - table: food_prices
    type: primary
    columns:
    - product_id!
    - um_id!
    - price!
    - price_usd!

The search_paths section defines where to find the data. The example above uses the timestamp.method of mtime to set the as-of date of the data.

The pipeline section contains a sequence of tasks that are run in order. The example above demonstrates two tasks:

For flattened payloads, the pipeline is also the authoritative input schema. Every source column must be declared. Its input type is resolved from ingestion_data_type, falling back to target_data_type and then the target table column type. Declare ingestion_data_type only when the raw type differs from the target type. Undeclared columns, incompatible values, missing required columns, conflicting declarations, and nulls in non-nullable columns fail before payloads are combined or staged.

Use column_type: input_only for a declared, typed source field that must be accepted but not staged or persisted:

columns:
- {source: record.id, target: id}
- {source: record.measurement, target: measurement, ingestion_data_type: DOUBLE}
- {source: record.unused_note, column_type: input_only, ingestion_data_type: VARCHAR(64)}

Transformation results and staging types continue to come from target_data_type or the target table schema, so a separate output schema is not required. The older dsv_only spelling remains supported for existing DSV configurations.

Task 1: Unit Info Table

This task populates the unit_info table:

• Reads um_id and um_name columns from the staging table
• Renames them using < operator:
  • um_id to id
  • um_name to name
• Both columns are required (marked with !)

Task 2: Food Prices Table

This task populates the food_prices table:

• Reads product_id, um_id, price, and price_usd columns
• Maps directly to matching column names (no renaming needed)
• All columns are required (marked with !)
• Table is marked as type: primary for system versioning
• Note: Currently only one primary task is allowed per pipeline

Valid time

Datasets can map source validity columns onto a backend’s valid-time metadata with valid_time entries:

datasets:
- name: source_event_stream
  delta_table_schema: source
  valid_time:
  - schema: source
    table: events
    from_column: event_timestamp

  pipeline:
  - schema: source
    table: events
    type: primary
    columns:
    - {source: id, target: id}
    - {source: event_timestamp, target: event_timestamp}

The configured from_column identifies when a row becomes valid. An optional to_column identifies when the row stops being valid for rows with an explicit validity window:

valid_time:
- schema: overrides
  table: data_override_operations
  from_column: valid_from
  to_column: valid_to

Mapped valid-time source columns are required. Ingest fails if a configured from_column or to_column is missing or contains null values, because falling back to upload/system time would change the source semantics.

Backends decide how to materialize the backend-neutral valid-time contract. The XTDB backend maps from_column to _valid_from and to_column to _valid_to. SQL/MariaDB ingestion validates the configured source columns in the staging table, but does not materialize separate valid-time metadata.

Override Ledgers

polars-hist-db exposes a backend-independent override ledger contract for applications that need append-only user-authored corrections without mutating canonical source-feed tables.

from polars_hist_db.overrides import (
    OverrideLedgerConfig,
    build_override_table_config,
    build_override_valid_time_config,
)

config = OverrideLedgerConfig(
    schema="overrides",
    table="data_override_operations",
)

table_config = build_override_table_config(config)
valid_time = build_override_valid_time_config(config)

Equivalent YAML shape for downstream applications:

override_ledgers:
  - schema: overrides
    table: data_override_operations
    valid_time:
      from_column: valid_from
      to_column: valid_to

valid_from and valid_to are explicit business-validity columns. XTDB also has built-in _valid_from and _valid_to columns, but applications should not depend on those system columns as the only representation of user correction windows.

The generated table includes owner, actor, feed, entity, field path, operation type, typed JSON value, observed canonical value, stale-source flag, validity window, reason, comment, and metadata fields. The same table contract is intended to work with MariaDB and XTDB adapters.