Analysis of Distinct Values vs Full Schema SQL Conditional Checks

December 10, 2025 · View on GitHub

📍 IMPORTANT: This analysis has been integrated into the main schema maintenance guide. For operational procedures, data quality checklists, and verification queries, see:
Data Quality Analysis Section in README-SCHEMA-MAINTENANCE.md

This document provides detailed technical findings. For day-to-day maintenance, use the consolidated guide.

Date: 2025-12-03
Author: Intelligence Operative
Purpose: Identify discrepancies between actual data values in distinct_values CSVs and conditional checks in full_schema.sql views

Schema Maintenance Compliance

This analysis follows the schema maintenance procedures documented in README-SCHEMA-MAINTENANCE.md:

  • ✅ Analyzed 91 distinct value CSV files extracted from database
  • ✅ Compared conditional checks in full_schema.sql against actual data values
  • ✅ Created Liquibase changelog (db-changelog-1.44.xml) for identified fix
  • ✅ Integrated findings into main maintenance guide (2025-12-10)
  • ✅ Added data quality warnings to affected views in DATABASE_VIEW_INTELLIGENCE_CATALOG.md

Executive Summary

After comprehensive analysis of 91 distinct value CSV files against the conditional checks in full_schema.sql, the following issues were identified:

Critical Issues Found

  1. Missing 'Förste vice talman' in talmansuppdrag role scoring

    • Location: Line 9693 in view_riksdagen_politician_experience_summary
    • Current: Only 'Andre vice talman' and 'Tredje vice talman' are scored
    • Missing: 'Förste vice talman' (8 occurrences in database)
    • Impact: Förste vice talman roles are not properly weighted in experience calculations

  2. Decision Chamber Values - Partial Pattern Matching

    • Location: Lines 6291-6294, 6355-6360 in decision views
    • Issue: =utskottet (6501 rows), = utskottet (517), utskottet (12), =utskott (19) are not matched by any pattern
    • Impact: 7,049 decision records fall into "other_decisions" category instead of proper classification
    • Suggested Fix: Add '%UTSKOTT%' pattern for committee referrals

Data Quality Observations

  1. Assignment Type Values - Properly Handled

    • All 7 assignment types from database are properly handled in views:
      • uppdrag (19,034), kammaruppdrag (7,171), Riksdagsorgan (4,229)
      • Departement (500), partiuppdrag (299), Europaparlamentet (85), talmansuppdrag (33)

  2. Role Code Values - Mostly Handled via Patterns

    • '%minister%' pattern matches all minister roles (20+ different minister titles)
    • '%ordförande%' and '%vice ordförande%' patterns properly match variations
    • '%suppleant%' and '%ersĂ€ttare%' patterns match substitute roles

  3. Vote Values - Properly Handled

    • All 4 vote values properly matched: JA, NEJ, AVSTÅR, FRÅNVARANDE

  4. Document Type Values - Properly Handled

    • 'mot', 'bet', 'prop' patterns are properly matched with case handling

  5. Party Short Codes - Properly Handled

    • All major party codes are supported in the party-related views

Detailed Analysis

1. Role Code Analysis

Values in Views vs Database:

Role Code in ViewsDatabase CountStatus
Ordförande435✓ Matched
Vice ordförande408✓ Matched
Ledamot6,056✓ Matched
Suppleant14,757✓ Matched
ErsĂ€ttare933✓ Matched
Riksdagsledamot6,052✓ Matched
Deputerad920✓ Matched
StatsrĂ„d259✓ Matched
StatsrĂ„dsersĂ€ttare295✓ Matched
Gruppledare71✓ Matched
Partiledare23✓ Matched
Partisekreterare28✓ Matched
Talman12✓ Matched
Förste vice talman8⚠ NOT in talmansuppdrag scoring
Andre vice talman6✓ Matched
Tredje vice talman7✓ Matched
Statsminister10✓ Matched
Vice statsminister1✓ Matched

Issue Identified: view_riksdagen_politician_experience_summary line 9693 checks for:

WHEN ((a.assignment_type)::text = 'talmansuppdrag'::text) 
  AND ((a.role_code)::text = ANY (ARRAY[
    'Andre vice talman',
    'Tredje vice talman'
  ])) THEN 750.0

Should be:

WHEN ((a.assignment_type)::text = 'talmansuppdrag'::text) 
  AND ((a.role_code)::text = ANY (ARRAY[
    'Förste vice talman',
    'Andre vice talman',
    'Tredje vice talman'
  ])) THEN 750.0

2. Decision Chamber Pattern Analysis

Current Pattern Matching:

PatternTypeMatched Values
%BIFALL%ApprovalBifall, BifallDelvis, Bifall delvis, =delvis bifall
%GODKÄNT%Approval(none found in data)
%BIFALLA%Approval(none found in data)
%AVSLAG%RejectionAvslag, avslag
%AVSLÅ%Rejection(none found in data)
%ÅTERFÖRVISNING%Referral=Återförvisning till utskottet
%ÅTERFÖRVISA%ReferralĂ„terförvisning

Unmatched Values (falling into 'other_decisions'):

ValueCountSuggested Classification
=utskottet6,501Committee referral
= utskottet517Committee referral
utskottet12Committee referral
=utskott19Committee referral
ubtskottet1Committee referral (typo)
=utkottet1Committee referral (typo)
Lagd till handlingarna2Filed/archived
-1Unknown
(empty)5,444Unclassified

