Data Quality Checks: Migration Playbook

Dashboard Trust

Data quality checks are the tests that tell you whether migrated data is fit for use. During a migration, they should answer a practical question: can the new system produce the same trusted decisions as the old system, or are there gaps that will break reporting, metrics, and operations after cutover?

What data quality checks mean in a migration

In a migration, data quality checks are not just generic tests. They are controls that compare the old and new data paths, expose transformation mistakes, and protect the business from making decisions on incomplete or distorted information.

A useful check has three parts: the condition being tested, the place where it runs, and the action to take when it fails. For example, "orders must have a non-null order_id" is a condition. Running it after the orders model is built is the place. Blocking the revenue dashboard refresh when it fails is the action.

The goal is not to prove that every record is perfect. The goal is to make the important failure modes visible before they reach dashboards, finance reports, customer operations, or executive metrics.

Why migrations create quality risk

Migrations create quality risk because they change more than storage location. They often change extraction logic, warehouse behavior, transformation code, data types, naming conventions, business definitions, orchestration, and dashboard connections at the same time.

This means a dashboard can show a familiar metric while using a different join, a different timestamp, a different filter, or a different definition of an active customer. That is why migration validation needs to test both the physical data and the business meaning of the data.

• Physical quality: Are records present, current, unique, and shaped correctly?
• Logical quality: Do joins, transformations, and filters behave as expected?
• Metric quality: Do business numbers reconcile within an agreed tolerance?
• Operational quality: Do failures alert the right owner before users notice?

The migration playbook: test in layers

The safest way to run data quality checks during a migration is to test in layers. Do not wait until the final dashboard to discover that the source extract missed deleted records or that a customer_id changed type.

Use this sequence:

1. Inventory critical assets. Identify the datasets, models, metrics, and dashboards that the business actually uses.
2. Define expected behavior. Document freshness, grain, keys, accepted values, and metric definitions.
3. Validate source ingestion. Confirm that data lands in the new system completely and on time.
4. Validate modeled data. Test joins, uniqueness, nulls, referential integrity, and business rules.
5. Reconcile metrics. Compare old and new outputs for agreed periods and segments.
6. Run parallel reporting. Let users compare old and new dashboards before cutover.
7. Monitor after cutover. Keep checks running so migration issues do not become permanent reporting issues.

Choose checks by business risk, not by test count

A common mistake is adding many shallow checks while missing the few checks that would catch a business-critical problem. More tests do not automatically create dashboard trust. Better tests are tied to how the data is used.

Start with the assets that support revenue reporting, customer lifecycle reporting, board metrics, regulatory reporting, billing operations, or daily executive decisions. For each one, ask what would cause a wrong decision if the data changed quietly.

For a sales dashboard, the important checks may include opportunity uniqueness, expected stage values, owner mappings, close date validity, and pipeline amount reconciliation. For a product usage dashboard, the important checks may include event freshness, user identity joins, bot filtering, and sessionization logic.

Baseline checks to run before cutover

Before moving users to the new system, run a baseline set of data quality checks against critical tables and metrics. These checks are simple, but they catch many migration problems quickly.

• Freshness: Is the table updated within the expected window?
• Volume: Are row counts within an expected range compared with the old system?
• Completeness: Are required fields populated?
• Uniqueness: Are primary keys unique at the intended grain?
• Referential integrity: Do foreign keys match valid records in related tables?
• Accepted values: Do status, type, country, currency, and category fields contain expected values?
• Type and format: Are dates, decimals, booleans, and identifiers represented consistently?
• Metric reconciliation: Do core metrics match the old system within an agreed tolerance?

These are not the only checks you may need, but they are the foundation. If these fail, advanced monitoring will not compensate for the missing basics.

Check type	What it catches	Example migration use
Freshness	Late or stalled pipeline loads	Orders table has not updated since yesterday even though the old system has new orders
Volume	Missing extracts, duplicated loads, unexpected filters	New warehouse has 30% fewer customer records than the old system for the same period
Completeness	Required fields arriving as null	Account owner is missing after a CRM connector change
Uniqueness	Broken grain or duplicate incremental loads	One invoice_id appears multiple times in a table expected to be one row per invoice
Referential integrity	Broken joins between entities	Order rows reference customer_ids that do not exist in the customer table
Accepted values	Unexpected statuses, categories, or enum drift	Subscription status includes a new value that the model does not handle
Metric reconciliation	Definition drift or transformation mistakes	Monthly recurring revenue differs between old and new reporting by more than the approved tolerance

Where to place checks in the pipeline

Data quality checks should run at multiple points because each layer catches different problems. A source-level check can catch missing input. A model-level check can catch transformation logic errors. A metric-level check can catch business definition drift.

A practical pattern is to place checks at four control points: raw ingestion, cleaned staging models, business-ready marts, and published metrics or dashboards.

