DataZen Documentation
DataZen User Guide

Implement Claim Check Patterns

Overview

A Claim Check pattern exchanges a lightweight reference instead of moving the complete payload with a request. DataZen supports lookup-based claim checks, where leading records contain keys used to retrieve additional details, and file-based claim checks, where a large pipeline data set is stored temporarily and only links to the generated files are sent to a remote system.

Use HTTP or database claim checks to retrieve details for leading records. Use a file-based claim check when a remote service should retrieve and process a large payload from temporary storage.

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;

File-Based Claim Check

A file-based Claim Check is useful when data engineers need to send a large amount of data to a remote system, but want the request itself to contain only a command and links to the data files. Use SINK INTO DRIVE with the REPLACE and AUTO_DELETE options to implement this pattern.

  • REPLACE — replaces the current pipeline data with one or more rows that identify the file name and path created for each file. A single SINK operation can generate multiple files, depending on its options.
  • AUTO_DELETE — automatically deletes files created by the SINK operation when the pipeline completes, regardless of success or failure. Use it when files are needed only temporarily by a remote service, such as an AI agent, and should be removed after the call.

The remote service must retrieve the temporary files before the pipeline completes, because AUTO_DELETE removes them at completion. 

-- Get HTTP data 
SELECT *
FROM HTTP [RSS Gov Feed] (GET /econi.xml) APPLY TX (//item);

-- Transform the data into a CSV format 
ADD COLUMN 'csv' FORMAT CSV OPTIONS 'delimiter:|' EXCLUDE 'description';
ZIP COLUMN 'csv' FORMAT CSV OPTIONS 'headers:1';

-- Create a CSV file and return the file name of the file created 
-- The file name is variable since it is using the current execution id 
-- and it will be automatically deleted at the completion of this pipeline 
SINK INTO DRIVE [adls] FORMAT CSV 
  FILE 'data_@executionid.txt'
  REPLACE 
  AUTO_DELETE 
;

-- Call an AI agent to process the content of the temporary file 
-- Notice the use of {{container}} and {{file}} which are columns returned by the 
-- SINK operation when REPLACE is used 
CALL AGENT [AIAgent]
 TEMPERATURE 0.2
 PROMPT 'Inspect the file located here: 
  https://mystorage.blob.core.windows.net/{{container}}/{{file}} 
  and provide a short summary of its content.

  You should stop processing this request after 30 seconds if no answer can be 
  returned in that timeframe.
'
;

-- Extract the response of the agent 
APPLY TX '..text';

In this example, REPLACE makes the generated container and file values available to the prompt. The AI agent reads the CSV from storage, returns a summary, and the temporary file is deleted when the pipeline completes. See CALL AGENT for agent-call configuration.