Assessment Data Runbook: Grade Import Staging, Grading-Scale Mapping and Transcript Reconciliation

The grade import that goes sideways almost never announces itself. It looks fine on the summary screen. Counts match. No red errors. Then three weeks later a parent calls because their kid's B+ in Chemistry became a 3.3 on the transcript when your scale says a B+ is a 3.5, and now you're pulling load logs at 6pm trying to figure out which of four possible mapping tables the importer actually used.

That's the failure mode worth designing against. Not the import that crashes — those are easy, you notice them. The dangerous ones are the imports that succeed while quietly corrupting a subset of records. This runbook covers the staging discipline, the grading-scale mapping decisions, and the transcript reconciliation scripts that turn "we think it worked" into "we verified it worked, and here's who signed off."

Why Grade Imports Fail Silently

Grade data comes from more places than most people admit. You've got the LMS gradebook export, teacher-entered marks in the SIS, standards-based scores from specialist programs, dual-enrollment grades that arrive as a college transcript PDF someone retypes, and credit-recovery platforms that use their own scale entirely. Each source has its own idea of what a "grade" is.

The silent corruption happens at the seams between these. A few patterns come up over and over:

1. 1
   Blank vs. zero vs. exempt. A missing assignment reads as blank in one system, gets imported as a literal 0, and drags a final grade down a full letter. The student earned an 88; the transcript says 79.
2. 2
   Rounding drift. The gradebook stores 89.5. The SIS rounds to 90 and calls it an A. The transcript pipeline truncates to 89 and calls it a B+. Same underlying number, three different outcomes.
3. 3
   Pass/Fail masquerading as numeric. A course flagged P/F in one system imports as a numeric 100, inflating GPA for every enrolled student.
4. 4
   Late-arriving corrections. A teacher fixes a grade after the import ran. Now the SIS and the source disagree, and nobody knows which is authoritative.

None of these throw an error. The import "works." That's the whole problem — the validation most schools run confirms that rows loaded, not that values are correct.

The Staging Layer: Never Import Straight into the Live Gradebook

The single highest-leverage change is refusing to write directly to production. Every grade file lands in a staging table first, gets checked, and only promotes to live records after it passes. If you already treat this as a governance concern, the logic overlaps with the ideas in Student Data Governance for K–12 — that's the IEP piece specifically, but the principle carries: staging is where you catch things before they become permanent.

Pre-import staging checks should run automatically the moment a file arrives and block promotion if any hard check fails. Here's what's worth running before anything touches a live record:

1. 1
   Row count reconciliation. Compare enrollment count for the course to grade-row count. A section with 27 students and 25 grade rows means two kids are about to get nothing — or two rows are duplicated.
2. 2
   Domain validation per column. Every grade value must fall inside the allowed set for that course's grading scale. A numeric 105 in a 0–100 course fails. A letter E where only A–F exist fails.
3. 3
   Null-handling classification. Force every blank to resolve to an explicit category: not-yet-graded, exempt, incomplete, or true zero. No blanks pass through unclassified.
4. 4
   Scale-tag presence. Every incoming row must carry a scale identifier (numeric-100, letter-AF, standards-4pt, P/F). Rows without a scale tag get held, not guessed.
5. 5
   Duplicate detection. Same student + same course + same term appearing twice is an automatic hold pending owner review.
6. 6
   Delta report against current live values. For any student who already has a grade on file, show old value vs. incoming value. Large swings — a full letter or more — get flagged for eyeballs.

“

Run the delta report grouped by course and section to surface scale-wide shifts quickly rather than chasing individual outliers.

That last check catches more real problems than the other five combined. When you can see that 40 students in one section all shifted down by roughly 10 points between the last import and this one, you've found a scale mismatch before it hits a transcript.

Grading-Scale Mapping: The Part Everyone Underestimates

Mapping is where the quiet damage lives. The mistake is treating scale conversion as a lookup table someone builds once and forgets. Scales carry judgment calls that need an owner and a documented decision — because reasonable people map them differently.

A common example: your district uses a 4.0 unweighted GPA, but a partner online-credit provider reports letter grades with plus/minus, and a dual-enrollment college reports on a 0–4.0 scale that already bakes in weighting. When you merge these, three separate decisions have to be made, and if they're not written down, whoever runs the next import will make different ones.

Here's the kind of mapping table that should be version-controlled and owner-signed, not living in someone's head:

Notice the B+ row. The external provider says 3.3, your district's own scale says a B+ is 3.5. That's not a bug to auto-correct — it's a policy decision. Do you honor the source's interpretation or your own? Either answer is defensible. What isn't defensible is different people answering it differently across terms, so a student's GPA shifts depending on who ran the import.

The rule that saves you: every mapping row has exactly one named owner and a note explaining any non-obvious choice. When a discrepancy surfaces later, you're not reconstructing intent — you're reading it.

A Workflow for Scale Changes

Scales don't stay static. A board adopts plus/minus grading, a new dual-credit partner comes online, standards-based reporting expands to another grade band. When a mapping needs to change, the workflow should look like this:

`` Proposed change drafted against a copy of the mapping table ↓ Run against last term's real grade file in a sandbox ↓ Pipeline produces a diff (how many GPAs change, by how much, in which direction) ↓ Diff reviewed and approved by the named decision owner ↓ Change versioned with an effective date; old version archived ``

Skip the sandbox diff and you're deploying a scale change blind — which is how a district ends up silently re-weighting three years of transcripts. The archived version matters too: any past transcript should be reproducible exactly as it was calculated at the time, not as the current mapping would calculate it.

A diagram of the workflow follows.

