Implement a Claim Check Reader

Overview

A claim-check pattern is implemented by first obtaining one or more leading records and request additional details for each leading record. A leading record may come from any source system; it also has a unique key field (or fields) that are used to request additional details.

While any source system may be used to fetch leading records, DataZen only supports the claim-check pattern when details are returned by a DB or HTTP endpoint.

HTTP Claim Check

Use the APPLY HTTP operation with the left join or inner join PROCESSING option to implement the claim check pattern with HTTP endpoints. For example, this pipeline gets airport codes from a database and calls the flightaware API to retrieve departures from each airport.

-- Get a list of airport codes 
SELECT * FROM DB [sql] 
(SELECT icao FROM airportcodes);

-- Call the flightaware API for each airport code; this API supports paging using the link strategy
APPLY HTTP [flightaware] (GET /airports/{{icao}}/flights?type=Airline&max_pages=2)
WITH 
    PROCESSING 'left join'
    PAGING 'link'
    PAGING_PATH 'links.next'
APPLY TX 'scheduled_departures';

Database Claim Check

Use the EXEC ON DB operation with the PER_ROW option to use the claim check pattern against a database. For example, while not very efficient from a performance standpoint, this pipeline returns a list of databases with a count of their tables.

-- Get leading records
SELECT TOP 25 * FROM DB [sql2017]
(
  SELECT database_id, name FROM sys.databases
);

-- Execute this script for every record in the data pipeline
EXEC ON DB [sql2017]
(
  SELECT {{database_id}} as database_id, '{{name}}' as name, COUNT(*) as tableCount FROM [{{name}}].sys.tables 
) WITH REPLACE PER_ROW;