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

Data Cleanup for Invalid metric_id in contract_metric Table

XMLWordPrintable

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

      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 a Liquibase script 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.
      • No valid data is affected.
      • 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 New Undefined Unassigned

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

                Created:
                Updated: