Query data with SQL

Use an analysis query to answer a question about existing data: inspect rows, check a total, validate a join, or test a calculation before making it part of an app. You do not need to create a transform for each investigation.

The CLI submits one read-only DuckDB SELECT or WITH query against explicitly selected project tables. The server resolves their data and stores the query's result as an analysis artifact.

Inspect one datasetCopy Anchor Link

Find the table ID with vortexify tables and inspect its schema with vortexify table TABLE_ID --json. Suppose the selected orders table has customer_id and numeric amount. Save this as customer-revenue.sql:

SELECT customer_id, SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
ORDER BY revenue DESC;

Run it from your project workspace:

vortexify analyze --query-file customer-revenue.sql \
  --table orders=ORDERS_TABLE_ID --limit 20 --wait --json

Replace ORDERS_TABLE_ID with the inventory ID. orders is your SQL alias for this query; it does not need to match the source resource or table display name. The selected table supplies rows, while SQL chooses the fields and calculation.

Join datasets deliberatelyCopy Anchor Link

Repeat --table to register another SQL alias. If customers has one row per customer_id, a query could use:

SELECT c.customer_id, c.name, SUM(o.amount) AS revenue
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY revenue DESC;

vortexify analyze --query-file customer-revenue.sql \
  --table orders=ORDERS_TABLE_ID \
  --table customers=CUSTOMERS_TABLE_ID --wait

Save the join query into the file before running the second command. First inspect key and relationship findings and the actual columns. If customers contains duplicate customer IDs, joining can multiply order amounts. A declared alias or inferred relationship does not prevent that.

The server accepts one to ten table aliases. Use simple SQL identifiers, such as orders or customer_totals; file paths, arbitrary database connection strings, and unselected datasets are not query inputs.

Select current or deployed dataCopy Anchor Link

By default, file-backed analysis uses the latest published data available to project inspection. Add --deployed when checking publications for production producer versions:

vortexify analyze --query-file customer-revenue.sql \
  --table orders=ORDERS_TABLE_ID --deployed --wait

Check the producer's deployment before interpreting the result as production evidence. Managed database tables use the environment represented by the selected table entry. An analysis reads data; it does not apply database migrations or write application records.

The analysis command does not offer a --job input-publication selector. Its --job flag retrieves an existing analysis result. To inspect a precise historical table output, use table inspection or the platform's run-pinned table view.

Follow one analysis jobCopy Anchor Link

Submission returns a job ID. --wait observes that job until its processing is complete. To retrieve it later:

vortexify analyze --job ANALYSIS_JOB_ID --json
vortexify job ANALYSIS_JOB_ID --wait
vortexify logs ANALYSIS_JOB_ID

Use --job without --query-file or --table; it reads stored work rather than rerunning SQL. If the query fails, inspect the result and logs, revise the SQL, and submit a new analysis intentionally. Interrupting a wait leaves the existing job running.

Understand preview rows and saved resultsCopy Anchor Link

--limit controls the number of rows in the returned preview: default 20, allowed range 1–100. It is separate from SQL LIMIT, which changes the query's result itself.

A query can save more rows than the CLI preview shows. Inspect the result's row count, columns, and truncation flags. The current analysis worker caps stored results at 100,000 rows and rejects a Parquet result larger than 64 MiB. Aggregate or add a deliberate SQL limit for large outputs. A truncated result cannot establish a complete total over the omitted rows.

The saved artifact can be read later through the analysis data service, including by the React SDK's getAnalysisData. An assistant-generated chart uses this same result pattern, with a stricter chart limit; see analysis charts.

Make the calculation reusableCopy Anchor Link

Keep using analysis for exploration. Move a calculation into a transform when it needs source-controlled tests, scheduling, published output tables, and downstream dependencies. Use an API when a screen should send inputs and receive a response.

See CLI data commands for all flags and tables for publication selection.