Skip to main content

Tests

Validate your semantic model with automated tests.

Learning Objectives​

After completing this guide, you will be able to:

  • Write test definitions
  • Validate SQL generation
  • Use SQL and regex assertions
  • Run tests during deployment

What are Tests?​

Tests validate that the Planner generates correct SQL from your semantic model. They:

  • Verify query generation works correctly
  • Catch breaking changes early
  • Document expected behavior
  • Run automatically during deployment

Test Structure​

Tests are YAML files in the tests/ directory:

name: Store Sales Positive Values

projections:
  - Store Sales Price
  - Store Quantity

assert_sql: "SELECT sum(T0.\"ss_quantity\") AS \"Store Quantity\", sum(T0.\"ss_sales_price\") AS \"Store Sales Price\" FROM store_sales T0"

Required Fields​

name​

Display name for the test.

name: Store Sales Positive Values

projections​

List of semantic field names to query. Must match field names in your model.

projections:
  - Store Sales Price
  - Store Quantity
  - Order Count

Assertions​

At least one assertion is required:

  • assert_sql: Exact SQL match
  • assert_regex: Regex pattern match

SQL Assertion​

Assert the exact SQL generated by the Planner:

name: Store Sales Query

projections:
  - Store Sales Price
  - Store Quantity

assert_sql: "SELECT sum(T0.\"ss_quantity\") AS \"Store Quantity\", sum(T0.\"ss_sales_price\") AS \"Store Sales Price\" FROM store_sales T0"

Notes:

  • Whitespace is normalized (tabs/newlines become spaces)
  • Must match exactly (case-sensitive)
  • Use for precise validation

Regex Assertion​

Assert SQL matches a pattern (more flexible):

name: Customer Email Validation

projections:
  - Customer Email

assert_regex: "c_email_address"

Use cases:

  • Partial matching
  • Flexible column names
  • Testing specific patterns

Filters​

Test queries with filters:

name: Recent Orders

projections:
  - Order Count
  - Total Revenue

filters:
  - dimension: Order Date
    predicate: between
    filter_value: 2024-01-01
    filter_value_end: 2024-12-31

assert_sql: "SELECT count(*) AS \"Order Count\", sum(T0.\"amount\") AS \"Total Revenue\" FROM orders T0 WHERE T0.\"order_date\" BETWEEN '2024-01-01' AND '2024-12-31'"

Complete Examples​

Simple Measure Test​

name: Total Revenue Calculation

projections:
  - Total Revenue

assert_sql: "SELECT sum(T0.\"amount\") AS \"Total Revenue\" FROM orders T0"

Multiple Projections​

name: Sales Summary

projections:
  - Store Sales Price
  - Store Quantity
  - Order Count

assert_sql: "SELECT sum(T0.\"ss_quantity\") AS \"Store Quantity\", count(*) AS \"Order Count\", sum(T0.\"ss_sales_price\") AS \"Store Sales Price\" FROM store_sales T0"

With Filters​

name: High Value Orders

projections:
  - Total Revenue

filters:
  - measure: Total Revenue
    predicate: greater_than
    filter_value: 1000

assert_regex: "HAVING.*sum.*amount.*>.*1000"

Dimension Test​

name: Customer Name Field

projections:
  - Customer Name

assert_sql: "SELECT T0.\"customer_name\" AS \"Customer Name\" FROM customers T0"

Best Practices​

  1. Test critical queries - Focus on important business metrics
  2. Use SQL assertions for exact validation
  3. Use regex assertions for flexible patterns
  4. Test edge cases - Filters, aggregations, joins
  5. Update tests when models change
  6. Name tests descriptively - Clear purpose

Test Execution​

Tests run automatically during deployment:

strata deploy

Process:

  1. YAML files validated
  2. Models deployed to server
  3. Tests executed
  4. Results reported

If tests fail:

  • Deployment may be blocked (depending on configuration)
  • Review test output
  • Fix model or update test expectations

Writing Effective Tests​

Test What Matters​

Focus on:

  • Critical business metrics
  • Complex calculations
  • Join behavior
  • Filter logic

Keep Tests Maintainable​

  • Use descriptive names
  • Comment complex assertions
  • Group related tests
  • Update when models change

Test Patterns​

Measure aggregation:

assert_sql: "SELECT sum(T0.\"amount\") AS \"Total Revenue\" FROM orders T0"

Join behavior:

assert_regex: "JOIN.*customers.*ON"

Filter application:

assert_regex: "WHERE.*order_date.*BETWEEN"

Troubleshooting​

Test Fails on Deployment​

  • Check SQL matches exactly (whitespace normalized)
  • Verify field names match model definitions
  • Review test output for differences

Regex Not Matching​

  • Test regex pattern separately
  • Check for special characters
  • Verify case sensitivity

Field Not Found​

  • Verify field name matches exactly
  • Check field exists in deployed model
  • Ensure field is in correct table

Next Steps​