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:
- Define a model — describe your domain as classes and associations in Markdown.
- Define a mapping — map each class property to a database table column in Markdown.
- 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 |
Join conditions
A join can have a condition besides its key columns, as Legend's Join Firm_Person(firm.ID = person.FIRMID and firm.FLAG is null and person.FLAG is not null). Add a Condition column to the Association table and write the condition for each row over the two
tables that row joins:
#### Association: FirmPerson
| Source Column | Target Table | Target Column | Condition |
|---------------|--------------|---------------|---------------------------------------------------|
| firm_id | firms | id | firms.flag IS NULL AND persons.flag IS NOT NULL |
A column is written table.column (or schema.table.column) and says which side of the join it is on by its table, so the condition
reads the same from either end: person.firm().legal_name() and firm.employees().first_name() use it alike. It is ANDed into
the ON of the left outer join, so a row it rejects leaves the joined columns NULL and drops no row. In a path of several rows, each
row has its own condition, on the hop it belongs to. A blank cell is a hop with only its keys.
A condition can use =, <>, !=, <, <=, >, >=, IS [NOT] NULL, AND, OR, NOT, parentheses and string and number
literals. A function (now()), anything else, and a column with no table are refused when the mapping is loaded.
When a table is joined to itself its name is both sides, so the side the join reaches is written {target}, as in Legend's
Join Person_Manager(personTable.MANAGERID = {target}.ID): the table's own name is the row the join starts from.
#### Association: EmployeeManager
| Source Column | Target Table | Target Column | Condition |
|---------------|--------------|---------------|------------------------------------------|
| manager_id | staff | id | staff.active = 1 AND {target}.active = 1 |
Followed from the other end, as employee.reports() is from employee.manager(), the sides trade places: {target} is then the
employee you started from. {target} can also be written in a join to another table, where it is that table.
SQL queries
A class can be mapped to a query as it is mapped to a table. Write the query as a Query section: its name and the class in the heading, its
SQL in a sql block right after it, and then the columns the query gives and what they map to, as a Table section has them:
#### Query: FIRM_QUERY → Firm
```sql
SELECT DISTINCT firm_id, firm_name
FROM people
```
| Column | Type | Key | Nullable | Property ID |
|-----------|---------|-----|----------|-------------|
| firm_id | INT | PK | N | id |
| firm_name | VARCHAR | | Y | legalName |
This is not a database view: nothing is created in the database. The query is a statement that is run, written into the SQL of a query over the class as
a derived table, FROM (SELECT DISTINCT ...) AS t0. It is read as PostgreSQL, the standard dialect here, and checked when the mapping is loaded: it is
read into the same query objects a query is made of (a TableAlias of the query, which has the columns the section lists), the tables and columns it reads
must exist, and the columns it gives must be the ones the section lists. A query can use DISTINCT, any join (INNER, LEFT, RIGHT, FULL, with an
ON), WHERE, GROUP BY with SUM, COUNT, MIN, MAX and AVG, HAVING, UNION [ALL], and named expressions ('Mr ' || first_name AS text,
CASE, UPPER, LOWER, TRIM, LENGTH, ABS). It can read another query that comes before it.
A query is milestoned as a table is, by the columns its SQL gives. Name the scheme in the heading, #### Query: RATE_QUERY → Rate (milestoning: business_date_range), and map the scheme's
columns to the businessFrom, businessThru, validFrom and validTo properties as a Table section does. A query over the class is then given the dates, find_all("2021-06-01", None, ...),
which are written over the query's columns, outside the query: FROM (SELECT ...) AS t0 WHERE t0.from_z <= '2021-06-01' AND .... A join to the query, such as holder.rate(business_date="2021-06-01"),
is given the date of its step in its ON, as a join to a milestoned table is. The query's own WHERE is its own: it decides which rows there are, and the milestoning chooses among them. A join, such as Firm.employees, can start from the
query's columns, so the query lists the key it joins on. ORDER BY and LIMIT are not part of a query a class is mapped to, and anything it cannot read is
refused with the part of the SQL it is.
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([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([employee.name(), employee.home().street(), employee.home().country().name()])
That property takes a date like any other: employee.home().country(business_date=as_of).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.
A class with a scheme has a find_all that takes the dates of its scheme. A class with no milestoning has a find_all that takes none, find_all(columns, filter): a query of it has no dates of its own. To read a milestoned class it reaches as of a date, give the date to the property that reaches it, as in branch.find_all([branch.city(), branch.accounts(processing_time=at).name()]). A date given to a property applies to that class and to the classes below it, unless one of them is given its own.
| 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.
A row that has not ended holds an infinity in its end column. A scheme has one for each axis, a processing and a business
infinite datetime, and a scheme with none has a NULL end, which a query accepts. The infinite_datetime column of the
scheme table gives the same infinity to both axes. A bitemporal table can end its two axes differently, a datetime on one
and a date on the other, or an infinity on one and NULL on the other, and its scheme then gives each its own, in
processing_infinite_datetime and business_infinite_datetime columns instead of infinite_datetime. A column left blank
is an axis with no infinity. An infinity is a datetime (9999-12-31 00:00:00) or a date with no time (9999-12-31),
whichever the end column holds; a business range, which has no processing axis, gives its date as
business_infinite_datetime.
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([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.
The time between two datetimes is an interval, as Python's datetime - datetime is a timedelta, and it is read, compared
and ordered as one:
length = TradeFinder.valid_to() - TradeFinder.valid_from() # an interval; a datetime minus a timedelta is a datetime
TradeFinder.find_all(None, now, [TradeFinder.symbol(), length.alias("length")], length > datetime.timedelta(days=30))
length.min() # the shortest interval, also .max()
An interval is not a number, so it has no sum or average; diff_seconds and diff_days give whole numbers of a unit.
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([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([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([CompanyFinder.name(), constant("EU").alias("Region")])
A constant has the functions of its type, as a column of that type has, so a value can be worked on in the query:
constant("Hello").upper().length(), constant(16).sqrt(), constant(datetime.date(2024, 1, 31)).add_months(1),
constant(datetime.datetime(2016, 8, 10, 8, 55)).hour(). Text has the string functions and pattern tests (and joins with
+), a number its numeric functions, a date or datetime its parts, the first day of a period, additions and differences.
A bool has none. A function of a constant is a column named for the function and the value, Length Hello, unless you
alias it.
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([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([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([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.
~ negates a condition, as in pandas and polars (Python's not cannot be overloaded): ~CompanyFinder.name().starts_with("Acme").
matches(pattern) is a regular expression searched anywhere in the text, as re.search:
CompanyFinder.name().matches("^(Acme|Beta) .*p$").
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([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([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.
Chaining steps
What find_all(...) returns is a relation: its columns have names (the alias you gave a column, else its display name in
snake_case), and the step after it reads them as it reads a table's. As in Legend, where filter, extend, select
and concatenate each take a relation and return one, each step is a query over the query before it. A column of
a result is result["name"], and a function of a row reads it as r.name, as Legend's $r.name.
trades = TradeFinder.find_all(None, now, [TradeFinder.account().name().alias("account"),
TradeFinder.symbol().alias("sym"), TradeFinder.price().alias("price")])
# the trade at the highest price of its account: a window function adds the highest price to each row,
# and a filter keeps the rows at it (a window cannot be tested in WHERE, so the filter is a step of its own)
with_top = trades.extend(top=lambda r: r.price.max().over(partition_by=[r.account]))
top = with_top.filter(lambda r: r.price == r.top).select("account", "sym", "price")
top.order_by(top["account"].ascending())
filter(lambda r: ...)keeps the rows for which every condition holds (several functions are all required);select("a", "b")keeps columns by name;extend(name=lambda r: ...)adds columns, including a window (.over(partition_by=[...]), or.over()for all the rows). To rename a column,extendit andselect.concatenate(other)isUNION(all=TrueforUNION ALL).join(other, lambda a, b: a.key == b.key, how="inner")puts the rows of two results together, as Legend'sjoinand as pandas, polars and ibis do. The condition is a function of a row of each, so it can be more than equality, and several comparisons are combined with&and|(each in brackets: Python'sandwould keep only one).howis required, as Legend's join kind is, and isinner,left,rightorfull: ANSI SQL's join types, the outer ones keeping the rows that find no match, with NULL for the other side's columns. The two sides' columns must be named differently (alias(), orextendthenselect), as Legend requires.exists(other, lambda o: o.key == r.key)is a condition for thefilterof a result: it holds whenotherhas a row meeting the condition, as Legend'sexistsand SQL'sEXISTS. The function is of a row ofother, and it reads the row being filtered from the function it is in:rows.filter(lambda r: exists(other, lambda o: (o.key == r.key) & (o.n != r.n))).~exists(...)isNOT EXISTS, andexistsconditions combine with&and|like any other. None ofother's columns are in the result.group_by(lambda r: r.dept).aggregate(headcount=lambda r: r.id.count(), avg=lambda r: r.salary.average())groups the rows and gives the key columns and an aggregate of each group, as Legend'sgroupBy. Several keys are a tuple (lambda r: (r.dept, r.title)), and a key that is worked out is named withalias.aggregate(...)alone is one row for the whole result, as Legend'saggregate. Each column is an aggregate of the rows (sum,count,average,min,max, or arithmetic of them such asr.price.sum() / 7.0), not a window.- A step can be ordered, limited, grouped and aggregated like any result, by its own columns:
step.order_by(step["price"].descending()).
A value compared with every row of a relation, as a scalar subquery would give, is a window over the rows: .over().
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 BYor 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>
Metadata
Release files for data-finder 0.1.21
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| data_finder-0.1.21.tar.gz | 279.9 kB | With provenance | Details for data_finder-0.1.21.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| data_finder-0.1.21-py3-none-any.whl | Python 3 | none | any | Details for data_finder-0.1.21-py3-none-any.whl |
Total release size: 592.8 kB
Release files / data_finder-0.1.21.tar.gz
| Download URL | data_finder-0.1.21.tar.gz |
|---|---|
| Size | 279.9 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
7bf057415318e6c5d63272e79f93c42536d1878213d4de78f99dac25b48a16ec
|
|
BLAKE2b-256 checksum How to use checksums |
da9e8b2a91015380a0cc30383a5d92136d036becf9a70fd54ed6557a12626d8b
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
twine/7.0.0 CPython/3.13.16
|
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Nov 12, 2026.
Transparency logRelease files / data_finder-0.1.21-py3-none-any.whl
| Download URL | data_finder-0.1.21-py3-none-any.whl |
|---|---|
| Size | 312.9 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
e653f62fc991ca9f9a9c613da11ef791d79306ca8fa096acb0f27e38e126c4ab
|
|
BLAKE2b-256 checksum How to use checksums |
c0ea74e4235f30e159005792b1007310621f325bfc2421d333b90eff20e5d8ca
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
twine/7.0.0 CPython/3.13.16
|
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Nov 12, 2026.
Transparency logSecurity for data-finder 0.1.21
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
Full provenance
Both files in this release have been uploaded with provenance metadata.
Attestation for data_finder-0.1.21.tar.gz
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Nov 12, 2026.
Transparency logA note on package security
PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.
To learn more about protecting yourself from malicious actors, visit our package security documentation.