Databricks Migration Changes a Certified Metric by 7%
A MicroStrategy project migrates from Teradata to Databricks SQL. The certified metric Avg Order Value (AOV) is defined as Sum(Order Amount) / Count(Distinct Order ID). The denominator uses ApplySimple '#0 / #1'. The metric has VLDB properties Full Outer Join=on and Intermediate Table=use temporary table. After migration, AOV is $86.40 in MicroStrategy but $80.75 in a Databricks control query. The gap is concentrated in orders with NULL product category, about 4% of orders. Finance signed off on the old number. The project has 1,800 reports; 140 use AOV. The team wants to patch AOV with a Databricks-specific ApplySimple. Separately, the AOV dashboard shows different values for two finance users even though no security filter is attached to the metric. The migration deadline is three weeks.
1. Diagnose why AOV changed after the Databricks migration. Consider ApplySimple, VLDB properties, NULL handling, and join/aggregation behavior.
Read the worked answer
AOV is a ratio, so a small change in the denominator population can move it materially. ApplySimple('#0 / #1') is a database-specific expression. It does not describe the semantic intent; it delegates parsing, integer/decimal division, and NULL behavior to the warehouse. Databricks may divide decimals differently from Teradata, and it may propagate NULLs differently. The VLDB properties change the physical plan: Full Outer Join=on can retain unmatched rows, and Intermediate Table=use temporary table can change when filters and aggregations are applied. Either can alter which orders survive into the denominator.
The NULL product category clue points to the join path. If the report joins Product and the join eliminates NULL-category orders in MicroStrategy, Count(Distinct Order ID) sees a smaller denominator. The Databricks control query may use a left join or a different filter order and include those orders, producing a larger denominator and a lower AOV. The 4% subset is large enough to explain a 7% gap. The fact that the numerator and denominator may be computed at different points in the SQL is the core defect.
The metric is certified but not portable. It encodes a physical expression and relies on VLDB behavior that changed with the database. The migration exposed a latent semantic-layer problem rather than creating a new one.
2. Propose a governed fix and a migration test plan for the 140 reports that use AOV. Explain why a Databricks-specific ApplySimple patch is risky.
Read the worked answer
Replace the ApplySimple ratio with a portable metric definition. Define Numerator = Sum(Order Amount) and Denominator = Count(Distinct Order ID) as separate governed metrics, apply the same filter and level to both, and let MicroStrategy perform the division with its own divide function. Decide explicitly whether NULL-category orders are in scope. If they are out of scope, add a governed filter and document it; if they are in scope, map NULL to an 'Unassigned' category before aggregation. Remove or justify the Full Outer Join and Intermediate Table VLDB overrides rather than carrying them forward blindly.
A Databricks-specific ApplySimple patch is risky because it fixes one warehouse and one report path while preserving the hidden semantics. It will break again on the next warehouse change and makes the metric impossible to validate from the definition alone. It also hides whether the difference is numerator, denominator, or filter scope.
Test with an order-level reconciliation set that includes NULL categories, multi-line orders, and orders with no product match. Compare MicroStrategy SQL and results to a control query for each of the 140 reports, not just the dashboard. Use SQL preview and audit logs to confirm the join and filter order. Automate the comparison through the REST API, store baseline results before migration, and require finance sign-off on the reconciled definition before certification.
3. Why might two finance users see different AOV values with no security filter on the metric, and how would you investigate?
Read the worked answer
A metric without a security filter can still produce user-dependent results through the report or dossier context. The two users may have different default prompt answers, different report filters saved in their personal views, or different database connection mappings that route them to different Databricks warehouses or schemas. A dossier can also contain a filter on the dataset or a hidden filter that is not part of the metric definition. Result caching can preserve an older value for one user while the other gets a fresh query.
Investigate from the execution outward. Compare the SQL generated for each user in the audit log or SQL preview, including the database instance, connection mapping, prompt answers, and report filter. Check whether the dossier uses personalized views or a user attribute in a filter. Inspect the metric's level for any user-related attribute, and check whether one user is hitting cache while the other is querying live. Reproduce both sessions with a controlled test account.
The fix may be outside the metric: align the connection mappings, remove personal filters from the certified dossier, or make the cache key and prompt defaults explicit. Document the user-dependent behavior as a certification defect until the SQL and data source are identical for both users.