Query Celigo Storage from a flow with an async helper

Start a flow from the rows of a Celigo Storage query. You create an export on a Celigo APIs connection that submits the query, and you attach an async helper that polls the job and fetches its result pages. The rows become the flow's source records, ready to filter, map, and import like any other export. Use it to start a flow from a deduplicated folder of daily files, an aggregate over a month of orders, or a join of two exports, without a second flow or a hook.

The query API is a job, not a request. You submit a statement, poll its status, and fetch the result one page at a time. That is three calls, and an HTTP export makes one. The async helper adds the other two. It's the same platform feature that drives Amazon report and feed flows, configured here for the query API. There is no query step in the flow builder — this export-plus-helper configuration is how a flow runs a query.

For an introduction to queries, including folder references, authorization, and limits, see Query files in Celigo Storage. To learn more about Celigo Storage, see Celigo Storage overview.

How the pieces fit

Three resources make the three calls. The export you add to the flow makes the first, and the async helper owns two more exports that make the other two.

1. Submit. The flow's export sends POST /v1/storage/query with { "sql": "..." }. Celigo checks the statement, the paths, and the connection's access, and returns 202 with a jobId and status: "queued". The async helper keeps that response, so {{{data.jobId}}} in the next two exports refers to this job.
2. Poll. The async helper's status export sends GET /v1/storage/query/{jobId}/status on the interval you set, 1 minute by default. The helper reads the status field. queued and running mean poll again. completed means fetch the result. failed stops the flow step with an error.
3. Fetch. The async helper's result export sends GET /v1/storage/query/{jobId}/results?page=1, then page=2, and so on until the page number reaches the response's pageCount. Every object in rows becomes a record, and the flow pages the records as it does for any export.

The limits of a job apply unchanged. Execution times out after 300 seconds, counted from when the job starts running, and the job record and its pages last about 24 hours. Results are capped at 1,000,000 rows; a job whose result hits the cap completes with truncated: true on its status response, and the flow receives only the rows that were kept. Use LIMIT in the statement to bound a result.

Create the export

1. In the flow builder, select Add source.
2. In Create source, select Celigo APIs in Application, and then select your connection in Connection.
3. Select Export records from source application, and then select Next.
4. In General, name the export, and then set Form view to HTTP. The other option, the connector's own form, doesn't reach the query API. The HTTP view exposes the request fields.
5. In What would you like to export?, enter the request.
   • HTTP method — POST
   • Relative URI — /v1/storage/query

   • HTTP request body — one JSON object whose sql field holds the statement. Reference a folder with a trailing slash to read every file directly inside it.

     {
       "sql": "SELECT DISTINCT invoice_number, invoice_date, amount FROM '/inbound/invoices/' ORDER BY invoice_date"
     }

6. In Configure export type, leave Export type at All – always export all data. To export only new rows on each run, see Use the export in a flow.
7. Leave the Does this API use paging? section unconfigured and Path to records in HTTP response body empty. The submit response is one object, and the async helper's result export does the paging.

Before you preview, attach the async helper so that the preview parses the response the way the flow will.

Create the async helper

You create the async helper from the export's Advanced section. It holds two more exports on the same connection: one that checks the job's status and one that fetches the result pages.

1. Expand Advanced, and then select Configure async helper.
2. Next to Async helper, select +. The Create async helper panel opens.
3. In Name, enter a name, for example Celigo Storage query job.

4. Under Configure how to check status, next to Status export, select + to create the status export, and then enter the following.

   • Name your export — for example Storage query status
   • Connection — the same Celigo APIs connection
   • HTTP method — GET
   • Relative URI — /v1/storage/query/{{{data.jobId}}}/status
   • Export type — All – always export all data
   • Path to records in HTTP response body — leave empty. The whole status response is the record the async helper reads.

   Select Save.

5. Finish the status settings in the async helper.
   • Initial wait time — leave empty to check 1 minute after submit, or enter a number of minutes. Most queries finish within seconds, so the first check usually finds the job done.
   • Poll wait time — leave empty to check every minute while the job runs, or enter a number of minutes.
   • Status path — status
   • In progress values — queued and running
   • Done values — completed
   • Done without data values — leave empty. A query that matches no rows completes normally and returns an empty first page.
   • Error values — failed

6. Under Configure how to get results, next to Result export, select + to create the result export, and then enter the following.

   • Name your export — for example Storage query results
   • Connection — the same Celigo APIs connection
   • HTTP method — GET
   • Relative URI — /v1/storage/query/{{{data.jobId}}}/results?page={{export.http.paging.page}}
   • Export type — All – always export all data
   • In Does this API use paging?, set three fields.
     • Paging method — Page number parameter
     • Override page number start index — 1
     • Path to total number of pages field in HTTP response body — pageCount
   • In Non-standard API response patterns, set Path to records in HTTP response body to rows.

   Select Save.

7. Under Configure how to process initial submission, leave Same as check status selected. The submit response is a single object with no wrapper, the same shape as the status response, so it needs no Resource path or Transform rules.
8. Select Save & close. The export's Async helper field shows the helper you created.

The status values are case-sensitive and matched exactly. Page numbers start at 1, so the start index must be 1 — the API rejects page=0. The result export stops when the page number reaches pageCount. A job with no rows reports pageCount: 0, so an empty result ends after one request.

Preview and save the export

1. Select Preview. The preview sends the submit request and shows its response: a jobId and status: "queued". The status and result calls run only when the flow runs. Each preview submits, and runs, one query job.
2. If the preview shows a 4xx error, Celigo rejected the statement, a path, or the connection's access, and no job exists. The response body's errors array carries a code and message. Look the code up in Troubleshoot a Celigo Storage query job, fix the cause, and preview again.
3. Select Save & close.

