Metadata-Version: 2.4
Name: data-finder
Version: 0.1.19
Summary: Model driven data finders
License: Apache-2.0
Classifier: License :: OSI Approved :: Apache Software License
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: jinja2>=3.1.6
Requires-Dist: numpy>=2.0.0
Requires-Dist: pandas>=2.2.2
Requires-Dist: sqlglot>=30.24.0
Provides-Extra: test
Requires-Dist: databricks-sql-connector>=3.0.0; extra == "test"
Requires-Dist: duckdb>=1.0.0; extra == "test"
Requires-Dist: ibis-framework~=12.0.0; extra == "test"
Requires-Dist: linkml-runtime>=1.9.5; extra == "test"
Requires-Dist: markdown-it-py>=4.0.0; extra == "test"
Requires-Dist: pyarrow>=24.0.0; extra == "test"
Requires-Dist: pyarrow-hotfix>=0.7; extra == "test"
Requires-Dist: pyiceberg>=0.11.0; extra == "test"
Requires-Dist: pyright>=1.1.410; extra == "test"
Requires-Dist: pytest>=9.0.0; extra == "test"
Requires-Dist: pytest-cov>=7.1.0; extra == "test"
Requires-Dist: sqlalchemy>=2.0.0; extra == "test"
Provides-Extra: ibis
Requires-Dist: ibis-framework~=12.0.0; extra == "ibis"
Requires-Dist: pyarrow>=24.0.0; extra == "ibis"
Requires-Dist: pyarrow-hotfix>=0.7; extra == "ibis"
Provides-Extra: duckdb
Requires-Dist: duckdb>=1.0.0; extra == "duckdb"
Provides-Extra: databricks
Requires-Dist: databricks-sql-connector>=3.0.0; extra == "databricks"
Provides-Extra: linkml
Requires-Dist: linkml-runtime>=1.9.5; extra == "linkml"
Provides-Extra: iceberg
Requires-Dist: pyiceberg>=0.11.0; extra == "iceberg"
Provides-Extra: iceberg-test
Requires-Dist: sqlalchemy>=2.0.0; extra == "iceberg-test"
Provides-Extra: markdown
Requires-Dist: markdown-it-py>=4.0.0; extra == "markdown"
Dynamic: license-file

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

```python
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.

```markdown
### 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.

```markdown
## 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:

```markdown
### 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.

```markdown
### 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:

```markdown
| 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

```markdown
# 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 |
```

```python
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          |
```

```python
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:

```markdown
#### 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.

```markdown
| 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:

```python
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`:

```python
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:

```bash
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:

```bash
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):

```json
{ "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

```python
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`:

```python
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:

```python
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`:

```python
# 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:

```python
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:

```python
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:

```python
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.

```python
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:

```python
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:

```python
# 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:

```python
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`):

```python
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.

```python
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

```python
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.

```python
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:

```python
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:

```bash
uv run pytest
```

To add a dependency to a sub-project:

```bash
uv add --optional <group> <package>
```
