# Test Transform Tasks

> Write and run unit tests for Starlake SQL transformations locally on DuckDB. Automatic SQL transpilation from BigQuery, Snowflake, or Databricks. Compare transform results against expected CSV or SQL output with detailed test reports.

<script type="application/ld+json">
    {JSON.stringify({
      "@context": "https://schema.org",
      "@type": "HowTo",
      "name": "How to write transform tests in Starlake",
      "description": "Write and run unit tests for Starlake SQL transformations locally on DuckDB with automatic SQL transpilation from BigQuery, Snowflake, or Databricks.",
      "step": [
        {
          "@type": "HowToStep",
          "name": "Create the test directory",
          "text": "Create a test directory under metadata/tests/transform/{domain}/{table}/test-name.",
          "position": 1
        },
        {
          "@type": "HowToStep",
          "name": "Add source data files",
          "text": "Add CSV or JSONL files named domain.table.csv or domain.table.json containing the source data for all tables referenced by the transformation.",
          "position": 2
        },
        {
          "@type": "HowToStep",
          "name": "Define expected output",
          "text": "Add a _expected.csv file with static expected data or a _expected.sql file with a SQL query whose result is compared to actual output.",
          "position": 3
        },
        {
          "@type": "HowToStep",
          "name": "Run the test",
          "text": "Run 'starlake test --transform' to execute all transform tests, or 'starlake test --domain <domain> --table
          --test <test-name>' for a specific test.",
          "position": 4
        }
      ]
    })}
  </script>

  <script type="application/ld+json">
    {JSON.stringify({
      "@context": "https://schema.org",
      "@type": "FAQPage",
      "mainEntity": [
        {
          "@type": "Question",
          "name": "Where to place transform tests in Starlake?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Transform tests are in metadata/tests/transform. Each test is a directory in the domain/table subdirectory."
          }
        },
        {
          "@type": "Question",
          "name": "What files make up a transform test?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "CSV or JSONL files containing initial source data (named domain.table.csv or .json), and a _expected.csv or _expected.sql file containing the expected data after transformation."
          }
        },
        {
          "@type": "Question",
          "name": "How does Starlake handle SQL dialect differences?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Starlake automatically transpiles SQL queries from the target dialect (BigQuery, Snowflake, etc.) to the DuckDB dialect before execution. No query modification is needed."
          }
        },
        {
          "@type": "Question",
          "name": "Can I use a SQL file as expected output?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Yes. The expected file can be _expected.csv (static data) or _expected.sql (a SQL query whose result is compared to the actual data)."
          }
        },
        {
          "@type": "Question",
          "name": "Where to find transform test reports?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "In test-reports/transform/test-name/. The report contains the DuckDB database (testname.db), unexpected data (not_expected.csv), and missing data (missing.csv)."
          }
        },
        {
          "@type": "Question",
          "name": "How to run only transform tests?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Use the command starlake test --transform. For a specific test: starlake test --domain <domain> --table
            --test <test-name>."
          }
        },
        {
          "@type": "Question",
          "name": "Do tests generate a visual report?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Yes. Running tests generates a complete website in the test-reports directory."
          }
        }
      ]
    })}
  </script>



Starlake transform tests validate SQL transformations locally on DuckDB with automatic dialect transpilation from BigQuery, Snowflake, or Databricks. Provide source data files and expected output (CSV or SQL), and Starlake compares schema and results. No query modification needed -- Starlake transpiles your production SQL automatically.

For an overview of the testing approach and DuckDB transpilation, see [Unit Testing Concepts](./050-concepts.mdx). For data loading tests, see [Test Load Tasks](./100-load-tests.mdx).

## How to write a transform test

1. **Create the test directory** -- Add a directory under `metadata/tests/transform/{domain}/{table}/test-name`.
2. **Add source data files** -- Place CSV or JSONL files named `domain.table.csv` (or `.json`) for each table referenced by the transformation.
3. **Define expected output** -- Add `_expected.csv` (static data) or `_expected.sql` (SQL query whose result is compared).
4. **Run the test** -- Execute `starlake test --transform` or `starlake test --domain <domain> --table <table> --test <test-name>`.

## Test directory structure

Transform tests reside in `metadata/tests/transform`. Each test is a directory inside the `domain/table` subdirectory.

A test directory contains:

- **Source data files** -- CSV or JSONL files with initial data for every table referenced by the SQL transformation. Name each file after its domain and table: `starbake.product.json` or `starbake.product.csv`.
- **Expected output** -- Either:
  - `_expected.csv` -- Static expected data in CSV format.
  - `_expected.sql` -- A SQL query whose result is compared to the actual transformation output.

## How validation works

Before running the transformation, Starlake transpiles your SQL statements from the target dialect to the DuckDB dialect.

After executing the transpiled SQL against a local DuckDB database populated with the test source data, Starlake:

1. Compares the table schema with the expected data schema.
2. Compares the actual transformation results with the expected output.

The test passes if both schema and data match. Any mismatch raises an error.

## Test reports

Reports for a test named `test-name` are stored in `test-reports/transform/test-name/` and contain:

- **`testname.db`** -- The DuckDB database after the transform task runs, containing:
  - `sl_expected` -- The expected data.
  - `domain.table` (e.g., `starbake.product`) -- The actual transformed data.
  - `sl_expectations` -- Results of any expectation related to this table.
  - `audit.audit` -- The audit log of the transform task.
- **`not_expected.csv`** -- Rows in the actual table that are not in the expected data.
- **`missing.csv`** -- Rows in the expected data that are missing from the actual table.

## Running transform tests

Run all transform tests (skip load tests):

```bash
starlake test --transform
```

Run a specific test:

```bash
starlake test --domain starbake --table product --test test-name
```

Running tests also generates a complete HTML report website in the `test-reports` directory.

## Example: Transform test report

![Transform test report](/img/tests/transform.png)

## Example: Combined load and transform report

![Combined load and transform report](/img/tests/report.png)

## Frequently Asked Questions

### Where to place transform tests in Starlake?

Transform tests are in `metadata/tests/transform`. Each test is a directory in the `domain/table` subdirectory.

### What files make up a transform test?

CSV or JSONL files containing initial source data (named `domain.table.csv` or `.json`), and a `_expected.csv` or `_expected.sql` file containing the expected data after transformation.

### How does Starlake handle SQL dialect differences?

Starlake automatically transpiles SQL queries from the target dialect (BigQuery, Snowflake, etc.) to the DuckDB dialect before execution. No query modification is needed.

### Can I use a SQL file as expected output?

Yes. The expected file can be `_expected.csv` (static data) or `_expected.sql` (a SQL query whose result is compared to the actual data).

### Where to find transform test reports?

In `test-reports/transform/test-name/`. The report contains the DuckDB database (`testname.db`), unexpected data (`not_expected.csv`), and missing data (`missing.csv`).

### How to run only transform tests?

Use the command `starlake test --transform`. For a specific test: `starlake test --domain <domain> --table <table> --test <test-name>`.

### Do tests generate a visual report?

Yes. Running tests generates a complete website in the `test-reports` directory.