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_time=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:

Column	Meaning
Property	Human-readable label used in generated docstrings
Id	Machine identifier used in code and mappings
Type	A primitive type or another class name (for navigations defined via Associations)
Multiplicity	Required. * or a bare number (1, 2) stand alone; anything else is lower..upper, a number then a number or * (0..1, 1..*, 2..5). There is no default: a blank or invalid cell is an error
Key	Y marks the business key
Description	Optional documentation

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 |

Column	Meaning
Source	Name of the source class
Source Property Name	Display name of the navigation property added to the source class (Trade.account). Required
Source Property ID	camelCase id of that property (account). Required
Source Multiplicity	Same forms as a property's Multiplicity: how many targets the source property returns
Target	Name of the target class
Target Property Name	Display name of the navigation property added to the target class (Account.trades). Optional
Target Property ID	camelCase id of that property (trades). Optional, and given together with the name
Target Multiplicity	Same forms as a property's Multiplicity: how many sources the target property returns, or would return if there were one. Required

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.

Column	Meaning
Source Column	The FK column in the source table
Target Table	The table the FK references
Target Column	The primary key column in the target table

Joining through other tables

An Association table with more than one row is a path through several tables. Each row after the first starts from a column of the table the row before it reached, and the last row ends at the table of the target class. The tables on the way need no class: declare them with #### Table: <name> and no arrow. Give such a table (milestoning: <scheme>) if its rows are milestoned, and the dates of the query apply to it like any other table on the path.

#### Association: EmployeeCitizenship

| Source Column | Target Table | Target Column |
|---------------|--------------|---------------|
| citizen_ref   | country_refs | ref           |
| code          | countries    | code          |

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.

A property of an embedded object can itself reach another table. Give the column the join starts from the dotted Property ID of that property, with FK as its Key, such as home.country. Then say which tables it joins to in an Association block headed with where the object is embedded, (embedded in <Class>.<path>). The block can have several rows, as above.

| home_country | INT | FK | Y | home.country |

#### Association: AddressCountry (embedded in Employee.home)

| Source Column | Target Table | Target Column |
|---------------|--------------|---------------|
| home_country  | country_refs | ref           |
| code          | countries    | code          |

employee.find_all(None, None, [employee.name(), employee.home().street(), employee.home().country().name()])

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.

Scheme	Required columns	find_all signature
processing_only	processing_start, processing_end	processing_time
business_date	business_date	business_date, processing_time
business_date_processing	processing_start, processing_end, business_date	business_date, processing_time
bitemporal	processing_start, processing_end, business_date_from, business_date_to	business_date, processing_time
business_date_range	business_date_from, business_date_to	business_date, processing_time
processing_snapshot	processing_datetime	processing_time

Tag a table with the scheme name in parentheses:

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

business_date_range is bitemporal without the processing columns: a row applies from business_date_from up to, but not including, business_date_to, and a NULL end (or infinite_datetime) is open-ended. Any processing time passed to it is ignored, though one still reaches tables it joins to that have processing columns.

processing_snapshot is a single processing_datetime column holding the processing time each row was taken at, as a TIMESTAMP. The query's processing time must equal it, and is compared whole: it is never cut back to its date, so find_all(None, "2024-01-01", …) asks for midnight. A time with no row returns nothing. It is a scheme on its own, so a processing_datetime cannot be declared beside processing_start, processing_end or a business date column.

| Scheme              | processing_start | processing_end | processing_datetime | business_date |
|---------------------|------------------|----------------|---------------------|---------------|
| processing_snapshot |                  |                | taken_at            |               |

The rows taken over a span of times are read with find_for_processing_range, which a table with a processing snapshot has in place of find_for_date_range. Both ends are included. The business date comes last, and is None if there is no business axis:

QuoteFinder.find_for_processing_range(
    datetime.datetime(2024, 1, 1, 9), datetime.datetime(2024, 1, 2, 9), None,
    [QuoteFinder.symbol(), QuoteFinder.price()],
)

A table joined from a query with a processing range is read over the same range: a snapshot column must be within it, and a table with processing_start and processing_end must overlap it. A navigation given its own processing_time reads that one time instead.

---

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)).

A comparison is with a value, or with another column or expression. ne and != are the inequality:

OrderFinder.find_all(None, None, [OrderFinder.id_()], OrderFinder.commit_date() < OrderFinder.receipt_date())
TradeFinder.price() > TradeFinder.cost() * 1.075
TradeFinder.symbol().ne("AAPL")      # or TradeFinder.symbol() != "AAPL"

A date difference takes a column too: OrderFinder.ship_date().diff_days(OrderFinder.receipt_date()) is the number of days from the ship date to the receipt date.

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_time=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_time=datetime.datetime(2023, 1, 16),
    display_columns=[ContractualPositionFinder.quantity(), ContractualPositionFinder.npv()],
)

A date on a navigation

A query's dates reach every milestoned table it joins to. A navigation can be given its own with business_date and processing_time, which it and the navigations below it then use instead:

# the trades as of one time, each with the account as it was at another
TradeFinder.find_all(
    None, datetime.datetime(2021, 6, 1),
    [TradeFinder.symbol(), TradeFinder.account(processing_time=datetime.datetime(2023, 12, 1)).name()],
)

A navigation with no dates of its own uses those of the one above it, and at the top the query's, and each axis is taken separately. A business date given to a navigation is a point in time, so it ends a business range the query asked for with find_for_date_range. The two names are reserved, so they cannot be property filters on the same call. A navigation with a date does not change the finder: account() still returns the account with the query's dates. A view has no dates, so it refuses a navigation given one.

Navigating associations

Forward navigation — from Trade to its Account:

result = TradeFinder.find_all(
    processing_time=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(),
    ],
)

Reading a value through a to-many navigation gives a row for each of its rows. To ask only whether there is one, use exists() or not_exists(): an account with several trades is still one row, and counts once.

AccountFinder.find_all(None, None, [AccountFinder.name()], AccountFinder.trades().exists())
AccountFinder.find_all(None, None, [AccountFinder.name()], AccountFinder.trades().not_exists())

If the query also reads a value through the same navigation, as in AccountFinder.trades().symbol(), the rows are one for each trade, and exists() then holds for a trade that is in the result.

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

Directory	Contents
model/	Core metamodel (Class, Property, Association, Package)
model_markdown/	Load and save model definitions from Markdown
mapping_markdown/	Load and save mapping definitions from Markdown
datafinder/	Query engine — FinderResult, filter operations, typed attributes
datafinder_generator/	Jinja2-based code generator that produces Finder classes
datafinder_ibis/	Ibis backend for query execution
datafinder_ibis_duckdb/	DuckDB integration via Ibis
datafinder_liquibase/	Liquibase changelog output for milestoned views
datafinder_duckdb/	Schema reader — introspects a live DuckDB database
example/	End-to-end example with finance model, mapping, and queries

---

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>