When you run a pipeline on a schedule, one of the most important things is that it runs reliably. The issue is that sometimes systems fail, it is inevitable. Even though Datagran has an uptime of 99%, we can’t control some of the issues that might arise.

With that in mind we recommend clients to put in place a monitoring system in their pipelines.

One of the eassiest ways to do this is with our SQL Operator and the Slack destination. This way user will receive a Slack message every time the pipeline fails.

In the code example below which will be part the Code Operator what we do is we compare a list of stock symbols with the daily content of the integration. If they match it means that the integration pulled all the previous day data for all the symbols, if not it means that there was an error in the integration.

That said the code will generate a table if there was an error and it will make the SQL Operator fail if it was succesful.

The reason we want the Operator to fail if it was succesful is because we don’t want the Slack destination to send the message if everything was ok, we only want to see a message if there is an error.

WITH symbol_list AS (
  SELECT symbol
  FROM UNNEST(['A', 'AAL', 'AAP']) AS symbol
),

unique_symbols AS (
  SELECT DISTINCT symbol
  FROM `Iex_Data__iex_data`
  WHERE PARSE_DATE('%Y-%m-%d', priceDate) = date_sub(current_date(), INTERVAL 1 DAY)
)

SELECT 
  IF(
    (SELECT COUNT(*) FROM unique_symbols) = (SELECT COUNT(*) FROM symbol_list)
    AND (SELECT COUNT(*) FROM symbol_list) = (SELECT COUNT(DISTINCT symbol) FROM unique_symbols), 
    ERROR("Verification successful"), 
    'verification'
  ) AS verification,
  'error' AS status

Below is a visual representation of the pipeline used:

The SQL Operator is in error because the check resulted in “successful”.