Developing a Transparent Chargeback Model Using the Organizations feature in Snowflake

While Snowflake is often touted as a product with infinitely scalable compute/storage, it also has a lot to offer in terms of flexibility of the overall account structure. In this article, I will go into further detail about how the Organization feature has the ability to build a scalable but also flexible architecture for companies that complements and simplifies and company structure that may be in constant flux.

This architecture has the ability to benefit individual departments within a company (IE: splitting for cost perspectives) or even if a company oftentimes buys and sells brands within its portfolio. For those unfamiliar with Organizations in Snowflake, it enables various features including getting a unified view of all accounts within an organization, creating additional accounts, monitoring each account's consumption, and more (additional details here). For the sake of the post today, I will be using a fictional company called ACME Group which has various companies. Over the past year, they have purchased 2 brands and are looking to sell off another brand in the future. In order to take this into account, they need to be able to create an architecture that allows them to easily add in data infrastructure and easily remove it if a company were to be sold.

The architecture setup above represents individual Snowflake accounts for each individual brand. Similarly, this same method can be used to split different departments within one organization. Snowflake’s unique ability to share data across accounts in a seamless manner allows each of these brands the ability to collaborate as if the data lived in their own account while also keeping borders between each for a chargeback model. The orgadmin. Although this example utilizes Brands this is also a great way to build a chargeback model between departments within an organization. The central data exchange or direct data sharing still allows the organization to collaborate while still keeping a model to chargeback each department.

If you are looking to have notifications you can create resource monitors at the account level or utilize one of the altering/notification features within Snowflake

In addition to this, I have created a dashboard targeted at seeing usage across each account. This is targeted towards finance teams or if individual dollars are all the end user cares about. To build out the dashboard see the details below.

**These all require ORGADMIN access. To get ORGADMIN access see (here)**

Dollars of Capacity Remaining ($)

-----------------------------------
--$ of Capacity Remaining
-----------------------------------
select
    capacity_balance
from
    snowflake.organization_usage.remaining_balance_daily
where
    date =(
        select
            max(DATE)
        from
            snowflake.organization_usage.remaining_balance_daily
    );

Dollars of Capacity Remaining Config (*$ are 0 here since this is a demo account)

Yearly Run Rate $(1-year Estimate based on Past 30 days)

-----------------------------------
--Yearly Run Rate (Past 30 Days)
-----------------------------------

--Note: 31 days used to account for 24 hour dealy on RATE_SHEET_DAILY data

select
round(sum(usage_in_currency)*31,0) as total_spend
from SNOWFLAKE.ORGANIZATION_USAGE.USAGE_IN_CURRENCY_DAILY
where usage_date > current_date() - 31;

Account Run Rate (1-year Estimate based on past 30 days)

Yearly Run Rate $(1-year Estimate based on Past 30 days)

---------------------------------------------
--Monthly Usage after Last Snowflake Contract
---------------------------------------------
--**Please validate Contract Start date is correct with contract using the below sql**
--SQL: select * from SNOWFLAKE.ORGANIZATION_USAGE.CONTRACT_ITEMS;

select    
*
from
    SNOWFLAKE.ORGANIZATION_USAGE.USAGE_IN_CURRENCY_DAILY
where
    usage_date >= (select max(start_date) from SNOWFLAKE.ORGANIZATION_USAGE.CONTRACT_ITEMS);

Total Monthly Usage (Since last Contract)

Daily Usage ($)

---------------------------------------------
--Daily Usage ($)
---------------------------------------------
select
    *
from
    SNOWFLAKE.ORGANIZATION_USAGE.USAGE_IN_CURRENCY_DAILY
where
    usage_date = :daterange

Daily Usage ($)

Usage by Account ($)

---------------------------------------------
--Usage by Account ($)
---------------------------------------------
select
    *
from
    SNOWFLAKE.ORGANIZATION_USAGE.USAGE_IN_CURRENCY_DAILY
where
    usage_date = :daterange

Usage by Account ($)

Accounts in Organization

---------------------------------------------
--Accounts in Organization
---------------------------------------------
show organization accounts

Accounts in Organization

*For the tile above utilize the results and you can generate a table from that. No chart needed**