Data Warehouse Modernization: From Stored Procedures and Database Views to dbt, CI/CD and Governed Models

What makes a data warehouse 'legacy'?

Not its age. A warehouse becomes legacy when it is hard to understand, test and change safely—regardless of whether it runs on a modern cloud platform.

A three-year-old cloud warehouse can be more fragile than a ten-year-old on-premises one if its logic grew without structure. The useful signals are operational, not chronological.

• Transformation chains that are difficult to follow from source to report
• Stored procedures carrying extensive business logic
• Manual deployment by running scripts in production
• Undocumented dependencies between views, procedures and jobs
• Fragile incremental jobs that need manual reruns
• Environment changes applied inconsistently across dev, test and production
• Obsolete database objects nobody is confident enough to remove
• Testing that is difficult or does not happen

Do you need to replace a legacy data warehouse?

Not necessarily. The constraint is often in how transformation and change are managed, not in the platform itself. Choose the smallest path that removes the real constraint.

The same reasoning applies to operational applications; see Modernize or replace? for the broader decision framework.

Start with dependency discovery

Before changing any object, map what feeds it and what depends on it. In warehouses, the most damaging failures happen downstream of the change, often days later.

A typical dependency path runs: sources → ingestion → staging → views → procedures → scheduled tasks or jobs → reporting models → dashboards and applications. A view that looks unused may feed a procedure that runs monthly; a column rename may break an extract consumed by a partner. Dependency discovery combines system metadata, query history, job schedules and conversations with report owners, because not every consumer is visible in the database.

The output is a map ranked by criticality: which objects serve finance close, regulatory reporting or customer-facing applications, and which are genuinely peripheral. That ranking decides where modernization starts—and where it should be most cautious.

Should stored procedures be migrated to dbt?

Some should, some should not. Stored procedures are not inherently bad; they become a problem when they hold large amounts of transformation logic that is monolithic, hard to test, weakly versioned or unclearly owned.

A thousand-line procedure that stages, joins, applies rules and writes several tables in sequence is difficult to review and nearly impossible to test in parts. A modular approach splits that work into separate models—each a single SELECT with explicit dependencies—so each step can be tested, documented and changed independently. dbt is a common framework for this: it builds models in dependency order, runs tests between them and records lineage.

Procedures that perform procedural or administrative work—permission management, operational utilities, complex control flow—may be better left as procedures under source control. The goal is maintainable transformation, not the elimination of a language feature.

Moving database views into dbt models

Treat each migration as a controlled replacement: reproduce the logic, prove equivalence, then move consumers. Retire the legacy object only after confidence is established.

Comparison should cover row counts, key totals and a sample of row-level differences. Any difference must be explained—sometimes it reveals a long-standing defect in the original, which then becomes a business decision rather than a technical one.

Source control changes the operating model

Once warehouse code lives in Git, every change has an author, a reason, a review and a history. Data-platform delivery starts to behave like software engineering.

Branching lets engineers work on changes without affecting production. Pull requests make changes visible and reviewable before merge. Code review catches logic errors and enforces conventions. Version history makes it possible to see exactly what changed when a number moves. Controlled merges ensure only reviewed code reaches deployment.

This also applies to schema changes, not just transformations. Tools such as schemachange apply versioned migration scripts to Snowflake in a defined order, so database structure is managed in the same controlled way as model code.

What does CI/CD mean for data engineering?

CI/CD automates how data-platform changes are built, tested and promoted between environments. For data, deployment without validation is not enough—the pipeline must prove the data is still correct.

• Code: models, scripts and configuration built from source control
• Environment configuration: separate dev, test and production targets
• Validation: tests run against built models before promotion
• Controlled promotion: approvals and gates between environments
• Logs: a record of what was deployed, when and by whom
• Rollback or recovery strategy where feasible—data changes are not always reversible, so plan recovery explicitly

How should incremental loads be tested?

Incremental loads process only new or changed data, which makes them efficient—and makes their failures quiet. Test them against the edge cases, not the normal day.

In dbt, incremental models use a unique key and filter logic to merge new data, and can be rebuilt with a full refresh when needed. That full-refresh path should itself be tested before you depend on it.

What is a data backfill?

A backfill recalculates historical data—typically because a schema or business rule changed and existing records must reflect the new logic. It is part of modernization, not an afterthought.

• Scope: which tables, periods and consumers are affected
• Validation: expected before-and-after differences, agreed in advance
• Controlled execution: in batches, outside critical reporting windows
• Monitoring: run progress, failures, compute consumption
• Reconciliation: confirm totals against source and explain every change

Retiring obsolete database objects

Do not delete something because it appears unused. Prove it is unused, then retire it in stages.

Check dependencies in metadata, scheduled jobs and tasks, reports and extracts, hidden consumers such as analysts' saved queries, and external integrations. A cautious pattern is to revoke access or rename first, observe for a defined period, and only then drop—keeping the definition in source control so it can be restored.

Modernization does not end with deployment

The final rung—govern—is permanent. Without operating practice, a modernized warehouse drifts back toward the problems it replaced.

For the broader context of which layers a data platform needs, or how to build a reliable foundation from scratch, see the other two articles in this series.

• Monitoring of freshness, failures and test outcomes
• Data-quality rules maintained as business rules change
• Documentation generated from and kept alongside code
• Operational ownership for each domain and critical model
• Incident handling: who is alerted, how issues are triaged and communicated