Data Finder

Data Finder is a model-driven query library for Python. You define your domain model and map it to a relational schema; the library generates type-safe Python Finder classes that let you query your data without writing SQL. Temporal versioning (milestoning) is a first-class concept, not an afterthought.

Overview

The workflow has three steps:

1. Define a model — describe your domain as classes and associations in Markdown.
2. Define a mapping — map each class property to a database table column in Markdown.
3. Generate finders — run the generator once to produce Python query classes.

Generated finders expose every mapped property as a typed method, and queries are assembled from those methods rather than from raw strings.

from finance.trade.trade_finder import TradeFinder

results = TradeFinder.find_all(
    processing_valid_at=datetime.datetime.now(),
    display_columns=[TradeFinder.symbol(), TradeFinder.price()],
    filter_op=TradeFinder.symbol().eq("AAPL"),
)
df = results.to_pandas()

Concepts

Class

A Class represents a logical entity — the equivalent of a UML class or a business object. It has a name, an optional description, and a set of properties.

### Class: Account

| Name    | Description                                     |
|---------|-------------------------------------------------|
| Account | Trading account used to buy and sell securities |

| Property | Id   | Type    | Multiplicity | Key | Description |
|----------|------|---------|--------------|-----|-------------|
| Id       | id   | Integer | 1            | Y   |             |
| Name     | name | String  | 1            |     |             |

Each property has:

Supported primitive types: String, Integer, Double, Float, Decimal, Date, DateTime, Boolean.

Classes belong to a Sub-Domain (package), which controls the Python package structure of the generated finders.

## Sub-Domain: finance.reference_data

### Class: Account
...

A sub-domain with a dotted name (finance.reference_data) generates a nested package hierarchy.

Inheritance

A class can extend one or more parent classes:

### Class: Employee extends Person, Contactable

All inherited properties are available via all_properties() and appear in the generated finder.

Association

An Association declares a named relationship between two classes. Each end is the property added to that end's own class, and its multiplicity: the source property is added to the source class and returns the target (Trade.account), and the target property is added to the target class and returns the source (Account.trades). The property names are the navigation methods in the generated finders.

### Association: TradeAccount

| Name         | Source | Source Property Name | Source Property ID | Source Multiplicity | Target  | Target Property Name | Target Property ID | Target Multiplicity | Description                  |
|--------------|--------|----------------------|--------------------|---------------------|---------|----------------------|--------------------|---------------------|------------------------------|
| TradeAccount | Trade  | Account              | account            | 1                   | Account | Trades               | trades             | *                   | Links a trade to its account |

The source property and both multiplicities are mandatory. Leave the target property name and id empty for a one-way relationship, where the target class gets no accessor back:

| Name            | Source | Source Property Name | Source Property ID | Source Multiplicity | Target     | Target Property Name | Target Property ID | Target Multiplicity | Description                 |
|-----------------|--------|----------------------|--------------------|---------------------|------------|----------------------|--------------------|---------------------|-----------------------------|
| TradeInstrument | Trade  | Instrument           | instrument         | 0..1                | Instrument |                      |                    | *                   | The instrument a trade is in |

The association is the single source of truth for cross-class navigation — do not also declare the same property explicitly on the class. The mapping defines the join in an #### Association: section, whether or not the relationship is one-way.

When an association is loaded, its navigation properties are automatically available via all_properties() on the relevant classes and generate methods in the finder.

Mapping

A Mapping file connects the logical model to the physical relational schema. It references one or more model files, declares the data store, and maps each class to a table.

Structure

# Finance Mapping

## Model: finance.md
## Model: finance_trade.md

## DataStore: finance_db (Database)

| Scheme                   | processing_start | processing_end | business_date |
|--------------------------|------------------|----------------|---------------|
| processing_only          | in_z             | out_z          |               |
| business_date_processing | in_z             | out_z          | DATE          |

### Schema: ref_data

#### Table: account_master → Account

| Column    | Type    | Key | Nullable | Property ID |
|-----------|---------|-----|----------|-------------|
| ID        | INT     | PK  | N        | id          |
| ACCT_NAME | VARCHAR |     | Y        | name        |

### Schema: trading

#### Table: trades → Trade (milestoning: processing_only)

| Column     | Type      | Key | Nullable | Property ID |
|------------|-----------|-----|----------|-------------|
| sym        | VARCHAR   |     | Y        | symbol      |
| price      | DOUBLE    |     | Y        | price       |
| account_id | INT       | FK  | Y        | account     |
| in_z       | TIMESTAMP |     | Y        | validFrom   |
| out_z      | TIMESTAMP |     | Y        | validTo     |

