unit_test_for_a_script.sql

config {
  type: "operations",
  hasOutput: true,
  schema: "production",
  name: "test_user_reputation",
  disabled: false,
  dependencies: [ "reputation_data", "reputation_data_v", "user_reputation" ],
  tags: ["unit_tests"],
  description: "Advanced unit test to test complex query operations.",
}

-- 1. Run with _tests suffix for schemas and get actual results:
create or replace table ${self()} as
select * from ${ref("user_reputation")}
;

--2. tidy up unit test dependencies:
drop table if exists `bq-shakhomirov-staging.production_tests.reputation_data`;
drop table if exists `bq-shakhomirov-staging.production_tests.user_reputation`;

--3. Compare with expected and raise error if doesn't match:
with exp as (
select array_to_string(array[
 '1'
, cast(timestamp('1999-01-01 00:00:01 UTC') as string) 
]
  ,',' ) as row

)

, act as (
select 
array_to_string(array
[
 cast(user_id        as string)       
,cast(updated_at     as string)
]
  ,','
  ) as row
from ${self()}
)

,results as (
select
    exp.row
    ,act.row as row_act
from
    act
    full join  exp on exp.row = act.row
)

select
if(row = row_act
    , 'all good'
    , ERROR('ATTENTION: unit_test_user_reputation has failed. For more info check: https://app.dataform.co/#/5650122588291072/b/master/file/definitions%2FunitTests%2Ftest_user_reputation.sqlx.'
        )
    )
    from results
;