Recommendation: Add '%UTSKOTT%' pattern to properly classify committee referrals.

3. Ministry Org Codes

All ministry org codes in views are valid:

  • SB (StatsrĂ„dsberedningen)
  • Fi (Finansdepartementet)
  • UD (Utrikesdepartementet)
  • Ju (Justitiedepartementet)
  • N (NĂ€ringsdepartementet)
  • M (Miljödepartementet)
  • A (Arbetsmarknadsdepartementet)
  • I (Infrastrukturdepartementet)
  • LI (Landsbygdsdepartementet)
  • Jo (Jordbruksdepartementet)
  • S (Socialdepartementet)
  • U (Utbildningsdepartementet)
  • FÖ (Försvarsdepartementet)
  • IJ (Integrations- och jĂ€mstĂ€lldhetsdepartementet)
  • KN (Klimat- och nĂ€ringslivsdepartementet)
  • Ku (Kulturdepartementet)

Recommendations

Priority 1 - Fix Now (Liquibase Changelog 1.44)

  1. Fix talmansuppdrag role scoring - Add 'Förste vice talman' to the ARRAY

Priority 2 - Consider for Future

  1. Add committee referral pattern - Add '%UTSKOTT%' to decision classification
  2. Add "Lagd till handlingarna" pattern - For filed/archived decisions

Files Reviewed

Distinct Values Files:

  • assignment_data_role_code.csv (76 distinct values)
  • assignment_data_assignment_type.csv (7 distinct values)
  • assignment_data_org_code.csv (39+ distinct values)
  • assignment_data_status.csv (5 distinct values)
  • document_proposal_data_chamber.csv (25 distinct values)
  • person_data_party.csv (17 distinct values)
  • person_data_status.csv (200+ distinct values)
  • vote_data_vote.csv (4 distinct values)
  • document_data_document_type.csv (4 distinct values)
  • document_data_sub_type.csv (16 distinct values)
  • committee_proposal_data_decision_type.csv (7 distinct values)
  • sweden_political_party_short_code.csv (30 distinct values)

SQL Views Analyzed:

  • view_riksdagen_politician_experience_summary
  • view_ministry_decision_impact
  • view_decision_temporal_trends
  • view_riksdagen_committee_productivity
  • view_riksdagen_goverment_proposals
  • view_ministry_productivity_matrix
  • view_ministry_effectiveness_trends

View Analysis Summary (Referenced in Documentation)

The following views from DATABASE_VIEW_INTELLIGENCE_CATALOG.md were analyzed:

Politician Views (Key Product Features)

View NameIntelligence ValueData QualityIssues
view_riksdagen_politician⭐⭐⭐⭐⭐✅ GoodNone
view_riksdagen_politician_experience_summary⭐⭐⭐⭐⭐⚠ FixedMissing 'Förste vice talman' (now fixed in v1.44)
view_riksdagen_politician_ballot_summary⭐⭐⭐⭐⭐✅ GoodNone
view_riksdagen_politician_influence_metrics⭐⭐⭐⭐⭐✅ GoodNone

Party Views (Key Product Features)

View NameIntelligence ValueData QualityIssues
view_riksdagen_party_summary⭐⭐⭐⭐✅ GoodNone
view_riksdagen_party_ballot_support_annual_summary⭐⭐⭐⭐✅ GoodNone
view_riksdagen_party_decision_flow⭐⭐⭐⭐⭐✅ GoodNone

Ministry Views (Key Product Features)

View NameIntelligence ValueData QualityIssues
view_ministry_decision_impact⭐⭐⭐⭐⭐✅ FixedCommittee referral pattern added (fixed in v1.45)
view_ministry_productivity_matrix⭐⭐⭐⭐✅ GoodNone
view_ministry_risk_evolution⭐⭐⭐⭐✅ FixedFixed in v1.43

Decision Intelligence Views (Key Product Features)

View NameIntelligence ValueData QualityIssues
view_decision_temporal_trends⭐⭐⭐⭐✅ FixedCommittee referral pattern added (fixed in v1.45)
view_riksdagen_goverment_proposals⭐⭐⭐⭐✅ GoodSimple data selection view - no pattern matching needed

Liquibase Changelog

db-changelog-1.44.xml was created to address the identified issue:

  • Fixed view_riksdagen_politician_experience_summary to include 'Förste vice talman' in talmansuppdrag role scoring
  • All three Deputy Speaker roles (Förste, Andre, Tredje vice talman) now weighted equally at 750.0

db-changelog-1.45.xml was created to address committee referral patterns:

  • Fixed view_decision_temporal_trends to include committee_referral_decisions column
  • Fixed view_ministry_decision_impact to include committee_referral_proposals and committee_referral_rate columns
  • Pattern %UTSKOTT% now captures 7,049 records previously in other_decisions:
    • =utskottet (6,501 records)
    • = utskottet (517 records)
    • utskottet (12 records)
    • =utskott (19 records)

Verification

After Liquibase execution:

  1. Run psql -U postgres -d cia_dev -f schema-validation-v2.sql to verify schema integrity
  2. Run psql -U postgres -d cia_dev -f schema-health-check.sql for health validation
  3. Update full_schema.sql as per README-SCHEMA-MAINTENANCE.md