Downloads
Municipal government data

Big Bear Lake (city)

Big Bear Lake is a small mountain resort city in California, and this is municipal-style operating data for it, the way a city manager, finance director or public works superintendent would see it: 311 requests, snow plow operations, fire and sheriff dispatch, building permits and code enforcement, a city-run water utility, the lodging tax on short-term rentals, parcels and owners, fund accounting with budgets, and council agendas and votes. The records are invented; the place and the kind of work are real. July-June fiscal year, with coordinates on almost everything.

bigbearSQL ServerMariaDB 61 tables
service_requestsparcelscad_callstot_filingsgl_journal_lines

Read the guide

City government operations (bigbear): A small resort city: 311 requests, snow operations, dispatch, permits and code enforcement, a water utility, the lodging tax, parcels and fund accounting, on a map and a July-June fiscal year.

Good for

Local-government analyticsMapping 311, dispatch and parcelsBudget versus actual and fund accountingSeasonality, weather and tourism

Questions to try

  • Does a busier town mean more dispatch calls?
  • Where does the General Fund's money come from?
  • What did each big storm cost?
  • Who owns the homes, and where do they live?

Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.

Connect to it

Server learnsql.cpetoday.com,1433, database bigbear, in Excel or Power BI (Import mode) or DBeaver. These tables are in the dbo schema, so once you have picked the database you can use plain names such as agenda_items.

Download the Excel connection (.odc)

How to use it: download the file and open it. Excel opens it as a connection to bigbear and asks for the SQL Server username and password from your CPE Today course. The file holds no password. The first table is selected by default, and you can pick others from the Data window. Setup details for other tools are on Connect.

Tables (61)

Click a table to preview it in the Playground.

Data dictionary

What each column means, its type and keys, and for codes and statuses the values seen in this copy. 12 of 61 tables are described so far. Read it as Markdown.

Click a table in the diagram above to jump to it.

business_licenses one row per business per license year 12 columns

Business licenses issued to businesses, by year.

ColumnTypeKeyMeaningValues seen
license_id int PK Unique license ID.
business_id int FK to businesses Business (businesses).
license_number varchar(14) License number.
license_year smallint Year the license covers. 2026 859 2027 854 2025 834 2024 822 2023 803 2022 781
issue_date date Date issued.
expiration_date date Expiration date. 2026-06-30 859 2027-06-30 854 2025-06-30 834 2024-06-30 822 2023-06-30 803 2022-06-30 781
annual_fee decimal Fee for the year.
paid_date date Date the fee was paid.
status varchar(12) License status (see the values). Expired 3908 Active 815 Unpaid 162 Closed 68
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
businesses one row per business 20 columns

Businesses that hold a city license, with their category and location.

ColumnTypeKeyMeaningValues seen
business_id int PK Unique business ID.
business_name varchar(120) Business name.
business_category varchar(40) Category (see the values).
naics_code char(6) NAICS industry code.
parcel_id int FK to parcels Location parcel (parcels).
address_line1 varchar(100) Not described yet
city varchar(60) Not described yet
state char(2) Not described yet
postal_code varchar(10) Not described yet
opened_date date Date opened.
closed_date date Date closed, if closed.
employee_count smallint Employees.
is_home_based bit 1 if the business operates from a home. False 784 True 132
is_seasonal bit Not described yet
county varchar(60) Not described yet
country char(2) Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
geocode_quality varchar(24) Not described yet
full_address varchar(220) Not described yet
cad_calls one row per call 23 columns

Emergency dispatch calls: type, priority, the times for each step of the response, and the disposition.

ColumnTypeKeyMeaningValues seen
call_id int PK Unique call ID.
incident_number varchar(14) Incident number.
call_type_id int FK to cad_call_types Type of call (cad_call_types).
agency varchar(10) Responding agency. Sheriff 21574 Fire/EMS 8875
priority tinyint Priority of the call (see the values). 3 13813 2 9456 4 4180 1 3000
received_at datetime2 When the call was received.
dispatched_at datetime2 When a unit was dispatched.
enroute_at datetime2 When the unit left the station.
onscene_at datetime2 When the unit arrived on scene.
cleared_at datetime2 When the unit cleared.
unit_id int FK to cad_units Unit that responded (cad_units).
jurisdiction varchar(30) Not described yet
neighborhood_id int FK to neighborhoods Not described yet
parcel_id int FK to parcels Not described yet
address_text varchar(120) Not described yet
disposition varchar(24) How the call ended (see the values).
weather_snowfall_in decimal Not described yet
geocode_quality varchar(24) Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
gl_accounts one row per account 6 columns

The chart of accounts.

