7 Things I wish I'd known before building a 60Tb DWH on a Dedicated Pool
summary
Build uses Azure Data Factory with a dedicated SQL pool and lots of automation and hits real limits: pause/resume can stall for an hour when killed transactions rollback, restores sometimes take 8–12 hours, truncating heaps without immediate rebuild balloons storage to 236TB vs a real 80TB, and 1,200 nightly loads force concurrency tuning. Practical fixes include buying reservations, using parquet staging files, making operations idempotent and singleton, and using workload groups and targeted rebuilds.
AI-written summary · about
chapters (22)
- 0:00Introduction and setup
- 3:09ELT framework necessity
- 4:57Pausing and resuming issues
- 7:47Reservations and scaling
- 8:10Backup and restore challenges
- 12:57Heap table storage problems
- 18:41Heap table optimization
- 19:03Idempotency and retries
- 21:32Idempotency explained
- 23:40Spark job failures
- 26:11Singleton pattern necessity
- 27:35Singleton implementation
- 28:56Concurrency and workload management
- 31:02Source system concurrency
- 32:04Parquet file advantages
- 33:47Workload groups and classifiers
- 36:33Resource allocation strategies
- 37:13Index and stats maintenance
- 37:57CI/CD and automation
- 39:01Replicated table pitfalls
- 40:24Hidden columns for change tracking
- 40:46Additional lessons learned
related talks
57mOptimize “All Data” with a Modern Data Warehouse Solution
47mPerfecting business continuity for Azure SQL DB
51mAzure SQL Hyperscale Deep Dive
21mHow Not to Build a Data Platform Lessons from Failure to Success
12mModern Data Warehouse for Small and Medium Business
15mTurning Growing Pains To Growing Wins With Azure Cosmos DB
59mWhy Databases Cry At Night
36mHow to Build a Solution Processing Terabytes of Data (...)?