Usage Data

SELECT
  DATE(transfer_ended_at) AS transfer_date,
  SUM(rows_transferred) AS total_rows_transferred
FROM
  `{{your-schema}}.model_transfers_log`
WHERE
  destination_id = '{{your-destination-id}}'
  AND transfer_ended_at >= CAST(CURRENT_DATE - INTERVAL '1 MONTH' AS TIMESTAMP)
GROUP BY
  transfer_date
ORDER BY
  transfer_date;

WITH LatestStatus AS (
  SELECT
    model_id,
    MAX(transfer_ended_at) AS last_transfer_time,
    ARRAY_AGG(status ORDER BY transfer_ended_at DESC LIMIT 1)[OFFSET(0)] AS latest_status
    MAX(CASE WHEN is_full_refresh THEN transfer_ended_at ELSE NULL END) AS last_full_refresh_time
  FROM
    `{{your-schema}}.model_transfers_log`
  WHERE
    destination_id = '{{your-destination-id}}'
  GROUP BY
    model_id
)
SELECT
  l.model_id,
  l.latest_status,
  l.last_transfer_time,
  l.last_full_refresh_time,
  CASE
    -- Have we ever triggered a transfer for this model?
    WHEN l.latest_status IS NULL THEN false
    ELSE true
  END AS transfer_has_been_triggered
FROM
  LatestStatus l
LEFT JOIN
  `{{your-schema}}.destinations_snapshot` d
  ON d.destination_id = '{{your-destination-id}}';

SELECT
  billing_date,
  destination_id,
  COUNT(*) AS count_transfers,
  COUNTIF(status = 'SUCCESS') AS count_transfers_succeeded,
  COUNTIF(status = 'ERROR') AS count_transfers_error,
  SUM(COALESCE(rows_transferred, 0)) AS total_rows_transferred,
  SUM(COALESCE(volume_transferred_in_mb, 0)) AS total_volume_transferred_mb,
  SUM(COALESCE(delta_rows_transferred, 0)) AS total_deltas_transferred,
  SUM(TIMESTAMP_DIFF(transfer_ended_at, transfer_started_at, SECOND)) AS total_transfer_time,
  SUM(TIMESTAMP_DIFF(transfer_started_at, transfer_submitted_at, SECOND)) AS total_queue_wait_time,
  SUM(TIMESTAMP_DIFF(transfer_ended_at, transfer_started_at, SECOND)) AS total_execution_time
FROM
  `prql-dev.testusagereporting.model_transfers_log`
GROUP BY
  billing_date, destination_id
ORDER BY
  billing_date DESC;

SELECT
  DATE(occurred_at) AS update_date,
  occurred_at AS update_timestamp,
  actor_id,
  actor_type,
  resource_id AS destination_id,
  previous_resource,
  current_resource
FROM
  `{{your-schema}}.audit_log`
WHERE
  event_type = 'DESTINATION_UPDATE'
  AND resource_id = '{{your-destination-id}}'
ORDER BY
  occurred_at DESC;