How to Avoid Hidden Cost Traps in a Snowflake Test Account

11 min readMar 2, 2024

There are many posts about Snowflake cost optimization, but all refer to rather heavy production accounts. This article will be about Snowflake cost optimization for only test or evaluation accounts. As you will see, the approach is very different in many regards…

What is a Snowflake Test Account

I call a test account a free Snowflake trial account someone decides to keep after the 30-day trial period. A few reasons why you may do this:

You run out of email addresses (as you cannot reuse the same email address for a new trial period, unless a certain time passed).
You need to rely on the Snowflake customer service (as trial account cannot use the customer service).
You may need to activate a service like Data Exchange, which cannot be enabled for trial accounts.
You have a new Snowflake feature in PuPr (Public Preview) like Budgets (which is not available to free trial accounts).
You need to test new features like the Snowflake Native App Framework, which at this moment can be tested with private Data Exchange only for accounts within the same organization.

In practice, I’ve used and still use such services in the following situations:

A Snowflake account solely at my discretion, paid and used by myself alone. For proof-of-concepts, experiments, ad-hoc analysis, potential customer demos, small projects I post with these blog articles, new Snowflake features I must try live…
A test Snowflake account paid by my consulting company and shared internally with other Data Engineers and Architects. A good practice we used with these accounts is to always define a Network Policies with our allowed IP addresses alone.
A Snowflake account used for testing and paid by one of our customers. It had sensitive customer data that we could not transfer and use outside the account, even if we always had to deal with test data.

The main differences from a production account — or even a development account used by a large team to implement and test a bigger project — is that, when not used for ad-hoc experiments by someone, everything should be shut down and the account should have zero or almost zero costs.

Good news is any Snowflake account could cost you as low as $0 or $0.01 a month, when you keep only a dozen of small databases max, with no warehouses up and no other cloud services running.

Standard vs Enterprise Edition

Beware that by default your free trial is on the Enterprise Edition, not the Standard Edition. And you may want this, of course, to test amazing features such as the multi-cluster warehouses, time travel, materialized views, data masking, and search optimization.

But when your trial ends and you remain with an on-demand paying account now, you start to pay 150% on ANY feature that could cost you much cheaper on the Standard Edition. Have a look here, as my test account costs me $3 per credit, instead of $2, just because I kept the Enterprise Edition:

And no, you do not pay the higher price only for the specific features coming with the Enterprise Edition. You pay $3 per credit for any simple SQL query that you may execute all the same with just $2 per credit in the Standard Edition!

Assume you have exactly the same configurations and data in a Standard and in an Enterprise Edition accounts. If you run the same exact query in Standard — that doesn’t require any time travel, masking or whatever — it may cost you let’s say $4 (if that’s a complex query that consumed 2 compute credits), while in Enterprise you’ll have to pay $6!

I queried here my consumption for the past month, and all compute usage (for warehouses, serverless tasks, and other cloud services) returns indeed a rate of $3 per credit:

SELECT USAGE_TYPE, USAGE,
  ROUND(USAGE_IN_CURRENCY/USAGE, 2) as RATE,
  ROUND(USAGE_IN_CURRENCY, 2) as USD
FROM snowflake.organization_usage.usage_in_currency_daily
WHERE USAGE > 0 AND USD > 0
  AND USAGE_DATE > DATEADD(month, -1, CURRENT_TIMESTAMP())
ORDER BY USD DESC;

My advice to you would be to keep two Snowflake test accounts: one with Standard and the other one with the Enterprise Edition. For those experiments where you don’t need the advanced features from Enterprise, use the Standard Edition account instead. You’ll cut the bill by 33%!

Automated Jobs

In most production accounts, which are usually highly optimized for processing, 70% of all Snowflake compute cost is the result of automated jobs. However, in our test accounts, you’ll have to make sure you shutdown any such service as soon as you are done experimenting with it.

And it is very easy to forget scheduled tasks running, conditional alerts, unnecessary optimization services… Always suspend your running tasks. I forgot mine once running for several weeks, and they were keeping alive a full warehouse, because they were waking up every minute for a silly job, and the warehouse was supposed to auto-suspend after 5 minutes.

If your Account Usage for Compute resources (warehouses and other cloud services combined) doesn’t show a very reduced number for the background tasks, it’s either because you experimented and maybe still experiment heavily with such services, or something you’re not fully aware of is still running in the background:

Warehouses

A warehouse is like a car engine. As long as the car is in the parking lot, with the engine off, it doesn’t cost you gas. When you create or alter a warehouse, make sure the Auto Suspend is always ON. And change the default value from 5 to 1 min(s) of inactivity!

That’s how I keep my own experimental warehouse as well. Beware 0 or empty (null) do not mean “immediate shutdown”, it is quite the opposite: they will never stop! And if you run CREATE WAREHOUSE with no AUTO_SUSPEND option, the default is 10 minutes!

Your account is automatically linked with a default ROLE and a WAREHOUSE, each time you connect to Snowflake with a session.

Always use an X-Small warehouse with an almost immediate shutdown of 1 minute when you experiment with queries, as you don’t want to start by mistake a much bigger warehouse when you don’t need such compute capacity. As for other warehouses, try to uncheck the Auto Resume option if they may consume a lot of credit when started.

Other general tips for the warehouses are to always use an attached resource monitor (to alert you on high unexpected consumption), and to reduce the STATEMENT_TIMEOUT_IN_SECONDS value (by default a query can run for 48 hours before being forced to shut down).

