Downloads

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.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Analyze 311 requests, snow operations and dispatch in SQL.
  • Map parcels and permits, and use the July to June fiscal year.
  • Work through the lodging tax and water utility tables.
  • Build fund accounting summaries across the city's funds.

bigbear is municipal-style data: the operating records of a city, in the way a city manager, finance director or public works superintendent would see them. The city is Big Bear Lake, California, a small mountain resort city in the San Bernardino Mountains at 6,750 feet. About 75 employees run streets, a municipal water utility and development services; police and fire are contracted; and the economy is built on visitors, second homes and short-term rentals. It covers fiscal 2022 through 24 October 2026 (the first months of fiscal 2027) in 61 tables and about 586,000 rows.

It is built for local-government analytics: 311 requests, snow operations, dispatch, permits and code enforcement, a water utility, the lodging tax, property and parcels, fund accounting and council records, all on a map and on a July-June fiscal year.

These are invented records. The place, the seasons and the kind of work are real and recognizable, but every parcel, owner, employee, vendor, call, permit and dollar amount is made up. Neighborhoods and the lake road follow the real layout only approximately, street names are typical of the area but house numbers and lot lines are invented, and tax rates, fees, budgets and ballot measures are fictional. Use it for learning, not for navigation or for any question about a real property or the real city's finances.

Connect to it

One engine drives the city: visitors and weather

weather_daily has a synthetic mountain climate and a visitor_index for every day (1.0 is an average day). Winter weekends, powder days, the summer season and holiday weeks run high; a blizzard or a road closure drops it. Almost everything else follows it, so the data behaves like a real tourist town:

Tables at a glance

Area Tables What they hold
Geography and calendar neighborhoods, zoning_districts, fiscal_calendar, geo_states, geo_places, geo_postal_codes 12 neighborhoods in the city and 6 outside it, zoning, the July-June calendar with budget phases, tax installment dates and seasons
Organization funds, departments, facilities 10 funds, 13 departments (two merged during the period), 32 facilities with addresses and coordinates
People job_classes, positions, employees, pay_periods, payroll_records Authorized positions, 174 employees over the years (including seasonal snow crews), biweekly payroll with overtime split into storm overtime
Governance council_members, council_meetings, agenda_items, council_votes Five seats with turnover, about 137 meetings, agenda items and every member's vote
Finance gl_accounts, gl_journal_entries, gl_journal_lines, budgets, vendors, invoices, grants, capital_projects A balanced fund ledger, adopted and amended budgets, accounts payable, 12 grants and 29 capital projects
Streets and property streets, street_condition_surveys, snow_plow_routes, property_owners, parcels, parcel_assessments, property_transfers 886 street segments with endpoints, pavement condition surveys, 24 plow routes, 8,643 parcels with owners, six years of assessed value and 2,000 transfers
Assets asset_types, assets Hydrants, valves, streetlights, signals, drains, signs, restrooms and more, each with coordinates
Water utility water_rates, utility_accounts, utility_bills Tiered rates by fiscal year, accounts that turn over when a home sells, 260,000 bimonthly bills
Weather and snow weather_daily, storm_events, snow_operations, fleet_vehicles, fleet_maintenance Daily weather and visitors, 30 storms, plow shifts, the fleet
311 and work orders service_request_types, service_requests, work_orders 18,600 requests with channel and location, and the work orders they create plus preventive programs
Dispatch cad_call_types, cad_units, cad_calls 30,000 Fire/EMS and Sheriff calls with every timestamp from receipt to clear
Community development permit_types, permits, inspections, code_violation_types, code_cases 5,800 permits, 27,000 inspections, code cases from 311, patrols and defensible-space inspections
Tourism economy short_term_rentals, tot_operators, tot_filings, businesses, business_licenses, special_events 1,400 rentals, lodging operators, 78,000 monthly tax filings, local businesses and licenses, festivals and parades

dataset_info holds the as-of date and a one-line description. Nine views save you the common joins: vw_budget_vs_actual, vw_fund_balances, vw_response_times, vw_service_request_sla, vw_monthly_tot, vw_parcel_current, vw_permit_cycle_times, vw_water_billing_monthly and vw_storm_cost_summary.

