# Test Capabilities

> Test Starlake loads and transforms locally on DuckDB, with no cloud credentials: test layout, expectations, data comparison, coverage and reports.

## 1. Local Execution with DuckDB

All tests run locally using DuckDB as an in-memory SQL engine. No cloud credentials or service accounts are required. SQL written for BigQuery, Snowflake, Databricks, or Redshift is automatically transpiled to DuckDB dialect before execution.

## 2. Test Types

Starlake supports two test types:

| Type | What it validates |
|---|---|
| **Load tests** | Ingestion of files into tables (parsing, validation, rejection) |
| **Transform tests** | SQL or Python transformations (query logic, output schema) |

If neither `--load` nor `--transform` is specified, both types are executed.

## 3. Load Test Structure

Each load test is a directory under `metadata/tests/load/{domain}/{table}/{test-name}/` containing:

| File | Required | Purpose |
|---|---|---|
| `{domain}.{table}.csv` or `.json` | No | Initial data to preload into the table |
| `_incoming.{filename}` | Yes | Input file to ingest (named per the table's file pattern) |
| `_expected.csv` or `.json` | Yes | Expected table contents after loading |
| `_expected_{name}.csv` / `.json` / `.sql` | No | Additional named expectations |
| `{table}.sql` | No | Pre-test SQL statements |
| `env.sl.yml` | No | Test-level environment variables |

## 4. Transform Test Structure

Each transform test is a directory under `metadata/tests/transform/{domain}/{task}/{test-name}/` containing:

| File | Required | Purpose |
|---|---|---|
| `{domain}.{table}.csv` or `.json` | Yes | Source data for each table referenced by the SQL |
| `_expected.csv` or `.json` | Yes | Expected transformation output |
| `_expected.sql` | No | SQL query whose result is compared to actual output |
| `_expected_{name}.csv` / `.json` / `.sql` | No | Additional named expectations |
| `{task}.sql` | No | Pre-test SQL statements |
| `env.sl.yml` | No | Test-level environment variables |

## 5. Multiple Expectations per Test

A single test can define multiple expected outputs, each validated independently:

```
test1/
  ├── _expected.csv              # default expectation
  ├── _expected_by_region.csv    # named expectation "by_region"
  └── _expected_totals.sql       # SQL-based expectation "totals"
```

Each generates separate pass/fail results and comparison artifacts.

## 6. SQL-Based Expectations

Instead of a static CSV, an `_expected.sql` file defines a query executed against the DuckDB database. The query result is compared to the actual output, enabling parameterized and computed assertions.

## 7. Test Data Formats

- **CSV**: standard comma-separated values with header row.
- **JSON**: JSONL format (one JSON object per line, no commas between objects).

```csv
order_id,customer_id,amount,seller_id,ts
12345,A009701,123.65,AQZERD,2024-02-05T21:19:15.454Z
56432,A009701,23.8,AQZERD,2024-02-05T21:19:15.454Z
```

```json
{ "id": "1", "city": "Paris", "country": "France" }
{ "id": "2", "city": "Berlin", "country": "Germany" }
```

## 8. Hierarchical Environment Configuration

Environment variables can be defined at multiple levels via `env.sl.yml` files. Each level overrides the one above:

1. `metadata/tests/env.sl.yml` — root
2. `metadata/tests/load/{domain}/env.sl.yml` — domain
3. `metadata/tests/load/{domain}/{table}/env.sl.yml` — table
4. `metadata/tests/load/{domain}/{table}/{test-name}/env.sl.yml` — test

Same hierarchy applies under `transform/`.

## 9. Schema Validation

Tests compare the actual table schema against the expected data schema. Missing or unexpected columns are reported separately from data mismatches.

## 10. Data Comparison

Row-by-row comparison is order-independent (set logic). Mismatches produce two artifact files:

- `missing_{name}.csv` — rows in expected but not in actual.
- `not_expected_{name}.csv` — rows in actual but not in expected.

## 11. Rejected Records

For load tests, records that fail validation are routed to `audit.rejected` in the test DuckDB database — the same behavior as production ingestion.

## 12. Test Coverage Metrics

The test framework tracks which domains and tables have tests, and reports:

- Tested domains and tables.
- Untested domains and tables.

## 13. CLI Commands

```bash
# Run all tests
starlake test

# Run only load or transform tests
starlake test --load
starlake test --transform

# Filter by domain, table, or test name
starlake test --domain sales
starlake test --domain sales --table orders
starlake test --domain sales --table orders --test test1

# Generate HTML report website
starlake test --site

# Custom output directory
starlake test --outputDir /path/to/reports
```

## 14. Report Generation

### JUnit XML

Generated by default at `test-reports/junit.xml`. Compatible with CI/CD systems (Jenkins, GitLab CI, GitHub Actions).

### HTML Report

Generated with `--site`. Produces a browsable website with:

- Root summary (pass/fail counts, success rates).
- Domain-level and table-level summaries.
- Individual test pages showing schema diffs, data mismatches, executed SQL, and execution time.

### DuckDB Artifacts

Each test produces a `{test-name}.db` file containing:

- `sl_expected` — expected data table.
- `{domain}.{table}` — actual data table.
- `audit.rejected` — rejected records (load tests).

## 15. Post-Load Expectations

In addition to test-level assertions, data quality expectations defined in the table YAML (`expectations` section) are also evaluated during test execution. See the full [Expectations reference](./700-expectations.md) for all 53 built-in macros.

---

## Summary

| Capability | Category |
|---|---|
| Local DuckDB execution (no cloud credentials) | Runtime |
| Automatic SQL transpilation (BigQuery, Snowflake, Databricks, Redshift) | Runtime |
| Load tests (file ingestion validation) | Test Types |
| Transform tests (SQL/Python logic validation) | Test Types |
| Multiple named expectations per test | Assertions |
| SQL-based expectations (`_expected.sql`) | Assertions |
| Schema validation (column names and types) | Assertions |
| Data comparison (order-independent, set logic) | Assertions |
| Rejected record tracking (`audit.rejected`) | Assertions |
| Post-load expectation macros | Assertions |
| Hierarchical environment configuration (`env.sl.yml`) | Configuration |
| Pre-test SQL statements | Configuration |
| Test coverage metrics (tested vs untested) | Reporting |
| JUnit XML reports (CI/CD integration) | Reporting |
| HTML report website (`--site`) | Reporting |
| DuckDB artifact databases | Reporting |