0:01 Hello, welcome to Dynamic Tables by Tom. My name is Tom Rottinger. I'm a principal technical architect with Selflight. Today I'm bringing you video number four where I'll be talking about control tables. My earlier video number three, I discussed some of the options for refreshing dynamic tables. One of the patterns I discussed was a control table. Today I'm going to take a much deeper dive, show how to set up an environment and set up a top level control table using Coco to build all this for you automatically. I hope you enjoy this video. Thank you. Before I jump into the demo, let's start with a quick refresher of what a control table is.
0:48 In my previous video, I talked about some different options for refreshing dynamic tables. One of those options was control tables. Just a quick review, some of those options I mentioned last time are target lag anduler for just setting how the dynamic table behaves. And then we also talked about a couple patterns. The one pattern I'm going to focus on today are control tables. Control table is nothing but a parent dynamic table that when refreshed forces all downstream dynamic tables to refresh. It's a common pattern used when you want to kick off all your tables or a group of your tables in one single command. This slide shows a sample control table. It's nothing but a dynamic table that references all of
1:34 your top level leaf nodes. Once referenced, Snowflake will handle the process automatically of rebuilding those dynamic tables. Where possible, it will rebuild those in parallel. Let's begin our demo by setting up all the necessary objects to create and hold dynamic tables. I'm going to start by just creating a database schema and warehouse. It will be used for the objects that are demo. We'll see how I'm creating a database DT demo with a schema TPCH. Uh TPCH stands for transaction processing performance council. And this is sample data for our demo. It will make it much easier instead of starting things from scratch. Next, I'm going to create local copies of these tables. I could reference the share that's there in snowflake, but I may do some other testing later that
2:21 will allow us to make updates to the table. So, this will just make it easier for us to do that. Now that the tables are created, I'm going to have Coco build these dynamic tables for me. You can see here I've actually put a prompt in to show the Coco prompt that was there. Basically, I told it to use the sample queries from TPCH that are out on the public internet. Uh, take this data and then build me dynamic tables for those. Let me open up my window and you can see where I ran this command earlier. Here's where I asked the question and you can see where each of these were built and actually put into uh the section below. So, let me scroll down
3:06 and show you those uh tables that were there. But each of these dynamic tables are the actual uh query that's run in a TPCH benchmark test. So you'll see 10 of these that were created in the environment. So I'm going to select those and create all these in the environment for us. So let's run these Now that we have the dynamic tables created, I'm going to create another top level table. So I'm going to call this one Q9A summary profit. It's just really looking at the profit dynamic table that was there. You can see here it's going
3:52 to be built on top of that. So this is creating a dependency of top level table referencing the downstream PPC9 table. So now great, that's created. Let's just review the data. I want to show you quickly what it looks like. Look at the original table. Uh, this was showing profit by country. And then the summary table that I just created just shows an overall profit by year. Next, we're ready to build a control table. Now, this is the prompt that I put here where I'm asking Koko to build a control table for us. Let me show you over here. I basically just cut and paste into here. I told KCO that we want to build a dynamic control table. Here is the statement that I send to Snowflake.
4:38 Let's see what it generated. You can see I told it to only reference the leaf nodes. So anytime you refresh an upstream table, all downstream tables are done. So your control table would only need to contain top level leaf nodes, not all. This will make Soflake run uh more efficient by not forcing everyone to go. That's going to already be done when you do the upstream table. So I look at the code that was generated. Here is the code that it created. It called it DT control table. And you will basically see it's doing nothing but a select table name and a row count. Again, the purpose of this is to really
5:23 reference the table. And this becomes a downstream table from this top level view. So each time we refresh this any dependent objects are refreshed and we can see here. So we wouldn't really use this dynamic table in a reporting or anything but by simply doing uh an alter dynamic table refresh all the objects that reference in this one control table would be upped. So I could do this with a task or some other thirdparty orchestration tool. Once I call this command, all of my objects will be referred at the pretty much the same time. One last thing that I would like to show would be using stoside to look at dynamic tables. Open it up and look at at the DT demo database. You will see
6:10 here the dynamic tables that I set up earlier. If you click on control table, you'll be able to see a graph that shows all the tables in the environment. We can see the control table and all the other subtables that were there. If I open up 9A that we set up, you can see that it shows the other table here. So, this is a very good visual way of seeing how your views are tied together. >> Thank you. I hope you've enjoyed this video.
