The database, table by table

The Chapter 70 formula term by term for every Massachusetts district, FY1993-FY2026: foundation enrollment, foundation budget, required local contribution, aid, required net school spending and what was actually spent. Thirty-four years. It answers a question nobody in town can currently put a number on -- does Lunenburg spend above or below the minimum the state requires -- and its FY2026 aid figure ties exactly to the town's own MUNIS revenue ledger.

THREE COLUMN NAMES IN THE SOURCE MEAN TWO THINGS EACH. required_nss is the workbook's column I, the formula's own arithmetic; required_nss_published is column J, what DESE publishes with carryover included, and the two differ in over a thousand district-years. ch70_aid and ch70_aid_after_penalties are columns H and M. AND nss_stage IS LOAD-BEARING: the source column is headed actualNSS for all thirty-four years, but FY2025 and FY2026 hold BUDGETED net school spending. Rule 1 -- a rate measured from an actual to a budget is partly growth and partly the step between the two. Publish the ratio to required as a measurement and never as a verdict: the required minimum is a floor, not a standard of adequacy. SCOPED to Lunenburg, its six peers and the state row, because the published database has to stay under Cloudflare's 25MB per-asset limit. Every identity the extract asserts is asserted across ALL districts before the scope is applied, and dese_ch70_statewide keeps the all-district comparison as a distribution.

First 3 rows, in rowid order:

State reimbursement for high-cost special education placements, FY2006-FY2026, split into instruction and tuition against transportation, with prior-year adjustments and relief payments separated -- so a year's receipt can be told apart from a year's entitlement. It also carries eligible_students_claimed, a count of CHILDREN.

KEYED ON FISCAL YEAR, NOT SCHOOL YEAR. Every other DESE table here is school year, and this repository has already had a fiscal-year type error that matched nothing silently. This is money the district RECEIVES and it does not appear in the general fund appropriation the town votes -- rule 11: a budget line that fell because circuit breaker rose is not a line that got cheaper. eligible_students_claimed counts CLAIMS, which is not the same set as the children in sources/data/placement-counts.csv. SCOPED to Lunenburg, its six peers and the state row, because the published database has to stay under Cloudflare's 25MB per-asset limit. Every identity the extract asserts is asserted across ALL districts before the scope is applied, and dese_ch70_statewide keeps the all-district comparison as a distribution.

First 3 rows, in rowid order:

Enrollment by grade and by selected population -- English learner, low income, economically disadvantaged, high needs, students with disabilities -- per state, district and school, back to SY1992. The denominator for nearly every rate on this site.

Filter org_level. swd_cnt is a published count of students with disabilities and it agrees exactly with dese_sped_program for Lunenburg SY2026; it does NOT agree with the denominator the staffing ratios in dese_sped_indicator use, which is twelve lower in the same year. A per-pupil figure is a division by a count whose definition has to be stated with it -- DESE publishes at least four different counts of Lunenburg's children and none of them equal, see money_gaps.

First 3 rows, in rowid order:

Where a town's resident children actually go to school, and who arrives from where, SY2014-SY2026, by reason: school choice, charter, tuitioned in several ways, METCO, foster care. Both directions of the choice question, and the headcounts behind the choice dollars on the cherry sheet.

ONE TABLE FROM TWO DATASET IDS. DESE publishes vxt3-k35x as Sending and 8xyg-59b2 as Receiving; compared row by row they hold the IDENTICAL rows and differ only in column order, so they are one measurement published twice and are loaded once. Do not treat a net position drawn from this as resting on two agreeing sources. Filter on town for children leaving, on lea for children arriving; town='Lunenburg' AND lea='01620000' is the resident children who stay. SCOPED: every row about Lunenburg in either direction plus both directions for the six peers, because 74,878 statewide rows would take the published database past Cloudflare's 25MB per-asset limit. The two source files were compared on all 74,878 rows before scoping, so the claim that they are one dataset published twice covers the whole publication.

First 3 rows, in rowid order:

The schools' own funds at one moment (FY26 period 9): per fund, revenue received, salaries paid, other expenditure, encumbered, and the balance left. Dollars.

