[CT-3567] [Feature] unit testing support format: sql

[graciegoheen]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

We should allow support to set format: sql for unit test definition:

  - name: test_valid_email_address
    model: dim_wizards 
    given: 
      - input: ref('stg_wizards')
        format: sql
        rows: |
          select 1 as wizard_id, [email protected] as email, example.com as email_top_level_domain union all 
          select 2 as wizard_id, [email protected] as email, unknown.com as email_top_level_domain union all 
          select 3 as wizard_id, badgmail.com as email, gmail.com as email_top_level_domain union all 
          select 4 as wizard_id, missingdot@gmailcom as email, gmail.com as email_top_level_domain
      - input: ref('top_level_email_domains')
        format: sql
        rows: |
          select example.com as tld union all,
          select gmail.com as tld
    expect: 
      format: sql
      rows: |
        select 1 as wizard_id, true as is_valid_email_address union all
        select 2 as wizard_id, false as is_valid_email_address union all
        select 3 as wizard_id, false as is_valid_email_address union all
        select 4 as wizard_id, false as is_valid_email_address 

We should also allow folks to also use sql fixutres:

  - name: test_valid_email_address
    model: dim_wizards 
    given: 
      - input: ref('stg_wizards')
        format: sql
        fixture: wizard_emails_input
      - input: ref('top_level_email_domains')
        format: sql
        fixture: tld_input
    expect: 
      format: sql
      fixture: wizard_emails_output

Anything else?

• One of the main motivations for this is this would automatically allow support for complex data types (if you want to use type variant/json/etc. just use format: sql) [CT-2998] [SPIKE] Handle unit testing of JSON and ARRAY data types #8423
• We would not allow for folks to use jinja in this SQL, the reasons being:
  • we don't think folks should {{ ref() }} a model from their static input data (we don't want to consider DAG order, we don't wan't to point to data that could change, etc.)
  • this would mean folks couldn't use cross-db macros in their SQL (we're ok with that sacrifice)
  • this would mean folks couldn't use macros in their SQL

Scope

• Allow for SQL fixtures, but throw error with jinja present.

[MichelleArk]

Caveats for documentation:

• if you use format: sql you have to specify all of the columns, but you can write out the exact SQL you want to pass (can use whatever datatypes you want)
• jinja is not supported in SQL fixtures for unit tests

[FishtownBuildBot]

Opened a new issue in dbt-labs/docs.getdbt.com: dbt-labs/docs.getdbt.com#5289