The archived version matters too: any past transcript should be reproducible exactly as it was calculated at the time, not as the current mapping would calculate it.

Edge-Case Test Records: Build Them Once, Run Them Forever

Before any importer or mapping change goes live, run it against a fixed set of deliberately weird records. Real production data won't reliably contain the edge cases — you have to manufacture them. Keep a permanent test fixture with these students baked in:

1. 1
   The exempt kid — has an exempt flag on two assignments; final should compute without them, not treat them as zeros.
2. 2
   The withdrawal — enrolled, then withdrew mid-term; should produce a W, not an F, and not a blank.
3. 3
   The retake — same course twice; mapping must apply your grade-replacement policy correctly.
4. 4
   The boundary case — an exact 89.5 and an exact 92.5, to prove your rounding rule does what the table says.
5. 5
   The multi-scale student — one numeric course, one P/F, one standards-based, one dual-enrollment. Confirms GPA only includes what it should.
6. 6
   The all-blank section — a course where no grades have been entered yet; should hold, not import a section of zeros.
7. 7
   The name collision — two students with the same name, different IDs; confirms matching is on ID, never name.

Every one of these has a known correct output. When you change the importer, you run the fixture and compare against expected results. If the exempt kid suddenly fails, you caught it in test instead of on a transcript.

This is the same discipline that keeps analytics honest — a lot of the reporting problems covered in School Performance Analytics: Avoid These KPI and Reporting Pitfalls trace back to bad grade data upstream that nobody validated at the point of import. The fixture doesn't take long to build once. Running it before every change costs almost nothing.

Transcript Reconciliation Scripts with Owner Sign-Offs

Staging catches problems on the way in. Reconciliation catches problems that already happened — drift between what the source system says today and what the transcript record shows. Run this on a schedule, not just after imports, because grades get edited constantly after the fact.

The core reconciliation script compares three things for every active student-course-term: the source-of-record value, the SIS live value, and the transcript-of-record value. Anywhere those three disagree, you get an exception row. Clean matches don't need a human. The exceptions do.

A practical exception queue looks like this:

1. 1
   Source ≠ SIS

   a grade changed at the source after import; needs re-import or manual correction, owner = registrar.

2. 2
   SIS ≠ Transcript

   the transcript pipeline dropped or mis-mapped a value; owner = registrar, high priority.

3. 3
   All three differ

   almost always a mapping-version mismatch; owner = data lead, investigate which scale version applied.

4. 4
   Missing transcript row

   student has a grade but no transcript entry; frequently a mid-year transfer, owner = counselor.

The sign-off piece is what makes this hold up. Each resolved exception gets closed by a named person with a timestamp and a one-line reason. When a parent disputes a GPA or an auditor asks how a transcript was produced, you have a chain: here's the source value, here's the mapping version applied, here's who reviewed the exception and when. That record is worth more than any explanation you'll improvise at 5pm on a Friday.

A Real Scenario

A mid-sized high school — around 1,400 students, three grade sources feeding transcripts — ran imports the traditional way: export, load, spot-check a few kids, move on. End of first semester, a counselor noticed a handful of GPAs looked low. Digging in, they found roughly 60 students whose credit-recovery grades had imported as raw numerics against a scale that treated anything under 70 as failing, when the recovery program's own passing threshold was 60. Students with passing grades were being reported as failures.

Unwinding it took most of a week: pulling original source files, recomputing by hand, reissuing transcripts, calling families. The fix afterward was unglamorous — a staging table, a versioned mapping with the recovery program's scale explicitly documented and owned by the registrar, and a nightly reconciliation script. The following semester, the same class of error surfaced as four held rows in the staging queue before anything promoted. Caught in minutes instead of discovered in weeks. No transcripts to reissue, no phone calls.

The shift that mattered wasn't a dramatic overhaul. It was going from "found 60 corrupted records after the fact" to "held 4 suspicious rows before they loaded." That's the entire value of staging.

When This Level of Rigor Makes Sense — and When It Doesn't

When it's worth it: multiple grade sources, any dual-enrollment or credit-recovery data, plus/minus grading, or anything feeding official transcripts and GPA. The more scales in play, the more you need documented ownership. If your imports have ever caused a transcript correction, you're past the point of debating this.

When it's overkill: a single small school, one grading scale, teachers entering directly into the SIS with no external feeds. Building six staging checks and a reconciliation script for one clean source is effort spent where the risk isn't. A solid delta report and a manual spot-check may genuinely be enough.

Who should not attempt the full build alone: whoever owns this can't be a single person who also happens to be the only one who understands the mapping. That's how you get a bus-factor of one on your entire transcript integrity. The mapping table, the sign-offs, the test fixture — those exist specifically so the knowledge doesn't live in one head.

Where Tooling Helps Without Taking Over

Most of this can be assembled with scheduled scripts and spreadsheets if someone maintains them carefully. The friction is consistency — checks that run only when someone remembers, mapping tables edited without versioning, sign-offs that live in an email thread from eight months ago.

Operational platforms with built-in staging, automated pre-import validation, and versioned reconciliation remove the "if someone remembers" dependency. Delta reports and exception queues run on schedule, and every resolution is logged with an owner automatically. The registrar still decides how a B+ maps — that judgment doesn't go away. The platform just makes sure the decision is captured, applied consistently, and reproducible when someone asks six months later.

A grade import runbook for K-12 isn't really about loading data faster. It's about being able to answer, with evidence, how a given grade became a given transcript entry — and who checked it. Get the staging, mapping, and reconciliation right, and that question stops being a scramble and starts being a lookup.

Get the staging, mapping, and reconciliation right, and that question stops being a scramble and starts being a lookup.