SQL statements to monitor or troubleshoot replication and related objects

montior_replication_and_related_objects.sql

-- Lists the streams for which you have access privileges
SHOW STREAMS LIKE 'dp_logs_stream' IN ACCOUNT;

-- Describes the columns in a stream
DESCRIBE STREAM amadeus_dynamic_pricing.dynamic_pricing.dp_logs_stream;

-- Lists the stored procedures that you have privileges to access
SHOW PROCEDURES LIKE 'INSERT_DP_LOGS' IN ACCOUNT;

-- Lists the tasks for which you have access privileges
SHOW TASKS LIKE 'DP_LOGS_TASK' IN ACCOUNT;

-- Table function can be used to query the history of task usage
SELECT *
FROM TABLE(information_schema.task_history())
ORDER BY scheduled_time DESC;

-- Describes the columns in a task
DESCRIBE TASK amadeus_dynamic_pricing.dynamic_pricing.dp_logs_task;

-- Table function can be used to query the history of task usage based on start time, row limit and task name
select *
  from table(information_schema.task_history(
    scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
    result_limit => 10,
    task_name=>'DP_LOGS_TASK'));
    
-- Lists all the accounts in your organization that are enabled for replication
SHOW REPLICATION ACCOUNTS;

-- Lists all the primary and secondary databases
SHOW REPLICATION DATABASES;

-- Match row count between shared table and target table in the Azure snowflake account
SELECT 'Share Table Count', COUNT(*)
FROM dynamic_pricing_data_share_by_amadeus.dynamic_pricing.dp_logs
UNION ALL
SELECT 'Target Table Count', COUNT(*)
FROM amadeus_dynamic_pricing.dynamic_pricing.dp_logs;

-- This table function can be used to query the replication history within a specified date range
SELECT *
  FROM TABLE(information_schema.replication_usage_history(
    date_range_start=>DATEADD(h, -5, CURRENT_TIMESTAMP)));
    
-- Family of functions can be used to query the status of a database refresh
SELECT *
FROM TABLE(information_schema.database_refresh_progress('AMADEUS_DYNAMIC_PRICING'));

SELECT *
FROM TABLE(information_schema.database_refresh_progress_by_job('Query_ID'));