Use the export in a flow

Add the destination steps and map the columns as you would for any export. Each result row is a flat JSON object keyed by column name. Whole-number columns arrive as strings, because Celigo returns 64-bit integers as strings so that no digits are lost. count() and sum() over an integer column follow the same rule. To map a number, cast in the statement, for example CAST(COUNT(*) AS INTEGER), or declare the type in the reader, for example read_csv('/inbound/invoices/', types = {'quantity': 'INTEGER'}). See Celigo storage query read options and format behavior.

Schedule the flow like any other. Each run submits one job, and the source step stays in progress while the async helper polls, so a run takes at least the first wait interval plus the query's execution time. A test run isn't available — the flow builder reports You can't use this source for a test run as it uses async helper. Use Run flow.

To export only rows added since the last run, set Export type to Delta – export only modified data and put {{lastExportDateTime}} in the statement. The connector renders handlebars in the HTTP request body before it sends the request, and the value is an ISO 8601 timestamp in UTC, for example 2026-09-12T18:02:11.418Z. Compare it with a DATE or TIMESTAMP column, or cast a text column to one of those types; a comparison against a text column is a string comparison and can return the wrong rows.

{
  "sql": "SELECT invoice_number, invoice_date, amount FROM '/inbound/invoices/' WHERE invoice_date > '{{lastExportDateTime}}' ORDER BY invoice_date"
}

The query API itself has no templating, so anything the connector doesn't render reaches the API as literal text. How that fails depends on where the expression sits. An unrendered expression outside a quoted string, such as WHERE invoice_date > {{lastExportDateTime}} with no quotes, isn't valid SQL, and Celigo rejects the statement at submit with 400 STORAGE_QUERY_INVALID_SQL. An unrendered expression inside a quoted string, as in the example above, is valid SQL — it's just a string literal — so the statement passes every check and the job runs. Compared with a DATE or TIMESTAMP column, the job then fails with a conversion error. Compared with a text column, the job completes and returns the wrong rows without any error. If a Delta export returns no rows or unexpected rows, run the rendered statement through the API and check the value that reached it.

Troubleshoot the flow configuration

The preview returns an error

A 4xx means Celigo rejected the request before any query ran, and no job exists. errors[0].code names the check that failed. The common ones are STORAGE_QUERY_SOURCE_NOT_FOUND for a path that is wrong, deleted, or outside the connection's File storage paths, STORAGE_NOT_A_FILE for a folder referenced without its trailing slash, and STORAGE_QUERY_INVALID_SQL for a statement that doesn't parse. 401 STORAGE_QUERY_ACCESS_REFUSED from a Custom connection means the connection has no File storage paths — add the folders, or use a Full access connection. 403 STORAGE_QUERY_NOT_ENTITLED means querying isn't enabled for the account. For every code, see Troubleshoot a Celigo Storage query job.

The flow fails with "Unable to process the request as the returned status is failed"

The job ran and failed. The flow's error names the status, not the cause. To find the cause, read the job's error from the API.

1. Open Debug connection on the Celigo APIs connection.
2. Run the flow, and then select View debug logs. The submit response holds the jobId.
3. Send GET /v1/storage/query/{jobId}/status with an API token, and read error.code and error.message. The job record is kept for about 24 hours.

STORAGE_QUERY_TIMEOUT means the query ran for more than 300 seconds, so narrow it with WHERE, reference a smaller folder, or select fewer columns. STORAGE_QUERY_EXECUTION_FAILED usually means a column's type drifted past the inference sample, so declare the type in the reader or use TRY_CAST. STORAGE_QUERY_JOB_INTERRUPTED means Celigo's service stopped while it held the job, so run the flow again.

The flow fails with "Async export status can not be extracted using give path"

The async helper couldn't find a value at Status path. Check that Status path is status and that the status export's Path to records in HTTP response body is empty. If the flow fails with Unknown async export status received, a value in In progress values, Done values, or Error values doesn't match the API's lowercase queued, running, completed, and failed.

The source step runs for a long time

The async helper checks the status once per Poll wait time, 1 minute by default, and a job waits in the queue before it runs, so a run of a fast query still takes a minute or two. A job normally reaches completed or failed within 300 seconds of starting. If Celigo's service stops while it holds the job, Celigo marks the job failed with STORAGE_QUERY_JOB_INTERRUPTED within about 7 minutes, and the flow step fails on its next poll. Run the flow again. If a step stays in progress much longer than that, cancel the run and run the flow again. If it recurs, contact Celigo Support with the jobId.

Only the first page of rows arrives, or the run fails with STORAGE_QUERY_PAGE_OUT_OF_RANGE

Check the result export's paging. Paging method must be Page number parameter, Override page number start index must be 1, Path to total number of pages field in HTTP response body must be pageCount, and the Relative URI must include ?page={{export.http.paging.page}}. A start index of 0 requests page=0, and the API rejects that page.

The run completes with no records

The job completed with rowCount: 0, and page 1 returned an empty rows array. Run the statement through the API to check that it matches rows. A Delta export finds no rows when nothing changed since the previous run, and that is expected. If the statement compares {{lastExportDateTime}} with a text column, the comparison is a string comparison and may match nothing; see Use the export in a flow.

Learn more

• About querying Celigo Storage files
• Query files in Celigo Storage
• Troubleshoot a Celigo Storage query job
• Celigo Storage query API reference
• Celigo Storage query SQL reference
• Asynchronous exports and imports using async helpers
• Celigo storage query read options and format behavior
• Export data from an HTTP source application
• API pagination
• Set up a connection to Celigo APIs
• Celigo Storage overview
• Manage Celigo Storage files with the Celigo APIs connector