# How Computed Files Work

Source: https://userguide.qualytics.io/container/computed-files/deep-dive/how-it-works/

A Computed File is a piece of metadata inside Qualytics. Its Spark SQL definition lives with the datastore configuration; nothing is written back to DFS storage. This page walks through how the platform resolves that metadata into actual data every time it needs to look at the container.

For what a Computed File can reference and how it composes with a Computed Join, see [Referencing](https://userguide.qualytics.io/container/computed-files/deep-dive/referencing/). For what happens on create/edit/delete and how tracking works, see [Lifecycle](https://userguide.qualytics.io/container/computed-files/deep-dive/lifecycle/). For who can do what, see [Permissions](https://userguide.qualytics.io/container/computed-files/deep-dive/permissions/).

## The Execution Model

A Computed File is never materialized as a physical file on storage. When Qualytics needs to look at the container (during a scan, a profile, a quality check run, or a preview), it takes the stored Spark SQL clauses, expands the source file pattern into the matching files, and evaluates the expression on top of them. The Dataplane returns the rows, and Qualytics treats those rows as the container's live data.

This has three practical consequences you should keep in mind while designing a Computed File:

- **Every scan is fresh.** There is no cached copy of the previous run. If the underlying files changed on storage, the next scan sees the new data.
- **Query cost is real.** Complex expressions run every time the container is scanned, profiled, or previewed. Aggregations over very large file patterns can slow the run down; consider narrowing the `where_clause`, using partition-aware source patterns, or moving heavy preparation into a separate base file pattern that Qualytics syncs independently.
- **Validation is not execution.** The **Validate** button (see below) confirms the clauses are syntactically and semantically valid against the source file schema. It does not run the expression end-to-end over the entire file pattern.

## Validation

Every Add and Edit modal exposes a **Validate** button. Clicking it sends the current form values to the platform for parse and semantic checks:

- The `select_clause`, `where_clause`, `group_by_clause`, and any `lateral_views` must all parse as valid Spark SQL.
- Every lateral view must begin with a supported generator function (see [Lateral Views](https://userguide.qualytics.io/container/computed-files/deep-dive/how-it-works/#lateral-views) below).
- Every field referenced by any clause must exist in the source file's profiled schema.
- The source file container identified by `source_container_id` must be reachable on storage.
- Aggregation and grouping must be internally consistent (every non-aggregated field in the Select Expression must appear in Group By, and so on).
- No output column may nest arrays of structs deeper than Qualytics can profile (see [Nesting Depth](https://userguide.qualytics.io/container/computed-files/deep-dive/how-it-works/#nesting-depth) below).

If any of those checks fail, the modal surfaces an inline error that quotes the failed clause and the reason so you can fix it in place. Running Validate is optional: you can click Save directly, in which case Qualytics validates the clauses as part of the save request and returns any error inline.

When you click the **Validate** button, the request runs synchronously with a default timeout of 150 seconds (configurable per request between 30 and 300 seconds). If the Dataplane does not respond in time, the request returns `408 Request Timeout`. If you skip Validate and click Save, the same validation runs inline as part of the Save request under a longer platform-level synchronous timeout (500 seconds by default), and any error still surfaces inline. Very complex expressions against very large file patterns can push either path toward its limit.

### Nesting Depth

A column that holds an array of arrays of structs (`array<array<struct>>`) can give its structs scalar or array fields, but not struct fields. A struct inside those structs is past the depth Qualytics can profile, so Validate refuses the definition and names the field. Saving it, when you add or edit the Computed File, fails with the same message:

```text
Nested arrays of structs go deeper than supported at order_items.price. In an array of arrays of structs (array<array<struct>>), the struct's fields can be scalars or arrays but not structs. Select the nested struct's fields as separate fields instead, for example with transform() in a computed file's select expression.
```

This shape usually comes from the Select Expression itself. Say the source has a `line_items` array where each item holds a `sku` and a `price` struct with `amount` and `currency`. Grouping by `customer_id` with `collect_list(line_items) AS order_items` builds an array of arrays of those items, and `price` is the struct the message names.

To fix it, rebuild each item so the nested struct's fields become fields of their own. `transform()` rewrites every element of an array, and `named_struct()` builds the new struct:

```text
customer_id,
collect_list(
  transform(line_items, i -> named_struct(
    'sku', i.sku,
    'price_amount', i.price.amount,
    'price_currency', i.price.currency
  ))
) AS order_items
```

Every field of the items in `order_items` is now a scalar, so the definition passes Validate. Array fields such as a list of tags can stay as they are.

## Spark SQL Rules

Every Computed File is written in Spark SQL, whichever DFS connector holds the files (Amazon S3, Google Cloud Storage, or Azure Data Lake Storage). The storage only holds the files and Qualytics evaluates the clauses itself, so there is no connector-specific dialect to learn. See [SQL Dialects per Connector](https://userguide.qualytics.io/container/computed-tables/deep-dive/sql-dialects/#dfs-connectors) for how this compares with Computed Tables.

Qualytics builds the query from the form fields, so each field holds only a fragment of it:

| Field | What you write | Keyword Qualytics adds |
| --- | --- | --- |
| **Select Expression** | The column list, for example `customer_id, CAST(amount AS DOUBLE) AS amount` | `SELECT` |
| **Filter Clause** | A condition, for example `status = 'active' AND amount > 0` | `WHERE` |
| **Group By Clause** | The grouping columns, for example `customer_id, region` | `GROUP BY` |
| **Lateral Views** | A generator expression, for example `EXPLODE(tags) t AS tag` | `LATERAL VIEW` |

A few rules apply on top of standard Spark SQL:

- **No `DISTINCT` in the Select Expression.** The keyword is refused in any form, including `COUNT(DISTINCT ...)`, `APPROX_COUNT_DISTINCT(...)`, and `DISTINCT(col)`. List the same columns in the Group By Clause to remove duplicates, or use `explode(collect_set(...))` for distinct values.
- **Aggregations need the Group By Clause.** When the Select Expression uses `COUNT`, `SUM`, `AVG`, `MIN`, or `MAX`, every column that is not aggregated must appear in the Group By Clause.
- **Give each expression an alias.** An expression without an output name, such as `amount * 2`, needs one (`amount * 2 AS doubled_amount`). Plain columns do not.
- **Reference fields, not tables.** The clauses read the single source file container picked in the form, so they name its fields directly and never a table. Field names come from the source's profiled schema.

## Lateral Views

The **Advanced Options** section of the Add and Edit forms exposes a multi-row **Lateral Views** input. Each row defines a single `LATERAL VIEW` clause, and you supply only the body: Qualytics prepends the `LATERAL VIEW` keyword for you. Rows are applied in order, so a later row can reference an alias produced by an earlier one.

### Supported Generators

A lateral view must begin with one of these generator functions:

| Generator | What it does |
| --- | --- |
| `EXPLODE` | Turns each element of an array, or each key-value pair of a map, into its own row. |
| `POSEXPLODE` | Like `EXPLODE`, and also returns the position of each element. |
| `INLINE` | Expands an array of structs, giving each struct field its own column. |
| `STACK` | Reshapes a set of values into a fixed number of rows. |
| `JSON_TUPLE` | Pulls several keys out of a JSON string in one pass. |

Every generator also has an outer form, which keeps the source row even when there is nothing to expand (an empty or null array) instead of dropping it. Write it either way:

- With the keyword in front: `OUTER EXPLODE(tags) t AS tag`
- As a single word: `EXPLODE_OUTER(tags) t AS tag`, `POSEXPLODE_OUTER(...)`, `INLINE_OUTER(...)`

### What Gets Rejected

Validation runs when you click **Validate**, and again on **Save** if you skip it. Two mistakes account for most failures:

- **The clause does not start with a supported generator.** The error names the generators that are accepted and quotes the start of the offending clause. A common cause is putting the `LATERAL VIEW` keyword in the row yourself, since Qualytics already adds it.
- **The Select Expression references an alias the lateral view never declared.** `EXPLODE(tags) t AS tag` declares `tag`, so selecting `tags_value` fails as an unresolved column. Check that the alias after `AS` matches what the Select Expression uses.

A lateral view is a generator expression and nothing else, so the clause is also refused if it contains a semicolon, a set operator (`UNION`, `INTERSECT`, `EXCEPT`), a subquery, or a data-modifying keyword.

## Query Editor

The Query editor in the Add and Edit modals is a code-aware surface with a few features that speed up writing Spark SQL:

- **Syntax highlighting.** SQL keywords, identifiers, string literals, and comments are colored.
- **Autocomplete.** Press `Ctrl`+`Space` inside the editor to see a hint list based on the source file's profiled schema (fields Qualytics has already discovered).
- **Inline hint label.** The editor shows a subtle "Any valid SparkSQL" caption in the expanded editor identifying which dialect applies, and a "Press Ctrl+Space for hints" hint under the inline input.
- **Fullscreen editor.** Click the expand icon in the editor's toolbar to open a larger dialog when the expression grows beyond a few lines.

*Last updated: November 13, 2026.*