14Audit & health check

SELECT table_schema, COUNT(*) AS tables
FROM   system.information_schema.tables
WHERE  table_catalog = 'system'
GROUP BY table_schema
ORDER BY table_schema;

SELECT
  SUM(usage_quantity)                                AS dbus,
  ROUND(100 * SUM(CASE WHEN custom_tags['domain'] IS NULL
                       THEN usage_quantity ELSE 0 END)
            / NULLIF(SUM(usage_quantity), 0), 1)     AS pct_no_domain,
  ROUND(100 * SUM(CASE WHEN custom_tags['environment'] IS NULL
                       THEN usage_quantity ELSE 0 END)
            / NULLIF(SUM(usage_quantity), 0), 1)     AS pct_no_environment
FROM system.billing.usage
-- 30 complete days, ending two days back so ingestion latency cannot clip the
-- right edge. current_date() - 31 .. - 2 is 30 days inclusive.
WHERE usage_date BETWEEN current_date() - INTERVAL 31 DAYS
                     AND current_date() - INTERVAL 2 DAYS
-- ORIGINAL only: this is a SHARE, and counting RETRACTION/RESTATEMENT rows
-- alongside their originals double-counts the denominator. A DOLLAR total needs
-- the netted form instead — see the library's restatement trust query.
  AND record_type = 'ORIGINAL';

SELECT c.column_name, t.tag_name, t.tag_value
FROM      system.information_schema.columns     AS c
LEFT JOIN system.information_schema.column_tags AS t
       ON t.catalog_name = c.table_catalog
      AND t.schema_name  = c.table_schema
      AND t.table_name   = c.table_name
      AND t.column_name  = c.column_name
WHERE c.table_catalog = 'dwh_prod'
  AND c.table_schema  = 'gold'
  AND c.table_name    = 'dim_customer'
  AND c.column_name  IN ('email', 'phone', 'street')
ORDER BY c.column_name;