Permanent, Transient, and Temporary Tables
1. Permanent, Transient, and Temporary Tables
In the last video, we talked about time travel, and we mentioned that each table has a retention period. Time travel also applies to databases and schemas, as you can probably guess from the fact that you can undrop databases and schemas, but we chose to focus on tables. We haven’t discussed this yet, but Snowflake also provides an additional 7-day “fail safe” period *after* the retention period has elapsed during which Snowflake staff can help you recover historical data. But to use Fail Safe, you have to contact the Snowflake team directly and get their help – it’s a last resort. It turns out that understanding retention periods and the fail safe period is key to what we’ll discuss next – the differences between three of the main table types in Snowflake: Permanent tables, transient tables, and temporary tables. This is useful, but not complicated, so we’ll move through this quickly. Let’s dig in by quickly creating a table of each of these types using our Tasty Bytes data, and then comparing them. When you create a table with the normal “CREATE TABLE” command, the result is a permanent table, so we already have a number of those around we can look at. We don’t need to make a new one. So let’s get started by creating a transient table. Let’s use the truck table that we worked with in the last video. Let’s clone that table (again, don’t worry about cloning – we’ll talk about that soon), but this time let’s clone it as a transient table. CREATE TRANSIENT TABLE FROSTBYTE_TASTY_BYTES.RAW_POS.TRUCK_TRANSIENT CLONE FROSTBYTE_TASTY_BYTES.RAW_POS.TRUCK; So we’re creating a table, and notice that we’ve included the word “TRANSIENT” in there this time – so “CREATE TRANSIENT TABLE” instead of our normal “CREATE TABLE.” We called this “TRUCK_TRANSIENT” instead of just “TRUCK.” Then let’s do the same thing, but creating a temporary table instead. CREATE TEMPORARY TABLE FROSTBYTE_TASTY_BYTES.RAW_POS.TRUCK_TEMPORARY CLONE FROSTBYTE_TASTY_BYTES.RAW_POS.TRUCK; So this says “CREATE TEMPORARY TABLE,” and we called the table “TRUCK_TEMPORARY.” Awesome! So now let’s try a little experiment. Let’s change the retention period for each of these tables to 90 days. First, let’s look at the current retention periods: SHOW TABLES LIKE 'TRUCK%'; By including “LIKE” and then truck (with the percent symbol), we’ll pull out information for only those tables that start with the word “truck.” If we scroll over, we can see that the retention period for each of these tables is 1 day. Okay, now let’s set each retention period to 90 days, one by one. ALTER TABLE FROSTBYTE_TASTY_BYTES.RAW_POS.TRUCK SET DATA_RETENTION_TIME_IN_DAYS \= 90; Okay, that seemed to work just fine for our permanent table. That makes sense – if you’re in the enterprise edition, permanent tables can have a retention period of up to 90 days. Now let’s try it for the transient table: ALTER TABLE FROSTBYTE_TASTY_BYTES.RAW_POS.TRUCK_TRANSIENT SET DATA_RETENTION_TIME_IN_DAYS \= 90; Uh oh! That seemed to fail. Okay, and let’s try it for the temporary table. ALTER TABLE FROSTBYTE_TASTY_BYTES.RAW_POS.TRUCK_TEMPORARY SET DATA_RETENTION_TIME_IN_DAYS \= 90; That seemed to fail too! Let’s double check to confirm that this worked for the permanent tables, but failed for the transient and temporary tables. SHOW TABLES LIKE 'TRUCK%'; Yep, sure enough, the permanent table’s retention period went up to 90 days, but the others stayed at 1 day. We *can* decrease the retention period for the transient and temporary tables, though. Let’s drop both to zero. ALTER TABLE FROSTBYTE_TASTY_BYTES.RAW_POS.TRUCK_TRANSIENT SET DATA_RETENTION_TIME_IN_DAYS \= 0; ALTER TABLE FROSTBYTE_TASTY_BYTES.RAW_POS.TRUCK_TEMPORARY SET DATA_RETENTION_TIME_IN_DAYS \= 0; That worked, so it’s not that their retention periods are unalterable. So this brings us to the first thing we should understand about permanent, transient, and temporary tables. Permanent tables can have a [retention](https://docs.snowflake.com/en/user-guide/tables-temp-transient#comparison-of-table-types) period of up to 90 days if you’re in the enterprise edition. Transient and temporary tables *cannot*. Their retention periods have to be between 0 and 1 day, inclusive. This is useful if you want to make sure you’ll save on storage costs for tables that don’t matter much. There’s another key difference between permanent tables and transient + temporary tables: Permanent tables have a fail-safe period of seven days. Transient and temporary tables have a fail-safe period of zero days. None of this is configurable. What this means is that after your retention period is over for a permanent table, there’s still hope of recovering historical data by working with the Snowflake team. But for transient and temporary tables, that’s not the case. “Abandon hope, all ye who enter transient or temporary tables hoping for fail safe to rescue you.” So at this point you might be wondering: How, then, are transient and temporary tables different? They both have the same possible retention periods, and neither have fail-safe periods. The answer is that temporary tables only persist as long as your session lasts. Transient tables last until they’re dropped. So you can think of transient tables as being part way between permanent and temporary tables – They’re like permanent tables in that they persist, and they’re like temporary tables in that you don’t get much of a retention period with them, and you get no fail-safe protection. Okay, so that’s all for this video! To recap, we learned about fail safe, we created a transient table, we created a temporary table, and we explored the differences between permanent, transient, and temporary tables.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.