# Query and prepare data

Source: /docs/building/query-and-prepare-data.md

Use a one-off query to understand a dataset. Use a **transform** when a calculation should become a reusable dataset: daily stock risk, customer totals, model coefficients, or a cleaned table shared by several screens.

A transform runs in the background and publishes tables. An [API](/docs/building/backend-operations) answers a request. Prepare expensive or shared results in a transform so each user request can read the result without repeating the full calculation.

## Inspect the source before writing the calculation [#inspect-the-source-before-writing-the-calculation]

Have your coding agent inspect the actual schema and a bounded sample first. If the calculation depends on inferred keys or joins, follow the [data readiness checks](/docs/building/bring-in-data#wait-for-the-result-your-next-step-needs) before choosing the join.

For exploratory SQL, `vortexify analyze` submits a query against explicitly selected table IDs and returns an analysis job. This is useful for checking a join or aggregate; it does not create a source-controlled transform. Follow [Query data with SQL](/docs/data/querying) for complete single-table and join examples, result handling, and publication selection. The [CLI data reference](/docs/reference/cli/data) lists exact flags.

## Declare dependencies in code [#declare-dependencies-in-code]

Suppose `shop.orders` contains `customer_id` and numeric `amount`. Your coding agent can create `order_totals` and implement:

```python
from vortexify import Table, vxfy_transform


@vxfy_transform(engine="duckdb", inputs={"orders": Table("shop.orders")})
def transform(conn):
    return conn.execute("""
        SELECT customer_id, SUM(amount) AS total
        FROM orders
        GROUP BY customer_id
    """).df()
```

`Table("shop.orders")` selects the upstream dataset. The local key `orders` becomes the DuckDB relation name. Declaring it does not run the sync. You can commit this transform once the sync source is committed, even while its first run is unfinished. The required upstream output must exist when the transform executes. Use `Table("_upload.orders")` instead for an uploaded table named `orders`.

Choose Pandas for ordinary DataFrame processing, DuckDB for SQL over tables, or PySpark for workloads that need distributed processing. Declare the corresponding compute allocation in Python. Memory and disk settings use MiB; the [compute reference](/docs/reference/python/compute-and-schedules) lists the supported fields and presets.

Return a DataFrame for one output, or a dictionary of named DataFrames for several. Consumers use `TransformOutput("order_totals")` for a single output or `TransformOutput("resource.output")` for a named one. Keep names and column contracts stable when other resources depend on them.

## Test logic, then run against real data [#test-logic-then-run-against-real-data]

Your coding agent saves the source and runs the resource's mock tests before a live preview:

```sh
vortexify commit --type transform --resource order_totals
vortexify test transform order_totals --wait
vortexify run transform order_totals --wait
```

Mock tests supply their own inputs; they do not fetch current source data. The live run resolves saved input declarations and publishes real output. Optional output validation adds findings about that result, but validator findings or errors do not block otherwise successful publication. Inspect those findings separately from the run status.

Wait for completion and inspect output names, schema, row counts, and sample values before building a dependent API or transform. If that next step needs inferred keys or relationships, inspect the separate analysis status too. A previous successful output can remain readable after a newer run fails, so check the producer version and job you are inspecting.

In the platform, open **Playground → History** and select a run. **Output** opens its published tables, **Processing** shows validation and related jobs, and **Logs** shows execution output. In the table view, choose **Preview runs** or **Deployed version** and a published run; Data, Schema, and Relationships all refer to that selection. **Overview** shows the transform's upstream and downstream resources.

> **Platform screenshot placeholder:** A transform output showing its producer version, completed run, schema, and sample rows, with a validation finding visible beside the result.

## Rebuild consumers after a change [#rebuild-consumers-after-a-change]

A changed producer can make API or app previews stale. When you want to refresh affected preview consumers too, use the explicit `--rebuild-downstream` option on the producer run. The platform plans those downstream previews in dependency order and exposes their jobs under Processing. Otherwise, only the requested resource is run; see [CLI execution](/docs/reference/cli/execution).

## Decide when to refresh [#decide-when-to-refresh]

Declare a `CronSchedule` for a regular refresh or a `TriggerSchedule` for updates to upstream tables or transform outputs. Trigger mode `any` reacts to any declared dependency; `all` waits for all configured dependencies to update. Schedules are declared alongside the transform in Python.

Manual preview runs do not activate production schedules or fire downstream production triggers. Deploying selects the production source version; it does not execute the transform. Before publishing consumers, produce a successful output for that deployed version. See [publishing](/docs/building/publish-your-app) for the dependency order.

Next: [Build backend operations](/docs/building/backend-operations). See [transform contracts](/docs/reference/python/transforms) for injection rules and [testing and validation](/docs/reference/python/testing-and-validation) for test declarations.