Table: aws_dynamodb_tables

SELECT
  'DynamoDB tables should automatically scale capacity with demand' AS title,
  t.account_id,
  pr.arn AS resource_id,
  CASE
  WHEN t.billing_mode_summary->>'BillingMode' IS DISTINCT FROM 'PAY_PER_REQUEST'
  AND (
      (
        s.replica_provisioned_read_capacity_auto_scaling_settings->>'AutoScalingDisabled'
      )::BOOL
      IS NOT false
      OR (
          s.replica_provisioned_write_capacity_auto_scaling_settings->>'AutoScalingDisabled'
        )::BOOL
        IS NOT false
    )
  AND (pr._cq_id IS NULL OR pw._cq_id IS NULL)
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_dynamodb_tables AS t
  LEFT JOIN aws_dynamodb_table_replica_auto_scalings AS s ON s.table_arn = t.arn
  LEFT JOIN aws_applicationautoscaling_policies AS pr ON
      pr.service_namespace = 'dynamodb'
      AND pr.resource_id = concat('table/', t.table_name)
      AND pr.policy_type = 'TargetTrackingScaling'
      AND pr.scalable_dimension = 'dynamodb:table:ReadCapacityUnits'
  LEFT JOIN aws_applicationautoscaling_policies AS pw ON
      pw.service_namespace = 'dynamodb'
      AND pw.resource_id = concat('table/', t.table_name)
      AND pw.policy_type = 'TargetTrackingScaling'
      AND pw.scalable_dimension = 'dynamodb:table:WriteCapacityUnits';

SELECT
  'DynamoDB tables should have point-in-time recovery enabled' AS title,
  t.account_id,
  t.arn AS resource_id,
  CASE
  WHEN b.point_in_time_recovery_description->>'PointInTimeRecoveryStatus'
  IS DISTINCT FROM 'ENABLED'
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_dynamodb_tables AS t
  LEFT JOIN aws_dynamodb_table_continuous_backups AS b ON b.table_arn = t.arn;

SELECT
  'DynamoDB table with no items' AS title,
  account_id,
  arn AS resource_id,
  'fail' AS status
FROM
  aws_dynamodb_tables
WHERE
  item_count = 0;