Data warehouse modernization: from SQL Server to Snowflake for a German company

The situation

A mid-sized German company had 250 business intelligence users across claims, underwriting, and actuarial teams, all running off a SQL Server 2012 data warehouse. The platform had been stretched past its design limits. Reports ran overnight. The concurrent user cap was 50. Above that, performance fell off. A 50TB dataset was growing 20% per year. And the system had no meaningful data lineage, which was becoming a GDPR problem the compliance team kept flagging.

Specifically:

Underwriters were making decisions on yesterday’s data because nothing ran in real time
No audit trail or lineage for personal data
Pipelines needed frequent manual intervention to stay running
Hard cap of 50 concurrent users before queries started queuing

What I did

Assessment (weeks 1–2)

Started by cataloguing 200+ tables and mapping PII fields to understand the GDPR exposure. Mapped 50+ existing reports and dashboards before touching anything. The architecture decision to split the data into hot (Snowflake) and cold (object storage) data came out of this work.

Foundation (weeks 3–5)

Configured Snowflake with multi-cluster virtual warehouses and auto-scaling. Used Data Vault 2.0 to model the claims and policy data, which gave the audit-friendly structure GDPR requires. Row-level security and automated audit logging went in at this stage.

Migration (weeks 6–11)

Used Snowflake’s native SQL Server connectors for incremental loading. Built the transformation layer in dbt. Added data validation checks at each pipeline stage, not just at the end.

Handover (weeks 12–13)

Migrated SQL Server Reporting Services dashboards to Tableau. Two workshop sessions for BI users on the self-service features. A support channel during the transition period.

Results

Reporting latency dropped from 24 hours to 2 hours. Complex actuarial queries that previously ran overnight now complete in minutes. The concurrent user limit went from 50 to 250. The queuing problem is gone.

Infrastructure costs came down about 30% by moving from SQL Server’s fixed licensing to Snowflake’s usage-based pricing. Maintenance overhead dropped significantly once the manual data loading tasks were automated.

Full GDPR compliance: data lineage, audit trails, role-based access with encryption at rest and in transit. A DPA audit that had been a recurring concern passed without issues.

What I’d do differently

Data profiling at the start surfaced schema inconsistencies I underestimated. I’d build more time into the assessment phase for that. I’d also start user training earlier.

Client details anonymized. Metrics are from the actual project.