CODEWITHSUNDEEP
sqlgoogle-bigquery
Ben
Ben

Reputation: 21625

How can I validate a table's data and <potentially> throw an error with BigQuery SQL?

Suppose I have a scheduled query called doStuff which runs daily and looks like this

select * from bleh.foo left join bleh.bar on foo.a = bar.a

How can I validate the data in foo before executing the query? For example, I may want to confirm that foo doesn't have duplicate values in column a, in which case I'd like to do something like this.

if duplicates_exist(a):
  raise exception "query failed because column 'a' contains dupes"
else:
  select * from bleh.foo left join bleh.bar on foo.a = bar.a

Upvotes: 0

Views: 345

Answers (1)

eshirvana
eshirvana

Reputation: 24593

If I understand correctly this is what you want to do :

if exists (select 1 from foo group by a having count(*) > 1) 
   raise exception "query failed because column 'a' contains dupes"
else
  select * from bleh.foo left join bleh.bar on foo.a = bar.a

Upvotes: 1

Related Questions