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

Data Cleanup for Invalid metric_id in contract_metric Table in prod

XMLWordPrintable

    • Icon: Story Story
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • None
    • swatch-contracts

      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 contract sync API. 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.
      • 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.

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

                Created:
                Updated: