Parity
SQL is the production result; pandas is the reference. The strict suite (tests/parity) runs a seeded two-equipment,
three-day SI dataset through PostgreSQL SQL (PGlite), SQLite and pandas and pins the match and known-divergence sets. A pass is
evidence for that dataset, not proof for every input. Run it with python -m pytest tests/parity -q.
| Rules | |
|---|---|
| SQL rules | 77 |
| With a pandas twin | 71 |
| Match on the synthetic suite | 50 |
| Known divergence | 21 |
| SQL only (no twin) | 6 |
Units. Parity is checked in SI. Imperial input is converted to SI at ingest, so SQL on imperial input equals SQL on SI input by
construction; to_imperial / from_imperial in the harness round-trip the dataset to 1e-9. There is no separate
per-rule imperial parity run.
Per rule
| Rule | pandas SI vs SQL SI | Note |
|---|---|---|
| AHU-DUCTHI | match | |
| AHU-SATDEV | known divergence | SQL and pandas gate ‘equipment on’ with different fan/pump proof order and thresholds |
| AHU-SIMUL | match | |
| AVG-ZONE-TEMP | SQL only | aggregate or utility analytic, no pandas function |
| CHW-1 | known divergence | SQL holds the fan-restart / mode-delay window over rolling rows, pandas GL36 suppresses after a mode change: differs by a few samples at fault edges |
| CHW-2 | match | |
| CHW-3 | match | |
| CHW-4 | match | |
| CHW-NOLOAD-1 | known divergence | pandas proves equipment/cooling from typed roles (mechanical_proof_mask, equipment_type); SQL ORs chiller status / valve |
| CMD-1 | match | |
| CW-APR-1 | match | |
| CW-FAN-1 | match | |
| CW-OPT-1 | match | |
| DMP-1 | match | |
| ECON-1 | match | |
| ECON-2 | match | |
| ECON-3 | known divergence | weather source: SQL prefers equipment web_oa_* then as-of weather rows; pandas resolves its own web columns |
| ECON-4 | known divergence | SQL holds the fan-restart / mode-delay window over rolling rows, pandas GL36 suppresses after a mode change: differs by a few samples at fault edges |
| ECON-5 | match | |
| ECON-6 | match | |
| ECON-7 | known divergence | weather source: SQL prefers equipment web_oa_* then as-of weather rows; pandas resolves its own web columns |
| FAN-RUNTIME-HOURS | SQL only | aggregate or utility analytic, no pandas function |
| FAULT-ELAPSED-HOURS | SQL only | aggregate or utility analytic, no pandas function |
| FC1 | match | |
| FC10 | match | |
| FC11 | match | |
| FC12 | match | |
| FC13-SAT-HIGH | known divergence | SQL and pandas gate ‘equipment on’ with different fan/pump proof order and thresholds |
| FC14 | known divergence | SQL and pandas gate ‘equipment on’ with different fan/pump proof order and thresholds |
| FC15 | match | |
| FC2 | match | |
| FC3 | match | match; differs only on edge data: Same cause as FC8: the series begins with the fan already running (edge DST-week start is 18:00 local); pandas applies its 600 s operational startup holdoff at the first sample, SQL does not (verified by trimming the first 6 h of rows). |
| FC4 | match | |
| FC5 | known divergence | SQL holds the fan-restart / mode-delay window over rolling rows, pandas GL36 suppresses after a mode change: differs by a few samples at fault edges |
| FC6 | known divergence | SQL and pandas gate ‘equipment on’ with different fan/pump proof order and thresholds |
| FC7 | known divergence | SQL and pandas gate ‘equipment on’ with different fan/pump proof order and thresholds |
| FC8 | match | match; differs only on edge data: Series starts with the fan already running (edge DST-week start is 18:00 local): pandas applies its 600 s operational startup holdoff at the first sample, SQL has none, so SQL confirms one 5-minute sample earlier. Verified: trimming the first 6 h of rows makes SQL equal pandas. |
| FC9 | match | match; differs only on edge data: Same cause as FC8: the series begins with the fan already running; pandas holds off 600 s from the first sample, SQL does not (verified by trimming the start). |
| FCU-CLG-COIL | match | |
| FCU-CO2-DAMPER | match | |
| FCU-DAMPER-POS | match | |
| FCU-DEADBAND | match | |
| FCU-HTG-COIL | known divergence | SQL and pandas gate ‘equipment on’ with different fan/pump proof order and thresholds |
| FCU-MODE-CYCLE | known divergence | SQL holds the fan-restart / mode-delay window over rolling rows, pandas GL36 suppresses after a mode change: differs by a few samples at fault edges |
| FCU-SENSOR-NULL | match | |
| FCU-VALVE-PASS-CLG | match | match; differs only on edge data: Planted out-of-range zone_t (80 degC): the pandas quality layer marks those samples OUT_OF_RANGE and excludes them, SQL computes zone_t - sat on the value as given and faults. |
| FCU-VALVE-PASS-HTG | match | |
| HP-1 | known divergence | SQL and pandas gate ‘equipment on’ with different fan/pump proof order and thresholds |
| MECH-OAT-1 | known divergence | pandas proves equipment/cooling from typed roles (mechanical_proof_mask, equipment_type); SQL ORs chiller status / valve |
| OA-1 | known divergence | SQL holds the fan-restart / mode-delay window over rolling rows, pandas GL36 suppresses after a mode change: differs by a few samples at fault edges |
| OAT-METEO | known divergence | weather source: SQL prefers equipment web_oa_* then as-of weather rows; pandas resolves its own web columns |
| PID-HUNT-1 | match | |
| RESET-1 | match | |
| SCHED-1 | match | |
| SCHED-247 | match | |
| SV-FLATLINE | match | |
| SV-RANGE | match | |
| SV-RATE | known divergence | documented in sql/fdd/KNOWN_DIFFERENCES.md: pandas uses profile/median/persistence logic, SQL two consecutive exceedances |
| SV-SPIKE | match | |
| SV-STALE | match | |
| TRIM-1 | known divergence | SQL and pandas gate ‘equipment on’ with different fan/pump proof order and thresholds |
| TRIM-3 | match | match; differs only on stress-only data: role names, threshold and missing input are aligned (#1588: contract roles hot-water- / chilled-water-reset-request-sum, fault at request sum <= request_lo, an absent request sum is not assessed on either side). Residual: pandas applies its hydronic proof gate with the 600 s startup holdoff, SQL has no startup holdoff, so SQL confirms one 5-minute sample earlier per pump start; matches on the 2 x 3 and 4 x 3 datasets, differs on the 8 x 7 run |
| TRIM-4 | match | match; differs only on stress-only data: role names, threshold and missing input are aligned (#1588: contract roles hot-water- / chilled-water-reset-request-sum, fault at request sum <= request_lo, an absent request sum is not assessed on either side). Residual: pandas applies its hydronic proof gate with the 600 s startup holdoff, SQL has no startup holdoff, so SQL confirms one 5-minute sample earlier per pump start; matches on the 2 x 3 and 4 x 3 datasets, differs on the 8 x 7 run |
| UTIL-INTERVAL | SQL only | aggregate or utility analytic, no pandas function |
| UTIL-MONTHLY | SQL only | aggregate or utility analytic, no pandas function |
| VAV-1 | match | match; differs only on edge data: Two input-handling differences: (1) NULL zone_t cells: SQL drops NULL rows (WHERE zone_t IS NOT NULL) and holds the previous sample’s state across the null run, pandas scores a NULL as not faulted; (2) planted out-of-range zone_t (80 degC): the pandas quality layer marks it OUT_OF_RANGE and excludes it, SQL scores the value as given. |
| VAV-2 | match | |
| VAV-3 | match | |
| VAV-4 | match | |
| VAV-5 | match | |
| VAV-6 | known divergence | SQL holds the fan-restart / mode-delay window over rolling rows, pandas GL36 suppresses after a mode change: differs by a few samples at fault edges |
| VAV-7 | match | match; differs only on edge data: Duplicate (equipment, timestamp) rows: SQL scores the rows as delivered, the reference de-duplicates first (the input contract is one row per timestamp). Verified: de-duplicating the rows before SQL makes SQL equal pandas. |
| VAV-AHU-LEAVE | known divergence | SQL holds the fan-restart / mode-delay window over rolling rows, pandas GL36 suppresses after a mode change: differs by a few samples at fault edges |
| VAV-REHEAT | match | match; differs only on stress-only data: pandas operational gate holds a fan-running rule for startup_delay_seconds=600 after the fan proves on, SQL has no startup holdoff: a fault already present at fan start is confirmed one 5-minute sample earlier in SQL per fan start (0.1667 h over the two fan starts of the 8-equipment 7-day run); with the gate off pandas equals SQL |
| VLV-1 | match | |
| WX-1 | match | match; differs only on edge data: Gap handling: SQL flags |oa_t - previous sample| above the fixed limit whatever the time between samples, pandas scales the limit by the sample gap (limit x dt/poll), so a real change across a >2 h gap is a spike in SQL only. |
| ZONE-COMFORT-PCT | SQL only | aggregate or utility analytic, no pandas function |
Pinned deltas
Each known difference carries its expected delta: the sum over all equipment of the absolute fault-hours difference between SQL and
pandas (a skipped pandas side counts as 0 h), with a tolerance, for the canonical seed (0) and dataset. tests/parity/stress.py fails
when an observed delta leaves its band, so a known difference cannot grow unnoticed; a rule that matches on a dataset is pinned at 0 h.
The deltas live in open_fdd/data/parity_known_differences.json (expected, keyed profile/seed/equips/days).
| Rule | Scope | 4 equip x 3 d | 8 equip x 7 d (CI stress size) |
|---|---|---|---|
| AHU-SATDEV | all | 4.5 h ± 0.01 | 5.6667 h ± 0.01 |
| CHW-1 | all | 0.3333 h ± 0.01 | 0.5 h ± 0.01 |
| CHW-NOLOAD-1 | all | 3.0833 h ± 0.01 | 5.1667 h ± 0.01 |
| ECON-3 | all | 11.5 h ± 0.01 | 24.5833 h ± 0.01 |
| ECON-4 | all | 1.0833 h ± 0.01 | 2.8333 h ± 0.01 |
| ECON-7 | all | 15.5833 h ± 0.01 | 30.6667 h ± 0.01 |
| FC13-SAT-HIGH | all | 10.0833 h ± 0.01 | 26.0833 h ± 0.01 |
| FC14 | all | 93.75 h ± 0.01 | 410.083 h ± 0.01 |
| FC3 | edge | - | - |
| FC5 | all | 1.8333 h ± 0.01 | 3.9167 h ± 0.01 |
| FC6 | all | 6.75 h ± 0.01 | 15.75 h ± 0.01 |
| FC7 | all | 3.9167 h ± 0.01 | 4.5833 h ± 0.01 |
| FC8 | edge | - | - |
| FC9 | edge | - | - |
| FCU-HTG-COIL | all | 4.6667 h ± 0.01 | 5.4167 h ± 0.01 |
| FCU-MODE-CYCLE | all | 1 h ± 0.01 | 1.75 h ± 0.01 |
| FCU-VALVE-PASS-CLG | edge | - | - |
| HP-1 | all | 6.1667 h ± 0.01 | 17.0833 h ± 0.01 |
| MECH-OAT-1 | all | 56.1667 h ± 0.01 | 208.667 h ± 0.01 |
| OA-1 | all | 0.1667 h ± 0.01 | 0.25 h ± 0.01 |
| OAT-METEO | all | 77 h ± 0.01 | 359.333 h ± 0.01 |
| SV-RATE | all | 108.917 h ± 0.01 | 468.333 h ± 0.01 |
| TRIM-1 | all | 51.25 h ± 0.01 | 293.417 h ± 0.01 |
| TRIM-3 | stress-only | 0 h ± 0.01 | 1.3333 h ± 0.01 |
| TRIM-4 | stress-only | 0 h ± 0.01 | 1.25 h ± 0.01 |
| VAV-1 | edge | - | - |
| VAV-6 | all | 0.3333 h ± 0.01 | 0.6667 h ± 0.01 |
| VAV-7 | edge | - | - |
| VAV-AHU-LEAVE | all | 0.4167 h ± 0.01 | 1.6667 h ± 0.01 |
| VAV-REHEAT | stress-only | 0 h ± 0.01 | 0.1667 h ± 0.01 |
| WX-1 | edge | - | - |
Known differences: KNOWN_DIFFERENCES.md. Wider SI stress run: python tests/parity/stress.py --equips 8 --days 7.
Change a rule
- SQL: edit
sql/fdd/postgres/rules/<rule>.sql, thensql/fdd/registry.yamlandsql/fdd/tuning_catalog.json. New roles go inrole_map.jsonandinput_contract.json. See Writing SQL rules. - pandas: add the function in
open_fdd/rules/and register it incookbook_catalog.py. Keep role names, SI units, defaults, confirm time and gating the same as SQL. - Add the fault, healthy, missing-input and boundary cases to the parity dataset.
- If the two intentionally differ, add the rule, a reason and the expected delta (
expected, fault hours and tolerance per canonical dataset) toopen_fdd/data/parity_known_differences.json. - Regenerate:
python scripts/nf/gen_rule_docs.py && python scripts/nf/gen_cookbook.py.
Use the parity harness to run the same proof on your own database.