Skip to content

Writing Tests

EchoSphere tests are plain SQL files with the .es.sql suffix. A test passes when the query returns zero rows. If the query returns one or more rows, the test fails and the returned rows explain what was wrong.

File and Directory Structure

  • Use .es.sql as the file extension so EchoSphere can discover tests automatically
  • Organize tests in subdirectories ("subsuites") for logical grouping (e.g., smoke/, daily/, integrity/)
  • Reference tests by subsuite using es view (e.g., es view test smoke/orders_total)

Example layout:

es_suite/
  smoke/
    orders_total.es.sql
  integrity/
    no_duplicate_keys.es.sql

Test Authoring Principles

  • Assert via row presence: return a row only when a rule is violated
  • Keep tests focused and isolated — one assertion per file is easier to triage
  • Prefer deterministic logic; avoid nondeterministic functions unless controlled
  • Keep queries performant; filter early and leverage indexes/partitions where applicable

Naming Conventions

  • Use descriptive, intent‑revealing names: no_null_customer_ids.es.sql
  • Consider a prefix for categories or ownership if helpful

Metadata Comments

You can define optional metadata at the top of a test file using SQL comments:

-- @name: Example Test
-- @tag: critical, nightly
-- @timeout: 30
SELECT ...
  • @name: Display name used in run output and reports
  • @tag: Comma-separated tags used for filtering (es run --tag ..., es run --exclude-tag ...)
  • @timeout: Per-test timeout in seconds (positive integer)

Metadata is only parsed from the leading comment block at the start of the file. When @name is present, it is shown in es run / es view tests output.

Example Tests

Validate a specific aggregation is as expected:

-- orders_total.es.sql
SELECT *
FROM (
  SELECT SUM(O_TOTALPRICE) AS total
  FROM ORDERS
  WHERE O_ORDERDATE = '1995-02-19'
)
WHERE total <> 944870465.07;

Check for unexpected NULLs:

-- no_null_customer_ids.es.sql
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE CUSTOMER_ID IS NULL;

Detect duplicate business keys:

-- no_duplicate_orders.es.sql
SELECT ORDER_ID
FROM ORDERS
GROUP BY ORDER_ID
HAVING COUNT(*) > 1;

Best Practices

  • Avoid using fully qualified table paths across environments. Rely on environment configuration (database/schema) via es.ini.
  • Keep tests small and composable — split overly complex checks into multiple tests.
  • Use comments to explain the business rule and failure rationale at the top of each test.
  • For long‑running queries, consider limiting the scope (e.g., date partitions) or creating summarized tables upstream.

Running and Inspecting

Run all tests:

es run -e env.snowflake.dev

List discovered tests:

es view tests --all

Print the SQL of a specific test:

es view test integrity/no_duplicate_orders