Openbridge is continually processing requests, retrying in the event of API errors, or executing rate limit backoff strategies mandated by the upstream API. As a result, data pipelines are in a continuous state of operations, especially those pipelines that leverage lookbacks.

Upstream APIs rarely guarantee a service level. As a result, data availability, or velocity, can vary significantly depending on API errors, limits, changes, or service degradation. Different data sources have different cadences. A source can be hourly, daily, some weekly, and others bi-weekly or monthly. This timing is dependent on the source system. As a result, the timing of when data may land at your destination can vary.

Please look at these two articles, which cover these topics in-depth:

To check the status of a data pipeline, understanding what is currently at your destination is the primary mechanism to accomplish this task. Below are a collection of diagnostic queries you can run to determine the current state of the data in your destination.

Below is a simple diagnostic query. The query provides a high-level overview of what is resident in your destination for a given data feed. You will want to adjust the FROM to your target. The example contains the sp_orders_report_master view for reference.

Below is the output of the query for sp_orders_report_master.

Looking at the results, you can see a column called ob_date or the "report" date. This is the report date Openbridge requested from the source system. Think of it like this; "Amazon, provide the report date 2023-05-17 for Order Tracking report(s) for Seller X". The response from Amazon to that request is loaded to sp_orders_report_master. As a result, the ob_date would-be 2023-05-17, the report date from Amazon.

Next, the load column reflected when data started to load in your destination for the report date. In our example query, we are looking at sp_orders_report_master. Since this report is DAILY, the settled, complete report date of 2023-05-17 would be requested on 2023-05-18. You can see an example of the data loading cadence below.

You can also see the last load for each reporting date by adjusting MIN to MAX in your query:

First and last load times can change depending on the data source limits, errors, or outages. If an API is delayed in supplying a report, we queue a request to try again later. As a result, the first load for a report can be 24-48 hours from a report date. This is normal behavior.

To see why this occurs, see the reference docs :

Here is another example using an infrequent bi-weekly report with variable report dates. This is not a function of "missing dates" but that the upstream system only makes data available infrequently.:

The cadence of the upstream source will dictate the frequency and cadence of timing.

Data sources may require LOOKBACK windows to re-request older dates for updated information. For example, on June 15th, Amazon back-updated the report data for May 20th. If May 20th is not re-requested, the data for that date may not be accurate or complete.

In a modified version of the query, we can include the first and latest load for a report date in the same result set:

As a general rule, any data source with LOOKBACKS will exhibit this behavior we detail for sp_vendor_traffic .

Most Vendor Retail Analytics reports have an offset in report availability. Typically this can be 3-7 days from the end of the reporting period. For example, Amazon will make the 5/20 Traffic Report available on 5/24, 5/25, or later. This is the latency of the source system making data available. As a result, there will always be latency of 3-7 days from the report date and the first load.

This data also employs LOOKBACKS. When reviewing LOOKBACK timing, the first_load column reflects the oldest load date and the latest_load the most recent. In most cases, these will mirror each other. This is a function of LOOKBACKS running.

In our table below, the 2023-06-05 ob_date shows that the latest load was on 2023-06-09. However, if you look at ob_date 2023-05-24, the latest update was 2023-06-07. This means the source system supplied updated information for 2023-05-24 on 2023-06-07.

Not every prior date may get updated. For example, 2023-06-05 may not have data loaded past 2023-06-09 if the source system does not supply updates. As a result, if you are looking at this data in July, the 2023-06-05 report may still have a last_load of 2023-06-09

If you want a finer-grain diagnostic, you can inspect by the hour. This can be useful, especially for hourly grain data feeds.

The query essentially retrieves data from a target table, calculates the day and hour-level information from the ob_date and ob_modified_date columns, and provides the minimum and maximum ob_modified_date values for each combination of transaction_day, transaction_hour, and ob_date within the past 14 days.

This query uses ORDER BY transaction_day DESC; because the data reflects daily reports. The results show that multiple load events are occurring each day and at different hourly intervals;

If you know your data source is hourly, you can adjust the results with ORDER BY transaction_hour DESC;. Here is an example of that query with a slight modification for ORDER BY and the target table;

This query will return results for sp_orders_master like this;

Providing objective, measurable examples is critical to troubleshooting. If you feel data is delayed or missing, please provide the relevant diagnostics detailed in this document with a support request. Providing guidance or support without objective and quantifiable measurements will be slow, time-consuming, and limited.

This document details how objectively quantity what is resident in your destination against the known behavior of various source systems.

When you submit a support request, please supply your analysis and the outputs from the diagnostic queries.