A query must return the same rows every time it is run.
Deterministic SQL is the discipline of writing corpus queries that produce identical results when run against the same sealed corpus — today, in six months, or by an opposing expert. Non-deterministic patterns are not a style choice: they are an evidentiary failure.
LLX-ACADEMY-CRS-02 · R1
Prompt craft is the recommended prerequisite.
Deterministic SQL operates at a lower level than prompt craft — it addresses the index schemas directly rather than through the ASL decomposition. Practitioners should complete prompt craft first to understand the retrieval primitive model before writing raw SQL.
Prompt craft (recommended)
Understanding retrieval primitives and the ASL decomposition makes deterministic SQL easier to learn — the SQL expresses what the ASL infers automatically.
Basic SQL literacy
The course assumes the practitioner can write a SELECT statement and understands WHERE clauses, JOINs and ORDER BY. SQL expertise is not required — deterministic discipline is what is taught here.
Non-reproducible SQL is not evidence.
Any SQL pattern that can produce different results on different runs — functions that depend on execution time, random ordering, unstable aggregates — produces a finding that cannot be replicated. This session identifies every such pattern and explains why each one disqualifies the result.
Time-dependent functions
NOW(), CURRENT_DATE, CURRENT_TIMESTAMP — any function whose value changes between runs. How to replace them with sealed ingestion timestamps from the index. Practicum: rewrite a time-dependent query to use the XATR timestamp field instead.
Unstable ordering
ORDER BY without a deterministic tiebreaker produces different row sequences on repeated runs. The required pattern: always include the ingestion timestamp and byte-range as secondary sort keys.
Non-deterministic aggregates
Aggregate functions that depend on input order (STRING_AGG without ORDER BY, FIRST without ordering) produce different results across runs. The correct pattern for each.
The determinism test
How to test whether a query is deterministic: run it twice against the same sealed corpus, compare the full result sets including row order. If they differ, the query fails. Practicum: apply the test to a set of provided queries and identify which fail.
Each index has a schema. Know it before you query it.
The five PARALLAX RC® indexes — Passage, Metadata, Spatial, XATR, KUNZU — each have a defined schema. Querying across schemas requires explicit JOINs on the sealed passage identifier. This session teaches each schema and the correct join pattern.
Passage index schema
Fields: passage_id, file_hash, byte_start, byte_end, page, text, sha512. The passage_id is the join key to all other indexes. Every result set must return passage_id and sha512 for citation compliance.
Metadata index schema
Fields: passage_id, attribute_name, attribute_value, attribute_type. A document with ten metadata attributes has ten rows in the metadata index. Pivot patterns and how to avoid producing non-deterministic output when pivoting.
XATR index schema
Fields: passage_id, date_type, date_value, confidence. Multiple date types per passage. How to query the XATR index for a specific date type without accidentally including conflicting dates.
KUNZU graph schema
Nodes: entity_id, entity_type, entity_text. Edges: source_entity_id, relation_type, target_entity_id, passage_id. Graph traversal patterns and how to ensure the traversal is deterministic.
XATR and KUNZU require their own query patterns.
Temporal queries against the XATR index and relation queries against the KUNZU graph require patterns that are not standard SQL. This session teaches the correct pattern for each class of forensic query.
Temporal range queries
Querying XATR for documents dated within a range, by a specific date type. The pitfall: a document with three conflicting dates appears three times. How to select the authoritative date type for the question.
Supersession queries
Using the XATR supersession chain to find all revisions of a drawing or document, ordered by issue date. How to identify the current revision and the revision that was current at a given date.
Entity resolution queries
Finding all passages in which a named entity appears, across all spellings and aliases recorded in the KUNZU index. The alias join pattern and how to avoid missing a variant.
Relation traversal queries
Following an edge in the KUNZU graph to find all obligations issued by a party, all responses to a notice, all documents that reference a drawing. The bounded traversal pattern that prevents infinite loops on cyclic graphs.
Submit a query. The examiner runs it twice.
The deterministic SQL assessment provides a set of forensic questions and a sealed examination corpus. The practitioner writes a SQL query for each question. The examiner runs each query twice and verifies that the full result sets — including row order — are identical.
Query submission
The practitioner submits a SQL query for each forensic question. The query must include the passage_id and sha512 fields in every result set.
Determinism verification
The examiner runs each query twice on the same sealed corpus. Both runs must return identical full result sets. A query that fails the determinism test is a failing submission regardless of whether the result is otherwise correct.
Citation tracing
For one result from each query, the examiner locates the cited passage in the source file using only the passage_id, file_hash and byte range from the result. The citation must be accurate.
Passing deterministic SQL contributes to Certified analyst.
Deterministic SQL is a required module for the Certified analyst examination. It combines with custody and verification and method work to complete the analyst certification. Passing this module alone unlocks access to the PARALLAX RC® query interface for supervised practicum work.