Dynamic data tests

[Bob_Liu]

Hi all, looked around and couldn’t find a solution to this. Are we able to pass a second value from a different source to data tests?

On the documentation page, I noticed that the data test is only for static values.
https://docs.looker.com/reference/model-params/test

For example how can we do something like this:

test: historic_revenue_is_accurate {
  explore_source: orders {
    column: total_revenue {
      field: orders.total_revenue
    }
    filters: [orders.created_date: "2017"]
  }
  assert: revenue_is_expected_value {
    expression: ${orders.total_revenue} = ${orders_sql_check.total_reve};;
  }
}

[thomas_brittain]

Hello @Bob_Liu ,

I've not found a way to parameterize data tests.   

Though, one project I'll be working on soon is moving away from the Looker UI's data tests and use the Python SDK for creating unit tests around [pytest](https://docs.pytest.org/en/stable/).  That way, we'd get all the lift of pytest flexibility--like parameterized tests.  Whenever I get some time to implement a proof-of-concept, I'll ping back in this ticket.  

Kind regards,
--Thomas

[ernesto1 · Accepted solution]

Hi @Bob_Liu ! That’s a great question, certainly makes sense to have a different query to be able to compare results to. The thing is, the expression has to come from the same explore source as it’s using the table calcualtions mechanism. 

I found creating a tiny table with your custom SQL that you want to check and then cross join into the model

In your model:

  join: orders_sql_check {
    type: cross
    relationship: one_to_one
  }

And your test:

test: historic_revenue_is_accurate {

  explore_source: events {
    column: total_revenue {}
    column: events_total_revenue { field: orders_sql_check.events_total_revenue }
    filters: {
      field: events.event_date
      value: "this year"
    }
  }

  assert: revenue_is_expected_value {
    expression: ${events.total_revenue} = ${orders_sql_check.events_total_revenue};;

  }

}

Just make sure to hide the fields in orders_sql_check!