If possible, never leave your queries unattended when executing, as they may take forever. As a rule of thumb, expect that a query running for well beyond 10 minutes may cost you eventually $10 or even more.

Querying Metadata

Unlike many other databases system — including other cloud services— in Snowflake querying metadata is not always free. Most DDL statement — like CREATE, or SHOW — do not require a warehouse. But querying the system views from the ACCOUNT_USAGE or ORGANISATION_USAGE schemas will not only cost you credits, but will usually go through a large amount of data, as they both store a lot of information, for the past year.

A while ago I wrote this article for Snowflake about column data lineage, and it cost me over $20 USD in 2–3 days just for querying several times the ACCOUNT_HISTORY table.

The nice Snowsight GUI can be misleading with its nice interfaces, and you may be tempted to switch to the account usage, copy history, task history, or even dynamic tables views with no idea that each such screen will cost you money after all. Each time you see a warehouse selected in the top-right corner (or elsewhere sometimes), this means the information already displayed on your screen has been obtained by running a query with that warehouse. It means you just paid more for the list you just had in front of you, like me here:

When you keep an eye on your Account Usage history, you may reduce costs with some actions you later take. But for now you just increased your costs just by looking at this data! And this applies as well to any SQL query on the system views. There was no free lunch for this screen:

Internal Stages

Unless you store TB or PB of data, storage will be much cheaper than compute, but any database in the order of hundreds of MB could add a cost in your bill. And you may delete databases and tables — as I did — but do not forget internal user stages where you eventually uploaded the external files, to copy them in tables with the next step.

It’s easy to forget to delete such files, at at the end they could make up more than the data stored in databases.

The previous picture presents a use case in which the amount of data from files stored in internal stages was significantly much higher than anything left in databases.

Time Travel and Fail-Safe

In Enterprise Edition, Time Travel is enabled for max 90 days, and Fail-Safe always for 7 days. These are indeed wonderful features, but could be important money suckers when you don’t need them.

You cannot actually disable any of them for everyone in your Snowflake account. You can only set the default value for time travel to 0 (by default it is 1), hoping users will also use this default value when creating databases, schemas and tables. And create temporary or transient tables — with no Fail-Safe — instead.

The picture below shows when I created a hard copy from a free dataset shared from the Marketplace, with the intention to play with and change some data. I duplicated there a large amount of data. Which consumed both compute credits (in the CTAS query), and storage credits as you see below (the green color).

Later on, when I realized my mistake, I deleted the copy, but something else happened: all deleted data moved into the Fail Safe (the red color), and I had to live with this data I never needed in the first place for 7 more days! You cannot remove what moved into the Fail Safe, you have to wait one week to go away!

Third-Party Applications

From Marketplace or elsewhere, you may install free or commercial applications to enhance Snowflake’s functionality. And some of them are great. But even the “free” application will cost you money, beware! Because most if not all of them rely on the compute and storage offered by your Snowflake account to function.

A test Snowflake account is usually the ideal place to test such applications, and I know I do this frequently. But lesson learned, try to always remove them, and everything they created, after you’re done with your tests. Most of these applications create new static and dynamic resources, like databases and scheduled tasks. Some are truly intrusive, and once you granted them rights, you’ll have a hard time to figure out every change they made in your Snowflake account.

Just as a simple example, I installed a few days ago a free Snowflake native app from the Marketplace that has some interesting potential useful features. I can also see how it can help Snowflake users save money, especially for production accounts.

But when I drilled down on compute, I’ve seen a bunch of serverless tasks that simply …consumed credits:

This free application cost me already, in less than one single day, more than 1.5 total compute credits (0.7 + 0.4 + 0.2 + 0.2). At $3 the compute credit, and at this rhythm, this app could cost me around $135 USD per month (1.5 x $3 x 30 days)!

It may be free, but if its benefits do not save me way more than $135 USD a month, even on a small test paying account, it just barely breaks even! And that’s the case with many similar apps out there…

Conclusions

Snowflake cost optimization should be done as well on this kind of test accounts. And the focus is different from the big production accounts.
The Enterprise Edition will cost you way more than the Standard edition for the exact same queries. For exactly the same configuration and data, the same query supported in a Standard edition that costs you $2, will cost you $3 in the Enterprise edition!
Shutdown the automated jobs from your test account as soon as you no longer experiment with them. It’s easy to forget them up and running, and any tasks scheduled any minute or so could get you a hefty bill.
Use by default an X-Small warehouse associated to your test account, that Auto-Suspends after only one minute. Do not rely on the default (hidden) values. Or just manually suspend the warehouse yourself.
Beware that any time you look at so many screens in the Snowflake UI online — to check on metadata — you may automatically increase your bill without even knowing it. With rare exceptions, querying the system views is rather expensive, in terms of compute.
Do not forget to clean-up your stages after you pass intermediate data files through them. It’s obvious to delete tables and databases to save space, but easy to forget that stages contribute as well to the total storage space.
Change the default value for Time Travel to zero, at the account level, when you don’t want this feature by default. Avoid Fail-safe by using transient tables. Dropping a large database will force you to wait 7 more days to get your huge amount of data removed from Fail-safe as well.
Most third-party applications integrated with Snowflake will consume your credits! They could be useful in other areas, but there are no “free” applications, as long as all of them will cost you money after all.