Cross-database support for dbt

This package is designed to make your sql purely portable in the DRYest possible way.

in your packages.yml add this line:

  - git: "[email protected]:Health-Union/dbt-xdb.git"
    revision: master

(no worries, this will be tagging very soon. Right now we are committing new macros rapidly and don't want version hangups.)

xdb macros are written as close to ansii spec as possible. For something like regexp searching to see if the case-insensitive username starts with "soup":

    ...
    CASE
        WHEN {{xdb.regexp('username','soup.*','i')}} THEN 'Is Soup User'
        ELSE 'Not Soup User'
    END AS soup_user 

Vendor-specific SQL is a relationship. Using xdb is like a prenuptual agreement for your code; because no matter how much you love your data warehouse vendor today, it is better to be safe than sorry.

To get started clone this repo with

git clone [email protected]:Health-Union/dbt-xdb.git

Then set up your profiles.yml file and freeze it with

git update-index --assume-unchanged profiles.yml

You can add your keyfile.json directly to the test_xdb package root, it is already in .gitignore, and then reference directly in your profiles.yml.

Note: each target you set up in profiles.yml will get tested. So if you have access to a BigQuery, Redshift and Snowflake instance, you can test them all! Since both AWS and GCP have a lot of free development credits for new accounts, this is not as heavy a lift as it sounds.

On to the dev! This dev env uses Docker. Start your env with:

docker-compose up -d

From here you can run the tests with

docker-compose exec testxdb test

Which does a dbt run and dbt test and returns results. You can flag only certain targets (say, when you are squashing a targeted bug) with

docker-compose exec testxdb test bigquery snowflake ## list only the targets you want

To test your macros:

• write your xdb macro in xdb/macros/.
• build a model in test_xdb/models/under_test/ that uses the macro with the simplest possible case using the sample data. (Note: try using SELECT .. UNION ALL syntax for your test source directly in the model, it makes the tests much cleaner.
• add tests to confirm the macro works in test_xdb/models/under_test/schema.yml.

You can exclude specific models from tests for specific targets (i.e. when the same test cannot support all the targets) using config flags.

{{ config(tags=["exclude_bigquery","exclude_bigquery_tests"]) }}

-- this model will be not be built or tested against bigquery

XDB is grounded in Test Driven Development. Before macro code can be merged it must pass our code coverage and linting standards. You can check your coverage / linting status with:

docker-compose exec testxdb coverage

This will report back if the codebase passes or, if it fails it will report why.

NOTE: this same coverage check is required to pass for code to merge.

Generate fresh docs at any time with

docker-compose exec testxdb docs 

Note: Docs will be built automatically during deployment, so you don't need to do this during development - but it is helpful to see how your docs will render.

Autodocs read from the macros into the /docs folder. This uses docstring syntax (and will be split into a stand-alone module soon).