#### Association: TradeAccount

| Source Column | Target Table   | Target Column |
|---------------|----------------|---------------|
| account_id    | account_master | ID            |

Table mapping

#### Table: <table_name> → <ClassName> maps a database table to a class. The Property ID column references the property Id defined in the model.

Every column row needs a Nullable value: Y if the column can hold NULL, N if it is NOT NULL. A PK column is never nullable, so it should be N.

For foreign-key columns that represent association navigations, set Key to FK and use the association's Source Property ID as the property name. Follow the table block immediately with an #### Association: block that provides the join target.

Association mapping

#### Association: <AssociationName> specifies how the foreign key joins to the target table.

Embedded properties

A table can hold the columns of a related object. Give those columns a dotted Property ID that follows the associations, such as firm.legalName or firm.address.name. If the table has no #### Association: block for that navigation, nothing is joined: the properties are read straight from the row.

| Column            | Type    | Key | Nullable | Property ID       |
|-------------------|---------|-----|----------|-------------------|
| FIRM_LEGALNAME    | VARCHAR |     | Y        | firm.legalName    |
| FIRM_ADDRESS_NAME | VARCHAR |     | Y        | firm.address.name |

person.find_all(None, None, [person.first_name(), person.firm().address().name()])

With an #### Association: block the dotted columns are a shortcut instead, and the join is used only when the query needs a property that is not embedded.

Milestoning

Milestoning lets you query data as it was at a specific point in time. Declare named milestoning schemes on the DataStore and reference them per table.

Tag a table with the scheme name in parentheses:

#### Table: trades → Trade (milestoning: processing_only)

Generating Finders

Load the mapping markdown and call generate:

from mapping_markdown.markdown_mapping import load
from datafinder_generator.generator import generate

mapping = load("finance_mapping.md")
generate(mapping, output_directory="src/")

This writes one <classname>_finder.py file per mapped class, organised into the package structure declared by the sub-domain names.

Refreshing a mapping from a live database

If you have a DuckDB database you can auto-populate the mapping skeleton from the live schema:

uv run datafinder_duckdb/src/datafinder_duckdb/refresh_mapping.py path/to/finance.db finance_mapping.md

Exporting the model and mappings to JSON

For tools that are not written in Python (the ER diagram viewer in data-finder-ts, for one), mapping_markdown.export_json parses model and mapping markdown with the same loaders and writes plain JSON, so the format has one definition:

python -m mapping_markdown.export_json --config export.config.json --out data.json

