Dead code is the cheapest migration you will ever do

The fastest way to migrate an object is not to migrate it.

Every warehouse that has run for a decade carries logic nobody uses. A procedure written for a quarterly report that was retired in 2019. Staging tables for a vendor feed switched off when the contract ended. A job that still runs at 3 a.m., still costs compute, and writes to a table that nothing has read in four years.

Nobody deletes these, because nobody is sure, and deleting the wrong one is a career event. So they stay. Then someone plans a migration, counts everything in, and prices the ghosts.

What does dead code cost a migration?

It costs three times.

Once to convert. An unreferenced object costs exactly what a live one costs: the same rules, the same review, the same reconciliation. If it is a cursor-heavy procedure or an SSIS package with a C# Script Task, it may cost more than most of the live estate.

Again to prove. Every object in scope has to be reconciled, and every one that fails has to be investigated. A dead procedure that fails reconciliation blocks the domain it sits in until someone works out that it does not matter. That investigation is often the slowest kind, because nobody remembers what the object was for.

And every month after. On a cloud platform, a job that runs is a job that is billed. Migrating dead jobs is paying to keep running something you already stopped needing.

How do you find dead code in a data warehouse?

The comment in that query is the whole lesson. Catalog dependencies miss calls made through dynamic SQL, from SQL Agent job steps, from SSIS packages, from applications and from other databases. A query like this produces candidates, not conclusions.

-- Procedures that no other object in this database references (first pass only:
-- does not see SQL Agent jobs, dynamic SQL, applications or other databases)
SELECT s.name AS schema_name, p.name AS procedure_name, p.modify_date
FROM sys.procedures AS p
JOIN sys.schemas AS s ON s.schema_id = p.schema_id
WHERE NOT EXISTS (
  SELECT 1 FROM sys.sql_expression_dependencies AS d
  WHERE d.referenced_id = p.object_id
)
ORDER BY p.modify_date;

1. Build the dependency graph inside the warehouse. For every object, list what it reads and writes and what reads it, across tables, views, procedures, functions and packages.
2. Add the schedulers. SQL Agent, Control-M, cron, Oozie, Snowflake tasks. A procedure called by nothing in the warehouse may be called by a scheduler outside it. A graph without scheduler definitions is wrong in the direction that hurts.
3. Add the edges. Linked servers, applications that call procedures directly, BI tools that query tables, file exports a partner picks up. Each external dependency is listed with the object it depends on.
4. Check activity where the platform records it. Last-modified dates, execution statistics and query history, where available and retained long enough, add evidence. They do not replace the graph; a yearly job will look dead in a 90-day window.
5. Record the evidence per object. What it is, its complexity band, what does and does not reference it, last activity, and why it looks unused. A list without evidence will not be signed.

What are the traps?

How do you retire dead code safely?

Dead by reference is not always dead in practice, so the rule is conservative.

The business owner of each domain confirms the list before anything leaves scope. Objects are dropped from the migration, not from the source. DDL is archived. Jobs are disabled before cutover, not deleted, so they can be re-enabled in a minute if someone calls. If an object turns out to be needed, it comes back into scope and is converted then, at the same per-tier price. Nothing is lost, and nothing is migrated on a guess.

Why clean up before the migration rather than after?

Teams often plan to tidy up once they land on the new platform. It is the wrong end, for four reasons.

• It shrinks the price. Fewer components can move an estate into a lower size tier.
• It shrinks the proof. Fewer objects to reconcile, fewer failures to investigate.
• It shortens the parallel run. Dead objects cannot hold a domain hostage.
• It produces the first honest map. In many estates, the dead-code list is the first document that has ever shown the real size of the warehouse.