# Query and Affordability Use Manual — Loader 0303

## Start with source facts

All parcel rows:

```sql
SELECT *
FROM cske.reporting_affordability_parcel_tax_base_v1
ORDER BY source_native_object_id;
```

All class history, including explicit missing rows:

```sql
SELECT *
FROM cske.reporting_affordability_tax_class_series_v1
WHERE business_reporting_entity_code='TOWN_OF_WILBRAHAM'
ORDER BY period_fiscal_year,business_property_class_code;
```

All official totals and shares:

```sql
SELECT *
FROM cske.reporting_affordability_tax_summary_series_v1
WHERE business_reporting_entity_code='TOWN_OF_WILBRAHAM'
ORDER BY period_fiscal_year;
```

Do not use `COALESCE(value,0)` unless a specific model rule has independently established that missing truly means zero. The FY2027 rows demonstrate why.

## Explain why property-tax amounts changed

```sql
SELECT *
FROM cske.reporting_affordability_tax_class_change_v1
WHERE business_reporting_entity_code='TOWN_OF_WILBRAHAM'
ORDER BY business_property_class_code,period_fiscal_year;
```

Interpret the fields separately:

- assessed-value change describes the base;
- tax-rate change describes the certified rate per $1,000;
- reported-levy change describes the resulting class levy; and
- calculated changes in the view are analysis, not source-reported explanations.

A correlation between base/rate/levy movement does not prove a policy cause. Budget decisions, exclusions, new growth, revaluation, classification, and timing require their own evidence.

## Standardize a levy or debt-service amount

The supplied view uses $1 million because it makes scenarios comparable:

```sql
SELECT
  source_native_parcel_id,
  business_property_class_code,
  value_total_assessed_amount,
  value_standardized_parcel_impact_per_one_million_levy,
  control_qualification
FROM cske.reporting_affordability_parcel_levy_sensitivity_v1
ORDER BY value_total_assessed_amount DESC;
```

For a stated scenario annual levy amount, multiply the standardized result:

```sql
WITH scenario AS (
  SELECT 3500000::numeric AS annual_levy_amount
)
SELECT
  p.source_native_parcel_id,
  p.value_total_assessed_amount,
  s.annual_levy_amount,
  p.value_standardized_parcel_impact_per_one_million_levy
    * s.annual_levy_amount / 1000000 AS calculated_scenario_parcel_impact
FROM cske.reporting_affordability_parcel_levy_sensitivity_v1 p
CROSS JOIN scenario s
ORDER BY calculated_scenario_parcel_impact DESC;
```

That calculation assumes one uniform allocation across candidate taxable real property. Before publishing it, state whether the scenario is inside the levy, debt-excluded, capital-excluded, temporary, recurring, level-debt, level-principal, or otherwise structured. Authorization is not issued debt, and issued principal is not annual debt service.

## Summarize distribution without identifying parcels

```sql
SELECT
  CASE
    WHEN value_total_assessed_amount < 250000 THEN 'UNDER_250K'
    WHEN value_total_assessed_amount < 400000 THEN '250K_TO_399999'
    WHEN value_total_assessed_amount < 600000 THEN '400K_TO_599999'
    WHEN value_total_assessed_amount < 1000000 THEN '600K_TO_999999'
    ELSE 'ONE_MILLION_PLUS'
  END AS assessed_value_band,
  count(*) AS parcel_count,
  sum(value_total_assessed_amount) AS assessed_value,
  sum(value_standardized_parcel_impact_per_one_million_levy) AS impact_per_one_million_levy
FROM cske.reporting_affordability_parcel_levy_sensitivity_v1
GROUP BY 1
ORDER BY min(value_total_assessed_amount);
```

This is the preferred public-reporting pattern when parcel-level identifiers are not needed.

## Use the normalized financial model

```sql
SELECT
  o.finrpt_observation_code,
  e.finrpt_reporting_entity_code,
  p.finrpt_reporting_period_fiscal_year,
  m.finrpt_ref_metric_code,
  dm.finrpt_ref_dimension_member_code AS property_tax_class_code,
  o.finrpt_observation_amount,
  o.finrpt_observation_unit_code,
  a.source_artifact_code,
  r.source_record_code
FROM cske.finrpt_observation o
JOIN cske.finrpt_reporting_entity e USING(finrpt_reporting_entity_id)
JOIN cske.finrpt_reporting_period p USING(finrpt_reporting_period_id)
JOIN cske.finrpt_ref_metric m USING(finrpt_ref_metric_id)
JOIN cske.source_artifact a USING(source_artifact_id)
LEFT JOIN cske.source_record r USING(source_record_id)
LEFT JOIN cske.finrpt_observation_dimension od USING(finrpt_observation_id)
LEFT JOIN cske.finrpt_ref_dimension_member dm USING(finrpt_ref_dimension_member_id)
WHERE o.finrpt_observation_source_module_code='AFFORDABILITY_0303_DLS'
ORDER BY p.finrpt_reporting_period_fiscal_year,m.finrpt_ref_metric_code,
         dm.finrpt_ref_dimension_member_code NULLS FIRST;
```

