Monitor scheduled queries

Adobe Experience Platform provides improved visibility for the status of all query jobs through the UI. From the Scheduled Queries tab, you can now find important information about your query runs that includes the status, schedule details, and error messages/codes should they fail. You can also subscribe to alerts for queries based on their status through the UI for any of these queries through Scheduled Queries tab.

Scheduled Queries

The Scheduled Queries tab provides an overview of all your scheduled CTAS and ITAS queries. Run details can be found for all scheduled queries as well as error codes and messages for any failed queries.

To navigate to the Scheduled Queries tab, select Queries from the left navigation bar followed by Scheduled Queries

The Scheduled Queries tab in the Queries workspace with Scheduled Queries and Queries highlighted.

The table below describes each available column.

NOTE
The alert subscriptions icon ( An alert subscriptions icon. ) is contained in each row in an untitled column. See the alert subscriptions section for more information.
Column
Description
Name
The name field is either the template name or the first few characters of your SQL query. Any query created through the UI with the Query Editor is named at inception. If the query was created through the API, then its name becomes a snippet of the initial SQL used to create the query. To see a list of all runs associated with the query, select an item from the Name column. For more information, see the query runs schedule details section.
Template
The template name of the query. Select a template name to navigate to the Query Editor. The query template is displayed in the Query Editor for convenience. If there is no template name, the row is marked with a hyphen and there is no ability to redirect to the Query Editor to view the query.
SQL
A snippet of the SQL query.
Run frequency
The cadence at which your query is set to run. The available values are Run once and Scheduled.
Created by
The name of the user who created the query.
Created
The timestamp when the query was created, in UTC format.
Last run timestamp
The most recent timestamp when the query was run. This column highlights whether a query has been executed according to its current schedule.
Last run status
The status of the most recent query execution. The status values are: Success, Failed, In progress, and No runs.
Schedule Status

The current status of the scheduled query. There are six potential values, Registering, Active, Inactive, Deleted, a hyphen, and Quarantined.

  • The Registering status indicates that the system is still processing the creation of the new schedule for the query. Note, you cannot disable or delete a scheduled query while it is registering.
  • The Active status indicates that the scheduled query has not yet passed its completion date and time.
  • The Inactive status indicates that the scheduled query has passed its completion date and time or has been marked by a user to be in a inactive state.
  • The Deleted status indicates that the query schedule has been deleted.
  • The hyphen indicates the scheduled query is a one-time, non-recurring query.
  • The Quarantined status indicates that the query has failed ten consecutive runs and requires your intervention before any further executions can take place.
TIP
If you navigate to the Query Editor, you can select Queries to return to the Templates tab.

Customize table settings for scheduled queries customize-table

You can adjust the columns on the Scheduled Queries tab to your needs. To open the Customize table settings dialog and edit available columns, select the settings icon ( A settings icon. ) from the top right of the screen.

NOTE
The Created column that refers to the date the schedule was created, is hidden by default.

The Scheduled Queries tab with the Customize table settings icon highlighted.

Toggle the relevant checkboxes to remove or add a table column. Next, select Apply to confirm your choices.

NOTE
Any query that was created through the UI becomes a named template as part of the creation process. The template name is seen in the template column. If the query was created through the API, then the template column is blank.

The Customize table settings dialog.

Manage scheduled queries with inline actions inline-actions

The Scheduled Queries view offers various inline actions to manage all of your scheduled queries from one location. Inline actions are indicated in each row with ellipsis. Select the ellipsis of a scheduled query that you want to manage to see the available options in a pop-up menu. The available options include Disable schedule or Enable schedule, Delete schedule, Subscribe to query alerts, and Enable or Disable quarantine.

The Scheduled Queries tab with the inline action ellipses and popup menu highlighted.

Disable or enable a scheduled query disable

To disable a scheduled query, select the ellipsis for the scheduled query you want to manage, then select Disable schedule from the options in the pop-up menu. A dialog appears to confirm your action. Select Disable to confirm your setting.

