Why a partitioned table is never dropped by absence

Everything you have read in this course so far has been a version of the engine cannot do this safely. Partitioning cannot be changed without rewriting the table. A rebuild cannot preserve state the package never held. Real constraints, honestly reported.

This one is not that.

DROP TABLE is an ordinary statement. The engine has no objection whatsoever. You turned drop-by-absence on yourself, deliberately, and then deleted the table’s file from the package — which is precisely how you tell SchemaSmith to drop something.

And the deploy stops.

Partitioned table(s) removed from the product but NOT dropped: [dbo].[LedgerEntry]. SchemaSmith cannot
verify that data spread across partitions can be safely destroyed by DROP TABLE. Drop the table
manually after confirming the data is no longer needed, or mark it PreventDrop to keep it in the
product permanently.

Exit 2. The table is still there. Nothing about the situation was impossible — SchemaSmith just decided your package probably didn’t mean it.

Why this one is different

Modules 1 and 2 were about the tool’s limits. This is about its judgement, and that is a much bigger claim to make. A tool that overrides your explicit instruction had better have a good reason and a narrow trigger.

Here is the reason. A table is usually partitioned because it grew — years of rows, often spread across storage a DBA arranged on purpose. DROP TABLE takes all of it, and no re-deploy brings it back, because your package describes the shape and never the rows. Meanwhile, what actually triggered the drop was a file disappearing from a directory. Files disappear for a lot of reasons: a bad merge, a mis-resolved conflict, someone tidying a folder, a refactor that moved it somewhere the template doesn’t scan.

So the guard weighs a deleted file against an irreversible loss, and decides the deleted file is the likelier mistake.

And here is the part worth arguing with yourself about: it fails closed rather than skipping. A skip-with-warning would have been the polite choice — green pipeline, note in the log, deploy proceeds. It would also mean you believe that table is gone, your monitoring believes it is gone, and the warning scrolls past in CI where nobody reads warnings on green builds. Exit 2 puts the decision in front of a person while they are still looking at it.

The trigger is narrow, which is what makes it credible

LegalHold is in the same package and is not partitioned. Delete its file and it drops, without ceremony, exactly as drop-by-absence promises. The guard does not second-guess deletions in general — it fires on one specific, checkable signal that the loss would be large and unrecoverable.

That narrowness is the whole design. A tool that refuses too broadly gets routed around: people turn the setting off, or stop reading its output. This one intervenes rarely enough that when it does, you look.

Say what you meant instead

The guard is a safety net, not the mechanism. If you actually want the table kept, say so while it is still declared:

{ "Name": "[LedgerEntry]", "PreventDrop": true }

Then remove it from the package:

    Table LedgerEntry removed from product but PreventDrop is set - skipping drop (protected)

Exit 0. A marked table never reaches the guard at all — the skip is an ordinary recorded event and the run succeeds.

The marker is sticky: it survives the table leaving the package. That has to be true, because the only moment PreventDrop matters is after the declaration is gone — a marker that disappeared along with the file would protect nothing at exactly the point it was needed. Which gives you the rule worth remembering: PreventDrop is how you say “keep this.” The guard is what catches you when you forgot to.

It protects tables SchemaSmith itself built

There is an easy misreading of this guard: that it exists because a partitioned table must be somebody else’s work, so the tool stays off it. That would make the protection an accident of ignorance — and it would evaporate the moment the tool learned to partition.

It doesn’t. Module 1 had you declare partitioning, and in this module’s lab SchemaSmith created the partitioned table itself, from your package — and then refused to drop it anyway.

The reason is the one in the error message: a partitioned table holds data spread across every partition, and no declaration can tell SchemaSmith that data is disposable. That holds whoever built the table, which makes it the stronger guarantee — the protection does not depend on the tool having stayed out of the way.

Check yourself: A colleague hits this guard in CI and proposes 'fixing' it by adding a step that drops any partitioned table the package no longer declares, so the pipeline stops failing. What is wrong with that, and what should the pipeline do instead?

It reinstates precisely the behaviour the guard exists to prevent, and does it in the place where nobody is watching. The guard’s whole value is that an irreversible loss requires a human decision; automating past it means the next bad merge or mis-resolved conflict silently destroys a partitioned table on the next green build.

Notice too that the failure is doing its job: the pipeline stopped because a partitioned table left the package, and somebody should find out whether that was intended. Treating exit 2 as noise to be suppressed is the actual bug.

What the pipeline should do is nothing — the fix belongs in the package. If the table should stay, mark it PreventDrop while it is still declared, and the removal becomes clean at exit 0. If it should genuinely go, drop it deliberately in a migration script after confirming the data is not needed, then remove the declaration. Either way a person decides once, in review, and the pipeline goes green because the ambiguity is resolved rather than because it was ignored.

Three modules, three different reasons to stop. Placement, because the operation itself would rewrite your data. Rebuild guards, because the copy would lose what the package never held. And this one, because the tool read an explicit instruction, checked what carrying it out would cost, and decided the instruction was more likely to be a mistake than the loss was to be intended.

Only one of those is judgement rather than capability, and it is the one that most obviously could have been designed the other way. It is worth understanding why it wasn’t.

Next: Module 4 — unset means unmanaged, the doctrine underneath all three, and the reason you can point this tool at a database you did not build.

Until then, may the only things you lose be the ones you meant to.

— Forge

Mark this lesson complete