Meridian Outdoor: Choosing Compute for a 3 TB Mart with a Five-Minute Hot Path
Meridian Outdoor runs SQL Server 2019 and an SSAS tabular model for 180 analysts. The sales fact table is 3.1 TB and grows about 18% a year; the nightly load currently takes 5 hours 40 minutes and must complete before a 06:00 business cut-off. The company has committed to Azure but has a fixed annual budget, and the CFO will not approve a design that scales compute linearly with data volume. Two report families exist: (1) a curated executive and finance mart refreshed nightly, used by 90% of queries; (2) ad hoc exploration by 12 data scientists over the raw Parquet landing zone, which is queried maybe 30 times a day. Separately, the operations team wants order-status dashboards no more than five minutes old for 40 dispatchers. Compliance requires that raw data stay in the primary region and that all access be auditable.
1. Recommend the compute and storage layout for the two report families, and justify the choice against the 06:00 SLA and the fixed-budget constraint.
Read the worked answer
Recommendation
- Curated mart: a Synapse dedicated SQL pool. Store facts as clustered columnstore, hash-distribute on a high-cardinality key such as CustomerKey (never DateKey - date distribution creates skew), and partition monthly on DateKey. Serve the 90% dashboard workload in import mode from this layer.
- Ad hoc: a serverless SQL pool over the ADLS Gen2 Parquet/Delta landing zone, plus a Spark pool for transformations that write the curated Delta layer.
Why not the alternatives
- Running the dashboard workload on serverless bills per TB scanned. A 3.1 TB fact scanned repeatedly by refreshes and interactive queries produces a cost curve the CFO explicitly rejected.
- Running ad hoc on the dedicated pool makes the 12 scientists' unpredictable scans compete with the nightly load and forces DWU sizing for a workload that runs 30 times a day.
Meeting the SLA
- Incremental load: rebuild only the current partition, then partition-switch atomically. The 5h40m window collapses to one partition plus dimension loads.
- Pause the pool outside 20:00-06:30, scale up only for the load, and add result set caching plus materialized views for repeated aggregate queries.
Compliance
- Keep storage in the primary region, enable Synapse auditing to Log Analytics, and use managed private endpoints so no public path exists to the raw zone.
2. How would you deliver the five-minute order-status dashboards without duplicating the whole pipeline or destabilizing the nightly batch?
Read the worked answer
Build a separate hot path, not a faster batch path.
- Stream order events through Event Hubs into Spark Structured Streaming (or Stream Analytics) writing to a small table that holds only open orders - realistically tens of thousands of rows, not the 3.1 TB history.
- Expose that table as its own DirectQuery semantic model, refreshed on a short tumbling window, in its own workspace and app for the 40 dispatchers.
Reasoning
- DirectQuery is appropriate here only because the table is tiny and indexed; pointing DirectQuery at the 3 TB fact would move the cost problem into the interactive tier.
- Keeping the hot path out of the nightly model means a failed stream does not break finance reporting, and a long batch load does not delay operations. Blast radius is the deciding factor.
- Five minutes is generous for streaming; you can comfortably meet it with a one-minute micro-batch and still tolerate a restart.
What not to do
- Do not shorten the batch cadence to every five minutes; that multiplies compute cost and conflicts with the 06:00 cut-off.
- Do not add the streaming table to the curated model as a composite table unless you accept that one slow DirectQuery visual can degrade the whole report.
Auditability
Register the streaming landing zone as a Purview source alongside the batch zone so both paths appear in the same lineage graph.
3. The SSAS tabular model must be replaced or migrated. How do you decide between Azure Analysis Services and a Power BI semantic model, and how do you de-risk the migration?
Read the worked answer
Decision criteria
- If the organization already owns Premium capacity for the 180 analysts, a Power BI semantic model gives the same tabular engine with XMLA read/write, deployment pipelines and one less licence. Consolidation usually wins.
- Keep Azure Analysis Services when the model needs processing isolation, very granular object-level security, or a memory footprint that does not fit the capacity SKU alongside other datasets. AAS also remains attractive if Power BI is not the only consumer.
Migration approach
- Raise compatibility level to 1500+ and move the model with Tabular Editor rather than rebuilding by hand; run Best Practice Analyzer before and after.
- Preserve measure names and folder structure so existing reports do not break. This is the most common cause of a failed migration.
- Recreate partitions to match the incremental strategy; do not carry over hundreds of calculated columns - push them upstream.
- Compare DAX query plans and timings in DAX Studio for the ten slowest reports.
- Run both models in parallel for two weeks and reconcile totals by fiscal year and school against warehouse control totals.
Risk controls
- Cut over report-by-report using deployment pipelines, not in one release.
- Keep AAS read-only for a month as a rollback path, then decommission.