Four billion rows migrated
Bulk loaded from SQL Server to Azure Synapse with integrity proven, not assumed.
Data engineering · Data platform
Four billion rows moved from SQL Server to Azure Synapse without losing one
A four-billion-row warehouse rebuilt on Azure Synapse, with Power BI served through an API layer rather than competing with the transactional system for resources.
- Client
- Large-scale data business, under NDA
- Market
- Brazil
- Duration
- 6 months
- Year
- 2023
- Delivered by
- The group's Brazilian engineering practice, in 2023, before Vigiles AI Ltd was incorporated.
- Rows migrated
- 4B+
- From SQL Server to Azure Synapse, reconciled table by table.
- Analytical query time
- Hours to minutes
- After distribution, columnstore indexing and T-SQL calibration.
- Rows lost or duplicated
- Zero
- Proven by counts, checksums and sampled comparison, not asserted.
- End to end
- 6 months
- Assessment, re-architecture, migration, tuning and Power BI integration.
A warehouse of more than four billion rows was moved from SQL Server to an Azure Synapse dedicated SQL pool. Distribution and partitioning were redesigned table by table for the MPP engine rather than copied across, and every table was reconciled on counts, checksums and samples before it was accepted. Power BI was then served through an API layer so that reporting no longer competed with the operational system.
On arrival
On arrival
- The legacy SQL Server warehouse had passed the point where it could be tuned. Analytical queries and loads ran for hours and degraded the operational system while they did.
- The architecture did not scale with the data. Each increment of volume made extracting anything from it slower and more expensive.
- Dashboards read directly from the transactional database, so reporting and operations competed for the same resources and both suffered.
- Moving four billion rows required a load and validation strategy that could prove integrity. Not one row could be lost or duplicated, and an assertion was not going to be enough.
- The model had to be redesigned for a massively parallel engine. Copying the tables as they stood would have reproduced the performance problem on more expensive infrastructure.
Approach
Approach
- Assessment of the warehouse and its workloadsThe data model, the volume per table and the real query patterns were analysed first, so that distribution and partitioning could be designed from evidence rather than inherited from the legacy schema.
- Architecture on a dedicated SQL poolEach table was assigned a distribution strategy, hash, replicated or round robin, and a partitioning scheme suited to a massively parallel engine holding four billion rows.
- Bulk load pipelines in Azure Data FactoryExtraction and load were built with parallelism, batch control and the ability to reprocess a failed batch without restarting the migration.
- Integrity reconciliationRow counts, checksums over key columns and sampled comparisons were run between source and target for every table. A load was only accepted when the numbers matched.
- Performance tuning in T-SQLClustered columnstore indexes, statistics and query rewrites were calibrated against the chosen distribution to eliminate data movement, which is what kills performance on an MPP engine.
- Power BI served through an API layerReporting was pointed at the Synapse layer through an API rather than at the operational database, separating analytical consumption from transactional work.
Solution
What was built
- The migration was treated as re-engineering rather than as a copy. On a distributed columnstore engine the choice of distribution key and partitioning decides performance, so volumes and query patterns were analysed table by table to choose between hash, replicated and round-robin distribution. That choice is what avoids the data movement that makes an MPP warehouse slower than the system it replaced.
- With the model settled, bulk load pipelines were built in Azure Data Factory. Moving four billion rows needs parallelism, batch control and tolerance of failure, so ingestion was structured to load large volumes efficiently and to be reprocessable without starting again.
- Integrity was not negotiable. For every table, row counts, checksums over key columns and sampled rows were reconciled between source and target, and a load was only considered complete when they agreed. That is the difference between believing nothing was lost and being able to show it.
- Performance came from calibration rather than from hardware. Clustered columnstore indexes, refreshed statistics and T-SQL rewritten to exploit the distribution turned analytical queries that had taken hours into queries that returned in a fraction of the time, and loads stopped degrading the operation while they ran.
- Finally, analytical and transactional consumption were separated. Data was exposed through an API and Power BI connected to the Synapse layer, so dashboards were fast without competing for resources with the operation. The platform now grows with the volume instead of being limited by it.
What mattered
What made the difference
Dedicated SQL pool designed for MPP
Hash, replicated and round-robin distribution chosen per table against real query patterns.
Reprocessable Data Factory pipelines
Parallel extraction and load with batch control, so a failed batch does not restart the migration.
Reconciliation as an acceptance gate
Counts, checksums and samples between source and target, with no table accepted until they agreed.
T-SQL tuned to the distribution
Columnstore indexes, statistics and rewrites calibrated to eliminate data movement.
Power BI through an API layer
Fast dashboards over Synapse without loading the transactional environment.
Analytical and operational loads separated
Reporting isolated from the operation, removing the contention that slowed both.
A platform that scales with volume
Architecture designed to grow with the data rather than to be constrained by it.
Questions
Common questions
It depends on volume, on how much business logic is buried in the model, and on how much tuning the result needs. Large estates usually run four to eight months. This one, at over four billion rows including re-architecture, tuning and Power BI integration, took six.
By reconciling rather than asserting. For each table we compared row counts, checksums over key columns and sampled rows between source and target, and treated a load as incomplete until all three agreed. An unreconciled migration is a migration whose integrity nobody can evidence.
You can, and it usually performs badly. Synapse is a massively parallel columnstore engine, so distribution and partitioning have to be redesigned per table against the real query patterns. Copying the schema across reproduces the original performance problem on more expensive infrastructure.
By not reading the operational database. Analytical consumption was separated from transactional, the Synapse layer was modelled and indexed for it, and Power BI was served through an API, so dashboards no longer compete with the operation for resources.
Next step
Arrange a technical review
A 45-minute working session with an engineer rather than a sales call. You describe the problem; we set out what we would build, in what order, and what it is likely to cost.
Answered within one working day, by the engineer who would do the work