First 3 rows, in rowid order:

One dollar total per year per stage — the district's own stated total expenses, as each document states it.

First 3 rows, in rowid order:

One dollar total per year per stage — the district's own stated total salaries.

First 3 rows, in rowid order:

Athletics money from BOTH sides, and these are three different quantities that must never be added: what the town appropriated from the general fund, what the revolving fund took IN, and what the revolving fund SPENT. Dollars per year with the basis and source document for each.

Appropriation, fund revenue and fund spending are three quantities. Never add two of them.

First 3 rows, in rowid order:

The district's LINE LIST — no money at all. One row per budget line with its section, its function group and what kind of line it is. This is the dimension budget_figure and workbook_figure hang their dollars on.

with its section and function group.

First 3 rows, in rowid order:

Advanced Placement PARTICIPATION and PERFORMANCE for Lunenburg, SY2007-SY2025: how many students sat AP, how many tests, the distribution of tests per student, and the score distribution 1-5. Extracted from two 174MB statewide DESE workbooks filtered to LEA 01620000.

kind separates the PARTICIPATION extract from the PERFORMANCE extract and they are different quantities in one file. stu_grp OVERLAPS -- "All Students" sits beside race, gender and selected-population groups and they may never be summed. org_type separates district from school. A subject absent for a year is not established to be a published zero; do not read it as one without checking the extractor. Tests are not students: tests_taken_cnt counts sittings and test_takers_cnt counts children. kind is a SECOND level column: participation and performance are two publications in one table and each leaves the other’s figure columns NULL. District and school rows are the SAME SIZE here (4,953 each) because Lunenburg’s AP is all at one school, so summing them doubles silently.

First 3 rows, in rowid order:

The share of students who LEFT, by grade, SY2010-SY2026 -- which is the dataset that answers "which grades are students leaving in, over time". Every figure is a PERCENTAGE, not a count.

RATES, NOT COUNTS -- g01_pct through g11_pct and grd_all are percentages, so they may not be summed or averaged across grades without weighting by the enrolment in each grade. stu_grp OVERLAPS ("All Students" beside race and selected-population groups). A rate off a small grade cohort moves violently; read the year-by-year before concluding a trend. Attrition counts leaving for any reason, so it does not separate a move out of town from a transfer to another school.

First 3 rows, in rowid order:

The aid build-up, term by term, FY2007 on: every increment the Chapter 70 formula adds between the foundation budget and the aid actually paid, and every reduction it applies. This is what lets anybody model how the aid RESPONDS to a change in enrollment rather than assuming it moves linearly.

level separates the state row from the districts. Its ch70_aid agrees with dese_ch70_formula in every district-year they share, checked on every build -- they are two sheets of one workbook, not two sources. The increments are the formula's own machinery and several are zero in most years by design; a zero here is a term that did not apply, not a missing figure. SCOPED to Lunenburg, its six peers and the state row, because the published database has to stay under Cloudflare's 25MB per-asset limit. Every identity the extract asserts is asserted across ALL districts before the scope is applied, and dese_ch70_statewide keeps the all-district comparison as a distribution.

First 3 rows, in rowid order:

HOW MANY CLASSES RAN, and how full they were, by school and by subject, SY2011-SY2025. The only table here that counts what was TAUGHT rather than who was employed, what was appropriated or how many children enrolled -- so it is what answers "did the cuts change what a child can take" without reaching for teacher FTE, which cannot tell four Spanish sections from two.

THREE SPLITS BEFORE ANY SUM. org_type separates the DISTRICT rollup from its own schools. subj = "All" is a rollup of the 25 named subjects beside it. And the fifteen subjects prefixed "CH74 - " are a SECOND, PARALLEL classification -- Chapter 74 vocational programme areas, every Lunenburg row of them zero -- which are not part of the subject total. THE THREE FIGURE COLUMNS ARE THREE QUANTITIES: tot_clss_cnt is classes that ran, avg_clss_cnt is DESE’s own average class size (seats over classes), and tot_stu_cnt is DISTINCT students who took the subject -- NOT seats, and not the product of the other two. sy is the school year ENDING and equals fy in every other DESE table here; it keeps the publisher’s own column name. AND A SCHOOL NAME IS NOT A FIXED SCHOOL: Lunenburg High held grades 8-12 from SY2013 to SY2016 and grades 9-12 either side, so join dese_enrollment for the grade span before comparing two years. The demographic percentage columns OVERLAP each other and may never be added. Two school-years report an average class size no primary school can have -- 87.1 in SY2011 and 36.5 in SY2022, both Lunenburg Primary -- so the elementary series is not trendable.

