What an honest pre-migration assessment finds in a 6-month-old data warehouse

Migration vendors love selling the destination. Buyers usually need help with the source. This post walks through what we actually look for in the first 10 working days of an engagement — what we call pre-migration discovery — and what the methodology consistently surfaces in any analytical data warehouse that has been running for more than 6 months. The categories below are stable across Redshift, BigQuery, Snowflake, Databricks SQL, and even older Oracle / Teradata estates. The exact numbers vary; the categories do not.

Nothing in this post is from a specific client engagement — every number is either from public industry research (cited inline) or describes the analytical method itself. Replatform is launch-phase; until we have named clients who have approved their writeups, we do not publish first-person engagement metrics. This article is methodology, not war-story.

Why “a 6-month-old data warehouse” is the sweet spot

Buyers usually come to us at one of three moments:

• A vendor renewal is due in 60-180 days and the bill jumped enough to make finance nervous.
• A platform shift is being seriously considered (Redshift → Snowflake, BigQuery → Databricks, on-prem → managed cloud, etc.).
• A new VP of Data or Head of Platform has joined and is asking “what do we actually have?”

By the 6-month mark of any warehouse, three things have happened that make assessment valuable:

1. The “everyone-can-write-anywhere” honeymoon has ended. Tables proliferated, schemas drifted, dashboards forked off staging views, ad-hoc analyses got promoted into “blessed” tables. Nobody can confidently say what is still in use.
2. The first wave of analytical workloads matured. You can see real query patterns, not just synthetic load. The patterns reveal which assets are load-bearing and which are zombie.
3. The first cost surprises landed. Something quintupled and nobody knew it could. There is now executive appetite to fix it, which means there is appetite to pay for an honest answer.

Pre-migration discovery at that moment is a forcing function — somebody senior, paid to look at everything, with no incentive to defend prior decisions.

What we ask the buyer up front

Three questions, before any access is granted, before any pricing is sent:

1. What outcome would make this assessment worth the fee, six months from now? The good answers are concrete — “we knew before we signed the Snowflake contract whether the storage estimate was real” or “we cut warehouse spend 30% before the renewal” or “we shipped the lakehouse migration without a single missed reconciliation.” The bad answers are vague — “a clearer picture” or “an external opinion.” Vague briefs produce vague deliverables.
2. Who will read the report cover-to-cover within 72 hours of delivery? If the buyer cannot name two specific people, the report will sit and nothing will happen. We have learned to push hard on this one before signing.

3. What is the single decision the report needs to inform? Migration go/no-go. Vendor negotiation leverage. FinOps optimization spend. Hire-vs-outsource the next data engineer. Each of those changes the depth and emphasis of the deliverable.

The whole discovery then gets scoped backwards from those three answers.

Five categories we find every time

The output of a pre-migration discovery is a complete inventory artifact — every object, every dependency (what Replatform calls Appendix A in the scope contract). From the inventory, we build the findings sections of the report. These five categories show up in every engagement at this size, in different proportions.

Each finding maps back to a verifiable data source — query logs, system metadata, billing exports. The methodology produces conclusions an external reviewer can re-derive.

1. Retire-don’t-migrate candidates

Industry research (Wakefield Research / Pragmatic Works, 2023-2024) suggests 20-40% of analytical objects in mid-market estates have not been queried in 90+ days. The pattern is structural: tables created for a one-off analysis, views built around a deprecated KPI, scheduled jobs orphaned when their consumer dashboard was decommissioned, “blessed” tables superseded by a successor that nobody told the original creator about.

The methodology:

• Pull every object’s last-queried-at from the source platform’s query log (stl_query on Redshift, INFORMATION_SCHEMA.JOBS on BigQuery, QUERY_HISTORY on Snowflake).
• Pull every object’s last-modified-at from system metadata.
• Cross-reference against the dependency graph — a table that hasn’t been read in 90 days could still be writing into something live, in which case its retirement risks downstream breakage.
• Flag “retire candidates” as: not queried in 90+ days AND no downstream consumer AND no upstream producer that is itself live.

The retire-candidate list (Appendix B in the deliverable) is often the single biggest dollar-line in the executive summary. Storage costs, compute time during refreshes, the carrying cost of mental model — all gone the moment the table is dropped.

2. Schema drift, silent

Every analytical estate older than six months has schemas that drifted from their definitions. The symptoms:

• Columns added by hand to a single environment (staging or prod) and never propagated back to the IaC repo. The IaC then can’t recreate the environment correctly.
• VARCHAR columns where the longest actual value is 3× the declared max. The platform happily stored it (most do, with truncation warnings that nobody monitors), but the migration target may reject on import.
• NULL constraint violations in flight. Existing rows violate the constraint that the schema says exists. Migration with reconciliation will surface it; migration without reconciliation will accumulate silent bad data.
• IDENTITY / SEQUENCE columns whose runtime value has diverged from the schema’s declared MAX VALUE. Inserts work; CDC replays don’t.

The methodology:

• Sample-check the top 200 tables by query frequency: compare declared schema against an aggregate column profile (MIN/MAX/length/null-rate/distinct-count).
• For every column where the profile breaks the declared schema, flag the type of drift and the count of affected rows.
• The aggregate profile is cheap; we use it as the front-end to a more expensive deep-check only on objects in scope for migration.

Schema drift findings are almost never headline-grabbing in the executive summary, but they consistently change wave-planning. A drifted IDENTITY column on a high-write table can move a wave from “easy” to “needs a deprecated-sequence-mapper deployed alongside.”

3. Lineage and consumer mapping

