Snowflake implementation

You set up the account so multiple teams can work in it securely and cost-effectively: a cloud region, databases and schemas for each layer, a role hierarchy, virtual warehouses for different tasks, loading routes for data sources, transformations in dbt and monitoring of credit consumption.

We do this as part of the data and AI platform, so reports and models read from the same data.

A Snowflake consultant designs the roles, warehouses and loading routes, and sets up transformations and cost monitoring. Snowflake recommends a hierarchy of custom roles and advises against combining account administration privileges and data access privileges in the same role.

ACCOUNTADMIN is the highest role and, according to Snowflake, should be assigned to only a limited number of users. There is no role that bypasses all privilege checks.

Snowflake runs on Amazon Web Services, Google Cloud and Microsoft Azure. You choose a region, such as Azure West Europe or Google Cloud europe-west4, both of which are in the Netherlands.

To run queries and load data, you need a virtual warehouse: computing power that you switch on separately. Its size determines the computing power available per cluster, and each increase in size doubles hourly credit consumption.

Snowflake bills for warehouses by the second, with a one-minute minimum each time one starts. Auto-suspend switches off an idle warehouse; auto-resume switches it back on for the next query.

A resource monitor tracks a credit quota and can issue warnings or suspend warehouses. Only ACCOUNTADMIN can create monitors, and you use a budget for serverless features.

Snowflake stores the tables, while dbt transforms them into models with tests. In our churn analysis, we describe this combination: models that select columns, perform calculations and produce a combined dataset.

Note that dbt creates tables in Snowflake as transient by default, without Fail-safe. You can choose a separate warehouse for each model. Read more about dbt implementation.

In batches using COPY INTO from a stage, on a warehouse you choose and pay for based on its running time. Or continuously with Snowpipe: it loads files as soon as they arrive in a stage, using Snowflake computing power and billed according to the amount of data loaded.

For sources such as an ERP or CRM system, you would usually put a loading tool or pipeline in front of it to supply the files or tables.

Time Travel lets you recover earlier data. The default retention period is one day; you can extend it for permanent objects, except in the Standard Edition. You can restore a deleted table with UNDROP. After that, the data moves to Fail-safe, where you can no longer recover it yourself.

A zero-copy clone lets you create a test environment that only incurs storage costs when you make changes to it.

No. According to our partners page, we are a Databricks Partner and a Microsoft Partner. We work with Snowflake where it suits an organisation or is already in place, as at Athlon.

If you’re weighing up platforms, we’ll compare the differences openly. Read about Databricks implementation too; the choice is yours.