First 3 rows, in rowid order:

HEADCOUNT by job class -- Administrator, Teacher, Paraprofessional, Other Licensed, Other Non-Licensed -- with hires and retention, SY2021-SY2023. The only DESE table here that counts administrators and paraprofessionals as PEOPLE rather than as FTE or as dollars.

race_level is the level column and getting it wrong doubles every figure. All Educators is a TOTAL row sitting beside the seven reported races; summing the whole job class gives exactly twice the truth, and this project's own notes recorded 118 paraprofessionals and 38 administrators for SY2023 that way when the file holds 59 and 19. There is no all-job-classes row, so a district total is the sum of the five race_level='all' rows and no published figure states it. Headcount is not FTE: a half-time director and a principal are one each. Three years is a thin series on a base of tens, and a reclassification would look identical to a change in staffing.

First 3 rows, in rowid order:

DESE's spending by function code with the GENERAL FUND and GRANTS/REVOLVING money in separate columns, SY2009-SY2025, for Lunenburg and its six peers. The only published split of the two in this archive: Lunenburg SY2025 shows $478,097 of grant and revolving money against function 2330, Paraprofessionals, beside $1,338,477 of general fund.

Filter on level before aggregating ANYTHING. The source puts rollups beside detail and nothing in its column names says which is which -- summing every Lunenburg SY2025 row gives $117,996,913 against an in-district total of $27,903,187. level='total' is TTPP; level='rollup' is IIII and OODD; level='category' is the ten in-district categories plus ODTR and COMM; level='detail' is the printed function codes, plus TUIT, which has no category row. ODTR is already inside OODD -- adding it again double counts -- and its detail is only ever code 9130, a component and not the whole, which is what reconciles='partial' means. This is DESE's definition of spending, all funds, and it is NOT the town's appropriation; it must never be subtracted from one. A function code is not a budget line and not a post, so this bounds the funding-source question rather than settling it.

First 3 rows, in rowid order:

Special education indicators SY2017-SY2026: the context counts, the special education staffing ratios per 100 students with disabilities, and the outcome, assessment and postsecondary measures, each with students with disabilities set beside students without.

Three things. (1) grades_level says all or subset and THE SUBSETS OVERLAP -- Grade 10 is inside Grades 9-12 is inside K-12 -- so no sum across grades means anything. (2) denominator_cnt is the population the percentage is OF; the indicator's own figure is measure_cnt, and on the SPECIAL EDUCATION STAFF rows measure_cnt is the students-with-disabilities denominator while measure_pct is the FTE per 100 of them. (3) value_type says whether a row is a percentage, an average, an FTE or an FTE per 100 -- read it before doing arithmetic. The staffing ratios times the count are what produce the unresolved $69,161 per paraprofessional FTE; see money_gaps. printing is part of the key -- DESE publishes a handful of these rows twice -- so collapse on it before aggregating.

First 3 rows, in rowid order:

Caseload movement: how many children entered special education services in a year and how many left. The dynamic behind the spending line that a headcount alone cannot show. SY2019-SY2025.

TWO TRAPS. repeats_prior_year is yes where all four figures exactly equal the same district's previous year -- Lunenburg's SY2024 and SY2025 are such a pair, and four independently counted quantities landing identically two years running is not plausible where a row carried forward is. Never sum two such years as two years of movement. And grade_rows_sum is NOT enrolled_cnt: the grade rows are not the K-12 row broken down, because the Grade 12 row is a fraction of the size of the others. What the residual is has not been established.

First 3 rows, in rowid order:

