Refactor: parameterize crime rate pipeline for annual data updates

Problem Statement

Each annual SES Index refresh requires touching the crime rate pipeline by hand to update year-sensitive values. Right now those values are scattered as literals throughout the single procedural script, and some of them already disagree with the central config. Concretely, the developer's pain points are:

• The GCS table snapshot is hardcoded inline (e.g. the June 2026 snapshot), even though config.yml already reserves a key for it that points at an older snapshot. The script ignores the config key, so the two values have drifted apart and there is no single source of truth for which snapshot a run uses.
• The cansim cache directory is repeated as a literal in several places, despite a config key already existing for it.
• The crime data window start year (REF_DATE >= "2000") is a bare literal with no named meaning.
• The data dictionary's human-readable description hardcodes a year range ("from 2000 to 2023") that silently goes stale every refresh and has to be remembered and edited by hand.

The net effect: a refresh is error-prone, the "what changed this year?" story is hidden in literals, and the script and config contradict each other on the GCS snapshot.

Solution

Make the crime rate pipeline parameterizable for annual updates using a hybrid approach:

• Keep project/infrastructure constants (GCS table name, cache path) in config.yml as the single source of truth, and have the script read them from config rather than from literals.
• Surface the genuine annual run parameter (the crime data window start year) as a single, clearly named top-of-script variable, following the same convention already established by the geo-suppression script's output_year parameter.
• Replace the hardcoded end year in the data dictionary with a value derived from the data itself, so the metadata stays correct automatically.
• Lock in a regression baseline (a golden snapshot of the known-good output) and a comparison test so we can prove the parameterization is behavior-preserving.

The procedural structure of the script is intentionally left intact; this refactor is about parameterization, not about decomposing the pipeline into functions.

Commits

Commit 1 - Capture the regression baseline (golden snapshot).
Before changing any behavior, copy the current known-good data output (the DA-level crime rate CSV written to the local output folder) into a snapshot location under the test directory as the golden reference for 2023. Commit only this snapshot artifact. No production code changes. This gives every later commit something deterministic to compare against.

Commit 2 - Update the GCS snapshot value in config.
Change the existing config entry for the GCS table so its value reflects the snapshot the pipeline is currently expected to use (the June 2026 snapshot), resolving the drift between config and the inline literal. The script still uses its inline literal at this point, so runtime behavior is unchanged; this commit only corrects the config source of truth.

Commit 3 - Read the GCS table name from config.
Replace the inline hardcoded GCS table name in the database identifier with a lookup of the config value, and load the full config object once at the top of the script (reusing it for the lan_path that is already read). Keep the database schema as a literal for now. Run the pipeline and confirm the output is byte-identical to the golden snapshot, proving this wiring change is behavior-preserving.

Commit 4 - Read the cansim cache path from config.
Introduce a single local variable for the cansim cache path that is read from its existing config key, and replace each inline cache-path literal (the environment variable set, the directory-existence check, and the directory creation) with that variable. Run the pipeline and confirm the output still matches the golden snapshot.

Commit 5 - Add the top-of-script parameter block and parameterize the data window.
Introduce a clearly named annual parameter for the crime data window start year (currently 2000), with an inline comment noting it as the refresh parameter, mirroring the established convention in the geo-suppression script. Replace the bare literal in the REF_DATE filter with the parameter, taking care that the comparison stays type-safe (the existing filter compares as a string against four-digit years, so preserve that semantics rather than silently introducing a numeric coercion). Run the pipeline and confirm the output still matches the golden snapshot.

Commit 6 - Derive the dictionary end year dynamically.
Replace the hardcoded year range in the data dictionary description so that the start year comes from the new parameter and the end year is computed from the maximum reference year actually present in the pulled data. This removes the last stale-able literal. Run the pipeline and confirm the DA-level data output still matches the golden snapshot; note that the dictionary's descriptive text is expected to change and is not part of the byte-identical guarantee.

Commit 7 - Add the snapshot regression test.
Add a test script under the test directory that re-runs the crime rate pipeline and compares the freshly produced DA-level data output against the golden snapshot: assert equal row count, identical column set and order, and value-level equality (a digest or direct frame comparison). Document at the top of the test that it requires live database access (cansim and the SQL Server) and is intended to be run locally as a refactor-fidelity check, not as CI, and that because the upstream data refreshes annually the comparison is most meaningful when run in the same data window as the golden snapshot.

Decision Document

• Parameter location (hybrid): Project and infrastructure constants stay in the YAML config as the single source of truth; the genuine per-refresh parameter (the data window start year) lives as a named top-of-script variable. This matches the existing in-repo convention for per-run years while keeping infrastructure out of the script.
• GCS table name: Read from the existing config key. The config value is corrected to the currently-used snapshot so config and script agree. No new config key is introduced for this.
• Database schema: Left as a literal in this refactor (it is not year-dependent and is out of the stated scope).
• cansim cache path: Read from its existing config key rather than introducing a new one, accepting the key's current (mis-spelled) name to avoid a breaking rename; a spelling fix is tracked separately as out of scope.
• Data window start year: Treated as the primary annual parameter and surfaced prominently with a comment.
• Dictionary end year: Derived from the data at run time rather than stored, so it never silently goes stale.
• Project edition folder: The shared project folder name that appears in LAN paths is treated as a fixed edition name and left as a literal, per the explicit scope decision.
• Script structure: The procedural, single-script shape is preserved. No functions or modules are introduced.

Testing Decisions

• What makes a good test here: The refactor must be behavior-preserving, so the test should assert that the external observable output (the DA-level crime rate data file) is unchanged by parameterization. It should test external behavior (the produced file), not implementation details (which variable holds which value).
• Approach: Golden-file / snapshot comparison against the known-good 2023 output. A baseline snapshot is committed before any code changes, and a comparison test reproduces the output and checks row count, column set/order, and values.
• Prior art: The repository's existing test directory contains only a standalone reproduction script for an unrelated issue and no pipeline tests, so this snapshot test establishes the first regression coverage for the crime rate pipeline.
• Constraints acknowledged: The pipeline depends on live external systems (cansim and the SQL Server database), so the test is a local, manually-run fidelity check rather than CI, and is most meaningful when run against the same data window as the snapshot. Testing the transformation in isolation would require extracting it into a pure function, which is deliberately out of scope for this parameterization-only refactor.

Out of Scope

• Decomposing the crime rate script into functions or modules.
• Parameterizing the project edition folder name used in LAN paths.
• Renaming or otherwise fixing the misspelled cansim cache config key.
• Parameterizing the database schema name.
• Applying the same parameterization pattern to the other numbered pipeline scripts (this plan covers the crime rate pipeline only).
• Changing the cansim table identifier (it is a stable Statistics Canada table ID, not year-dependent).

Further Notes

• Because the script currently pulls cansim data with refresh enabled, the golden snapshot should be captured and the regression test run in the same data window to avoid spurious diffs from upstream data revisions. The test's primary purpose is to prove the parameterization is behavior-preserving, not to guard against upstream data changes.
• A natural future follow-up (explicitly not part of this plan) would be to extract the pure join-and-weighted-average transformation into a function so that the regression test can run deterministically against committed input fixtures without live database access.

Start with the crime rate pipeline script, config.yml, the geo-suppression script's output_year convention, and the existing test directory. Capture the current DA-level crime rate CSV as the 2023 golden snapshot, then run the pipeline while wiring the existing config values and start-year parameter into the described steps. Done means the output matches the snapshot by rows, columns, order, and values, while the dictionary description derives its year range from the run data.