Create data models for Snowflake cost tracking

[ian-r-rose]

We are currently not doing a good job of tracking our Snowflake credit usage. We should create some data models here to give us more visibility into costs.

Most of the relevant information will be in the SNOWFLAKE.ORGANIZATION_USAGE schema. Some views (non-exhaustive) that track credit usage are:

1. AUTOMATIC_CLUSTERING_HISTORY
2. DATA_TRANSFER_HISTORY
3. DATABASE_STORAGE_USAGE_HISTORY
4. MATERIALIZED_VIEW_REFRESH_HISTORY
5. METERING_DAILY_HISTORY
6. PIPE_USAGE_HISTORY
7. STAGE_STORAGE_USAGE_HISTORY
8. STORAGE_DAILY_HISTORY
9. WAREHOUSE_METERING_HISTORY

There are also a couple of views that would be helpful if we could use them, but do not work when Snowflake is purchased through a reseller:

1. USAGE_IN_CURRENCY_DAILY
2. REMAINING_BALANCE_DAILY

[summer-mothwood]

@ian-r-rose I just skimmed through this explanation of how Canva has built their own Snowflake cost usage reporting tools over the years: https://www.canva.dev/blog/engineering/our-journey-to-snowflake-monitoring-mastery/ An interesting read, and they also mention this dbt package which apparently does everything the Canva team had custom built for themselves: https://select.dev/docs/dbt-snowflake-monitoring Might be worth a look!

[ian-r-rose]

Neat, let's evaluate it! There are a couple of things I think we would look for out of a package:

1. Does it have models for organization usage, in addition to account usage? In our case the former is more helpful as we're heavy org users.
2. Do the models rely heavily on some of the usage views that don't work in our org? Some of them are disabled because of the way we procure Snowflake through a reseller

[tnrahim]

It looks like the dbt user requires the imported privilege and the account requires ORGADMIN access. I'll test it out and see if I have any errors.

[tnrahim]

I've created all the following staging models

AUTOMATIC_CLUSTERING_HISTORY
DATA_TRANSFER_HISTORY
DATABASE_STORAGE_USAGE_HISTORY
MATERIALIZED_VIEW_REFRESH_HISTORY
METERING_DAILY_HISTORY
PIPE_USAGE_HISTORY
STAGE_STORAGE_USAGE_HISTORY
STORAGE_DAILY_HISTORY
WAREHOUSE_METERING_HISTORY

Next step is to create incremental intermediate models.

[jkarpen]

Re-opened this as I believe it was closed by mistake when the PR closed. Per @tnrahim there are some additional steps needed on this (next step to create incremental intermediate models).