What the students with disabilities are: disability type, race, gender, grade span, in district against out of district, placement, and the special education FTE ratios. SY2020-SY2026. It carries a PUBLISHED count of students with disabilities, which used to be reachable here only by multiplying a percentage by enrollment.

indicator_level is total or member and every category carries its own total row. Three categories do not behave like the others: Placement runs SHORT of its own total because its four members are IN-DISTRICT placements and children placed out of district appear in the total and in none of them; Disability Type is a collapsed rendering of Disability Type All with several disabilities folded into Other Disability, so adding the two counts every child twice; and Special Education FTEs per 100 SWDs is a RATIO despite sitting in the same column and despite value_type saying Percent on every row of this file. printing is part of the key: 95 rows are published twice, most of them the same district under two different names (Ayer Shirley and Ayer Shirley School District share one org code). Collapse on it before aggregating.

First 3 rows, in rowid order:

Where a child's placement started and where the same children are now -- no IEP, included, substantially separate, or out of district. SY2018-SY2026. Out-of-district tuition is the largest single line in the special education budget and this is the pathway into it.

THE BASES ARE TINY. A Lunenburg cohort is tens of children, so a percentage here must never travel without its count -- 14.3% of 28 is four children, and one year of one cohort establishes nothing. grade_span K-12 and K-2 OVERLAP and must never be summed. unaccounted_cnt is the cohort less the four destinations: what it IS -- moved away, left for a private school, aged out, suppressed -- is not established by this file.

First 3 rows, in rowid order:

Teacher FTE by grade band AND subject AND school. This breaks a limit recorded here as structural: the town's staff rosters give grade detail with no FTE, and DESE elsewhere gives FTE with no grade. This gives both.

Two level columns and both matter: org_level and subject_level. Summing every subject for one organisation counts each teacher about three times over -- the build asserts that it would, so a lost level column fails there rather than in your query. agrees_with_teacher_subject is written on the All rows only and says whether this dataset and dese_teacher_subject state the same total FTE for that organisation; where they do not, BOTH are kept and neither corrects the other. A SUBJECT ABSENT FROM THIS TABLE for an organisation and year we hold is a PUBLISHED ZERO, not a gap -- rows where every grade band and the total are nought are not written, and they outnumber the real rows two to one. That is the opposite of the usual reading here, which is why it is said twice. printing is part of the key: DESE publishes the same organisation, subject and year twice with different figures in a small number of cases, and both are kept.

First 3 rows, in rowid order:

Teacher FTE split into general education, special education, career/technical and English learner, per state, district and school, SY2008-SY2026, for Lunenburg and its six peers. This is where Lunenburg's special education teacher FTE falls from 18.5 to 2.0 while its total FTE holds flat.

Filter org_level before summing ANYTHING: state, district and school rows sit in one table. A DISTRICT ROW IS NOT THE SUM OF ITS SCHOOL ROWS -- staff with no school assignment have a district row and no school row, and the gap reaches several hundred FTE in the largest districts, so the two must not be added or differenced. FTE here is EPIMS FTE, defined per ASSIGNMENT rather than per person, so it is not comparable with a budget line that pays whole salaries. The sped collapse is the signature of RECODING; nothing here establishes that staff left.

First 3 rows, in rowid order:

Every budget line × year × stage as EXTRACTED, before disagreements between documents are resolved. Dollars. This is the raw reading; budget_figure is the settled one.

every year

First 3 rows, in rowid order:

The district's budget book in ITS OWN shape: one row per line with every year's column side by side (FY23 actual through the four FY27 scenarios) rather than unpivoted. Includes the workbook's own comments column, which is where the district explains a line to itself.

in its own shape and column order.

First 3 rows, in rowid order:

Out-of-district special education tuition in DOLLARS, split private vs collaborative, per year per stage. Not a count of children — placement_counts is that, and the two do not divide into each other.

private and collaborative.

First 3 rows, in rowid order:

Budgeted DOLLARS for special education paraprofessionals, split by school, per year per stage. A budget line, never a headcount.

A budget line, not a headcount.

First 3 rows, in rowid order:

Budgeted DOLLARS for special education teachers, split by school, per year per stage. A budget line, never a headcount.

