Task: Data validation subticket #3: External input validators

[nlebovits]

Subticket 3: External Input Validators

Describe the task

Frequently, we have had issues with APIs or external sources changing without us realizing and then messing up the pipeline. Create validators that define our expectations for the structure of external sources so that we can identify what has changed and when to prevent bad data from getting into production and make troubleshooting easier.

Acceptance Criteria

• Identify all API/external input sources requiring validation
• Implement validator for each API/input confirming relevant columns still exist and shape of data is within an acceptable margin of difference
• Implement validator for each API/input confirming columns have expected data
• Add test coverage for all API validators
• Document any discrepancies found vs. expected schema

Additional context

• Nissim can provide expected column names and data types for each API
• Flag any unexpected schema changes as separate tickets

[gabecano4308]

@nlebovits I can take this task (and sub-tasks) on if needed

[nlebovits]

Assigned! :)

[nlebovits]

@gabecano4308 here is what I've been using to build these. The important thing is that the validators confirm 1) that there's not a huge change in the size of the dataset, and 2) that all the columns that we need for the piepline are still there and of the expected type.

Generally, we only need to flag services that are 10% or more larger or smaller than what we expect them to be. A couple of exceptions:

• For council districts, we should have exactly ten records always
• You don't need a breaking size check for vacant land or buildings; we know these are messed up and there's nothing we can do about it. It's built into the service itself.
• OPA properties and PWD parcels shouldn't change in size by more than, like, 3-5%. They're pretty stable.

To generate what's below, I just used curl requests and stuff like that to check what each external service returned. So if you have questions, you can always do the same.

Finally, note that we've added a couple new services since I did this, so you'll want to confirm that they're still there.

VACANT_PROPS_LAYERS_TO_LOAD = [
    "https://services.arcgis.com/fLeGjb7u4uXqeF9q/ArcGIS/rest/services/Vacant_Indicators_Land/FeatureServer/0/",
    "https://services.arcgis.com/fLeGjb7u4uXqeF9q/ArcGIS/rest/services/Vacant_Indicators_Bldg/FeatureServer/0/",
]

### Vacant Land Service Schema
**Total Records**: 6,528

**Columns**:
| Column | Type | Max Length | Nullable | Notes |
|--------|------|------------|----------|-------|
| OBJECTID | integer | - | No | Primary key |
| ADDRESS | string | 50 | No | Property address |
| OWNER1 | string | 50 | No | Primary owner |
| OWNER2 | string | 50 | Yes | Secondary owner |
| BLDG_DESC | string | 50 | No | Building description |
| OPA_ID | string | 10 | No | OPA account number |
| LNIADDRESSKEY | string | 255 | No | L&I address key |
| COUNCILDISTRICT | string | 255 | No | City Council District |
| ZONINGBASEDISTRICT | string | 255 | No | Zoning Base District |
| ZIPCODE | string | 255 | No | Zip Code |
| LAND_RANK | double | - | No | Vacancy rank (0.5 or 1.0) |
| DATE_UPDATE | date | - | No | Last update date |
| Shape__Area | double | - | No | Area in square meters |
| Shape__Length | double | - | No | Perimeter length in meters |

**Geometry**: Polygon (Web Mercator projection, wkid: 102100)


### Vacant Buildings Service Schema
**Total Records**: 7,577

**Columns**:
| Column | Type | Max Length | Nullable | Notes |
|--------|------|------------|----------|-------|
| OBJECTID | integer | - | No | Primary key |
| ADDRESS | string | 50 | No | Property address |
| OWNER1 | string | 50 | No | Primary owner |
| OWNER2 | string | 50 | Yes | Secondary owner |
| BLDG_DESC | string | 50 | No | Building description |
| OPA_ID | string | 10 | No | OPA account number |
| LNIADDRESSKEY | string | 255 | No | L&I address key |
| COUNCILDISTRICT | string | 255 | No | City Council District |
| ZONINGBASEDISTRICT | string | 255 | No | Zoning Base District |
| ZIPCODE | string | 255 | No | Zip Code |
| BUILD_RANK | double | - | No | Vacancy rank (0.5 or 0.67) |
| DATE_UPDATE | date | - | No | Last update date |
| Shape__Area | double | - | No | Area in square meters |
| Shape__Length | double | - | No | Perimeter length in meters |

**Geometry**: Polygon (Web Mercator projection, wkid: 102100)