Only present DLS values enter `finrpt_observation`; explicit missing rows remain queryable in the affordability series tables/views.

## Verify the accepted-0301/current-DLS lineage

The DLS values are conformed serving facts, not unexplained new versions of already accepted observations. To see every present value with both provenance paths:

```sql
SELECT
  source_dor_observation_key,
  source_dor_upstream_file_name,
  source_dor_worksheet_name,
  source_dor_row_number,
  source_dor_column_number,
  source_dor_business_metric_label,
  source_dor_value_source_unit_code,
  source_dor_value_scale_code,
  source_dor_mapping_status_code,
  source_current_artifact_code,
  source_current_record_code,
  business_finrpt_observation_code,
  business_conformance_metric_code,
  period_fiscal_year,
  value_dor_0301_amount,
  value_current_dls_amount,
  value_difference_amount,
  value_unit_code,
  control_conformance_status_code
FROM cske.reporting_affordability_dor_0301_conformance_v1
ORDER BY period_fiscal_year,business_conformance_metric_code,
         source_dor_observation_key;
```

Expected result: 512 rows, every `value_difference_amount = 0`, every status `EXACT_MATCH_0301_TO_CURRENT_DLS`. This proves conformance; it is not independent corroboration because the observations are successive captures of the same official DLS series.

The accepted 0301 observations intentionally preserve source-distinct unit/mapping codes such as `SOURCE_UNIT_REVIEW_REQUIRED`; the 0303 serving metric supplies the conformed `USD`, `USD_PER_1000`, or `PERCENT` interpretation. Do not claim that 0301 originally published the 0303 conformed unit code. The view exposes both layers so that distinction remains visible.

The class and summary views also expose their accepted 0301 natural keys directly. Those keys remain populated even for FY2027 missing states, allowing a report builder to distinguish “source row exists but value is blank” from “no source observation exists.”

## Provenance drill-down

```sql
SELECT
  a.source_artifact_code,a.artifact_title,a.source_url,a.publication_date,a.access_date,
  a.local_file_name,a.file_sha256,r.source_record_code,r.source_native_identifier,
  r.source_locator,r.observed_as_of_date,r.record_sha256,r.raw_record_json
FROM cske.source_record r
JOIN cske.source_artifact a USING(source_artifact_id)
WHERE r.source_record_code='AFF0303.MASSGIS.OBJECTID.56047';
```

To see the exact-hash reuse and refreshed-export relationships:

```sql
SELECT
  al.source_artifact_alias_code,
  a.source_artifact_code AS canonical_source_artifact_code,
  al.source_artifact_alias_file_name,
  al.source_artifact_alias_sha256,
  al.source_artifact_alias_metadata_json
FROM cske.source_artifact_alias al
JOIN cske.source_artifact a USING(source_artifact_id)
WHERE al.source_artifact_alias_code='AFF0303_DLS_ASSESSED_VALUES_CURRENT_CAPTURE';

SELECT *
FROM cske.source_artifact_relationship_detail_v
WHERE source_artifact_relationship_code IN (
  'AFF0303_DLS_TAX_RATES_REFRESH_OF_0301',
  'AFF0303_DLS_TAX_LEVIES_REFRESH_OF_0301'
)
ORDER BY source_artifact_relationship_code;
```

## Complete restricted parcel/GIS history

The full owner, mailing, situs, assessor, deed-reference, building, other-legal, miscellaneous, lookup, and WKB geometry history is intentionally not projected into these core affordability views. Use the restricted `cske_gis.reporting_massgis_*_v1` views and the companion `FULL-PARCEL-GIS-HISTORY-DATA-USE-AND-QUERY-MANUAL.md` when that complete history is authorized and needed. Do not grant `cske_gis` to a public web/report role.

## One-command extracts

`Run-CSKE-Affordability-0303-All-Data-Query.command` embeds the `psql` connection and writes 20 real-data CSV files under a timestamped `output/` subdirectory. The extracts cover bounded parcel facts; DLS class, summary, and change history; standardized levy sensitivity; normalized observations; governed artifacts; accepted-0301 conformance; source-identity reuse/refresh links; all releases and field definitions; complete assessment/owner/address/building history; ownership-text changes; TaxPar WKB; other-legal and miscellaneous geometry; lookups; TaxPar-to-Assess source links; geometry comparisons; and an aggregate history summary. Restricted extracts must remain controlled and must not be put in GitHub or a public report package.

## Where this fits in “what can we afford?”

This loader supplies the tax-base history and the distribution mechanism. A sustainable-affordability decision still needs, at minimum:

- a recurring operating forecast that distinguishes budget, appropriation, expenditure, estimate, and actual;
- a multiyear capital and debt-service schedule with timing and financing assumptions;
- cash-flow and reserve timing;
- household burden and sensitivity evidence (0302 supplies a major part of this);
- service consequences and alternatives; and
- explicit recommendation conditions and uncertainty.

Legal levy or borrowing capacity is a constraint, not the affordability conclusion.