A budget line, not a headcount.

First 3 rows, in rowid order:

Budgeted DOLLARS for special education transportation, per year per stage.

First 3 rows, in rowid order:

What the district said in writing it was cutting, cycle by cycle, FY2020 to FY2027 — the position, the school, the FTE and the dollar figure WHERE THE DOCUMENT PRINTS THEM, and the district’s own stated consequence, quoted. Every row is a CLAIM: a reduction list is a document somebody assembled to argue for a budget (rule 13a), so basis is stated on all 193 rows and none of it is evidence that a post was removed. direction separates a reduction from a restoration or an addition; conditional_on records that FY2025 has TWO lists, one for each outcome of a ballot question that passed. /cut-register sets DESE’s teacher counts beside it.

NEVER aggregate across documents without splitting on doc_date: a position named on three lists in one cycle is three rows, because the register keeps every draft. Most rows carry no FTE and no amount because the document printed none, and a blank is an absence in the SOURCE rather than in the extract.

First 3 rows, in rowid order:

The FY27 budget SCENARIOS side by side -- the four whole budgets the district published (Balanced, Core Budget, Level Service, Restoration), 250 lines each. Every row of budget_figure whose variant is not blank, which is exactly the figures that belong to a named scenario rather than to the year.

These are FOUR ALTERNATIVE BUDGETS for the same year, not four parts of one. Summing across variant counts the same year four times; always group by variant. None of them is the year's budget -- the un-scenarioed figures are the ones with variant = '', which this view deliberately excludes. A scenario is a proposal, never something that happened.

No rows. That is the honest state of this table, not a load that failed — see its entry in SCHEMA.md.

Budget against what was actually spent, one row per line per year: the settled budget, the proposed budget and the actual (stage restated) side by side, FY2014-FY2027. This is the starting point for asking which lines underspend or overspend their budget. Scenario figures are excluded -- variant = '' only.

documents_disagree IS WRONG AS A BUDGET-VS-ACTUAL FLAG. It is MAX() across ALL stages, so a line where two documents disagree about the PROPOSED column is reported as a line whose budget and actual are disputed. Of the disagreeing line-year-stage cells, 254 are proposed against 45 settled and 35 restated, and the view flags 322 of its 4,506 rows on that basis. scripts/build_variance_charts.py does NOT use this view: it pivots the flag PER STAGE and records that the difference moved every figure on the budget-vs-actual page by about 15%. Do the same, or read the flag from budget_figure with a stage filter. Separately: a NULL column is a line the documents state at only one stage, not a zero.

No rows. That is the honest state of this table, not a load that failed — see its entry in SCHEMA.md.

Budget against actual TAKEN FROM ONE DOCUMENT -- the district's own workbook, matching a budget or final_budget column to the actual column on the same worksheet row, same year, same document. under_budget is budget minus actual. 245 lines, FY2025.

The join is doc_id AND row AND fy, so both figures come from the SAME sheet and the same printed row -- which is the like-for-like comparison, but it also means the coverage is whatever that one workbook prints, not the district's whole budget. row_kind = 'line' excludes subtotals, so these do not add to any printed total. under_budget is a difference between two columns of one document; it is not a statement about what was appropriated. And rule 13a applies: a hand-built workbook is an argument, not a printout from the books -- check document_basis for what produced this one.

No rows. That is the honest state of this table, not a load that failed — see its entry in SCHEMA.md.

Where Lunenburg sits among every Massachusetts district on four ratios: net school spending as a share of what the state requires, Chapter 70 aid per foundation pupil, the required local contribution as a share of the foundation budget, and circuit breaker claim per eligible student. Min, quartiles, median, max, Lunenburg's own figure and its rank.

Read the denominator before quoting a rank -- districts is it, and it counts only districts that reported the measure that year. basis says on every row what the ratio is OF, because each is a division by a count or a dollar whose definition has to travel with it: foundation enrollment is not a headcount of children in the buildings, and the circuit breaker rows are FISCAL year where the rest are the Chapter 70 fiscal year. Publish the share-of-required as a measurement and never as a verdict -- the required minimum is a floor, not a standard of adequacy, and a district above it is not thereby adequately funded.

