Skip to content

Instantly share code, notes, and snippets.

@mshakhomirov
Created November 27, 2022 11:28
Show Gist options
  • Save mshakhomirov/8f6cb17a642bd361399a554f5b29fa2e to your computer and use it in GitHub Desktop.
Save mshakhomirov/8f6cb17a642bd361399a554f5b29fa2e to your computer and use it in GitHub Desktop.
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
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment