"[BadRequest] Failed to execute script on database 'Ingestion'." with fresh install of v0.9

[philipstreet]

🐛 Problem

Clean install of v0.9 results in following error in "[ade name/Ingestion/SetupScript" step of dataExplorer;

{
    "code": "ScriptExecutionFailed",
    "message": "
        [BadRequest] Failed to execute script on database 'Ingestion'. Here is a list of the commands that failed: Command: 
        
        .create-or-alter function
        with (docstring = \"All recommendations transformed to FOCUS 1.0.\",folder = \"Recommendations\")
        Recommendations_transform_v1_0()
        {
            // NOTE: All open issues and questions are tracked @ https://github.com/microsoft/finops-toolkit/issues/1111
            let isoMonths = (duration: string) {
                let number = toint(replace_regex(duration, @'[PMY]', ''));
                toint(case(
                    duration == '', toint(''),
                    duration endswith \"Y\", number * 12,
                    duration endswith \"M\", number,
                    -1
                ))
            };
            Recommendations_raw
            | extend x_IngestionTime = ingestion_time()
            //
            // Set ProviderName
            | extend ProviderName = 'Microsoft'
            //
            // Set source columns
            | extend x_SourceName     = coalesce(x_SourceName, iff(ProviderName == 'Microsoft', 'Cost Management', ProviderName))
            | extend x_SourceProvider = coalesce(x_SourceProvider, ProviderName)
            | extend x_SourceType     = coalesce(x_SourceType, iff(ProviderName == 'Microsoft', 'ReservationRecommendations', ''))
            | extend x_SourceVersion  = coalesce(x_SourceVersion, iff(ProviderName == 'Microsoft', '2023-05-01', ''))
            //
            // Convert JSON cost columns to decimal
            | extend CostWithNoReservedInstances    = case(isnotempty(CostWithNoReservedInstances),    CostWithNoReservedInstances,    isnotempty(CostWithNoReservedInstancesJson),    todecimal(extract(@'\"value\":([0-9\\.]+)', 1, CostWithNoReservedInstancesJson)),    CostWithNoReservedInstances)
            | extend NetSavings                     = case(isnotempty(NetSavings),                     NetSavings,                     isnotempty(NetSavingsJson),                     todecimal(extract(@'\"value\":([0-9\\.]+)', 1, NetSavingsJson)),                     NetSavings)
            | extend TotalCostWithReservedInstances = case(isnotempty(TotalCostWithReservedInstances), TotalCostWithReservedInstances, isnotempty(TotalCostWithReservedInstancesJson), todecimal(extract(@'\"value\":([0-9\\.]+)', 1, TotalCostWithReservedInstancesJson)), TotalCostWithReservedInstances)
            //
            // Build recommendation details
            | lookup kind=leftouter (database('Ingestion').Regions | summarize RegionName = make_set(RegionName)[0] by Location = RegionId) on Location
            | extend x_RecommendationDetails = case(
              x_SourceType == 'ReservationRecommendations', bag_pack(
                'CommitmentDiscountNormalizedGroup', InstanceFlexibilityGroup,
                'CommitmentDiscountNormalizedRatio', InstanceFlexibilityRatio,
                'CommitmentDiscountNormalizedSize', NormalizedSize,
                'CommitmentDiscountResourceType', ResourceType,
                'CommitmentDiscountScope', coalesce(Scope, scope),
                'LookbackPeriodDuration', case(
                    LookBackPeriod matches regex @'^Last([0-9]+)Days$', replace_regex(LookBackPeriod, @'^Last([0-9]+)Days$', @'P\\1D'),
                    LookBackPeriod matches regex @'^[0-9]+$',           strcat('P', LookBackPeriod, 'D'),
                    ''
                ),
                'LookbackPeriodStart', FirstUsageDate,
                'RecommendedQuantity', RecommendedQuantity,
                'RecommendedQuantityNormalized', RecommendedQuantityNormalized,
                'RegionId', Location,
                'RegionName', RegionName,
                'SkuMeterId', coalesce(MeterId, MeterID),
                'SkuPriceDetails', SkuProperties,
                'SkuSize', coalesce(SKU, SkuName),
                'SkuTerm', isoMonths(Term)
              ),
              dynamic({})
            )
            //
            // Sort columns and apply final transforms
            | extend x_RecommendationDate = FirstUsageDate + (toint(extract(@'^P([0-9]+)D$', 1, tostring(x_RecommendationDetails.LookbackPeriodDuration))) * 1d)
            | extend x_RecommendationDate = iff(x_RecommendationDate > now(), startofday(now()), x_RecommendationDate)
            | project
                ProviderName,
                SubAccountId = iff(isnotempty(SubscriptionId), strcat('/subscriptions/', SubscriptionId), ''),
              x_IngestionTime,
                x_EffectiveCostAfter = TotalCostWithReservedInstances,
                x_EffectiveCostBefore = CostWithNoReservedInstances,
                x_EffectiveCostSavings = NetSavings,
                x_RecommendationDate,
                x_RecommendationDetails,
                x_SourceName,
                x_SourceProvider,
                x_SourceType,
                x_SourceVersion
        }
        
        Reason: Request is invalid and cannot be processed: Semantic error: SEM0100: 'extend' operator: Failed to resolve scalar expression named 'scope'"
}

👣 Repro steps

Perform installation of v0.9 using ARM template https://aka.ms/finops/hubs/deploy with default settings plus ADE cluster name (dec-finops).

On deployment page, the "dataExplorer" resource fails with status "Conflict". Clicking into that resource shows that "[ade name]/Ingestion/SetupScript" has Conflict with the above error details.

🤔 Expected

Deployment to complete without errors.

ℹ️ Additional context

I have Owner role assignment on the subscription.

🙋‍♀️ Ask for the community

1. Please vote this issue up (👍) to prioritize it.
2. Leave comments to help us solidify the vision.

[shubhamagarwalMSFT]

+1, I am getting the same

Michael Flanakin (@flanakin)

[flanakin]

Philip Street (@philipstreet) / Shubham Agarwal (@shubhamagarwalMSFT) Can y'all redeploy with the latest? We fixed a bug in the deployment a few days after the release.

[philipstreet]

Will do, when I return from vacation. :)