Test Capabilities

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).

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

{ "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

# 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 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