Once a scheduled query is disabled, you can enable the schedule through the same process. Select the ellipsis, then select Enable schedule from the available options.

NOTE
If a query has been Quarantined, you should review the template’s SQL before you enable its schedule. This prevents a waste of compute hours if the template query still has issues.

Delete a scheduled query delete

To delete a scheduled query, select the ellipsis for the scheduled query you want to manage, then select Delete schedule from the options in the pop-up menu. A dialog appears to confirm your action. Select Delete to confirm your setting.

Once a scheduled query is deleted, it is not removed from the list of scheduled queries. The inline actions provided by the ellipses are removed and replaced by the grayed out add alert subscription icon. You cannot subscribe to alerts for the deleted schedule. The row remains in the UI to provide information on runs conducted as part of the scheduled query.

The Scheduled Queries tab with a deleted scheduled query and greyed out alert subscription icon highlighted.

If you want to schedule runs for that query template, select the template name from the appropriate row to navigate to the Query Editor, then follow the instructions to add a schedule to a query as described in the documentation.

Subscribe to alerts alert-subscription

To subscribe to alerts for scheduled query runs, select either the ... (ellipsis) or alert subscription icon ( An alert subscription icon. ) for the scheduled query you want to manage. The inline actions dropdown menu appears. Next, select Subscribe from the available options.

The scheduled queries workspace with an ellipses, alert subscription icon, and the inline actions dropdown menu highlighted.

The Alerts dialog opens. The Alerts dialog subscribes you to both UI notifications and email alerts. There are several alert subscription options available: start, success, failure, quarantine, and delay. Check the appropriate box or boxes and select Save to subscribe.

The alert subscriptions dialog.

The table below explains the supported query alert types:

Alert type
Description
start
This alert notifies you when a scheduled query run is initiated or starts to process.
success
This alert informs you when a scheduled query run completes successfully, indicating that the query executed without any errors.
failed
This alert triggers when a scheduled query run encounters an error or fails to execute successfully. It helps you identify and address issues promptly.
quarantine
This alert is activated when a scheduled query run is put into a quarantined state. When queries are enrolled in the quarantine feature, any scheduled query that fails ten consecutive runs is automatically put into a Quarantined state. They then require your intervention before any further executions can take place.
delay
This alert notifies you if there is a delay in the outcome of a query execution beyond a specified threshold. You can set a custom time that trigger the alert when the query runs for that duration without either completing or failing.
NOTE
To be notified of query runs becoming quarantined, you must first enroll the scheduled query runs in the quarantine feature.

See the alert subscriptions API documentation for more information.

View the query details query-details

Select the information icon ( An information icon. ) to see the details panel for the query. The details panel contains all the relevant information on the query beyond the facts included in the scheduled queries table. The additional information includes the query ID, the last modified date, the SQL of the query, the schedule ID, and the current set schedule.

The Scheduled Queries tab with the information icon and the details panel highlighted.

Quarantined queries quarantined-queries

NOTE
The quarantine alert is not available for ‘run-once’ ad hoc queries. The quarantine alert is only applicable for scheduled batch (CTAS and ITAS) queries.

When enrolled in the quarantine feature, any scheduled query that fails ten consecutive runs is automatically put into a Quarantined status. A query with this status becomes inactive and does not execute at its scheduled cadence. It then requires your intervention before any further executions can take place. This safeguards system resources as you must review and correct the issues with your SQL before further executions occur.

To enable a scheduled query for the quarantine feature, select the ellipses (...) followed by Enable quarantine from the dropdown menu that appears.

The scheduled queries tab with the ellipses and Enable quarantine highlighted from the inline actions dropdown menu.

Queries can also be enrolled in the quarantine feature during the schedule creation process. See the query schedules documentation for more information.

Query run delay query-run-delay

Stay in control of your compute hours by setting alerts for query delays. You can monitor query performance and receive notifications if a query’s status remains unchanged after a specific period. Use the ‘Query Run Delay’ alert to be notified if a query continues to process after a specific period of time without completing.

