Uploaded image for project: 'Subscription Watch'
  1. Subscription Watch
  2. SWATCH-4015

Data Cleanup for Invalid metric_id in contract_metric Table in stage

XMLWordPrintable

    • Icon: Task Task
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • None
    • swatch-contracts
    • Swatch Lightning Sprint 5

      Currently, the contract_metric table contains metric_id/dimensions that are not part of our config. These invalid entries are not required for any processing or reporting.

      We should remove all such invalid entries using REST API to keep things clean. This was caused because we didn't filter metric id if there is no product tag.

      Query used to find such invalid tags

      SELECT                                          
      c.org_id,c.sku,spt.product_tag, cm.metric_id,c.last_updated, c.end_date
  FROM contracts c
  JOIN contract_metrics cm
      ON c.uuid = cm.contract_uuid
  JOIN subscription s
      ON c.subscription_number = s.subscription_number
      AND c.start_date = s.start_date
  LEFT JOIN subscription_measurements sm
      ON s.subscription_id = sm.subscription_id
      AND s.start_date = sm.start_date
      AND (
          (sm.metric_id = 'Managed-nodes'       AND cm.metric_id = 'managed_node') OR
          (sm.metric_id = 'Instance-hours'      AND cm.metric_id = 'infrastructure') OR
          (sm.metric_id = 'Instance-hours'      AND cm.metric_id = 'control_plane') OR
          (sm.metric_id = 'Instance-hours'      AND cm.metric_id = 'cluster_hour') OR
          (sm.metric_id = 'vCPUs'               AND cm.metric_id = 'vcpu_hourly') OR
          (sm.metric_id = 'vCPUs'               AND cm.metric_id = 'vCPU_hours') OR
          (sm.metric_id = 'vCPUs'               AND cm.metric_id = 'acm_vcpu_hours') OR
          (sm.metric_id = 'Cores'               AND cm.metric_id = 'four_vcpu_hour') OR
          (sm.metric_id = 'Cores'               AND cm.metric_id = 'cluster_cpu_hour') OR
          (sm.metric_id = 'Cores'               AND cm.metric_id = 'vCPU_Hour') OR
          (sm.metric_id = 'Transfer-gibibytes'  AND cm.metric_id = 'transfer_gb') OR
          (sm.metric_id = cm.metric_id)
      ) LEFT JOIN sku_product_tag spt on c.sku=spt.sku 
  WHERE sm.subscription_id IS NULL and (c.end_date>now() or c.end_date is null) order by c.last_updated desc

       
      AC:

      • All invalid metric_id are removed from the contract_metric table in stage.
      • No valid data is affected.
      • We should run contract sync for the orgs that have incorrect metric_id to get it consistent
      • Liquibase script is reviewed, tested in a staging environment, and approved before production deployment.

          1.
          		 Validate Test consistency in stage before and after change Sub-task In Progress Undefined Kartik Shah

              karshah@redhat.com Kartik Shah
              karshah@redhat.com Kartik Shah
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Created:
                Updated: