Solved: Dynamic data tests - Page 2

Bob_Liu · 10-20-2020 04:52 PM

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};;
  }
}

ernesto1

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!

View solution in original post