The config names the markdown to export (dir is a folder relative to the config file, or python:<package> for an installed package's folder):

{ "dir": ".", "models": ["finance.md", "finance_trade.md"], "mappings": ["finance_mapping.md"] }

models lists each model's packages, classes (own properties with type, multiplicity, key and description, and superclasses) and associations (both ends: the source property, the target property, which is null for a one-way association, and the multiplicity of each). mappings lists the data store, schemas, tables with the class each is mapped to, columns with the property each is mapped to, and the joins between tables with their association and multiplicities. Models that a mapping references are exported too.

Using the Generated Finders

Simple queries

from finance.reference_data.account_finder import AccountFinder

# All accounts
result = AccountFinder.find_all(
    display_columns=[AccountFinder.id(), AccountFinder.name()],
)

# Filtered
result = AccountFinder.find_all(
    display_columns=[AccountFinder.id(), AccountFinder.name()],
    filter_op=AccountFinder.name().eq("Acme Corp"),
)
df = result.to_pandas()

Combine filters with and_ and or_. A chain reads left to right, each call taking everything before it as its left side, so the first line below is (symbol is AAPL or symbol is MSFT) and price > 100:

TradeFinder.symbol().eq("AAPL").or_(TradeFinder.symbol().eq("MSFT")).and_(TradeFinder.price() > 100)
TradeFinder.symbol().eq("AAPL").and_(TradeFinder.price() > 100).or_(TradeFinder.symbol().eq("MSFT"))

The second is (symbol is AAPL and price > 100) or symbol is MSFT. To group differently, build the part that should bind first and combine it on the other side, e.g. a.or_(b.and_(c)).

Aggregates

A query that selects aggregates is grouped by its other columns, so you do not have to repeat them in group_by:

# average price for each account
TradeFinder.find_all(None, None, [TradeFinder.account().name(), TradeFinder.price().average()])

An aggregate over a to-many association is computed for each row of the root, as in Legend. This gives each employee the number of their reports, including 0 for those with none:

EmployeeFinder.find_all(None, None, [EmployeeFinder.name(), EmployeeFinder.employees().id_().count()])

A query that selects only aggregates over the root's own columns covers every row and is not grouped. Pass group_by to choose the grouping yourself; it is used as given.

Naming columns

A column is named in the result after its property. Give it another name with alias, on an attribute, an aggregate or a function of a column:

CompanyFinder.find_all(None, None, [CompanyFinder.name().alias("Company"), CompanyFinder.id_().count().alias("N")])

A fixed value can be a column too. constant takes a bool, number, string, date or datetime:

from datafinder import constant

CompanyFinder.find_all(None, None, [CompanyFinder.name(), constant("EU").alias("Region")])

Sort with order_by, and limit with limit. Several order_by calls accumulate: the latest call takes priority and the earlier ones break its ties, so the two lines below sort by category and then by name. Sorting a column again replaces its earlier direction.

result = CompanyFinder.find_all(None, None, [CompanyFinder.name(), CompanyFinder.category()])
result.order_by(CompanyFinder.name().ascending()).order_by(CompanyFinder.category().ascending())

count_all() counts the rows of a group, COUNT(*), where count() of a column skips rows where that column is missing: CompanyFinder.find_all(None, None, [CompanyFinder.category(), count_all()]).group_by(CompanyFinder.category()). std() and var() give the sample standard deviation and variance, and std(ddof=0) / var(ddof=0) the population ones, as in pandas. min() and max() also work on text and dates.

An expression has the aggregates a column has: (TradeFinder.price() * TradeFinder.quantity()).sum(), constant(1).sum(), when(TradeFinder.price() > 10).then(5.0).otherwise(1.0).sum(). A condition has all() and any(): (TradeFinder.price() > 10).all() is true for a group where every row holds.

having keeps the groups that satisfy a condition on an aggregate or a grouping column, HAVING: CompanyFinder.find_all(None, None, [CompanyFinder.category(), count_all()]).group_by(CompanyFinder.category()).having(count_all() > 1). An aggregate compares as a column does (< <= > >= == !=), and contains, starts_with and ends_with also work on an expression that holds text, such as CompanyFinder.name().upper().starts_with("AC") or a joined text.

count_distinct() counts the different values in a group, as COUNT(DISTINCT column).

fill_null replaces a missing (NULL) value: EmployeeFinder.manager().name().fill_null("nobody"), as fillna does for a pandas column.

join_strings(separator, order_by=...) is an aggregate that joins a group's text values into one: CompanyFinder.name().join_strings(", ", order_by=[CompanyFinder.name().ascending()]). Without order_by the order the

join(separator, order_by=...) is an aggregate that joins a group's text values into one: CompanyFinder.name().join(", ", order_by=[CompanyFinder.name().ascending()]). Without order_by the order the values are joined in is not defined. Missing values are skipped.

Numeric columns combine with + - * / and with numbers: TradeFinder.price() * 2, 100 - TradeFinder.price(), TradeFinder.price() * TradeFinder.account().id_(). Parentheses follow the usual precedence, so (price + 1) * 2 needs them in Python as it does in SQL. The column is named Expression.

Text joins with +: CompanyFinder.name() + " (" + CompanyFinder.category() + ")". As with SQL's ||, a missing (NULL) part makes the whole value missing.

Functions chain: the result of a function has the functions of its type, so CompanyFinder.name().upper().length() and TradeFinder.price().sqrt().floor() both work, and a number from a text function can be used in arithmetic.

An expression compares like a column, in a filter: CompanyFinder.name().upper().eq("ACME CORP"), TradeFinder.price() * 2 > 10, CompanyFinder.name().length() > CompanyFinder.category().length(). Use eq / ne or == / != and < <= > >=; the other side is a value or another expression.

A date can be cut back to the first day of its week (a Monday), month, quarter or year: PositionFinder.trade_date().first_day_of_month(). The result is a date, so it can be compared: PositionFinder.trade_date().first_day_of_month() == datetime.date(2024, 7, 1).

Sort by an aggregate the same way as by a column: .order_by(CompanyFinder.id_().count().descending()).

A condition can be a column too, true or false for each row: CompanyFinder.find_all(None, None, [CompanyFinder.name(), CompanyFinder.category().eq("Technology")]). As in SQL, a comparison with a missing (NULL) value is missing, not false. Two unnamed conditions have the same column name, so select them in separate queries (or give them names).

coalesce replaces a missing (NULL) value: EmployeeFinder.manager().name().coalesce("nobody"). Several defaults are tried in order.

distinct() returns each row once: CompanyFinder.find_all(None, None, [CompanyFinder.category()]).distinct(). A sort on a distinct query has to use a selected column.

offset(n) skips the first n rows and slice(start, stop) keeps rows start up to stop, counting from 0. Sort first: without an order, which rows come first is not defined.

when(condition).then(value).otherwise(value) chooses a value for each row, as in polars: when(CompanyFinder.category().eq("Technology")).then("tech").otherwise("other"). Python's own if cannot build a query (it needs a real True or False), so the condition goes through when. Add cases with .when(...).then(...). A value is a column, an expression or a Python value. With no otherwise the value is missing where no condition holds. A condition that is missing for a row (a comparison with NULL) is not true, so that row takes the next case or otherwise.

Temporal queries

For milestoned tables, supply the temporal arguments:

import datetime
from finance.trade.trade_finder import TradeFinder

# Trades as of now (processing_only milestoning)
result = TradeFinder.find_all(
    processing_valid_at=datetime.datetime.now(),
    display_columns=[TradeFinder.symbol(), TradeFinder.price()],
    filter_op=TradeFinder.symbol().eq("AAPL"),
)

# Positions on a specific business date as processed at a specific time
from finance.trade.contractualposition_finder import ContractualPositionFinder

result = ContractualPositionFinder.find_all(
    business_date=datetime.date(2023, 1, 15),
    processing_valid_at=datetime.datetime(2023, 1, 16),
    display_columns=[ContractualPositionFinder.quantity(), ContractualPositionFinder.npv()],
)

Navigating associations

Forward navigation — from Trade to its Account:

result = TradeFinder.find_all(
    processing_valid_at=datetime.datetime.now(),
    display_columns=[
        TradeFinder.symbol(),
        TradeFinder.account().name(),   # navigates the TradeAccount association
    ],
)

Reverse navigation — from Account to all its Trades (generated from Source Property ID: trades):

result = AccountFinder.find_all(
    display_columns=[
        AccountFinder.name(),
        AccountFinder.trades().symbol(),
        AccountFinder.trades().price(),
    ],
)

Result formats

result.to_pandas()   # pandas DataFrame
result.to_numpy()    # NumPy array

Milestoned views

A query can be defined as a SQL view with no business date or processing time. Use the same find_all as for any query, pass None for both dates, and call to_view. The view keeps the root table's milestoning columns, so consumers choose the date when they query it.

f = TradeFinder()
view = (f.find_all(None, None, [f.account().name(), f.symbol(), f.price()], f.is_settled().eq(True))
          .to_view("trade_enriched", "reporting"))

view.to_sql()                       # CREATE OR REPLACE VIEW reporting.trade_enriched AS ...
view.to_sql(dialect="databricks")   # transpiled with sqlglot
view.result().to_pandas()           # create the view on the registered backend and read its rows

Joins, the filter, group_by, aggregates and window functions all work as they do in find_all. Each column is named after its display name in snake_case (account_name, symbol, sum_price), and the root's milestoning columns (e.g. in_z, out_z) are added. Display names say how a column was reached, one relation per hop: f.account().branch().city() is "Account Branch City" in a query result and account_branch_city in a view. In a grouped view they are grouped by too, so each group is a set of rows with the same validity.

A view has no ORDER BY or row limit, so to_view rejects a query that has them. To order the rows you read back, use view.result().order_by("symbol").

Joined milestoned tables are pinned to the root row. Each is joined on the version that applied when the root row was recorded (child.in_z <= root.in_z AND child.out_z > root.in_z), and on the root's business date where there is one. Later changes to reference data never alter rows already in the view; a new version of the root row picks up the new reference data. A bitemporal root uses business_date_from as its business date. All joins are anchored to the root, including multi-hop ones.

to_view raises a ViewDefinitionError for:

• a query with a business date, a processing time, an ORDER BY or a row limit
• a joined table with a processing or business-date axis the root doesn't have
• projected columns that resolve to the same name, and view or schema names that aren't plain identifiers

Two limits to be aware of. Projecting across a to-many association (for example through a reverse accessor) multiplies the root's rows; that isn't checked yet. And a joined table is assumed never to have overlapping versions for one key at the same time.

Liquibase

datafinder_liquibase turns views into a Liquibase formatted-SQL changelog:

from datafinder_liquibase import write_changelog, to_liquibase_master_changelog

write_changelog([view], "db/changelog/views.sql", author="jackie", dialect="databricks")

Each view becomes one changeset with runOnChange:true, id <schema>.<view>, and a DROP VIEW IF EXISTS rollback, so changing a projection redeploys the view without a new id. to_liquibase_master_changelog(["views.sql"]) returns a YAML master that includes the changelog.

Project Layout

Development

This project uses uv. To run the tests:

uv run pytest

To add a dependency to a sub-project:

uv add --optional <group> <package>