Testing Datawarehouse to see what see what issues might have occurred after nightly jobs have run

I want to be able test all tables in the Data warehouse to see what has changed after nightly jobs have run. I am using the query below to see what tables have no rows however i'd like to expand the testing to see other things such as what fields have null values after the job has run. does anyone do similar testing and have a script that they use for this or any other things I should test for?

select
    t.name table_name,
    s.name schema_name,
    sum(p.rows) total_rows
from
    sys.tables t
    join sys.schemas s on (t.schema_id = s.schema_id)
    join sys.partitions p on (t.object_id = p.object_id)
where p.index_id in (0,1)
group by t.name,s.name
having sum(p.rows) = 0;