This repository was created to help answer common questions about becoming a super user and how to query the property tax assessment database. This repo also contains helpful sql scripts and other templates for common analysis.
A Super User has the ability to access the raw data stored in our database using SQL queries. If you do not have the technical skills or expertise to be a super user, you can hire a person with those skills and they can be given super user access to perform analysis for you.
Super Users are billed for the queries that they perform in their Google Cloud Project. Google Cloud Platform has a “Free Tier” of services which covers up to 1TB of queries per month (Depending on the size and complexity of the query, this usually covers 10-20 complex queries), however any queries beyond that limit will be charged to the super user. The exact billing is determined by your billing plan by Google, but a good rule of thumb is ~$5 per 1TB of data queried. If you notice your bill increasing or costing lots of money please reach out and we can show you how to partition your tables and query in a way that reduces costs.
To become a super user, there are a few steps:
-
Please send Alex Nielson a valid gmail account you want to gain access to the data. If you do not have a gmail account, you will need to set one up for your user or organization.
-
Next, set up a Google Cloud platform billing account and project for the valid gmail account you sent Alexander Nielson. The steps to do this are detailed in this pdf: https://docs.google.com/document/d/1H8U5pGS4iY7J6YyuPrGwSBbECM2nm8Q3MdNNmR9aqgc/edit?usp=sharing
-
Confirm you have access to the database by pasting the following query into your Big Query console:
SELECT county, year FROM `ut-sao-tax-prod.research_public.tax_roll`
If you have a green "check" mark in the upper right hand corner of the query editor page then you are good to go!
- (Optional) If you wish to request a one time 30 minute database demo and explanation of the database, this can be scheduled with Alexander Nielson ([email protected]) per his schedule. These meetings are at the Office of the State Auditor’s whim and convenience. If we do not have the time or resources, you may not get a tutorial meeting.
If you wish to perform your analysis in R, Python, or other programming language, then you must set up a special credential file called a "Service Account".
This tutorial will document how you can set up a service account: https://docs.google.com/document/d/1PwEITu7y0xuq9flRPcKJ3vlBTUSI_Epq43iszHNgGTA/edit?usp=sharing Please make sure you to send the service account's email once it is created since it will need to be whitelisted in our database to get access to the data.
If you have other questions please email Alexander Nielson ([email protected]).
There is a "public" and "protected" version of the database. While all the data sent to the Office of the State Auditor is public data, and not according to the state code protected or private information, there is a change in ability and scope to search owner information using this database.
After receiving feedback from various stakeholders, the OSA has decided to only allow county assessors and other required governmental employees access to the owner information. Super users do not by default get access to owner information and a few additional fields related to the property that are non-essential.
To see the fields available to you as a super user, please visit the schema here: https://docs.google.com/spreadsheets/d/1CNwGEL5QfvNGaslamYfF-QR28baPZdqh5yfG3_cLgNE/edit?usp=sharing
| Field | Description | Type | Restrictions | Required? | Example |
|---|---|---|---|---|---|
| county | The reporting county's name | TEXT | Yes | Salt Lake County | |
| county_id | Alphabetically sorted integer (1=Beaver County,2 = Box Elder,...,29 = Weber) | INTEGER | Yes | 1 | |
| year | The reporting year | INTEGER | 4 digit year | Yes | 2022 |
| parcel_id | The parcels unique id number. Should match other legal documents | TEXT | Must match parcel id used in tax roll and recorders office | Yes | 12345-678 |
| serial_id | The parcels serial id | TEXT | No | 123a | |
| entry_id | The parcels entry id | TEXT | No | 4 | |
| update_date | The last date the property was changed in the CAMA system | DATE | yyyy-mm-dd format | Yes | 2023-02-10 |
| detailed_review_date | The last date in which the property had a detailed review | DATE | yyyy-mm-dd format | Yes | 2022-01-01 |
| accessed_date | The data the database was accessed or when the export was sent | yyyy-mm-dd format | Yes | 2023-06-01 | |
| parcel_has_multiple_buildings | If the parcel has multiple buildings (such as an apartment complex or condo), this value should be "TRUE" otherwise it should be left "FALSE" | BOOLEAN | Yes | TRUE | |
| building_count | If the parcel has multiple buildings, how many are in the parcel | INTEGER | Yes | 10 | |
| building_id | If there are multiple buildings on the property and each building is its own record/line, then the building id is used to identify which building is is which. | TEXT | No | 1a | |
| district | The tax district the parcel resides | TEXT | Yes | 12 | |
| neighborhood_id | The neighborhood id for which the parcel resides | TEXT | Yes | 12a | |
| neighborhood | The neighborhood the parcel resides in | TEXT | Yes | Central Woods | |
| subdivision | The subdivision the parcel resides in | TEXT | Yes | Sage Vista | |
| situs_address1 | The property address first line | TEXT | Yes | 1234 M Fake Street | |
| situs_address2 | The property address second line | TEXT | Yes | Suite 30 | |
| situs_city | The property city | TEXT | Yes | Salt Lake City | |
| situs_state | The property state | TEXT | Yes | UT | |
| situs_zip | The property zip code | TEXT | Yes | 84102 | |
| property_type | The property type standardized by the OSA | TEXT | Yes | See the "Property Types" tab of this workbook | |
| property_type_internal | The property type used internally in the county assessors software | TEXT | Yes | 112, "Primary Residential", "Vacant Land", "Commercial" | |
| primary_residence | Is the parcel a primary residence (Yes, No or Unknown) | BOOLEAN | Yes | TRUE | |
| tax_exempt | Is the parcel tax exempt? | BOOLEAN | Yes | TRUE | |
| tax_exempt_type | The standardized tax exemption type. See "Exemption Types" for available options | TEXT | Yes | Non Profit - Charitable | |
| tax_exempt_type_internal | The tax_exempt_type code or description used internally by the county assessor's CAMA system | TEXT | Yes | State | |
| accessory_dwelling_unit | Does the residential parcel contain an accessory dwelling or mother in law unit? | BOOLEAN | Yes | TRUE | |
| floors_count | The number of floors the parcel's building contains | INTEGER | positive integer non zero integer | No | 3 floors |
| floors_info | Note for how floors are counted by the County | TEXT | Yes | Only above grade floors are counted | |
| number_of_bedrooms | The number of bedrooms in a parcel | INTEGER | No | 4 | |
| sq_feet | Total square footage of building | INTEGER | Yes | 2000 | |
| sq_feet_finished | Total finished square footage of building | INTEGER | Yes | 2000 | |
| sq_feet_basement | Basement square footage | INTEGER | Yes | 600 | |
| basement_finished_percent | Percent of Basement that is finished (0.00 to 1.00 scale) | DECIMAL | Yes | 1 | |
| sq_feet_ground | main floor square footage | INTEGER | No | 1000 | |
| sq_feet_floor1 | First floor above main floor square footage | INTEGER | No | 400 | |
| sq_feet_floor2 | Second floor above main floor square footage | INTEGER | No | 300 | |
| sq_feet_floor3 | Third floor above main floor square footage | INTEGER | No | 400 | |
| sq_feet_attic_unfinished | Attic square Footage that is unfinished | INTEGER | No | 600 | |
| sq_feet_attic_finished | Attic Square Footage that is finished | INTEGER | No | 100 | |
| acres | Parcel size in acres | DECIMAL | Yes | 0.5 | |
| year_built | Estimated year of initial construction of primary buildings | INTEGER | Yes | 1960 | |
| market | The total assessed market value of the parcel including land and improvements | DECIMAL | Yes | 300000 | |
| taxable | The total taxable value of the assessed parcel | DECIMAL | Yes | 180000 | |
| taxes_charged | The net taxes charged to the owner including abatements | DECIMAL | Yes | 2000 | |
| taxes_abated | The total taxes abated | DECIMAL | No | 0 | |
| taxes_paid | The total taxes paid | DECIMAL | No | 2000 | |
| tax_rate | The total tax rate for the parcel | DECIMAL | Yes | 0.006 |
If you are from a state agency or related researcher, you may be able to gain access to owner fields if it is approved by the State Auditor. To see a schema with ALL possible fields available visit this schema here:
https://docs.google.com/spreadsheets/d/1QXT760-BFgNnbljIxuVaQ1nrOYVDs03wuMF0u51RCik/edit?usp=sharing
| Field | Description | Type | Restrictions | Required? | Example |
|---|---|---|---|---|---|
| county | The reporting county's name | TEXT | Yes | Salt Lake County | |
| county_id | Alphabetically sorted integer (1=Beaver County,2 = Box Elder,...,29 = Weber) | INTEGER | Yes | 1 | |
| year | The reporting year | INTEGER | 4 digit year | Yes | 2022 |
| parcel_id | The parcels unique id number. Should match other legal documents | TEXT | Must match parcel id used in tax roll and recorders office | Yes | 12345-678 |
| serial_id | The parcels serial id | TEXT | No | 123a | |
| entry_id | The parcels entry id | TEXT | No | 4 | |
| update_date | The last date the property was changed in the CAMA system | DATE | yyyy-mm-dd format | Yes | 2023-02-10 |
| detailed_review_date | The last date in which the property had a detailed review | DATE | yyyy-mm-dd format | Yes | 2022-01-01 |
| accessed_date | The data the database was accessed or when the export was sent | yyyy-mm-dd format | Yes | 2023-06-01 | |
| parcel_has_multiple_buildings | If the parcel has multiple buildings (such as an apartment complex or condo), this value should be "TRUE" otherwise it should be left "FALSE" | BOOLEAN | Yes | TRUE | |
| building_count | If the parcel has multiple buildings, how many are in the parcel | INTEGER | Yes | 10 | |
| building_id | If there are multiple buildings on the property and each building is its own record/line, then the building id is used to identify which building is is which. | TEXT | No | 1a | |
| district | The tax district the parcel resides | TEXT | Yes | 12 | |
| neighborhood_id | The neighborhood id for which the parcel resides | TEXT | Yes | 12a | |
| neighborhood | The neighborhood the parcel resides in | TEXT | Yes | Central Woods | |
| subdivision | The subdivision the parcel resides in | TEXT | Yes | Sage Vista | |
| situs_address1 | The property address first line | TEXT | Yes | 1234 M Fake Street | |
| situs_address2 | The property address second line | TEXT | Yes | Suite 30 | |
| situs_city | The property city | TEXT | Yes | Salt Lake City | |
| situs_state | The property state | TEXT | Yes | UT | |
| situs_zip | The property zip code | TEXT | Yes | 84102 | |
| owner_name | The owner name | TEXT | Yes | Jimbo Jenkins | |
| owner_address1 | The owner address first line | TEXT | Yes | 1234 M Fake Street | |
| owner_address2 | The owner address second line | TEXT | Yes | Suite 30 | |
| owner_city | The owner address city | TEXT | Yes | Salt Lake City | |
| owner_state | The owner address state | TEXT | Yes | UT | |
| owner_zip | The owner address zip code | TEXT | Yes | 84102 | |
| owner_country | The owner address country | TEXT | Yes | USA | |
| property_type | The property type standardized by the OSA | TEXT | Yes | See the "Property Types" tab of this workbook | |
| property_type_internal | The property type used internally in the county assessors software | TEXT | Yes | 112, "Primary Residential", "Vacant Land", "Commercial" | |
| primary_residence | Is the parcel a primary residence (Yes, No or Unknown) | BOOLEAN | Yes | TRUE | |
| tax_exempt | Is the parcel tax exempt? | BOOLEAN | Yes | TRUE | |
| tax_exempt_type | The standardized tax exemption type. See "Exemption Types" for available options | TEXT | Yes | Non Profit - Charitable | |
| tax_exempt_type_internal | The tax_exempt_type code or description used internally by the county assessor's CAMA system | TEXT | Yes | State | |
| construction_material | Construction Material Types, Values for this field are expected to vary greatly by county | TEXT | Yes | Wood Frame, Brick, etc | |
| status | What is the condition of the property | TEXT | No | Very Good | |
| accessory_dwelling_unit | Does the residential parcel contain an accessory dwelling or mother in law unit? | BOOLEAN | Yes | TRUE | |
| floors_count | The number of floors the parcel's building contains | INTEGER | positive integer non zero integer | No | 3 floors |
| floors_info | Note for how floors are counted by the County | TEXT | Yes | Only above grade floors are counted | |
| number_of_bedrooms | The number of bedrooms in a parcel | INTEGER | No | 4 | |
| sq_feet | Total square footage of building | INTEGER | Yes | 2000 | |
| sq_feet_finished | Total finished square footage of building | INTEGER | Yes | 2000 | |
| sq_feet_basement | Basement square footage | INTEGER | Yes | 600 | |
| basement_finished_percent | Percent of Basement that is finished (0.00 to 1.00 scale) | DECIMAL | Yes | 1 | |
| sq_feet_ground | main floor square footage | INTEGER | No | 1000 | |
| sq_feet_floor1 | First floor above main floor square footage | INTEGER | No | 400 | |
| sq_feet_floor2 | Second floor above main floor square footage | INTEGER | No | 300 | |
| sq_feet_floor3 | Third floor above main floor square footage | INTEGER | No | 400 | |
| sq_feet_attic_unfinished | Attic square Footage that is unfinished | INTEGER | No | 600 | |
| sq_feet_attic_finished | Attic Square Footage that is finished | INTEGER | No | 100 | |
| acres | Parcel size in acres | DECIMAL | Yes | 0.5 | |
| year_built | Estimated year of initial construction of primary buildings | INTEGER | Yes | 1960 | |
| year_built_effective | The 'effective' year built of primary buildings that factors in updates after construction | INTEGER | No | 2005 | |
| remodel_year | The most recent year the improvement was remodeled | INTEGER | No | 2017 | |
| market | The total assessed market value of the parcel including land and improvements | DECIMAL | Yes | 300000 | |
| land | The assessed market value of land | DECIMAL | Yes | 100000 | |
| improvements | The assessed market value of improvements | DECIMAL | Yes | 200000 | |
| taxable | The total taxable value of the assessed parcel | DECIMAL | Yes | 180000 | |
| taxes_charged | The net taxes charged to the owner including abatements | DECIMAL | Yes | 2000 | |
| taxes_abated | The total taxes abated | DECIMAL | No | 0 | |
| taxes_paid | The total taxes paid | DECIMAL | No | 2000 | |
| tax_rate | The total tax rate for the parcel | DECIMAL | Yes | 0.006 |
Please contact Alex Nielson ([email protected]) if you need access to these fields and we can discuss further how to help you achieve your research/statutory obligations.
coming soon.