Most buyers cannot tell us, before assessment, who actually consumes their warehouse. The answers we get on day one are usually some version of “our BI tool, dbt, and a few API consumers” and the truth is usually 4-7 distinct consumer surfaces:

• The official BI tool (Tableau / Looker / Mode / Sigma / Hex).
• A second BI tool that someone in marketing prefers and has been running on top of the same warehouse without IT knowing.
• dbt for transformations.
• A reverse-ETL platform writing back to Salesforce / Marketo / HubSpot.
• A vendor SaaS that the procurement team configured with a service account two years ago and nobody remembers.
• Ad-hoc query traffic from internal SQL clients (Hex notebooks, DataGrip, Snowflake’s own UI).
• A handful of microservices that hit the warehouse directly for read-side traffic (this is always the riskiest one, because the SLA assumption is implicit).

The methodology:

• 90-day query log: parse every distinct query, extract the source application (most platforms log a user or application_name), aggregate by application.
• Tag each application with its consumer surface category.
• Compute “blast radius if migrated” per application — how many tables, how many queries/day, peak concurrency, SLA assumption.
• The output is a one-page consumer matrix that becomes load-bearing in wave planning.

This is the section of the assessment that most often produces an “oh” moment — usually when the buyer learns about consumer surface #5 or #6 that they did not know existed.

4. Compute and storage waste with named owners

Cost waste is the easiest section to write and the hardest to make actionable. The waste is real; the politics of fixing it are the constraint. The methodology specifically forces the politics into the open:

• Top-10 highest-cost queries by aggregate spend over 90 days. Naming who scheduled them and what consumer surface receives the output.
• Top-10 highest-cost tables by storage + refresh compute. Naming the team that owns the upstream pipeline.
• “Always-on” infrastructure: dev / staging warehouses that should be scaled down off-hours, but aren’t, with the team that owns the schedule.
• Idle replication paths (cross-region copies, DR replicas, ETL staging tables) that no longer have a downstream consumer.

Every line of the waste finding includes a named owner and an estimated savings if the finding is acted on. Without named owners, the report becomes a list nobody picks up. With named owners, the report becomes a series of one-on-one conversations the Head of Data has the week after delivery.

We do not propose savings without the owner attached. That is a deliberate methodology choice, learned the hard way during pre-Replatform engagements.

5. Wave plan and dependency-aware sequencing

The fifth category is the synthesis: given everything above, what is the recommended migration wave plan?

• Wave 0 — inventory freeze + retire-don’t-migrate execution. Always first. Saves money before any migration happens, and reduces the asset count for subsequent waves.
• Waves 1-N — grouped by dependency cluster, ordered by ascending consumer-surface risk and descending criticality. The lowest-risk, highest-value waves are first because they prove the methodology before higher-stakes waves land.
• Per wave: in-scope asset count, expected clock-time, deterministic reconciliation harness specification, dual-run window, rollback criteria.

The wave plan is the section of the report that gets reused — usually as the basis for the eventual implementation contract.

Why deterministic methodology beats opinion

Every category above is built on something an external person can verify after the fact:

• Last-queried-at numbers are queryable from system tables.
• Schema drift findings reproduce against a column profile.
• Consumer mapping reproduces against the query log.
• Cost waste is an arithmetic calculation against billing exports.
• Wave plan is a graph problem with stated optimization criteria.

The deliverable says, on every page: here is the data, here is the method, here is the conclusion. Pre-migration discovery is opinionated about what to do; it is deterministic about what is true.

This split is not aesthetic — it is what lets the client’s own engineers pressure-test the report and either agree or push back with their own evidence. Reports that get pushed back on, productively, are the reports that close the next engagement. Reports written as advocacy do not survive the second internal meeting.

What the deliverable looks like

A typical discovery deliverable is around 18-25 pages plus appendices:

• Executive summary (3 numbers: assets-in-scope, indicative savings range, recommended timeline).
• Scope and method.
• Inventory and usage tables.
• Complexity and automation forecast.
• Recommended wave plan with per-wave scope breakouts.
• 3-year TCO comparison, status-quo vs target, modeled as a range not a point estimate.
• Validation methodology — exactly how reconciliation will be performed on each wave.
• Risks register, top-5 surfaced in the executive summary, full register in an appendix.
• Fixed-fee proposal for the Execution work, or a paid second sprint if Execution is not yet greenlit.

Appendices include the complete inventory artifact, the retire-candidate list with owners, the consumer-surface matrix, schema-drift findings, cost-waste findings with named owners, and the dependency graph.

The deliverable hierarchy: executive summary anchors everything; the five findings feed into the TCO model and wave plan, which together form the basis for the Execution proposal.

This deliverable shape is what we standardize on. The depth in each section varies by engagement; the structure does not.

When a pre-migration assessment is the wrong answer

It is worth saying what this engagement is not for:

• If you are looking for an external advocate for a decision already made, this engagement will not help. Pre-migration discovery surfaces uncomfortable findings as often as it confirms expected ones.
• If you cannot get read-only access to your warehouse’s query log within the first 48 hours of engagement start, the methodology cannot run on time. Some procurement environments make this impossible; we have learned to ask up front.
• If the migration decision is already locked and the vendor selected, you do not need a discovery engagement — you need a Wave 0 inventory + execution kickoff, which is a smaller and cheaper engagement.

A full pre-migration assessment is a 10-working-day, fixed-fee commitment with a buyer that wants the truth. (Replatform’s Cloud Cost X-Ray is a narrower 5-day cost-only product; the 10-day shape covers the full five categories above, including wave planning and the dependency graph.) The methodology above is what they get. Everything in the deliverable maps back to evidence they can re-derive themselves.