Compiling isn't proof: validate state-changing SQL against the real schema

"It compiled and the first batch ran fine" is the most expensive false comfort in data work.

C# compiling tells you nothing about the SQL strings inside it. A mistyped column name, a wrong table, a bad cast - all compile clean and fail only when the query actually runs. And "the first batch ran" is often worse than no evidence at all, because the first batch usually exercised the old code path, not the new one you're trying to validate. That was exactly the shape of a recent change to a sync pipeline: I'd rewired the truncate/reset into a new begin-refresh method and added a whole verification subsystem around it - an insert, a get-active read, a finalize update - none of which had executed even once. The build was green. The build is always green.

The cheap insurance is to run the new state-changing statements against the actual deployed schema, inside a transaction you roll back:

BEGIN TRAN;

INSERT INTO dbo.SyncVerification (SyncType, Status, StartedAtUtc)
VALUES ('FullSync', 'InProgress', GETUTCDATE());
DECLARE @id int = SCOPE_IDENTITY();

SELECT [step] = 'in_tran', Status
FROM dbo.SyncVerification WHERE VerificationId = @id;

UPDATE dbo.SyncVerification
SET Status = 'Complete', CompletedAtUtc = GETUTCDATE()
WHERE VerificationId = @id;

ROLLBACK;

SELECT [step] = 'after_rollback', [rows] = COUNT(*)
FROM dbo.SyncVerification;   -- unchanged

The insert, the select, the update all execute - proving every table name, column, and cast is real against the real schema - and nothing persists. I ran that batch against the deployed dev database and then again against UAT: the in-transaction selects proved the statements, and the after-rollback counts proved no trace was left. This isn't a trick I invented, either - it's the operating premise of tSQLt, which wraps every database unit test in a transaction and rolls it back on completion. The ad-hoc BEGIN TRAN / ROLLBACK batch is the sixty-second version of the same idea, and it catches exactly the class of bug that compilation structurally cannot.

One honest footnote: rollback isn't perfectly invisible. SQL Server does not give back consumed IDENTITY values on rollback, so the test burns a few key numbers and leaves a small gap - harmless, but worth knowing so the gap never alarms anyone later.

Compile-time success tells you the C# is shaped correctly. Only execution against the true schema tells you the query is. Those are two different claims, and only one of them is the one you actually care about.