|
| 1 | +# Contributing an ML demo |
| 2 | + |
| 3 | +A demo here is a machine-learning model written as SQL over tables that |
| 4 | +xarray-sql registers from xarray Datasets. It is trained or differentiated |
| 5 | +with [ddx](https://github.com/xqlsystems/ddx), which differentiates SQL |
| 6 | +queries, and checked against the same model in |
| 7 | +[JAX](https://github.com/jax-ml/jax), Google's library for differentiable |
| 8 | +array programs. Each demo is also a benchmark: the same SQL runs on every |
| 9 | +engine in [`_hosts.py`](_hosts.py), and its times are what ddx, xarray-sql |
| 10 | +and [einfold](https://github.com/xqlsystems/einfold) (a query optimizer |
| 11 | +that rewrites SQL plans into array algorithms) work to bring down. The |
| 12 | +performance work is tracked in |
| 13 | +[#285](https://github.com/xqlsystems/xarray-sql/issues/285). |
| 14 | + |
| 15 | +Good demos are models whose SQL teaches something: a model people know |
| 16 | +from another library, written so a reader can follow it line by line. |
| 17 | +Some that would fit well: |
| 18 | + |
| 19 | +- linear or logistic regression on a gridded dataset, such as ERA5 |
| 20 | + temperatures; |
| 21 | +- a small convolutional network over a grid, where a convolution is a |
| 22 | + join on shifted coordinates; |
| 23 | +- an MLP on MNIST; |
| 24 | +- matrix factorization, or word2vec; |
| 25 | +- fitting a physical model's parameters to observations, such as a |
| 26 | + diffusion coefficient in the heat equation. |
| 27 | + |
| 28 | +## What a demo is made of |
| 29 | + |
| 30 | +Read [`01_attention.py`](01_attention.py) (short) and |
| 31 | +[`02_nanogpt.py`](02_nanogpt.py) (a whole training loop) first. A demo |
| 32 | +is one script, `NN_name.py`, numbered after the last one, with: |
| 33 | + |
| 34 | +1. **Inline script metadata**, copied from the other demos, so that |
| 35 | + `uv run benchmarks/ml/NN_name.py` runs it against this checkout. Add |
| 36 | + any dependency the demo needs beyond `xarray-sql[ml,duckdb]` and `jax`. |
| 37 | +2. **A module docstring** that says: |
| 38 | + - what the model is, and links its reference implementation or paper; |
| 39 | + - how each piece of the model maps to SQL (`02_nanogpt.py` has a |
| 40 | + table for this); |
| 41 | + - every way the demo differs from the reference, and why. |
| 42 | +3. **The model as SQL**, built by small Python functions that each return |
| 43 | + one piece of SQL, named after the pieces of the reference. In |
| 44 | + `02_nanogpt.py`, `block()` is `layer_norm()`, `attention()`, `mlp()` |
| 45 | + and `residual()`, as nanoGPT's `Block` is. Write the model as a view |
| 46 | + (`CREATE OR REPLACE VIEW … AS`), and the loss as a separate query |
| 47 | + that reads it, as a Python loss calls its model. |
| 48 | +4. **The same model in JAX**, from the same inputs and parameters, as the |
| 49 | + reference. Turn on 64-bit floats |
| 50 | + (`jax.config.update("jax_enable_x64", True)`) so the two agree to |
| 51 | + about 1e-15. |
| 52 | +5. **A `main()`** that: |
| 53 | + - parses the shared command line from `_bench.cli` (`--hosts`, |
| 54 | + `--sizes`, `--reps`, `--csv`), plus the demo's own options; |
| 55 | + - loads the tables on each host; |
| 56 | + - times each step with `Report.time`, or `Report.attempt` for a |
| 57 | + gradient that a host may refuse; |
| 58 | + - checks each result with `assert_close` and `Report.check`; |
| 59 | + - ends with `Report.finish`. |
| 60 | + |
| 61 | + A mismatch with JAX must fail the run, which `assert_close` does. |
| 62 | +6. **A line in `DEMOS`** in [`tests/test_ml_demos.py`](../../tests/test_ml_demos.py), |
| 63 | + with arguments that make the demo run in a few seconds. CI's `ml demos` |
| 64 | + job runs every demo there, and fails if one is missing. |
| 65 | +7. **A row in the table in [`README.md`](README.md)**, and anything it |
| 66 | + found under "Findings". |
| 67 | + |
| 68 | +## Writing the SQL |
| 69 | + |
| 70 | +- **Legibility over speed.** Write the model as its definition reads, |
| 71 | + even where a faster spelling exists. The slow parts are what the |
| 72 | + engines and optimizers need to make fast, and a demo that hides them |
| 73 | + measures nothing. |
| 74 | +- **Introduce every reference before relying on it**, in docstrings, |
| 75 | + comments and the README: say in a few words what a project, paper, |
| 76 | + algorithm or term is, and link it. This rule, and the others in |
| 77 | + [`AGENTS.md`](../../AGENTS.md), apply to demos too. |
| 78 | +- **Name issues only in TODOs.** Docstrings and comments name no issue |
| 79 | + numbers, except a workaround's `TODO(#NN): <action item>`, or |
| 80 | + `TODO(owner/repo#NN)` for another repository's issue. |
| 81 | +- **Make results deterministic** with `ORDER BY`, so they can be checked |
| 82 | + against JAX. |
| 83 | +- **Use no NaN as a value.** xarray-sql reads a NaN as SQL `NULL`, which |
| 84 | + aggregates skip. To check a mask, use an infinity instead, as |
| 85 | + `01_attention.py` does. |
| 86 | +- **Run the same SQL on every host.** If an engine needs different |
| 87 | + SQL, that difference is a finding: write it up rather than fork the |
| 88 | + query. |
| 89 | + |
| 90 | +## Running |
| 91 | + |
| 92 | +```shell |
| 93 | +uv run benchmarks/ml/NN_name.py --sizes 16 --hosts datafusion |
| 94 | +uv run pytest tests/test_ml_demos.py # every demo, small |
| 95 | +``` |
| 96 | + |
| 97 | +Against a ddx checkout rather than the release: |
| 98 | + |
| 99 | +1. Build ddxdb into this checkout's `.venv`, with `maturin develop |
| 100 | + --release` in ddx's `python/ddxdb`. |
| 101 | +2. Run the demos with `uv run --no-sync`, or the venv's `python`. A plain |
| 102 | + `uv run` or `uv sync` reinstalls the release. |
| 103 | + |
| 104 | +## When ddx or an engine can't run your model |
| 105 | + |
| 106 | +That's a useful result. Each of these is a finding, not a failure: |
| 107 | + |
| 108 | +- ddx raises a typed error (a `ddxdb.DdxError`) for anything it can't |
| 109 | + differentiate; |
| 110 | +- an engine may refuse a plan; |
| 111 | +- an engine may compute the wrong number. |
| 112 | + |
| 113 | +`Report.attempt` records a refusal in the results table as `refused:` |
| 114 | +with the error. Then: |
| 115 | + |
| 116 | +1. Cut the query down to a minimal repro. |
| 117 | +2. File it on [ddx](https://github.com/xqlsystems/ddx/issues), or on |
| 118 | + xarray-sql if it's about how a table is read. |
| 119 | +3. List the limit in the README's findings. |
| 120 | + |
| 121 | +A demo may land with a refused step. |
| 122 | + |
| 123 | +## Measuring |
| 124 | + |
| 125 | +The output ends with a table: one row per step, host and size, with the |
| 126 | +median time, the error against JAX, and the number of operators in the |
| 127 | +host's plan. To compare runs, pass `--reps 3 --csv results.csv`, and post |
| 128 | +the table on [#285](https://github.com/xqlsystems/xarray-sql/issues/285), |
| 129 | +with your CPU and the versions of DataFusion, DuckDB and ddxdb. |
0 commit comments