COUNCIL_DISTRICTS_TO_LOAD = [
    "https://services.arcgis.com/fLeGjb7u4uXqeF9q/arcgis/rest/services/Council_Districts_2024/FeatureServer/0/"
]

### Council Districts Service Schema
**Total Records**: 10

**Columns**:
| Column | Type | Max Length | Nullable | Notes |
|--------|------|------------|----------|-------|
| OBJECTID_1 | integer | - | No | Primary key |
| OBJECTID | integer | - | No | Secondary ID |
| DISTRICT | string | - | No | District number (1-10) |
| SHAPE_LENG | double | - | No | Length in meters |
| Shape__Area | double | - | No | Area in square meters |
| Shape__Length | double | - | No | Perimeter length in meters |

**Geometry**: Polygon (Web Mercator projection, wkid: 102100)


### City Owned Properties Service Schema
**Variable**: `CITY_OWNED_PROPERTIES_TO_LOAD`
**URL**: `https://services.arcgis.com/fLeGjb7u4uXqeF9q/ArcGIS/rest/services/LAMAAssets/FeatureServer/0/`
**Total Records**: 7,796

**Columns**:
| Column | Type | Max Length | Nullable | Notes |
|--------|------|------------|----------|-------|
| pin | integer | - | Yes | Property identification number |
| mapreg_1 | string | - | No | Map registration number |
| agency | string | - | No | Owning agency (e.g., "PRA", "PLB", "PUB") |
| opabrt | string | - | Yes | OPA account number |
| location | string | - | No | Property address |
| status_1 | string | - | No | Ownership status (e.g., "Owned - Available", "Owned - Not Available") |
| councildistrict | integer | - | No | Council district number |
| sideyardeligible | string | - | No | Side yard eligibility ("Yes"/"No") |
| zoning | string | - | No | Zoning code (e.g., "RSA-5", "CMX-3", "RM-1") |
| objectid | integer | - | No | Primary key |
| Shape__Area | double | - | No | Area in square meters |
| Shape__Length | double | - | No | Perimeter length in meters |

**Geometry**: Polygon (Web Mercator projection, wkid: 102100)

### PHS Landcare Service Schema
**Variable**: `PHS_LAYERS_TO_LOAD`
**URL**: `https://services.arcgis.com/fLeGjb7u4uXqeF9q/ArcGIS/rest/services/phs_landcare/FeatureServer/0`
**Total Records**: 9,442

**Columns**:
| Column | Type | Max Length | Nullable | Notes |
|--------|------|------------|----------|-------|
| address | string | - | No | Primary address |
| addr_range | string | - | No | Full address range |
| zipcode | integer | - | No | 5-digit zip code |
| district | string | - | No | Council district number |
| year | string | - | No | Year of program (e.g., "2008", "2013") |
| season | string | - | No | Season ("Spring" or "Fall") |
| program | string | - | No | Program code (e.g., "PLC") |
| comm_ptnr | string | - | No | Community partner (space if none) |
| fmr_prgm | string | - | No | Former program (space if none) |
| stabilized | string | - | No | Stabilization status ("YES") |
| numparcels | string | - | No | Number of parcels |
| parcelarea | integer | - | No | Parcel area in square feet |
| objectid | integer | - | No | Primary key |
| Shape__Area | double | - | No | Area in square meters |
| Shape__Length | double | - | No | Perimeter length in meters |

**Geometry**: Polygon (Web Mercator projection, wkid: 102100)


### RCOs (Registered Community Organizations) Service Schema
**Variable**: `RCOS_LAYERS_TO_LOAD`
**URL**: `https://services.arcgis.com/fLeGjb7u4uXqeF9q/ArcGIS/rest/services/Zoning_RCO/FeatureServer/0/`
**Total Records**: 257