First 3 rows, in rowid order:

Where Lunenburg sits among all Massachusetts districts on per-pupil spending, function by function, SY2009-SY2025 -- min, quartiles, median, max, Lunenburg's own figure and its rank. Also the statewide dollar totals and the share of each function paid by grants and revolving money rather than the general fund.

Read the denominator before quoting a rank. districts is the denominator and it INCLUDES charter and virtual districts, which are in DESE's file and are not municipal school districts. per_pupil_basis says on every row whether a per-pupil figure exists at all: DESE prints 0 against every out-of-district row, so the quartiles are blank there rather than a median of zeros. Filter on level here too -- a category and its own detail are separate rows.

First 3 rows, in rowid order:

Teacher FTE by subject, with the share licensed, the share teaching in field, the share experienced and the students-per-teacher ratio, SY2008-SY2026.

teacher_fte IS AN FTE even though DESE names the column TCHR_CNT. Filter subject_level: all is every teacher, group is Core-All Subjects and Total Non-Core Academic Subjects which are groups of the subjects beside them, and the members therefore DO NOT PARTITION -- summing subjects double counts. students_per_teacher is parsed from the sentence DESE prints, which is kept beside it in student_teacher_ratio_printed. A SUBJECT ABSENT FROM THIS TABLE for an organisation and year we hold is a PUBLISHED ZERO, not a gap: rows with no FTE at all are not written, and they outnumber the real ones.

First 3 rows, in rowid order:

TRANSACTIONS — the athletics revolving fund line by line: effective and posting dates, journal, references, vendor, amount, running balance, check and warrant numbers, and the comments the clerk typed. The only transaction-level data in the archive, obtained by records request.

from a records request.

First 3 rows, in rowid order:

The school department's own MUNIS year-to-date budget reports at the CLOSE of each fiscal year (period 13): original and revised budget, expended, encumbered and available, per account. Read from the published spreadsheets the Town delivered on 6 October 2026, tied to every total the sheet prints.

Fiscal year and period come from the FILENAME -- the workbooks state neither. Period 13 is the closed year; period 12 is June before the closing entries.

First 3 rows, in rowid order:

The FY2026 trial balance of fund 1300 per account: beginning balance, debits, credits, net change, ending balance -- as the MUNIS report prints it.

Fund 1300 only. One fiscal year (FY2026); the workbook's own cell B5 states the year and is checked against the filename.

First 3 rows, in rowid order:

Every journal line behind the fund 1300 trial balance: period, journal, source, effective date, reference, debits, credits, running balance. What moved each fund 1300 account, line by line.

is_soy_opening_balance marks the start-of-year opening entry, which is a balance and not an activity -- exclude it when summing a year's movement.

First 3 rows, in rowid order:

The published fee a family pays to play: dollars per fiscal year × school level × item, with the date it was set and the document that set it. Includes family caps and sibling discounts.

by level and year.

First 3 rows, in rowid order:

Per sport × season × level: participation COUNTS and DOLLAR figures mixed in one table, told apart by metric. raw keeps the value exactly as printed and is_numeric says whether it parsed, because some cells are text.

First 3 rows, in rowid order:

Not data — checks. For each total the source prints, what our rows sum to, the difference, and whether it ties.

First 3 rows, in rowid order:

Grant AWARDS: name, amount in dollars, who holds it, and the fiscal year — except one ESSER award that genuinely spans FY21–24, which is why fy is TEXT here.

by name and amount.

First 3 rows, in rowid order:

The same awards with their source addresses and checksums, plus whether documents disagree about an amount and whether it is ESSER money.

fy is TEXT — see grant_award.

First 3 rows, in rowid order:

Not figures — a report on extraction. Per source document: how many data rows, how many carried a usable layout, how many fell short of the expected column count, and why a document was or was not usable.

First 3 rows, in rowid order:

One row per disagreeing READING: where two documents state different dollar amounts for the same line-year, both values are kept with their source and the spread between them. Nothing here is resolved.

Kept rather than resolved. Which document is right is often not established.