ColumnTypeKeyMeaningValues seen
account_id int PK Unique account ID.
account_number varchar(10) Account number.
account_name varchar(80) Account name.
account_type varchar(20) Account type (see the values). Revenue 20 Expense 19 Asset 3 Fund Balance 1 Liability 1
category varchar(40) Reporting category.
parent_account_id int FK to gl_accounts Not described yet
gl_journal_lines one row per journal line 8 columns

Debit and credit lines of the city's general ledger entries, tagged by fund, department and project.

ColumnTypeKeyMeaningValues seen
line_id int PK Unique line ID.
entry_id int FK to gl_journal_entries Journal entry (gl_journal_entries).
account_id int FK to gl_accounts Account (gl_accounts).
fund_id int FK to funds Fund (funds). 1 6534 8 1714 9 941 7 499 3 419 2 314 4 232 5 174 10 174 6 130
department_id int FK to departments Department (departments).
project_id int FK to capital_projects Capital project (capital_projects).
debit decimal Debit amount.
credit decimal Credit amount.
invoices one row per invoice 17 columns

Bills the city has received from its vendors (accounts payable).

ColumnTypeKeyMeaningValues seen
invoice_id int PK Unique invoice ID.
vendor_id int FK to vendors Vendor (vendors).
invoice_number varchar(30) Vendor's invoice number.
invoice_date date Invoice date.
due_date date Due date.
paid_date date Date paid.
amount decimal Invoice amount.
account_id int FK to gl_accounts Account charged (gl_accounts).
fund_id int FK to funds Fund charged (funds). 1 2640 8 624 9 209 7 174 4 70 3 61 2 18 10 10
department_id int FK to departments Not described yet
project_id int FK to capital_projects Not described yet
grant_id int FK to grants Not described yet
status varchar(20) Invoice status (see the values). Paid 3734 Open 60 Overdue 12
description varchar(200) Not described yet
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
parcels one row per parcel 27 columns

Land parcels in the city, identified by assessor's parcel number (APN): address, land use and building facts.

ColumnTypeKeyMeaningValues seen
parcel_id int PK Unique parcel ID.
apn varchar(16) Assessor's parcel number.
address_line1 varchar(100) Street address.
city varchar(60) Not described yet
state char(2) Not described yet
postal_code varchar(10) Not described yet
street_id int FK to streets Not described yet
neighborhood_id int FK to neighborhoods Neighborhood (neighborhoods). 8 1565 10 921 3 905 6 864 9 780 2 726 12 631 1 587 11 542 5 486 4 379 7 257
zone_id int FK to zoning_districts Zoning district (zoning_districts). 1 3245 4 1178 3 1153 8 777 7 762 2 645 9 388 6 213 5 211 10 71
owner_id int FK to property_owners Owner (property_owners).
land_use varchar(30) Land use category (see the values). Single Family Residence 3514 Condominium 1231 Vacant Residential Lot 1138 Commercial Retail 570 Lodging 509 Townhome 435 Multi-Family (2-4 units) 313 Restaurant or Bar 313 Office or Services 259 Multi-Family (5+ units) 160 Mixed Use 130 Institutional or Public 71
lot_sqft int Lot size in square feet.
year_built smallint Year the building was built.
bedrooms tinyint Not described yet
bathrooms decimal Not described yet
building_sqft int Building size in square feet.
is_owner_occupied bit 1 if the owner lives on the parcel. False 7336 True 1307
miles_to_lake decimal Not described yet
elevation_ft smallint Not described yet
wildfire_zone varchar(12) Not described yet
has_water_service bit Not described yet
county varchar(60) Not described yet
country char(2) Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
geocode_quality varchar(24) Not described yet
full_address varchar(220) Not described yet
permits one row per permit 22 columns

Building and other permits: type, parcel, valuation, fee and status.

ColumnTypeKeyMeaningValues seen
permit_id int PK Unique permit ID.
permit_number varchar(14) Permit number.
permit_type_id int FK to permit_types Permit type (permit_types).
parcel_id int FK to parcels Parcel (parcels).
neighborhood_id int FK to neighborhoods Not described yet
applicant_type varchar(24) Who applied (see the values). Contractor 3063 Owner 2155 Architect or Engineer 369 Tenant 257
description varchar(200) Not described yet
valuation decimal Declared project value.
square_feet int Not described yet
applied_date date Date applied for.
issued_date date Date issued.
finaled_date date Date the work was signed off.
expires_date date Not described yet
status varchar(12) Permit status (see the values). Finaled 5261 Expired 237 Withdrawn 186 Cancelled 86 Issued 61 In Review 13
fee_assessed decimal Fee charged.
fee_paid decimal Fee paid.
reviewer_employee_id int FK to employees Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
service_requests one row per request 27 columns

Resident requests to the city, such as potholes or illegal dumping, from submission to closure.