**Columns**:
| Column | Type | Max Length | Nullable | Notes |
|--------|------|------------|----------|-------|
| OBJECTID | integer | - | No | Primary key |
| ORGANIZATION_NAME | string | - | No | Name of the RCO |
| ORGANIZATION_ADDRESS | string | - | No | Organization's mailing address |
| MEETING_LOCATION_ADDRESS | string | - | No | Physical meeting location |
| ORG_TYPE | string | - | No | Type of organization (e.g., "Other") |
| PREFFERED_CONTACT_METHOD | string | - | No | Preferred contact method (e.g., "Email") |
| PRIMARY_NAME | string | - | No | Primary contact name |
| PRIMARY_ADDRESS | string | - | No | Primary contact address |
| PRIMARY_EMAIL | string | - | No | Primary contact email |
| PRIMARY_PHONE | string | - | Yes | Primary contact phone |
| P_PHONE_EXT | string | - | Yes | Primary phone extension |
| ALTERNATE_NAME | string | - | No | Alternate contact name |
| ALTERNATE_ADDRESS | string | - | No | Alternate contact address |
| ALTERNATE_EMAIL | string | - | No | Alternate contact email |
| ALTERNATE_PHONE | string | - | Yes | Alternate contact phone |
| A_PHONE_EXT | string | - | Yes | Alternate phone extension |
| EXPIRATIONYEAR | integer | - | No | Year of expiration |
| EFFECTIVE_DATE | date | - | No | Effective date (Unix timestamp) |
| LNI_ID | integer | - | No | L&I identification number |
| Shape__Area | double | - | No | Area in square meters |
| Shape__Length | double | - | No | Perimeter length in meters |

**Geometry**: Polygon (Web Mercator projection, wkid: 102100)

### Community Gardens Service Schema
**Variable**: `COMMUNITY_GARDENS_TO_LOAD`
**URL**: `https://services2.arcgis.com/qjOOiLCYeUtwT7x7/arcgis/rest/services/PHS_NGT_Supported_Current_view/FeatureServer/0/`
**Total Records**: 205

**Columns**:
| Column | Type | Max Length | Nullable | Notes |
|--------|------|------------|----------|-------|
| OBJECTID | integer | - | No | Primary key |
| Site_Name | string | - | No | Name of the garden/farm |
| Supported | string | - | No | Support status ("PHS", "PHS and NGT") |
| Website | string | - | Yes | Website URL (null in sample) |

**Geometry**: Point (Web Mercator projection, wkid: 102100)

### PPR Properties Service Schema
**Variable**: `PPR_PROPERTIES_TO_LOAD`
**URL**: `https://services.arcgis.com/fLeGjb7u4uXqeF9q/ArcGIS/rest/services/PPR_Properties/FeatureServer/0`
**Total Records**: 507

**Columns**:
| Column | Type | Max Length | Nullable | Notes |
|--------|------|------------|----------|-------|
| OBJECTID | integer | - | No | Primary key |
| PUBLIC_NAME | string | - | No | Public name of the property |
| PARENT_NAME | string | - | No | Parent property name |
| NESTED | string | - | No | Nested status ("Y"/"N") |
| OFFICIAL_NAME | string | - | Yes | Official property name |
| LABEL | string | - | No | Short label/identifier |
| ALIAS | string | - | Yes | Alternative names |
| DPP_ASSET_ID | integer | - | No | DPP asset identifier |
| ADDRESS_911 | string | - | No | Primary address |
| ZIP_CODE | string | - | No | 5-digit zip code |
| ADDRESS_BRT | string | - | No | BRT address range |
| ALIAS_ADDRESS | string | - | No | Alternative addresses |
| ACREAGE | double | - | No | Property size in acres |
| PROPERTY_CLASSIFICATION | string | - | No | Property type (e.g., "RECREATION_CENTER", "NEIGHBORHOOD_PARK") |
| PPR_USE | string | - | No | PPR use type (e.g., "RECREATION_SITE", "GREENWAY_PARKWAY") |
| PPR_PROG_DISTRICT | string | - | No | Program district(s) |
| PPR_OPS_DISTRICT | string | - | No | Operations district(s) |
| COUNCIL_DISTRICT | string | - | No | Council district(s) |
| POLICE_DISTRICT | string | - | No | Police district(s) |
| CITY_SCALE_MAPS | string | - | No | City scale maps available ("Y"/"N") |
| LOCAL_SCALE_MAPS | string | - | No | Local scale maps available ("Y"/"N") |
| PROGRAM_SITES | string | - | No | Program sites available ("Y"/"N") |
| COMMENTS | string | - | Yes | Additional comments |
| Shape__Area | double | - | No | Area in square meters |
| Shape__Length | double | - | No | Perimeter length in meters |

**Geometry**: Polygon (Web Mercator projection, wkid: 102100)

### L&I Complaints Service Schema
**Variable**: `COMPLAINTS_SQL_QUERY`
**URL**: Carto SQL API
**Total Records**: 5,245,109

**Columns**:
| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| address | string | Yes | Property address |
| service_request_id | string | No | Unique identifier |
| subject | string | No | Complaint type |
| status | string | No | Current status |
| service_name | string | No | Service category |
| service_code | string | No | Service type code |
| lat | double | Yes | Latitude (y) |
| lon | double | Yes | Longitude (x) |