When you subscribe to alerts for scheduled query runs, one of the available alerts is the Query Run Delay. This alert requires you to set a threshold for the time spent executing, at which point you are notified of the delay in processing.

To choose a threshold duration that triggers the notification, either enter a number in the text input field or use the up and down arrows to increase in increments of one minute. Since the threshold is set in minutes, the maximum duration for observing a query run delay is 1440 minutes (24 hours). The default time period for a run delay is 150 minutes.

NOTE
A query run can only have one run delay time. If you change the delay threshold, it is changed for user subscribed to the alert and for your entire organization.

The Alerts dialog on the scheduled queries tab with the query run delay input field highlighted.

See the subscribe to alerts section to learn how to subscribe to Query Run Delay alerts.

Filter queries filter

You can filter queries based on run frequency. From the Scheduled Queries tab, select the filter icon ( A filter icon ) to open the filter sidebar.

The scheduled queries tab with the filter icon highlighted.

To filter the list of queries based on their run frequency, select either the Scheduled or Run once filter checkboxes.

NOTE
Any query that has been executed but not scheduled qualifies as Run once.

The scheduled queries tab with the filter sidebar highlighted.

Once you have enabled your filter criteria, select Hide Filters to close the filter panel.

Query runs schedule details query-runs

To open the schedule details page, select a query name from the Scheduled Queries tab. This view provides a list of all the runs executed as part of that scheduled query. The information provided includes the start and end time, status, and dataset used.

The schedule details page.

This information is provided in a five-column table. Each row denotes a query execution.

Column name
Description
Query run ID
The query run ID for the daily execution. Select the Query run ID to navigate to the Query run overview.
Query run start
The timestamp when the query was executed. The timestamp is in UTC format.
Query run complete
The timestamp when the query was completed. The timestamp is in UTC format.
Status
The status of the most recent query execution. The status values are: Success, Failed, In progress, or Quarantined.
Dataset
The dataset involved in the execution.

Details of the query being scheduled can be seen in the Properties panel. This panel includes the initial query ID, client type, template name, query SQL, and cadence of the schedule.

The schedule details page with the properties panel highlighted.

Select a query run ID to navigate to the run details page and view query information.

The schedule details screen with a run ID highlighted.

Query run overview query-run-overview

The Query run overview provides information on individual runs for this scheduled query and a more detailed breakdown of the run status. This page also includes the client information and details of any errors that may have caused the query to fail.

The run details screen with the overview section highlighted.

The query status section provides the error code and error message should the query have failed.

The run details screen with the errors section highlighted.

You can copy the query SQL to your clipboard from this view. To copy the query, select the copy icon in the top right of the SQL snippet. A popup message confirms that the code has been copied.

The run details screen with the SQL copy icon highlighted.

Run details for queries with anonymous block anonymous-block-queries

Queries that use anonymous blocks to comprise their SQL statements are separated into their individual subqueries. The separation into subqueries allows you to inspect the run details for each query block individually.

NOTE
The run details of an anonymous block that uses the DROP command will not be reported as a separate subquery. Separate run details are available for CTAS queries, ITAS queries, and COPY statements used as anonymous block subqueries. Run details for the DROP command are currently not supported.

Anonymous blocks are denoted through the use of a $$ prefix before the query. To find out more about anonymous blocks in query service, see the anonymous block document.

Anonymous block subqueries have tabs to the left of the run status. Select a tab to display the run details.

The Query run overview displaying an anonymous block query. The multiple query tabs are highlighted.

In the event an anonymous block query fails, you can find the error code for that particular block through this UI.

The Query run overview displaying an anonymous block query with the error code for a single block highlighted.

Select Query to return to the schedule details screen, or Scheduled Queries to return to the Scheduled Queries tab.

The run details screen with Query highlighted.

recommendation-more-help
ccf2b369-4031-483f-af63-a93b5ae5e3fb