How the tables join

neighborhoods ──< parcels >── zoning_districts          property_owners ──< parcels ──< parcel_assessments
                     │                                                         └──< property_transfers
streets ──< parcels   streets ──< assets >── asset_types      snow_plow_routes ──< streets
                     │
parcels ──< utility_accounts ──< utility_bills
parcels ──< short_term_rentals ──< tot_operators ──< tot_filings        businesses ──< tot_operators, business_licenses
parcels ──< permits ──< inspections       parcels ──< code_cases ──< inspections     violation types ──< code_cases
service_request_types ──< service_requests ──< work_orders >── assets        service_requests ──< code_cases
cad_call_types ──< cad_calls >── cad_units        storm_events ──< snow_operations, service_requests, work_orders

funds, departments, gl_accounts ──< gl_journal_lines >── gl_journal_entries        vendors ──< invoices >── capital_projects >── grants
council_meetings ──< agenda_items ──< council_votes >── council_members
employees ──< payroll_records >── pay_periods      positions ──< employees      fiscal_calendar joins on any date

Rules built into the data

Things that happen in the data

Questions to try

Does a busier town mean more dispatch calls? Group days by the visitor index:

SELECT CASE WHEN w.visitor_index < 0.7 THEN '1 quiet (under 0.7)' WHEN w.visitor_index < 1.3 THEN '2 normal' ELSE '3 busy (1.3 and up)' END AS day_type,
       COUNT(*) AS days, ROUND(AVG(c.calls * 1.0), 1) AS avg_dispatch_calls, ROUND(AVG(w.visitor_index), 2) AS avg_visitor_index
FROM (SELECT CAST(received_at AS DATE) AS call_date, COUNT(*) AS calls FROM cad_calls GROUP BY CAST(received_at AS DATE)) c
JOIN weather_daily w ON w.weather_date = c.call_date
GROUP BY CASE WHEN w.visitor_index < 0.7 THEN '1 quiet (under 0.7)' WHEN w.visitor_index < 1.3 THEN '2 normal' ELSE '3 busy (1.3 and up)' END
ORDER BY day_type;

Busy days see roughly twice the calls of quiet days.

What does the lodging tax look like month by month? The view already rolls up filings:

SELECT period_month, SUM(filings) AS filings, ROUND(SUM(tax_due), 0) AS tax_due,
       ROUND(SUM(occupied_nights) * 100.0 / SUM(available_nights), 1) AS occupancy_pct
FROM vw_monthly_tot
WHERE fiscal_year = 2026
GROUP BY period_month
ORDER BY period_month;

The winter holidays and the summer peak stand out; April and the fall are the troughs.

Where does the General Fund's money come from?

SELECT a.account_number, a.account_name, ROUND(SUM(l.credit - l.debit), 0) AS revenue
FROM gl_journal_lines l
JOIN gl_journal_entries e ON e.entry_id = l.entry_id
JOIN gl_accounts a ON a.account_id = l.account_id
WHERE e.fiscal_year = 2026 AND l.fund_id = 1 AND a.account_type = 'Revenue' AND a.account_number <> '4900'
GROUP BY a.account_number, a.account_name
ORDER BY revenue DESC;

The lodging tax is the largest single source, ahead of property tax and sales tax.

Did departments stay inside their budgets?

SELECT department_name, ROUND(SUM(adopted_amount), 0) AS adopted, ROUND(SUM(amended_amount), 0) AS amended, ROUND(SUM(actual_amount), 0) AS actual,
       ROUND(100.0 * SUM(actual_amount) / SUM(amended_amount), 1) AS pct_of_amended
FROM vw_budget_vs_actual
WHERE fiscal_year = 2026 AND fund_code = '001' AND account_type = 'Expense'
GROUP BY department_name
ORDER BY actual DESC;

Who owns the homes? Share of owner-occupied and absentee owners by neighborhood, with the active rentals:

SELECT n.neighborhood_name, COUNT(*) AS homes,
       ROUND(100.0 * SUM(CASE WHEN p.is_owner_occupied = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS owner_occupied_pct,
       ROUND(100.0 * SUM(CASE WHEN o.is_local = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS absentee_owner_pct,
       SUM(CASE WHEN s.str_id IS NOT NULL THEN 1 ELSE 0 END) AS short_term_rentals
FROM parcels p
JOIN neighborhoods n ON n.neighborhood_id = p.neighborhood_id
JOIN property_owners o ON o.owner_id = p.owner_id
LEFT JOIN short_term_rentals s ON s.parcel_id = p.parcel_id AND s.status = 'Active'
WHERE p.land_use IN ('Single Family Residence', 'Condominium', 'Townhome')
GROUP BY n.neighborhood_name
ORDER BY absentee_owner_pct DESC;

Resort areas such as Moonridge and the lakefront are mostly absentee; the residential hillsides are the most local.

Where do the owners live?

SELECT o.city AS owner_city, o.state AS owner_state, COUNT(*) AS parcels, ROUND(SUM(a.net_assessed_value) / 1000000.0, 1) AS assessed_value_millions
FROM parcels p
JOIN property_owners o ON o.owner_id = p.owner_id
JOIN parcel_assessments a ON a.parcel_id = p.parcel_id AND a.fiscal_year = 2026
WHERE o.is_local = 0
GROUP BY o.city, o.state
ORDER BY parcels DESC
OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;

What did each big storm cost?

SELECT storm_name, severity, total_snowfall_in, plow_shifts, ROUND(overtime_hours, 0) AS overtime_hours, ROUND(abrasive_tons, 0) AS abrasive_tons,
       work_orders, ROUND(work_order_cost, 0) AS work_order_cost, service_requests
FROM vw_storm_cost_summary
ORDER BY total_snowfall_in DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;

How much of public works overtime is storm overtime?

SELECT d.department_name, p.fiscal_year, ROUND(SUM(p.overtime_pay), 0) AS overtime_pay,
       ROUND(100.0 * SUM(p.storm_overtime_hours) / NULLIF(SUM(p.hours_overtime), 0), 1) AS storm_share_pct
FROM payroll_records p
JOIN departments d ON d.department_id = p.department_id
WHERE d.department_code IN ('PW', 'DWP', 'PRK')
GROUP BY d.department_name, p.fiscal_year
ORDER BY d.department_name, p.fiscal_year;

How fast do police and fire respond, and what does snow do to it?

SELECT agency, CASE WHEN weather_snowfall_in >= 4 THEN 'snow day (4+ in)' ELSE 'otherwise' END AS conditions, COUNT(*) AS calls,
       ROUND(AVG(response_seconds) / 60.0, 1) AS avg_response_min,
       ROUND(100.0 * SUM(CASE WHEN response_seconds <= 480 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_8_min
FROM vw_response_times
WHERE priority <= 2 AND response_seconds IS NOT NULL AND jurisdiction = 'City of Big Bear Lake'
GROUP BY agency, CASE WHEN weather_snowfall_in >= 4 THEN 'snow day (4+ in)' ELSE 'otherwise' END
ORDER BY agency, conditions;

Which request types meet their service level?

SELECT category, COUNT(*) AS requests, ROUND(100.0 * SUM(met_sla) / COUNT(*), 1) AS pct_met_sla, ROUND(AVG(hours_to_close), 1) AS avg_hours_to_close
FROM vw_service_request_sla
WHERE fiscal_year = 2026 AND hours_to_close IS NOT NULL
GROUP BY category
ORDER BY requests DESC;

Which short-term rentals draw the most noise complaints?

SELECT s.str_permit_number, p.address_line1, n.neighborhood_name, s.max_occupancy, COUNT(*) AS noise_complaints
FROM service_requests q
JOIN service_request_types t ON t.type_id = q.type_id
JOIN parcels p ON p.parcel_id = q.parcel_id
JOIN neighborhoods n ON n.neighborhood_id = p.neighborhood_id
JOIN short_term_rentals s ON s.parcel_id = p.parcel_id
WHERE t.type_name LIKE 'Noise complaint%' AND q.status <> 'Duplicate'
GROUP BY s.str_permit_number, p.address_line1, n.neighborhood_name, s.max_occupancy
ORDER BY noise_complaints DESC
OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;

How long do permits take compared with the target?

SELECT type_name, COUNT(*) AS permits, ROUND(AVG(review_days * 1.0), 1) AS avg_review_days, MAX(review_days_target) AS target_days,
       ROUND(100.0 * SUM(CASE WHEN review_days <= review_days_target THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_on_target
FROM vw_permit_cycle_times
WHERE review_days IS NOT NULL AND fiscal_year IN (2024, 2025, 2026)
GROUP BY type_name
ORDER BY permits DESC
OFFSET 0 ROWS FETCH FIRST 8 ROWS ONLY;

Which neighborhoods have the worst roads? Length-weighted pavement condition (0 to 100) from the latest survey:

SELECT n.neighborhood_name, COUNT(*) AS segments, ROUND(SUM(s.length_ft) / 5280.0, 1) AS miles,
       ROUND(SUM(s.last_pci * 1.0 * s.length_ft) / SUM(s.length_ft), 1) AS length_weighted_pci
FROM streets s
JOIN neighborhoods n ON n.neighborhood_id = s.neighborhood_id
WHERE s.maintained_by = 'City' AND s.last_pci IS NOT NULL
GROUP BY n.neighborhood_name
ORDER BY length_weighted_pci;

Did the snow cause estimated water reads? Buried meter boxes after the big storms show up as estimates:

SELECT YEAR(bill_date) AS yr, MONTH(bill_date) AS mo, COUNT(*) AS bills,
       ROUND(100.0 * SUM(CASE WHEN read_type = 'Estimated' THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_estimated
FROM utility_bills
WHERE bill_date >= '2023-01-01' AND bill_date < '2023-08-01'
GROUP BY YEAR(bill_date), MONTH(bill_date)
ORDER BY yr, mo;

Estimates jump in the January and March 2023 billing cycles, right after the big storms, and fall back to the usual 2 percent in the cycles between.

Which agenda items split the council?

SELECT i.title, m.meeting_date, SUM(CASE WHEN v.vote = 'Yes' THEN 1 ELSE 0 END) AS yes_votes, SUM(CASE WHEN v.vote = 'No' THEN 1 ELSE 0 END) AS no_votes, i.outcome
FROM agenda_items i
JOIN council_meetings m ON m.meeting_id = i.meeting_id
JOIN council_votes v ON v.agenda_item_id = i.agenda_item_id
GROUP BY i.agenda_item_id, i.title, m.meeting_date, i.outcome
HAVING SUM(CASE WHEN v.vote = 'No' THEN 1 ELSE 0 END) >= 2
ORDER BY m.meeting_date DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;

When are property tax payments due? The fiscal calendar carries the installment and apportionment dates:

SELECT calendar_date, fiscal_period_name, tax_installment, budget_phase
FROM fiscal_calendar
WHERE fiscal_year = 2026 AND tax_installment IS NOT NULL
ORDER BY calendar_date;

All of these run on SQL Server and MariaDB.

Maps and fiscal periods

SELECT ROUND(latitude, 3) AS lat, ROUND(longitude, 3) AS lon, COUNT(*) AS complaints
FROM service_requests q
JOIN service_request_types t ON t.type_id = q.type_id
WHERE t.type_name LIKE 'Noise complaint%' AND q.latitude IS NOT NULL AND q.fiscal_year = 2026
GROUP BY ROUND(latitude, 3), ROUND(longitude, 3)
ORDER BY complaints DESC
OFFSET 0 ROWS FETCH FIRST 6 ROWS ONLY;

Excel and Power BI

Realistic imperfections

Government data is messy, and so is this: duplicate 311 requests (duplicate_of_request_id), requests with no coordinates or a parcel that never matched, mixed-case free-text addresses, requests that were never closed, request types that still point at a department that has merged away, vendors entered twice with different spellings, rentals that file late or never, audit adjustments, permits that expire or are withdrawn, inspections cancelled by snow, estimated water reads, written-off water bills and cases that stay open.

Try it on the data

Open the bigbear database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.