### L&I Violations Service Schema
**Variable**: `VIOLATIONS_SQL_QUERY`
**URL**: Carto SQL API
**Total Records**: 54,401

**Columns**:
| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| parcel_id_num | string | Yes | Parcel identifier |
| casenumber | string | No | Case identifier |
| casecreateddate | date | No | Case creation date |
| casetype | string | No | Type of case |
| casestatus | string | No | Current status |
| violationnumber | string | No | Violation identifier |
| violationcodetitle | string | No | Violation description |
| violationstatus | string | No | Status of violation |
| opa_account_num | string | No | OPA account number |
| address | string | No | Property address |
| opa_owner | string | No | Property owner |
| geocode_x | double | Yes | Longitude (x) |
| geocode_y | double | Yes | Latitude (y) |

### Crime Incidents Service Schema
**Variable**: `GUNCRIME_SQL_QUERY` and `DRUGCRIME_SQL_QUERY`
**URL**: Carto SQL API
**Total Records**: 3,387,139

**Columns**:
| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| text_general_code | string | No | Crime type |
| dispatch_date | date | No | Incident date |
| point_x | double | Yes | Longitude (x) |
| point_y | double | Yes | Latitude (y) |

### OPA Properties Service Schema
**Variable**: `OPA_PROPERTIES_QUERY`
**URL**: Carto SQL API
**Total Records**: 582,781

**Columns**:
| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| building_code_description | string | Yes | Building description |
| market_value | numeric | No | Property value |
| sale_date | date | Yes | Last sale date |
| sale_price | numeric | Yes | Last sale price |
| parcel_number | string | No | Parcel identifier |
| owner_1 | string | No | Primary owner |
| owner_2 | string | Yes | Secondary owner |
| mailing_address_1 | string | Yes | Owner address line 1 |
| mailing_address_2 | string | Yes | Owner address line 2 |
| mailing_care_of | string | Yes | Care of information |
| mailing_street | string | Yes | Street address |
| mailing_zip | string | Yes | ZIP code |
| mailing_city_state | string | Yes | City and state |
| zip_code | string | No | Property ZIP code |
| zoning | string | Yes | Zoning code |
| the_geom | geometry | No | Polygon geometry |

### PWD Parcels Service Schema
**Variable**: `PWD_PARCELS_QUERY`
**URL**: Carto SQL API
**Total Records**: 547,351

**Columns**:
| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| address | string | Yes | Property address |
| owner | string | Yes | Property owner |
| building_code | string | Yes | Building classification |
| building_description | string | Yes | Building description |
| gross_area | numeric | No | Total area |
| the_geom | geometry | No | Polygon geometry |


### Unsafe Buildings Service Schema
**Variable**: `UNSAFE_BUILDINGS_QUERY`
**URL**: Carto SQL API
**Total Records**: 3,520

**Columns**:
| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| address | string | No | Property address |
| violation_date | date | No | Date of violation |
| opa_account_num | string | Yes | OPA account number |
| owner | string | Yes | Property owner |
| the_geom | geometry | No | Polygon geometry |

### Imminent Danger Buildings Service Schema
**Variable**: `IMMINENT_DANGER_BUILDINGS_QUERY`
**URL**: Carto SQL API
**Total Records**: 186

**Columns**:
| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| address | string | No | Property address |
| violation_date | date | No | Date of violation |
| opa_account_num | string | Yes | OPA account number |
| owner | string | Yes | Property owner |
| the_geom | geometry | No | Polygon geometry |

### Building Permits Service Schema
**Variable**: `PERMITS_QUERY`
**URL**: Carto SQL API
**Total Records**: 870,199

**Columns**:
| Column | Type | Nullable | Notes |
|--------|------|----------|-------|
| address | string | Yes | Property address |
| addressobjectid | string | Yes | Address identifier |
| approvedscopeofwork | string | Yes | Approved work scope |
| commercialorresidential | string | Yes | Property type |
| opa_account_num | string | Yes | OPA account number |
| permittype | string | No | Type of permit |
| status | string | No | Permit status |
| unit_num | string | Yes | Unit number |
| unit_type | string | Yes | Unit type |
| permitissuedate | date | No | Issue date |
| typeofwork | string | Yes | Work type |
| the_geom | geometry | No | Polygon geometry |
| the_geom_geojson | json | No | GeoJSON geometry |