Do not put every check everywhere. Place the check where the failure can be diagnosed most clearly. If a field is missing at ingestion, test it near ingestion. If revenue is wrong because of a join or filter, test the modeled revenue output.

Pipeline layer	Best checks	Why this layer matters
Raw ingestion	Freshness, row count, schema presence, source load status	Confirms that data arrived before transformations depend on it
Staging models	Type consistency, required fields, accepted values, basic deduplication	Catches source cleanup issues before business logic is applied
Business marts	Primary keys, referential integrity, grain, business rules	Protects the modeled datasets used by analysts and dashboards
Metrics and dashboards	Metric reconciliation, segment comparison, trend checks	Validates that users see numbers that match approved business definitions

Reconcile old and new systems without chasing false precision

Reconciliation is the part of a migration where teams compare outputs from the old and new systems. It is also where teams can lose time chasing differences that do not matter.

Define tolerances before testing. Some metrics should match exactly, such as a count of invoices for a closed accounting period. Other metrics may have acceptable differences because of late-arriving data, timezone changes, deduplication improvements, or corrected business logic.

For every important metric, document the comparison window, filters, grouping level, acceptable tolerance, and owner who can approve a difference. Without this discipline, reconciliation turns into a debate instead of a decision process.

How to triage failed checks

A failed data quality check is useful only if it changes behavior. Treat failures as operational signals, not as noise.

For each failure, assign severity:

• Blocker: The issue affects a critical dashboard, finance number, customer workflow, or executive metric. Do not cut over until resolved or formally accepted.
• High: The issue affects important analysis but has a workaround or limited scope. Fix before broad rollout if possible.
• Medium: The issue affects a non-critical field, segment, or historical period. Track and schedule.
• Low: The issue is cosmetic, documented, or outside current use. Backlog it.

The migration lead should maintain a visible issue log with the check name, failing asset, first detected time, owner, severity, root cause, decision, and expected resolution.

Severity	Typical decision	Example
Blocker	Stop cutover or disable affected output	Revenue dashboard does not reconcile for closed months
High	Fix before broad rollout if possible	Customer health score misses one important product event
Medium	Track with owner and date	Historical region mapping is incomplete before 2022
Low	Document or backlog	A non-critical descriptive field has inconsistent capitalization

Common migration failure modes to watch for

Most migration quality problems are not exotic. They come from predictable changes in data shape, timing, identity, and business logic.

• Timezone shifts: Daily metrics move because timestamps are interpreted differently.
• Changed grain: A table that used to have one row per order now has one row per order line.
• Duplicate keys: Incremental loads insert records that should have updated existing rows.
• Missing deletes: Records removed or archived in the source remain active in the new warehouse.
• Identity mismatch: Users, accounts, or customers are joined on a different identifier.
• Enum drift: New status values appear and are silently excluded from reports.
• Late-arriving data: Reports run before all upstream data has landed.
• Metric definition drift: The new system calculates the same metric name with different filters or business rules.

Keep checks operational after migration

Data quality checks should not be a one-time migration exercise. The checks that protect the migration should become part of normal operations.

After cutover, keep checks for freshness, uniqueness, required fields, accepted values, and critical metric reconciliation. Reduce or retire temporary checks only when the risk has passed and ownership is clear.

The operating model matters as much as the tests. A check that fails silently is documentation, not control. A check that alerts everyone creates fatigue. A useful check alerts the owner who can fix the problem and includes enough context to diagnose it.

Minimum viable checklist for a small team

If you are a small team, do not wait for a full data observability program before protecting a migration. Start with a minimum viable checklist.

1. List the top 10 dashboards, reports, or metrics people rely on.
2. Identify the source tables and modeled tables behind them.
3. Define freshness expectations for each critical table.
4. Add uniqueness and not-null checks for primary keys and required fields.
5. Add accepted-value checks for important status and category fields.
6. Compare row counts between old and new systems for recent and historical windows.
7. Reconcile the top business metrics by day, week, and key segment.
8. Run old and new dashboards side by side before cutover.
9. Create a failure log with owners and severities.
10. Keep the most important checks running after cutover.

This checklist will not catch every edge case, but it will catch many of the issues that damage trust quickly.

Key takeaways

• Data quality checks during migration should prove that critical data is fresh, complete, unique, consistent, and meaningful enough to use.
• Test in layers: ingestion, staging, business models, and published metrics each catch different failure modes.
• Reconciliation needs predefined windows, filters, tolerances, and owners, or it becomes an open-ended debate.
• The best checks are tied to business risk, not to the total number of tests in the system.
• After cutover, keep the important checks running so migration validation becomes ongoing data reliability.

Next step

Pick one critical dashboard and trace it backward to its source tables. Add freshness, row count, key uniqueness, required-field, accepted-value, and metric reconciliation checks before migrating the next dashboard.