ColumnTypeKeyMeaningValues seen
request_id int PK Unique request ID.
request_number varchar(14) Public request number.
type_id int FK to service_request_types Kind of request (service_request_types).
channel varchar(12) How it was submitted (see the values). Phone 5735 Mobile app 4748 Web form 3730 Email 1742 Staff 1646 Counter 1019
submitted_at datetime2 When it was submitted.
parcel_id int FK to parcels Parcel it relates to (parcels).
street_id int FK to streets Not described yet
neighborhood_id int FK to neighborhoods Neighborhood (neighborhoods).
address_text varchar(120) Not described yet
description varchar(200) Not described yet
reporter_type varchar(20) Not described yet
reporter_postal_code varchar(10) Not described yet
priority tinyint Priority (see the values). 3 9788 4 4728 2 2512 1 1592
status varchar(12) Request status (see the values). Closed 16019 Duplicate 1377 Referred 845 Cancelled 293 Open 77 In Progress 9
assigned_employee_id int FK to employees Employee assigned (employees).
acknowledged_at datetime2 When the city acknowledged it.
closed_at datetime2 When it was closed.
resolution varchar(60) Not described yet
duplicate_of_request_id int FK to service_requests Earlier request this duplicates, if any.
satisfaction tinyint Satisfaction score given at closure, if any. (blank) 15878 2 603 3 581 1 556 5 513 4 489
storm_id int FK to storm_events Storm event it relates to (storm_events).
geocode_quality varchar(24) Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
tot_filings one row per operator per month 19 columns

Monthly transient occupancy tax (TOT) returns filed by short-term rental operators.

ColumnTypeKeyMeaningValues seen
filing_id int PK Unique filing ID.
tot_operator_id int FK to tot_operators Operator (tot_operators).
period_month date Month the return covers.
due_date date Due date.
filed_date date Date filed.
available_nights smallint Not described yet
occupied_nights smallint Nights occupied.
gross_receipts decimal Gross rental receipts.
exempt_receipts decimal Receipts exempt from the tax.
taxable_receipts decimal Receipts subject to the tax.
tax_rate decimal Tax rate. 0.1 50960 0.12 27039
tax_due decimal Tax owed.
penalty decimal Late penalty.
paid_date date Not described yet
paid_amount decimal Amount paid.
status varchar(16) Filing status (see the values). Filed on time 70195 Late 5937 Audit adjusted 1396 Delinquent 370 Overdue 101
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
utility_bills one row per bill 23 columns

Monthly water bills: meter readings, usage, charges and payment status.

ColumnTypeKeyMeaningValues seen
bill_id int PK Unique bill ID.
account_id int FK to utility_accounts Utility account (utility_accounts).
bill_date date Bill date.
service_from date Start of the billed period.
service_to date End of the billed period.
billing_days smallint Not described yet
read_previous int Previous meter reading.
read_current int Current meter reading.
usage_ccf decimal Water used, in hundred cubic feet.
read_type varchar(10) How the reading was taken (see the values). Actual 185231 AMI 71909 Estimated 6481
leak_flag bit 1 if the usage looks like a leak. False 262434 True 1187
base_charge decimal Not described yet
usage_charge decimal Not described yet
surcharge decimal Not described yet
late_fee decimal Not described yet
total_amount decimal Total billed.
due_date date Not described yet
paid_date date Not described yet
paid_amount decimal Amount paid.
status varchar(12) Bill status (see the values). Paid 257314 Open 4475 Written off 1536 Delinquent 296
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
work_orders one row per work order 25 columns

Crew work orders for city assets and streets, from the request that started them to completion.

ColumnTypeKeyMeaningValues seen
work_order_id int PK Unique work order ID.
work_order_number varchar(14) Work order number.
request_id int FK to service_requests Service request that started it (service_requests).
asset_id int FK to assets Asset ID the crew worked on.
street_id int FK to streets Not described yet
neighborhood_id int FK to neighborhoods Not described yet
department_id int FK to departments Department (departments). 5 6556 6 5971
work_type varchar(40) Type of work (see the values).
is_preventive bit 1 for scheduled preventive work. True 7159 False 5368
crew varchar(24) Not described yet
priority tinyint Priority (see the values). 4 7697 3 3160 1 1098 2 572
status varchar(12) Order status (see the values). Completed 12394 Cancelled 95 Open 26 Scheduled 9 In Progress 3
opened_date date Date opened.
scheduled_date date Not described yet
completed_date date Date completed.
labor_hours decimal Labor hours.
labor_cost decimal Labor cost.
material_cost decimal Material cost.
equipment_cost decimal Not described yet
storm_id int FK to storm_events Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
fiscal_year smallint Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
49 more tables (no notes yet)

← All databases