From the SELECT team, a dbt package to help you monitor Snowflake performance and costs. Documentation for the models can be found here.
Grant dbt's role access to the snowflake
database:
grant imported privileges on database snowflake to role your_dbt_role_name;
Add the following to your packages.yml
file:
packages:
- package: get-select/dbt_snowflake_monitoring
version: 1.4.2
To attribute costs to individual models via the dbt_metadata
column in the query_history_enriched
model, add the following to dbt_project.yml
:
query-comment:
comment: '{{ dbt_snowflake_monitoring.get_query_comment(node) }}'
append: true # Snowflake removes prefixed comments.
The generate URLs to dbt Cloud jobs and runs in the dbt_queries
model, add the following variable to dbt_project.yml
:
vars:
dbt_cloud_account_id: 12345 # https://cloud.getdbt.com/next/deploy/<this_number>/
If you only want to use the get_query_comment
macro, and don't want to run the models, you can exclude them from running by adding the following to your dbt_project.yml
:
models:
dbt_snowflake_monitoring:
+enabled: false
Prior to releasing this package, snowflake-spend by the Gitlab data team was the only package available for monitoring Snowflake spend. According to their README, the package is currently maintained by the Gitlab data team, but there does not appear to be any active development in it (as of January 2023).
The dbt-snowflake-monitoring
package is actively developed & maintained by the SELECT. It powers many of the dashboards and features available in our product. The package goes beyond just modeling warehouse spend. We calculate cost per query using the methodology described here, model all components of your Snowflake bill, and plan to provide additional query & warehouse performance related models.
- Install pipx
pip install pipx
pipx ensurepath
- Install tox
pipx install tox
- Install pre-commit
pipx install pre-commit
pre-commit install
- Configure your profile (follow the prompts)
dbt init
We use changie to generate CHANGELOG entries. Note: Do not edit the CHANGELOG.md directly. Your modifications will be lost.
Follow the steps to install changie for your system.
Once changie is installed and your PR is created, simply run changie new
and changie will walk you through the process of creating a changelog entry. Commit the file that's created and your changelog entry is complete!
Simply treat this package like a dbt project. From the top level of the repo, you can run:
dbt build
and any other dbt command.
We use SQLFluff to keep SQL style consistent. By installing pre-commit
per the initial setup guide above, SQLFluff will run automatically when you make a commit locally. A GitHub action automatically tests pull requests and adds annotations where there are failures.
Lint all models in the /models directory:
tox -e lint_all
Fix all models in the /models directory:
tox -e fix_all
Lint (or subsitute lint to fix) a specific model:
tox -e lint -- models/path/to/model.sql
Lint (or subsitute lint to fix) a specific directory:
tox -e lint -- models/path/to/directory
Enforced rules are defined within tox.ini
. To view the full list of available rules and their configuration, see the SQLFluff documentation.