Getting started with Snowflake PT-4 : Time Travel, Security and Data shares

0:00Hey, it's Tim here. In today's video, we're

0:01hopefully going to finish the getting

0:03started with Snowflake guide that I've been

0:04working through over the last few weeks and

0:07essentially sharing it as a video.

0:08So in the last video, what we did is we

0:10finished working with the semi-structured

0:13data that we were using.

0:14We're actually using some JSON weather data

0:16.

0:16And so we actually just finished running

0:18through this module here, module number

0:20seven, basically loaded up some JSON data.

0:23And we kind of showed the power of Snow

0:25flake essentially saving you a whole lot of

0:27effort and sort of hassle in terms of

0:29working with JSON or semi-structured data

0:32in this particular case and just making it

0:34super easy to work with.

0:35And so what we've done in that exercise, we

0:37basically got to the end of this exercise

0:39and we sort of cut the video there.

0:40Now, the next stage is actually to use time

0:42travel.

0:43So today we're going to talk about what

0:44time travel is in Snowflake.

0:46We're going to do some general account

0:48admin as well.

0:49I've already using Snowflake as a proper

0:51customer, as it were.

0:53So some of this might sort of look a bit

0:54different to what we go through in the

0:56exercise, but essentially it's just me on

0:58here.

0:58So you kind of get a basic insight into

1:00what I'm using it for.

1:01The last thing we'll do is we'll go and

1:03sort of go through some of these other

1:05areas to do with data sharing and the data

1:07marketplace.

1:08And we'll reset the Snowflake environment

1:10so that you don't have anything that's been

1:12set in this tutorial and sort of sticking

1:14around costing you money over time.

1:16The storage is the only real thing to worry

1:18about.

1:18Everything else shouldn't, because unless

1:20someone uses those database tables, they're

1:22sort of not doing anything.

1:24You're just paying for the storage and

1:25storage on Snowflake is pretty cheap.

1:27It's actually very, very, very competitive.

1:29And so it's not something big to worry

1:31about.

1:31But nevertheless, we'll go through this

1:34last step here to get everything sort of

1:36reset.

1:37So let's try and make our way through this.

1:39I really want to try and finish it in this

1:40particular video so we can sort of keep the

1:42series nice and compact.

1:44For those of you who are joining this

1:45premiere or watching this premiere for the

1:47first time, thank you so much.

1:49You've probably figured out a pattern.

1:51Sundays 8 p.m. GMT is when we're going to

1:54be doing these going forward.

1:56I'm going to pretty much turn it into

1:57something like Snowflake Sundays where

1:59every Sunday I'll try and share my video

2:01showing you what I've learned during the

2:02course of the week.

2:03OK, so that's pretty much it.

2:05Let's get straight into the top content and

2:06let's figure out a way out.

2:08Let's look at this guide here.

2:10So Snowflake's time travel capability

2:12enables historical data access at any point

2:15with a pre configurable period of time

2:18within a pre configurable period of time.

2:20So essentially you can change the time that

2:22you can travel back in time.

2:24Essentially, the default window is 24 hours

2:26and with Snowflake's Enterprise Edition, it

2:29can be up to 90 days.

2:30So essentially by default, this is going to

2:33be what everyone gets.

2:3424 hours is standard feature.

2:36You have the Enterprise Edition, you can

2:37bump that all the way up to 90 days, which

2:39is kind of cool.

2:40Imagine being able to go back 90 days in

2:42the past and run a query as if you were

2:44running it three months ago.

2:45Let's say maybe someone in the business

2:47asked a question today and then three

2:49months later asked the same question, but

2:51changed it ever so slightly.

2:53You could essentially go back in time and

2:55run the query at that specific point in

2:56time, which is kind of cool.

2:58Most data warehouses cannot offer this

3:00functionality, but you guessed it, Snow

3:02flake makes it easy.

3:03That's a sort of that should be a T-shirt.

3:05Snowflake makes it easy.

3:06That'd be a really good one.

3:08Some useful applications include restoring

3:10data related objects such as tables, sche

3:12mas and databases that may have been deleted

3:14, duplicating and backing up data from key

3:17points in the past, analyzing data usage

3:19and manipulation over specific periods of

3:22time.

3:22And so, yeah, those are just sort of a

3:24couple of examples, but it's sort of

3:26interesting because actually this opens up

3:29a lot of possibilities.

3:31It's the kind of thing that you also maybe

3:33want to think about because it sort of

3:35opens up slightly different operational

3:37sort of capabilities that you maybe didn't

3:38have before.

3:39When you've got this sort of ability to go

3:41back in time up to 90 days, you can

3:43actually build that into sort of some of

3:45your standard ways of working.

3:47Let's read more into it.

3:49So let's drop in and drop a table.

3:50So here you can see that you can drop the

3:52weather JSON data.

3:53Okay, so let's go back into my worksheet.

3:55Just for context here, I'm working in the

3:57Snowflake interface.

3:58You don't have to do this.

3:59You can work in any other tool.

4:00In fact, my colleague Jonathan McDonald is

4:03a server guru, discovered that there's

4:05actually a plug in for, I forgot the name,

4:07VS Code.

4:08So there's actually a plug in for VS Code.

4:10Let me see if I can get VS Code up and I

4:11can actually just show you the plug in

4:14itself.

4:14So I'm not going to log into Snowflake with

4:17it.

4:18I've already got something here, but I just

4:20want to show you that everything we're

4:21doing can be done in different tools.

4:23So if I just go in here and I just search

4:26Snowflake, I think what happens is when you

4:29find the plug in, it automatically installs

4:33the relevant things required.

4:35So just go into the database and just I'm

4:37actually searching in the wrong place here.

4:40I need to go to the extensions and search

4:42Snowflake, not the file browser.

4:44So here we go.

4:44Snowflake driver for SQL.

4:46I think this is the one.

4:47Yeah, SQL tools.

4:49Yeah, this is the one.

4:49So if you click install, it goes ahead and

4:52installs it and once it's installed, you

4:55will see that it's installed just here.

4:57It says installing.

4:58I think it's installing everything that it

5:00needs to install in the back end as well.

5:02And you can also see that there's this

5:04little sort of sign up there, but now you

5:07can see that it's finished installing.

5:09And when it finishes, an additional icon

5:11appears here on the bottom left.

5:13So I have something called Docker, which is

5:16what this icon here is, and then it adds an

5:18additional icon on the bottom, which is

5:20basically telling you that, hey, you've got

5:22some additional capabilities here to work

5:24with databases.

5:25Go to that tab, add a new connection.

5:28And you can, of course, see there's a Snow

5:30flake connection there.

5:31Click on that and you're off to the races.

5:33Essentially, you get a basic sort of

5:36database tool right here in VS code.

5:39Like VS code can do literally everything at

5:41this rate.

5:41It's pretty amazing what Microsoft have

5:43done with this tool and sort of open it up

5:45and made other people sort of build things.

5:47And, you know, VS code is also built using

5:49browser technology.

5:51So it's going to work pretty much exactly

5:53the same as sort of what we're using here

5:55with the worksheet.

5:56Okay, so just that sort of small tangent

5:58just to let you know that you don't have to

6:00do everything we're doing using the web

6:02interface.

6:03You can do use whatever tool you want to

6:05use.

6:06Most of the queries we're running are just

6:08queries.

6:08So if you now to run queries and your tool

6:11supports Snowflake, then, you know, you're

6:13going to be able to use it.

6:13Okay, so let's go back to this little guide

6:16.

6:16And what they want us to do is drop a table

6:18and then undrop it essentially.

6:20So from the worksheet, run the following

6:22drop command to remove the JSON weather

6:24data.

6:25So this is just basically going to delete

6:27this table.

6:28So let's go ahead and run this.

6:29We're still using the weather data.

6:32So the key thing is when you're running any

6:34queries, we're using the JSON weather data.

6:36But this isn't going to work unless you're

6:38sort of you've got your context set

6:39correctly.

6:40And my context is still sysadmin compute

6:43warehouse weather public.

6:45I'm not using the analytical warehouse like

6:47it suggested.

6:47I'm just using the small one because I'm

6:49actually paying for this at this current

6:50point in time.

6:51So I'm going to save myself a little bit

6:53hassle and the small one runs just as fast

6:55for this kind of small query.

6:56So it's perfectly fine.

6:58We'll go and try and load 10 rows from this

7:00data that we've just deleted.

7:02And we should get an error essentially

7:03because this data doesn't exist.

7:05So you can see here that it says that JSON

7:07weather data does not exist or is not

7:09authorized.

7:10So the reason that's not working is because

7:13, again, just to sort of really make this

7:15clear and apparent,

7:16we've just run this command to drop the

7:19data.

7:19So that's why we're not seeing the data

7:22there.

7:22So the next step is to just undo that step,

7:25which is kind of nice.

7:26It has a nice little command for that.

7:28So undrop table JSON weather data, hit run,

7:32and then it will just basically undo that.

7:34And it will say here table JSON weather

7:37data successfully restored.

7:39And we're pretty much sort of good for the

7:41races.

7:41If we just go back, make sure there's no

7:44sort of any additional context that we're

7:46missing.

7:47No, there's no additional context there.

7:49It's really that easy.

7:50Sometimes when things are easy, there's

7:51sort of nothing to talk about.

7:52So, you know, it just is what it is.

7:55So let's go ahead to the next step, which

7:57is rolling back a table.

7:58So this is essentially where you've made a

8:00mistake.

8:01You've changed something and you'd like to

8:02roll it back.

8:03You'd like to undo that change.

8:04Now, in some databases, that's not possible

8:06.

8:06So what they do is they take it back up,

8:08they take a plan or whatever, and then they

8:10work on something.

8:11And if something goes wrong, then you have

8:13some sort of point in time.

8:14You can go back to a century.

8:15But with Snowflake, you kind of don't need

8:17to stop and do that because it kind of lets

8:19you do that anyway with this time travel

8:20feature.

8:21So let's go back and look at the guide here

8:25.

8:25So roll back a table.

8:26Let's roll back a table previous state to

8:29fix an unintentional DML error that

8:31replaces all the station names in the CityB

8:34ike database trips to table with the word o

8:36ops.

8:37So it looks like what we're going to do

8:38here is we're going to make sure we work in

8:40the right place.

8:41And then we're going to basically make a

8:43change that is going to be deliberate

8:45mistake.

8:46OK, and the deliberate mistake is we're

8:48going to change all the bike station points

8:50to be called oops, essentially.

8:52And so when we then run the query, we

8:54should see a table just says oops and the

8:56number of records.

8:57You can actually see that here says oops,

8:5961 million records, which is the exact

9:02number of records we have in the actual

9:04table that we loaded from S3.

9:07So normally we would need to scramble and

9:08hope we have a backup lying around, which

9:10is what I was saying earlier on.

9:12In Snowflake, we can simply run commands to

9:14find the query ID of the last update

9:16command and store it in a variable called

9:19query ID.

9:20So we're going to basically make a mistake.

9:23We're going to then look at the data,

9:25realize we've made a mistake, and we're

9:27going to figure out, hey, what was that

9:29query that made that mistake?

9:30And so that's what this little piece of SQL

9:33is.

9:33We'll do this in a second. Once we find the

9:35query, we're going to store that query

9:37somewhere.

9:38So we're going to call it in a variable

9:39called query ID.

9:40If you're used to programming, you'll be

9:42used to this. So you always store variables

9:43all the time.

9:44But if you're not used to programming, it's

9:47quite common in most tools where there's

9:49any sort of scripting to be able to store

9:51information in something called a variable.

9:54A variable is just like putting something

9:56in your memory, like noting something down

9:58and then being able to call it a little

10:00later on.

10:01So variable called query ID.

10:03And then what we can do with that variable

10:05is recreate the table with the correct

10:07station names from the point in time before

10:10that query, essentially.

10:11So create or replace table trips as and

10:13then select staff from trips before this

10:16statement query ID, essentially.

10:18So go back in time before this query was

10:20run and it will basically restore the table

10:22right before this query was executed, which

10:25is kind of cool.

10:25And then we'll run the query and we'll be

10:27able to see it sort of happening.

10:29So I just talked my way through that. It's

10:31very, very sort of simple.

10:32But now we're going to actually try and do

10:34it. So we're here in 7.2.

10:36So we need to make sure we're using the

10:38right database.

10:39So you can see here it says use database

10:41City Bike use schema public.

10:43You can run this query here by just sort of

10:45highlighting and hitting run.

10:47The other thing you could do is you could

10:49go up here and change it manually so you

10:51can do things sort of multiple ways.

10:54Depending on your tool, that might also

10:55give you the option to change this.

10:56So I'm just going to click on the top here

10:58and show you how to do that.

11:00So here I just need to go to City Bike and

11:01we need to make sure it's on public.

11:03Public is the one that comes up by default,

11:05so I don't need to change that.

11:06So now we are doing things as we should be.

11:09And the other thing to know is because I

11:11haven't done anything for a while, my

11:13compute warehouse has actually stopped.

11:15You can see here it's not green because I

11:17haven't done anything for a while.

11:19So there's nothing for the database to be

11:20doing. So it switches it off temporarily.

11:22As soon as I run the next query, of course,

11:24it will sort of switch it back on.

11:26So just so you can see that, let's go ahead

11:28and highlight these two rows and hit run.

11:30And once we've done that, you'll see that

11:33this will update to being green in a second

11:35.

11:35So just give it one second and we'll just

11:38think it might actually be waiting for me

11:40to do something that requires like a query.

11:45So if I if I just do this step, then I

11:46think this will go green.

11:48I'm pretty sure that's what's going on. So

11:50I think I misunderstood how that particular

11:52thing works.

11:53I think it only spools up the database when

11:55this is actually available.

11:57OK, so you can actually see here that this

11:59is called trips dev two.

12:01Now, I actually made a mistake the first

12:03time I went through this.

12:04And what I had to do is I had to fix this,

12:07essentially.

12:07So my way of fixing it was to basically

12:09create another dev instance and sort of

12:11carry on.

12:12So you can see here, I've actually made a

12:14mistake.

12:14So let me just go ahead and remove the

12:16number two there and run this again.

12:18And now we should have this working.

12:21So if we hit run, you'll see that it's

12:23actually going to go off and run those

12:26queries.

12:26Now, I'm just going to go down here and

12:28just make sure that I remove all all inf

12:31erences of this thing,

12:32because I'm fairly certain that I shouldn't

12:37I should have changed this back.

12:38I could have copied it all back, but I sort

12:40of wanted to learn as well.

12:41So I made a mistake and I was trying to fix

12:43it.

12:43So I took another copy. I set it to trips

12:46dev two and I set everything to work.

12:48But I think that's the only thing I needed

12:50to do there. Everything else should be back

12:52to normal.

12:52So the query we just ran here was this one.

12:56So now everything should say oops.

12:58So let's go ahead and actually run that

13:00query to check that that's what it's saying

13:02.

13:02So let's hit run. And you can see here that

13:05it says oops.

13:06So it's sort of there's not much to see

13:08here, but all you can see is, look, we've

13:10got this thing that says oops.

13:11And it's coming from our query where we've

13:13basically asked it to list out the station

13:16names,

13:17the first 20 stations that it comes across.

13:20But it's not doing that because there's

13:21only one station and it's called oops.

13:22So what we now need to do is run the

13:24operation to sort of save ourselves here.

13:26So let's go down and you can see here it

13:29says set query ID equals.

13:31OK, and it's got this query. So let's just

13:33sort of decipher this a little bit more.

13:35I blew past it last time, but now we're

13:37here. We're going to sort of interrogate

13:39this a little bit more.

13:41So the first thing is here set the query ID

13:43. Now, remember, I was talking about

13:44variables.

13:45This is where this is coming in. So

13:46whatever this query returns,

13:49it's basically going to be setting it to be

13:51the query ID that we want to use in the

13:53future.

13:54And we can see this here because you see

13:56this query ID that we basically set here.

13:58It's then used in the next query. So we're

14:01sort of preloading a variable into a sort

14:04of a box,

14:05or putting something in a box, and then in

14:06the next query, we're going to take it out

14:08the box and use that result.

14:10So the query we're trying to find, let's

14:12just go to the next step.

14:14So select the query ID from and it's

14:16basically saying, look, the table.

14:18So essentially there's this information

14:20schema query history by session.

14:22So I think it's in here and what we need to

14:26do query history by session.

14:28I believe that is actually a table that is

14:30somewhere here on the left.

14:32I think it might be something that I cannot

14:35see.

14:35This is a weird one because this is a view

14:38to be fair as well.

14:40So it's not exactly this is a good point.

14:42So this isn't. Yeah, this is a view from

14:45the information schema.

14:46So rather than the the actual thing. So I

14:50don't think I'm going to be able to see it

14:51here.

14:52But essentially, there's a table inside of

14:55this information schema set up.

14:58In fact, let's take a detour and see if I

15:00can go find it. I really don't know what I

15:02'm doing here if you haven't figured it out.

15:04So let's go to City Bike and let's see what

15:08we can find.

15:10OK, so you see, it's not here. It's sort of

15:13separate. I'm pretty certain this is

15:15separate.

15:16So if we go in here and see if there's

15:18anything called query history, there isn't.

15:20So these are the views and information

15:26schema catalog.

15:29So I think this is where sort of my

15:31knowledge on this is sort of not quite

15:33there yet, but I'm fairly certain that if I

15:37go back here,

15:38you can see the table information_schema.

15:41queryHistoryBySession.

15:43So in here, an information schema, I

15:45believe there's a table called query

15:47history by session.

15:48And in that, you basically want to return

15:53the last five queries, essentially, and

15:56where the query text is like update.

15:59So basically, you're searching for any

16:01query that uses this term update and you

16:03order by start time and limit it to one.

16:05So it's basically going to basically go get

16:08the last five and then it's going to find

16:09the one that says specifically update and

16:12just return that one.

16:13So let's go ahead and run this and hit run.

16:16So let's just hit select that hit run.

16:19And so, yeah, that that sort of statement

16:23has run successfully.

16:24If I wanted to if I wanted to find out what

16:27that query ID was, I believe I could just

16:30do this.

16:31So everything up until there is in brackets

16:34. I believe I could just essentially do this

16:38.

16:38And if I just run that, I should just get

16:40the query ID. OK, so let's just run that.

16:43So you can see this is the query ID that's

16:44in question.

16:45So what I did there is I just ran this

16:47query without the ability to set the query

16:50ID as a variable.

16:52So I basically left that out of the

16:54selection. And so it's giving me the query

16:56ID here, whereas before, because it set the

16:59variable to query ID, we couldn't see it.

17:01So it just told me that it was successful.

17:02So this is sort of a nice little way of

17:04just validating what it's going to have.

17:06So then the next step is to create or

17:09replace trips dev as essentially the table

17:12from this specific point in time.

17:15So what we're doing is we're essentially

17:18creating the table again. We're going to

17:20overwrite it.

17:21That's why it says create or replace with a

17:23copy from back in time before this query

17:26was run.

17:27So that's how this works. So let's go ahead

17:28and do that. This is the moment of truth.

17:30So let's go ahead and run this and make

17:32sure this all works.

17:34Ah, it doesn't work. So statement cannot be

17:37used to specify time for time travel query.

17:40So this is interesting. So what's going on

17:44here?

17:45So statement 0190. So this is the right

17:48query. Cannot be used to specify time for

17:52time travel query.

17:54Interesting. So what does this actually

17:58mean?

17:58So you know what, when you get stuck, what

18:00you got to do with these things is go to

18:01Google.

18:01So this is a different issue to what I had

18:04before.

18:05Last time I ran this, I ran into another

18:06issue where I just basically didn't do

18:08something right.

18:09And then it started being sort of a bit

18:12funny.

18:12So let's let's just remove the query

18:14because that's not useful for the statement

18:17.

18:17Cannot be used to specify time for time

18:19travel query.

18:21So here we are. This is a stack overflow,

18:24the place of all answers.

18:25Let's go. Let's find out what's going on

18:27here. I would like to use a time travel

18:29feature on StackFlow can restore the table.

18:30I've deleted and created a table using the

18:32following command. I'd like to go back and

18:34use the following code.

18:35Hit the transaction ID to blah, blah, blah.

18:38Select from the before statement received

18:42an error message.

18:43Right. After dropping the table, creating a

18:46table with the same name creates a new

18:47version of the table.

18:48Drop version of the previous table can

18:50still restore using the OK.

18:52Right. Right. Right. Looks like there's

18:54three common reasons that error is seen

18:56with solutions.

18:57The table has been dropped and created. I

19:00see the sense of the time travel period has

19:02been exceeded.

19:03No solution target. Not the case because we

19:05are within our time. The wrong statement

19:09type is being targeted.

19:11OK. So none of these are useful. So let's

19:13go. Let's go to just just just do the old

19:15hack way.

19:16Snowflake time travel and try and figure

19:20out what's going on.

19:22So this is not how I wanted this to go.

19:24Last time I had a different issue when I

19:26was doing on my own.

19:27And just when I thought I'd figure it out,

19:29something else is causing the problem.

19:31So select app before clone app before and

19:34drop time travel retention one to 90 days.

19:39OK. Do we have an example query here? Am I

19:42being lazy? I just want to get to a query

19:44and see how it's run.

19:45OK, cool. So select star from my table at

19:49timestamp.

19:50But the following query selects historical

19:53data from a table as of five minutes ago.

19:57OK, so let's try this one. The following

20:01query selects.

20:02So these all create select. But I think

20:05what we want to do is to.

20:07Yeah, essentially, this is what it's

20:08actually doing in the query. So create

20:10table and clothing.

20:12We're creating a client, but in this case,

20:14create or replaces what you want to use.

20:16So create table restored table plan my

20:20table at timestamp.

20:22So basically you're specifying the exact

20:25time there.

20:27The following creates schema command

20:29creates a clone of a schema and all of its

20:31subject has existed one hour before the

20:33current time.

20:34That doesn't make sense. So what I could do

20:37is I could try and use this one.

20:39So I could try and I could try and

20:42basically use this offset one potentially

20:45just to go back in time and actually do

20:49proper time travel.

20:50All this, the following creates a database

20:52command creates a client of a database and

20:54all of its objects as they existed prior to

20:56the completion of the specified statement.

20:58So. All right. So this is actually what we

21:01're trying to do.

21:01So interesting. So we actually know the

21:05query. We managed to get that anyway

21:08because we we asked what that was.

21:11Create database restored DB, clone my DB

21:13before the statement.

21:15So it's a clone of the database and create

21:19or replace.

21:21Interesting. So I think. I think.

21:27OK, what I want to do is let's let's first.

21:35Let's first run this one. Let's try and see

21:37if we can see the table before the actual

21:39query.

21:39That's step one, because then we know we've

21:41got the right thing working.

21:42So here's a here's a query that I ran and

21:45this obviously not working as it should be.

21:48So let's paste this one in here.

21:50OK. And we know that this is the actual

21:54query that we were trying to work with

21:57before.

21:58So let's go ahead and place that there. So

22:00now we've got everything set up.

22:02I want to basically go and run this query.

22:05So this is going to be essentially should

22:07be returning the data before we changed

22:09everything to OOPs.

22:10So if I still see OOPs, this is not worked.

22:13So let's hit run and let's see what happens

22:16.

22:16So object my table does not exist, of

22:19course, because I'm looking at the wrong

22:21table.

22:22So I think I need to replace this to devs.

22:25It's the classic problem when you copy and

22:26paste because, you know, you're not paying

22:30attention.

22:31So select staff from trips dev before the

22:33statement.

22:35OK, so this this should be correct now. I'm

22:36fairly confident this this has got to be

22:38working.

22:38So let's hit run. OK, so cannot be used to

22:42specify time for the time travel query.

22:45So this is interesting. Statement cannot be

22:50used to specify time for time travel query.

22:55I'm wondering what's going on here, so let

22:58me let me think about this for one second.

23:01Think I know the issue. I think I know the

23:06issue here.

23:06So can you see you've all probably been

23:10shouting at me and the screen, but I've

23:13only just figured this out.

23:14So minor detail, but very, very, very

23:17important.

23:18So you see here, trips dev and trips, trip

23:22dev and trips. What's wrong here?

23:25I'm trying to run this time travel query on

23:28the wrong table.

23:29So let's let's get this correct. I don't

23:34know why this is wrong.

23:35I don't know if it's maybe if you're

23:38running this exercise.

23:40Tell me if that is wrong for you, because

23:42if it is, then it's not just me who's had

23:44this problem.

23:45But I likely think I changed this worksheet

23:48somehow working through this, trying to

23:49play with a few things.

23:51And it's kind of come back to bite me. But

23:52I think this is it.

23:54So let's now everything should still be set

23:56up. We've still got my query ID set up.

23:59So if I run this, for example, and I've set

24:02it to 60 because the time limit is sort of

24:05moving on here.

24:06So let's hit run and you'll see here that

24:09it does return the right query.

24:11And if I oh, interesting, I might be doing

24:17this. I'm doing this in the right place.

24:18So everything is in trips dev, trips dev,

24:22trips dev, trips dev. Yes, that's correct.

24:25And now if I was to if I try and run this,

24:28this should be this should work.

24:31So if I try and run that, does that work?

24:34No, it doesn't work.

24:35So then just to double check that it's not

24:37me being an idiot with this trips dev

24:40statement here.

24:41So this is everything set now, hit run. And

24:44that doesn't work as well.

24:46So both of these just not working the way I

24:48expected them to. I wonder why.

24:52OK, so this is not working, which is I can

24:55't honestly understand what's not working

24:58here.

24:58And if you're watching this on Premiere,

25:00you're probably telling me, but I'll be sat

25:02in the comments,

25:03basically nodding my head, having figured

25:05this out.

25:06So let's just try and see what's going on

25:08here. So create or replace table trips.

25:11OK, let's go check this query. So what I

25:14want to do is I just want to quadruple

25:16check.

25:17Let's go to the history and let's just see

25:20if we can see what's going on here.

25:24Still loading up. And there we go. I can

25:26add a filter and I think we can filter to a

25:29specific query.

25:30Yeah, query ID. So let's just paste that

25:33query in there.

25:35Plus and that should basically just return

25:39this one query.

25:41And oh, actually, this query didn't run. So

25:44this maybe might be the wrong query.

25:46So let's just double check this. What did

25:48this do? Update trips dev does not exist.

25:54So that one didn't work. OK, so of course.

25:57OK, this is weird.

25:59OK, OK, so I figured it out. So this is

26:05this. Oh, God, this is this is a how do I

26:09explain this?

26:11This is probably the third time I've said I

26:12figured it out. But nonetheless, let me try

26:14and explain.

26:15So you see here it says trips dev two. OK,

26:18now what happened was in the previous

26:19exercise,

26:21I got something wrong and essentially I had

26:23to redo the whole exercise with the dev two

26:26.

26:26This is like a couple of weeks ago when I

26:29ran this. So when I did everything with dev

26:31two,

26:31of course, the first time I ran this, if we

26:33rewind and go back when I first ran this,

26:36I first ran this update trips dev two,

26:38which is which which makes sense.

26:40And of course, it didn't work. So this is

26:42actually the wrong query.

26:44The query that I'm telling it to use never

26:46successfully completed.

26:47And therefore it's sort of struggling with

26:50time travel because nothing this query didn

26:52't complete.

26:53I think that's what's going on. So if I go

26:55back and I look at the time and I say this

26:58is one ten thirty six.

27:00OK, so let's go back to the full history.

27:04Let's remove that filter.

27:08And I think what we can do is we can just

27:11go back in time. So one ten thirty six.

27:16So you see, this is the one that didn't run

27:18. OK, and the one that actually did run is

27:21this one.

27:22OK, so query ID this one. So if I actually

27:26highlight this and click on it, I was

27:28actually going to copy it.

27:29But you can see this is the one that

27:31actually ran successfully. So I actually

27:34got the wrong query.

27:36If I copy that, you can see the number of

27:38rows updated was sixty one million.

27:40So update trips dev start time station. So

27:44this is the one that actually made the

27:47mistake that actually changed the database.

27:49And because this was successful, this query

27:51should technically have something captured

27:54in terms of time travel.

27:56That's what I think is going to happen. So

27:58let's come back here and let's change this

28:02to the right query.

28:04OK, so now you can see this query has

28:06changed and I'm going to run this query to

28:08see what the table looked like before that

28:10mistake.

28:11So let's run that and we should this should

28:14work. Here we go. I think this is working.

28:17Yes. So here we go. This is working because

28:21I can see everything coming back.

28:23It's going to take time because it's a big

28:26table. I should have just limited it to the

28:29first 20 rows or something, shouldn't I?

28:32I wish I'd done that. It's too late now. It

28:33's it's running. So it is what it is.

28:36I could have just stolen this from here,

28:38for example, limit equals 20. I don't know

28:41bought because, you know, it's done most of

28:42the work.

28:43I think this one takes like 30 something

28:47seconds. So give it some time and let's see

28:51what happens here.

28:53It's only a real problem if it takes over a

28:55minute. So actually, I'm going to bought

28:58this and we're going to grab this limit

28:59statement and just bring back 20 because

29:03that's still going to be faster than what I

29:05was planning to do.

29:07So let's do this and paste that there and

29:10then run these two lines. Hit run. Here we

29:15go. Loading metrics. And there we go.

29:17So you can see that works. The time travel

29:21is working because the station name, start

29:23station is 435. These are all different.

29:26So we fixed it. Gosh. So moral of the story

29:30, if you ever break something in a tutorial,

29:34don't trust yourself to be able to return

29:37everything back to how it was.

29:40Make sure the content you've sort of taken

29:42a hard copy of the content and you start

29:44from a French because I sort of self-inf

29:46licted that pain on myself here because of

29:49an issue I made three weeks ago.

29:50Right. And I didn't sort of triple check

29:53the sequel that I was running as a result.

29:57So now that we've got this running, I feel

29:59fairly confident we can actually get

30:02something to work.

30:03So I'm actually going to cheat here a

30:05little bit. I'm going to set the query ID

30:07to this.

30:08I'm not going to use this query because the

30:10issue with this query is it's returned in

30:13the wrong one because I ran one update

30:15query that failed and one update query that

30:17was successful.

30:19And it picks the most the first one, which

30:21is the one that was unsuccessful.

30:23So all I'm going to do is I'm going to set

30:26the query ID to this value here.

30:28OK, and we're just going to highlight that.

30:31I think I think that should do might need

30:35to do that at the end and then just do this

30:39and hit run.

30:41OK, so that statement executed successfully

30:44, which now means my query ID is in the

30:47variables, so I can now hit select create

30:50or replace table trips dev and use the

30:52original sequel that was given to us.

30:54Hit run. And now now this should restore

30:58the table.

30:59So let's hit that and wait.

31:02This should take a bit of time. But, yeah,

31:05you can see that it is starting to work

31:07because it wouldn't sort of go this far if

31:09it wasn't working.

31:10So, you know, I think I've earned a sip of

31:12coffee while this runs.

31:13So I'm going to I'm going to have a small

31:16break and just pat myself on the back for

31:18figuring that out, even though it's self-

31:21inflicted.

31:21There we go. It's actually finished. No

31:22chance for coffee anyway.

31:25So, wow, gosh, that was that was good. That

31:27was good to figure out.

31:29This is the kind of stuff you sort of pick

31:30up as you learn. And, you know, it's just

31:33part of part of the nature.

31:36So the last thing we can now do is check

31:37that everything has been restored. So let's

31:40go ahead, select start station name as

31:42station and hit run and then hit run again.

31:46And then we should see that the station

31:48names have been restored. So that's the end

31:50of section seven.

31:51We can now go on to the next one. It's

31:53actually section eight in the guide. If you

31:55're following the guide, let me minimize

31:56this and go back to the guide.

31:58It's actually the end of section eight

31:59using time travel.

32:00The next thing is going to be talking about

32:03role based access controls, account usage

32:05and account admin.

32:06OK, so we'll we'll we'll get stuck into

32:09that now. Let's get stuck into role based

32:11access controls, account usage and account

32:14admin.

32:14In this section, we'll explore aspects of

32:16Snowflake's role based access controls.

32:19RBAC, it's not a nice acronym to sort of

32:22shorten and say it'll just be R.R.B.A.C.

32:26But now on such as creating a new role and

32:28granting it specific permissions, we will

32:31also cover the account admin, account

32:34administrator role.

32:35OK, to continue with the CityBike story,

32:37let's assume a junior DBA has joined CityB

32:40ike and we want to create a new role for

32:42them with less privileges than the system

32:44defined default role of sysadmin.

32:48Now, role based access control Snowflake

32:50offers very powerful and granular role

32:52based access control that dictates what

32:54objects and capabilities a user can access

32:56and what level of access they have.

32:58For more detail, check out the Snowflake

33:00documentation. If you if you smash that, it

33:03actually opens up a browser tab and you can

33:07actually get an overview of access control

33:10here.

33:10And it gives us a little guide as to what

33:12you can do. So here we go.

33:14We've got a little flow chart. There's

33:16nothing like a good role based flow chart

33:19that tells you what happens at where.

33:22So there's obviously there's always a flow

33:25basically because permissions, permissions

33:28typically have to sort of start somewhere.

33:29They have to basically check something

33:31first in order for them to work.

33:33And nearly all activities go through this

33:35flow essentially.

33:37And if you don't sort of meet the

33:38permission requirements, you don't sort of

33:40make your way through this flow, then

33:41essentially you basically get booted out of

33:43the system and you get denied access to

33:45whatever you're trying to access.

33:46So it's no surprise that there's obviously

33:49a hierarchy of what you can sort of not

33:52only secure, but also what you can do with

33:54those permissions as well.

33:55So you've got this little sort of chart

33:57that shows you what you can secure.

33:59So securable objects, accounts, users, role

34:02, databases, warehouses, objects, schemas,

34:05and then the things in the schemas, the

34:07table, view, stage, stored procedure, UDFs

34:10and other schema objects.

34:12And so I'm not going to sort of kind of go

34:15through this in depth.

34:17I think maybe this is worth another video

34:19once I get sort of a lot more comfortable

34:21with it.

34:21But if we go back to actual, if I just

34:25minimize this and I just go back to this

34:28view here, you can see that we're basically

34:31going to just work through the exercise and

34:32try and create this.

34:33So in the worksheet, switch the account

34:35admin role to create a new role account

34:37admin.

34:38Account admin encapsulates the sysadmin and

34:41security admin system defined roles.

34:44Okay, so that's interesting.

34:45It's a top level role in the system and

34:48should be granted only to a limited number

34:50of users in your account and the worksheet

34:53run this command.

34:54So essentially, we need to switch to this

34:56role because this role has like god power

34:58over the whole entire system.

35:00And that's why this says only a few people

35:02should have it.

35:03There's this principle in Plow technology

35:05that you should only have access to do

35:06whatever you need to do.

35:08And if you can even go as far as when you

35:10want to do that, then that's an even better

35:12way of doing it.

35:13So in AWS, it's not unfamiliar for you to

35:15be given access, maybe to do something very

35:18briefly through a role or through some sort

35:20of identity access management sort of

35:22definition.

35:23And then when you no longer need to do that

35:25, it's immediately removed through some sort

35:27of procedure.

35:28And that's just sort of good security cloud

35:30practice.

35:31Okay.

35:31And I think this is sort of coming through

35:33here inside of this.

35:34I wouldn't even be surprised if because

35:37Snowflake runs on AWS, it's using a little

35:39bit of the identity access management sort

35:42of, let's say philosophy to actually do

35:44this and actually get this to work.

35:46So let's go ahead and use the role account

35:49admin.

35:50So you can do this a couple of ways.

35:51It's showing you how, but I'll sort of go

35:53over that here.

35:54The first way is obviously if you go up

35:56here to this little window, you can

35:58obviously just go in here and change this

36:00to whatever role you have.

36:02And just so you clear this, this is the

36:04sort of dropdown of roles.

36:06Now I have my own roles in there.

36:08So I have five sysadmin, which is a sys

36:10admin account for fivetran for it to be able

36:13to do specific things.

36:15And it's actually not a sysadmin.

36:17I've just called it sysadmin because it's

36:19like the fivetran's own account to do its

36:21own things.

36:22But essentially you've got these other

36:24roles here that are sort of default.

36:26So sysadmin, accountadmin, public are

36:28default.

36:28I just sort of go out of this. There's a

36:32couple more, securityadmin and useradmin as

36:34well.

36:34So these all sort of restrict what someone

36:37can do.

36:37I'm pretty telling what they all do.

36:39Useradmin, I assume, will be able to go in,

36:41add users and do a bunch of things.

36:43Securityadmin will be able to go in and

36:45maybe change things like securities and

36:47permissions.

36:48And then you'll have sysadmin who will go

36:50in and be able to do a bunch of other

36:51things as well.

36:52So this is sort of one way of changing it.

36:55Now, there's a slight thing that you need

36:57to be sort of wary of.

36:59There's two places you can change this.

37:00So over here, what you're doing is you're

37:02changing it within this sort of query

37:04window context.

37:05So everything we're doing essentially over

37:07here is controlled through this set of

37:09permissions.

37:09But there is actually another one here at

37:11the top.

37:12If I just highlight in green, this is the

37:14one that actually changes sort of your

37:16perspective of the whole entire system and

37:18management.

37:18So if I was to, for example, go to the

37:21history tab, I'm looking at this as a sys

37:24admin

37:24because that's what's running here at the

37:26top left hand side.

37:28Whereas when I'm in the worksheet and I'm

37:30running queries, I'm actually over here

37:33specifying the permissions that I'm going

37:35to be using whilst I run queries.

37:37So I might be logged in as a sysadmin, but

37:39then decide to run queries using the

37:43account admin roles, which is sort of a

37:45slightly weird way of doing it, but

37:47definitely something you can do.

37:49OK, so the other places I see here at the

37:51top, I can switch the roles.

37:53And here you can see the default sysadmin.

37:56I can, of course, go to account admin as

37:57well, and then you're pretty much good to

37:59go.

38:00So let's just go ahead and check what we

38:02need to do for the next exercise.

38:03So what I will do is I'll switch this top

38:05one here.

38:06And we're already account admin in the

38:08query.

38:09And if I just double check, the sysadmin is

38:12here.

38:12So in order for a role to function, we need

38:15to at least one user assigned to it.

38:19So let's create a new role called junior D

38:21BA and then make sure that's assigned.

38:23OK, and then we're going to switch to that

38:25role.

38:25OK, OK, that makes sense.

38:29And assign your username to it, your

38:31username appears at the top right of the UI

38:33in the screenshot below, and it is a user

38:37123.

38:38OK, so let's go ahead and do that.

38:40And you can see if I just go here, use a

38:43role account admin.

38:44So we'll hit run on that.

38:46And you just want to validate that that's

38:48actually kicked in so you can see that that

38:50has because the account admin role is right

38:53here.

38:53So we are now in account admin.

38:55And then what we're going to do is create

38:57this role junior DBA.

38:59Now we're going to just do this through the

39:01query interface.

39:02And you can see here the way we do this is

39:03we create the role.

39:04Surprise, surprise.

39:05Then we grant that role specific permission

39:08.

39:08So grant role junior DBA to user Jeremy P.

39:13Your username goes here.

39:14OK, so my username is a really interesting

39:17question.

39:17So what I should do is I should say Tableau

39:21Tim actually click cancel.

39:24I need to just delete this and go Tableau

39:30Tim TMMD Tableau Tim TMMD.

39:34OK, I think that's right.

39:36I'm just making sure it's exactly the same

39:38as up there.

39:38So then let's run this query and see if

39:40that's that's the right thing to do.

39:43So hit run.

39:46OK, that statement sort of completed

39:48successfully, so I'm pretty sure my user

39:50was correct there.

39:51So now what I can do is I can go in here

39:55and use role junior DBA.

39:57If I hit run there, we can then review this

39:59step.

39:59So here we go.

40:01So essentially what we did is we created a

40:02role.

40:03Then we attached the role to my user so

40:05that my user can use that role.

40:07And now I can use that role junior DBA.

40:10Now, for the record, if I go back in, I

40:12change this.

40:13And just because I was attached to that

40:14role doesn't mean I don't have access to my

40:16other ones.

40:17I still can switch to other roles.

40:19I'm just choosing to go and sort of do this

40:21as if I was a junior DBA.

40:23So I haven't sort of downgraded myself.

40:26I haven't done that.

40:26I mean, I could I nuke myself in doing that

40:29.

40:29I'm sure there's probably some failsafes to

40:31stop the last person in Snowflake from

40:33doing that,

40:33because otherwise you lose access to

40:34everything.

40:35Right.

40:36So if I go down here, you'll see that, yeah

40:39, I still don't have access to all my other

40:41roles.

40:42But now I'm just working as a junior DBA,

40:43essentially.

40:44OK, so now that we've done that, we can see

40:47that that's working.

40:48I can even go up here, I believe, and

40:50switch the role.

40:51I think I need to update this window.

40:53So let's let's refresh that, because the

40:56query window is slightly separate from the

41:00other window outside of it.

41:01So until I refresh, I believe.

41:03Yeah, you see.

41:04So you can see here now the junior DBA is

41:06actually in the list and I'm actually here

41:08still an account admin.

41:09If I go down to where we need to be, where

41:13is this module eight here?

41:14So this is what we just ran and we use role

41:17junior DBA.

41:18We now actually need to be at this account

41:21admin role.

41:23So when we refresh, it came back to account

41:25admin.

41:26And that's what I'm going to do.

41:27I'm just going to double check that that's

41:29what we need to use.

41:29Just hit run again, make sure we're

41:31absolutely aligned with this exercise.

41:34And now what we can do is we can grant

41:35usage on database city bike to the junior

41:37role.

41:38So what we're not doing is giving

41:40individual people access to things.

41:42We're giving roles to these people.

41:45So this is, again, very similar to AWS,

41:47where you don't give people access to

41:49something.

41:49You put people in groups and then you

41:51assign permissions using those groups.

41:53That's a much easier way of making sure

41:55that you have the right set of permissions

41:56to a group of users.

41:58So let's go ahead and we can run these one

42:01by one, but I'm actually going to run them

42:03together like that.

42:03So let's just do this and hit run because

42:06we're account admin.

42:07This should work. You can see here

42:08statements executed successfully.

42:11And now if we go back to use this role and

42:14hit run.

42:15OK, so now we're going back to a junior DBA

42:17role.

42:17So if you want to double check that, you

42:19can see that just up here.

42:21I use the wrong annotation tool. My bad.

42:23Let's get this arrow here.

42:25And yeah, you can see I'm now a junior DBA.

42:29So now what we can do is we can go ahead

42:32and basically run the following items.

42:35So I think what's actually going on here,

42:38if I just go back in here, what we're doing

42:40is, yeah, essentially, if I refresh this

42:42left hand side.

42:43So now I'm a junior DBA. I've been given

42:47access to weather, city bike and weather.

42:52So if I go to city bike, you can see that's

42:55there and weather is now there.

42:57OK. And before that, I didn't have access

43:00to that.

43:01So I sort of didn't pay attention there.

43:04So I kind of missed the ability to see that

43:06that was actually there.

43:07So now the city bike now appear, try

43:09clicking refresh icon if they do not.

43:12OK, so essentially, I think I missed that

43:14step that I didn't sort of I didn't clock

43:17that we had to basically go back and check

43:19because I was looking at this in this

43:20interface.

43:20But essentially what we did is we created

43:23the role as a junior DBA.

43:26We then assigned ourselves to that role so

43:28that we could assume that role essentially.

43:31Then we basically went back to our account

43:34admin status and we gave access to city

43:36bike and weather to the junior DBA.

43:39So the junior DBA could actually see these

43:41databases.

43:42And having done that, we were then able to

43:45sort of go back to our junior DBA role and

43:48actually see the data there.

43:50So what you would have not seen, I didn't

43:52show you this, but what you would not have

43:54been able to see is weather and city bike.

43:57They would not have been there. You would

43:58have seen maybe the others, but not these

44:00two.

44:01Then when we gave access, these two would

44:02have appeared and then you would have been

44:04able to sort of work with them.

44:06OK, so let's go down here.

44:09We can go back to use the account admin.

44:11So I'm going to actually do that now.

44:13I'm going to run these two again, make sure

44:15we're going back to being the right set of

44:17permissions.

44:18So now we're account admins.

44:21And if I just go back and just go down, it

44:26just goes through a couple of other things.

44:28So there's not much sort of there in terms

44:30of the analytical perspectives.

44:32Essentially, it goes through the account

44:34admin, which I think I already did earlier

44:35on in the video.

44:36So in the first, I think in the second

44:38series, I actually went through to the

44:40account admin.

44:41So if I go here, switch role to the account

44:44admin at the very top, you'll see that we

44:47essentially get access to billing and

44:51history and account.

44:52Here you go. So you can go to the account

44:54and then here you can actually see how I'm

44:56using Snowflake.

44:57So you can see I have six gig of data, five

44:59warehouses.

45:00I've used about 20 credits and a credit is

45:03worth about two euros, 50 or something like

45:05that.

45:06So I've used about 40 pounds worth of

45:09credits in this particular month.

45:12Makes a lot of sense because I have been

45:14loading a lot of data like today alone.

45:16Today is the 22nd. So today alone, I've

45:18actually used three credits and that's just

45:20because I'm going through this video.

45:22So if you're wondering, hey, how much does

45:23it cost to go through all of this stuff?

45:25Well, it's 355.

45:26And don't forget, I actually ran the whole

45:29entire series of queries up until the point

45:31where this video starts,

45:33because I was actually setting everything

45:35up. I didn't have this sort of persisted as

45:37it were.

45:38Between the last video and this video, I've

45:40actually had to do a bit of tweaking on

45:42this whole environment.

45:43So I didn't actually have these available.

45:45So I sort of wiped it and then reset

45:47everything back for this particular

45:49exercise, because, of course,

45:50last time I made a mistake and I didn't

45:51want that to carry through.

45:53But I couldn't avoid that plight,

45:54unfortunately.

45:56So it's cost me a bit of additional like it

45:58's like three or four pounds, which is just

46:00fine for this video.

46:01So it's totally, totally OK.

46:03But you can see on other days where I've

46:05not used it that much, you can kind of see

46:07how many credits are actually being used.

46:09Let's look in here. You can see this is

46:11again me running things.

46:12So I have a system called Fivetran, which

46:15is another tool.

46:16I'll maybe do another video on it in the

46:17future.

46:18But essentially, this is using a very small

46:20amount of credits, loading up data into

46:24Snowflake.

46:25It's actually like trivial. It would cost

46:27you a lot more to sort of do what Fivetran

46:30is doing itself.

46:31Just if you hide someone. So it's a really

46:33good tool. I'll do a video on it in the

46:34future.

46:35But essentially, you can see here

46:36everything that's going on.

46:37So billing, users, roles, policy sessions,

46:40resource monitors, reader accounts.

46:42I won't go to billing because that has some

46:44of my own private card details in there.

46:46If I go to users, you can see all the

46:48different users, the ones that have expired

46:50, the ones that haven't and how they're all

46:53set up.

46:53You can see here that I have the Fivetran

46:55username and you can see they're sort of

46:57logging.

46:58The display names are fine. It's the

46:59passwords are probably a bigger issue.

47:01Roles, we just created the junior DBA role.

47:03So you can see that it's just here who is

47:05created by when it was created.

47:07It's kind of good to know some of this

47:09other stuff was bulk copied in by Snowflake

47:11for this particular demo.

47:13Policies, again, I think these is to do

47:15with permissions.

47:16So this is, sorry, this is network policies

47:18, actually. I should have seen that coming.

47:20So this is to do with how you can restrict

47:22how people access your database.

47:24So let's say you want to only allow people

47:26from a specific address to sort of access

47:28your database.

47:29This is something I should probably do. I

47:31should I have a static IP, so I should

47:33maybe make this so that you can only access

47:35the database from my static IP because that

47:38makes it even more secure.

47:39If someone tries to access something from

47:41outside of that, then essentially it would

47:42just behave like it didn't exist.

47:43So just block the network request. It doesn

47:45't even hit the Snowflake database to say,

47:48hey, this is what's going on.

47:49So it's a very AWS feature that sessions.

47:52These are basically people doing stuff.

47:54So this is going to be me. And you can see

47:56how I logged in and authenticated resource

47:58monitors.

47:59Now, if you want to track how much

48:00resources are being used, you can create a

48:02resource monitor here and you can create

48:04limits.

48:04And so essentially, once you hit a certain

48:06limit, you stop doing certain things or you

48:08sort of stop people doing certain things.

48:10So this is good. Let's say a business has a

48:13certain budget and they want to make sure

48:14that every single day they don't go over a

48:16certain limit.

48:17This might just sort of be a good thing to

48:19roll in because then it makes whoever's

48:21working on the database be really sort of

48:23consistent.

48:24And they can really sort of spread out the

48:25cost of how they're using this.

48:27Maybe let's say that you have an improved

48:29budget one particular quarter, but then a

48:32smaller budget in another.

48:34You could sort of switch these monitors on

48:36in different sort of periods of time to

48:38sort of grow or contract how you're using

48:41things so that people kind of stay within

48:43their boundaries.

48:44And I believe reader accounts are just, let

48:46's say, actually tells us here.

48:47So reader accounts enable providers to

48:49share data with consumers who are not

48:51already Snowflake customers without

48:53requiring the consumer to become Snowflake

48:55customers.

48:55So essentially you can create an account

48:57and then someone can actually basically

48:59connect to your Snowflake instance and

49:01start using them without having to create a

49:03Snowflake account, which is kind of cool.

49:05So this might be something I do for a Table

49:07au challenge in the future.

49:08So you can obviously create an account name

49:10and get people going on that.

49:12So that's that's really cool, right?

49:14It's a really sort of nice, nice way of

49:16using it and making sure that everything

49:19works.

49:19So let's go back in here and see if they

49:22covered anything else.

49:23So we looked at the usage.

49:24You can see what's going on.

49:26That's pretty much it.

49:27Let's go to the next step, which is secure

49:29data sharing and the data marketplace.

49:31So we're kind of sort of into the towards

49:33the end of the series now.

49:35So Snowflake enables database Snowflake

49:38enables data access between accounts

49:40through share shares are created by data

49:43providers and imported by data consumers,

49:46either through their own Snowflake account

49:48or provision Snowflake reader accounts.

49:50So we just saw the Snowflake reader account

49:52there.

49:52This consumer could be an external entity

49:54or different internal business unit that is

49:57required to have its own unique Snowflake

49:59instance.

50:00So the use case here is you might have, let

50:02's say, a third party business that you do

50:04business with who wants access to data.

50:06Or you could have an organization where

50:08every single part of the business has its

50:10own Snowflake account for specific reasons.

50:13Let's say, hey, China finance don't want

50:15their data to be shareable.

50:16So they might create different sort of Snow

50:18flake accounts rather than sort of having

50:20that risk that they're on the same account.

50:22Therefore, someone gets access to something

50:23they shouldn't have.

50:24Then a share is a nice, secure way of sort

50:27of making sure they can share data between

50:29each other without ever having to sort of

50:31mix the two things up.

50:33With secure data sharing, there is only one

50:35copy of the data that lives in the data

50:36providers account.

50:37So if I'm sharing something with you, the

50:39only place where the data is kept is on my

50:41account.

50:42You're just accessing it on my account.

50:44So it's sort of an interesting dilemma

50:46because in a lot of sort of B2B situations

50:49today, what's often required is some sort

50:52of sharing of data through FTP or Amazon S3

50:55.

50:55They dump the data here, we'll pick it up,

50:56we'll process it and we'll do things.

50:58But as soon as you do that, you lose sort

51:00of custody of your data because it

51:01essentially becomes someone else's data as

51:03well.

51:04They become sort of an owner of that as

51:05well.

51:06So it's nice here that Snowflake keeps it

51:08on your own account.

51:09Shared data is always live, real time and

51:11immediately available to consumers.

51:13So if I update the data that I share,

51:15everyone else gets it straight away.

51:17Providers can establish revocable fine gr

51:20ained access to share so you can, you know,

51:23use permissions really well to control how

51:24they are sharing everything.

51:27Data sharing is simple and safe, especially

51:29compared to older data sharing methods,

51:31which were often manual and insecure,

51:33involving the transfer of large CSV files

51:35across the Internet.

51:36I mean, this still happens today. Snowflake

51:37is talking like it's the thing of the past.

51:39It's still the prominent way of how data is

51:41shared in a lot of organizations,

51:43unfortunately.

51:44As a shared data across regions or cloud

51:46providers, you must set up replication.

51:48This is outside of the scope of the lab,

51:50but more information can be found in the

51:51Snowflake article.

51:52This is interesting. So let's say I want to

51:54do a Snowflake share with someone and their

51:56instance is not in Ireland.

51:58Let's say theirs is in Europe or America.

52:01What I have to do is I have to replicate my

52:04data from Snowflake set up in Ireland to a

52:07Snowflake set up in the US where I'm also

52:10having sort of a Snowflake account.

52:13So if I go back in here, you'll see when I

52:15do this dropdown, I get a little bit of

52:17information just here that says that I'm

52:19running an EU Ireland.

52:20So this essentially means I can use Snow

52:22flake shares with other people running in

52:25the same region in Ireland.

52:27Now EU Ireland covers most of Europe and

52:29there's another region in Ireland which I

52:31believe what is the other region in Europe?

52:34I think it's I want to say it's Copenhagen.

52:37I have no clue to be honest.

52:38I have to sort of dig it out. But

52:40essentially, if your data is in the same

52:42region, then it's really easy.

52:44If you don't have the data in the same

52:45region, then what you're going to need to

52:47do is replicate it to a different region so

52:49that then you can share it from that region

52:50to other people in that region.

52:52Okay, so see existing share so you can see

52:55click on the blue Snowflake logo at the

52:58very top of the UI on the left side of the

53:00page and database object browser.

53:02Notice the database Snowflake sample data,

53:04a small arrow on the database icon

53:06indicates that this is a share.

53:08This is kind of fun. I did actually wonder

53:09what was going on here. So let's go back in

53:12here to sysadmin.

53:13I sort of noticed this, but I sort of didn

53:15't know what to Google.

53:16And I thought I'll probably come across

53:17this answer at some point.

53:18I had sort of other things I thought were

53:20more important to learn.

53:21But if I go to the databases tab here and

53:25actually, you know, if I just click on the

53:27blue Snowflake logo, this is sort of brings

53:29us back here.

53:29And this is a very subtle thing to notice.

53:32But you can just see here that there is

53:34this little arrow that says, you know, Snow

53:36flake sample data.

53:37This is actually a share in itself. So it's

53:39not very good here.

53:41They've sort of created a share so you can

53:42't mess it up yourself.

53:43And you can see here that this is actually

53:46a share at the top right of the UI.

53:48Verify that you are in the account admin

53:49role. So let's go ahead and do that.

53:51I just switched to sysadmin, but let's

53:53switch back to account admin.

53:56And once you are there, what does it want

54:00us to do?

54:01OK, looking at the inbound shares, there

54:05are two share provides by Snowflake.

54:08One contains your account usage and the

54:10other has sample data. This is data sharing

54:12in action.

54:13Your Snowflake account is a consumer of

54:15data shared by Snowflake. Right.

54:17So it's a bit sort of a strange

54:18relationship.

54:19But anyway, if we go back to shares, other

54:24way account admin, you can see that we have

54:26account usage and sample data.

54:27So this is this is a very smart.

54:28So Snowflake obviously collects a lot of

54:30data in the background about what queries

54:32you're running, what you're doing.

54:34And interestingly, this data belongs to

54:37them.

54:37And then they share it back with you to

54:39show you how your account is doing.

54:42So it's a very interesting sort of

54:43structure. It's an interesting one.

54:45It's also sort of a weird relationship

54:47because it sort of assumes that Snowflake

54:50owns the metadata about everything you do

54:52on your database.

54:53And then they share it back with you, which

54:55is sort of a strange it's a strange thing

54:58to say out loud, isn't it?

54:59You know, you're paying them for a service,

55:01but what they're doing is sharing your

55:03metadata back with you.

55:04I'd be a lot more comfortable if what this

55:06was sort of doing was to say, listen,

55:08we can't provide you any account analytics

55:11unless you share data with us so we can

55:13accurately do this.

55:14That said, they do price based on usage. So

55:16they do sort of have to have this in a way.

55:18But it's just that sort of it's just the

55:21philosophy of that.

55:22Just how it's termed is a little bit weird.

55:24The sample data you see here is also from

55:25Snowflake.

55:26So it's shared with us. It was created back

55:28in twenty eighteen, which is kind of

55:29interesting.

55:30This has been shared with lots of people

55:32and it's probably just the standard thing

55:33that's available across a lot of Snowflake

55:35instances.

55:36So any time we use that data, I believe it

55:39's not actually charging us because the

55:41usage is occurring on Snowflake's end, not

55:43on our end.

55:44OK, so this data is your Snowflake account

55:50is a consumer of data shared by Snowflake.

55:52We can go ahead and create an outbound

55:54share. So let's go back into City Bike

55:56Store and assume we're the account

55:57administrator for the Snowflake at City

56:00Bike.

56:01We have trusted partner who wants to

56:03analyze the data in our trips on a near

56:05real time basis.

56:06This partner also has their own Snowflake

56:08account in our region.

56:09So let's use Snowflake to allow them to

56:11access this information. Navigate to the

56:14shares tab further down on the page.

56:16Click on outbound button. OK, so basically

56:18what we want to do is go over here, go to

56:20shares and click on the outbound thing.

56:23And then I assume here we're going to hit

56:25create. So let's go ahead and hit create.

56:28And here we are.

56:29So let's just make sure we get the things

56:30right. So secure share name is Tripshare.

56:34So let's go ahead and grab that and paste

56:37that in. Just keep it nice and simple.

56:40Database is City Bike. I think this might

56:43have a dropdown. Yeah.

56:44So I can just go ahead and look at that.

56:47Tables and views, select tables and secure

56:49views, zero tables.

56:51So if I click on that, it'll obviously give

56:54me some sort of perspective.

56:57I could also sort of choose Trips Dev and

57:00Trips. So you could do that. What do they

57:02recommend doing?

57:03Just Trips. We don't need access to the Dev

57:05's instance. But what you could do, this is

57:07a very smart way.

57:08What you could do is you could create a

57:10clone of your original database and then

57:12give them access to that.

57:13And what that allows you to do is

57:15essentially sort of separate usage of your

57:18databases.

57:18So you can sort of very easily see that,

57:20hey, this data is being used over here and

57:22this data is being used over there.

57:24And if you maybe sort of continuously

57:27updating data into Trips, but you don't

57:29want the same thing to be happening with

57:31Dev,

57:31then it's a nice way of sort of doing that,

57:33because at the point in time when you clone

57:34things, things are sort of as they are.

57:36But from that point on, there's this sort

57:38of static.

57:39So you can sort of create some sort of

57:41snapshot just by creating a clone, giving

57:43people access.

57:43But then, you know, they're not going to

57:45continue to receive updates so that they

57:47can then sort of monetize or do something.

57:49Let's say it's kind of nice could apply.

57:51That's everything.

57:52And you get this sort of interface to show

57:53you what you've added. You can add more

57:55things to the share if that makes sense.

57:57So Citibank public trips. Yep. Now that's

58:00all done. Click apply, then create.

58:02Note that the window indicates the secure

58:04share was successfully created.

58:06So let's hit create and let's see that

58:08going through. There you go.

58:09There's the share that's been done nicely

58:12and everything is there. Everything is good

58:15.

58:15So now we get asked, OK, like what

58:17warehouse would you like to use when that

58:19person is using this?

58:20So if we just go down realistically, the

58:23Citibank account administrator would like

58:25would click on next.

58:26And consumers button to input their

58:28partners, no fake account name and type.

58:30We'll stop here for the purpose of this lab

58:32. Click on done button in the in the bottom

58:34of the box below.

58:35OK, so what they're basically saying at

58:38this point, you could you might specify a

58:41few a bit a bit more information.

58:43So you next you'd add consumers and you'd

58:46sort of start to give people access.

58:48But because we're doing a lab, it's not

58:50sort of sort of got that option.

58:52So we'd need to create some reader accounts

58:55, of course, to then point into this place.

58:57So first we create the share, then we

58:59create the accounts.

59:00The accounts will go off to someone else

59:01who's maybe not a Snowflake user.

59:03And then they're able to come in and start

59:05using this. But we're just going to hit

59:07done and sort of end that there.

59:08And so you can actually see here that says

59:10trip share and consumer city bike.

59:12There are no consumers. So I've shared it,

59:14but I'm not sharing it with anyone because

59:16I haven't actually added them in.

59:17OK, so I said Snowflake provides several

59:19ways to securely share data without

59:21compromising confidentiality.

59:24You can share not only tables and views,

59:26but also secure views, secure UDFs, user

59:29defined functions and secure joins.

59:31For more details on how to use this, check

59:33out the documentation.

59:34So again, another video. And there's my dog

59:37yawning again.

59:38Every video now he just yawns. And last

59:40time he was snoring today, it's yawning.

59:42Is it boring, buddy? Is it boring?

59:46OK, navigate to the data marketplace tab.

59:50So now we're going to look at the data

59:51marketplace.

59:52So let's go have a look at that. So here we

59:54are, data marketplace.

59:55And it's going to have a big fat explore

59:58data marketplace button.

60:01Select the explore. It's your first time

60:03using the data marketplace.

60:05The following login screen will appear.

60:08Enter your credentials to access Snowflake

60:10data marketplace.

60:11And I just want to get an idea of what kind

60:13of things are in there.

60:15Let's go have a look. I think I have

60:16actually been in here, so I don't think I

60:18need to create a username.

60:19In fact, opened up a new tab. So let's

60:22bring that into view.

60:24And let's just see what happens here. So

60:27you can see it's got the new interface

60:30rather than the old one.

60:31So whereas here in the guide, it kind of

60:33does have the more modern looking interface

60:36, but it you know, the for sure.

60:40I'm absolutely certain that you can see

60:42here that it's got some more of the sort of

60:44more recent things that we've been using.

60:47So these are going to be, I guess,

60:49providers that do various things on Snow

60:51flake data.

60:52So US census income data set reduced bias.

60:55So, yeah, you can sort of dynamically load

60:57census data into your Snowflake instance.

61:00So I guess these these these are going to

61:02be places where you can go and either find

61:05data sets.

61:06I guess that's what it is or companies that

61:09can do things to your data set for you.

61:13Interesting. There's lots of different and

61:17unified identity customer identity

61:18management.

61:19Yeah, it's an interesting thing. It's

61:21always difficult to know how these stores

61:23sort of define themselves.

61:25You never really want to put yourself in a

61:27particular category because then it means

61:29you exclude a bunch of other categories.

61:32But it's nice to be able to see, you know,

61:34that essentially what providers seem to be

61:36providing is data sets that you can

61:38integrate into your own and that are sort

61:41of aggregated or cleansed.

61:42And, you know, someone's put a lot of time

61:45into this particular type of data and then

61:47they've basically sort of made it

61:49consistent and made it sort of easy for you

61:51to use.

61:52Let's go back into the lab and just see

61:53what at what point are they trying to make

61:55here?

61:56The search bar on the top allows you to

61:57query for listing or data provider.

61:59The left hand side menu shows the

62:01categories for data available in the data

62:03marketplace.

62:03Select the health category. OK, so, yeah,

62:06you can you can go in and connect to some

62:09health data and different data types.

62:11Today was you star scheme is covered 90 APD

62:15, APD on epidemiological data.

62:18Make sure you use your India count admin

62:22ROM. OK, so this is interesting.

62:24So here we go. We're in the with this world

62:28. What role are we in?

62:31How do I know what role I'm in? This is a

62:32new interface as well. So I'm just

62:34completely lost.

62:35So let's go. Let's just type in here health

62:40and star scheme is epidemiological data.

62:44I can't say that word. It's a bit of a

62:46tongue twister. And this is what's going on

62:49.

62:50Here's a role. Make sure you're an account

62:52admin. OK.

62:53And what's it saying about this? So here

62:57you can select the data and click on the

63:00get data button to access information

63:01within your snowflake account.

63:03And then you go through some permissions

63:06and so on and so forth.

63:08So interesting. Get total case count by

63:13countries.

63:14This is the covered 19 epidemiological data

63:17is. Yeah, that is the one that they were

63:19talking about.

63:20OK, so what's going on here? So if I just

63:24select get data and see what happens.

63:26Database name, which roles in addition to

63:30can and can access the status as admin.

63:34I'm just sort of guessing this as I go

63:35through. So it's basically made it

63:37available to all of them.

63:38So let's go ahead and do the same thing. So

63:41let's just then five five five.

63:44This doesn't need it. Let's just do that.

63:47And I accept snowflake terms of commissions

63:51.

63:51Create a space. Let's go view database.

63:56There it is.

63:56Star schema, AWS, E West Covid 19 dates.

64:02That's cool.

64:02That's cool. So basically I'm getting lost

64:04here between all the different instances of

64:07stuff like that I have because it's using a

64:09new interface.

64:10So eventually once it's created the

64:12database, you can now go view the database.

64:14We've done that.

64:15You can break that down into different sort

64:18of tables. So let's go ahead and just

64:20browse.

64:21I'm going to do this in the new interface

64:22so you can see what's going on. So we

64:24should get to the public here.

64:26And if I go to what's going on, this is

64:30schema details on the tables.

64:34Let's go to this and see what they have. So

64:35they do have quite a lot of data from

64:38various places.

64:39Right. And last updated between a year ago

64:42and two months.

64:43So these are public shares. I think they're

64:45sort of adding data to this over time.

64:47You can see how many million rows some of

64:49these have. So you have the Apple mobility

64:52data.

64:52Do you have the Google global mobility

64:54report? Yes, we do. Five point four million

64:56rows of data.

64:57That's really, really cool. They've made

64:59that available. This did stop roughly a

65:01year ago because I think what this data was

65:03used to do was to basically make sure that

65:06people had stopped using transport.

65:08So it's nice that you can get this data and

65:10it's sort of readily available, which is

65:13cool. I really like that.

65:14Actually, it's nice. It's a nice way of

65:16working as well, because essentially the

65:18data still belongs to star schema.

65:19They've done the work and I'm basically

65:22just using it through the marketplace there

65:24.

65:25And it's sort of made available to me,

65:26which is which is really nice.

65:28Now, I think this is a free data set. So

65:30this is why I should actually correct one

65:33thing.

65:34This is not a share. This is from the data

65:36marketplace.

65:37So once I get the data, it's actually in my

65:38own snowflake instance.

65:40So it takes up storage and it takes up my

65:42own credits when I use it.

65:44And I was incorrect of me to say otherwise

65:46just before that.

65:47So, yeah, this is this pretty much nicely

65:50set up. I'm pretty sure that we're pretty

65:52much at the end of this.

65:53So if I just go down, we can kind of see a

65:55few more sort of contextual information to

65:57do with the marketplace.

65:59But there we have it. I think we're pretty

66:01much at the end of this now.

66:02We're going to reset our account to how it

66:05should be before. So what I'm going to do,

66:09because we've brought data in for the

66:10marketplace, I'm going to keep that in

66:12there because it's kind of fun to play with

66:14.

66:14But what we will do is we'll go ahead and

66:17run this. I'll close this browser window

66:19because that's sort of confusing me.

66:21And we'll go back to our worksheet. I think

66:23I did actually add the commands here.

66:27So here we go. Yes. We've got this

66:30particular information.

66:32So we can actually make sure that we're

66:34doing the right thing.

66:36So use role account admin, use warehouse

66:38compute, use database weather, schema

66:40public.

66:41So let's run this. This will just help us

66:44reset everything back to where it should be

66:47because we're a junior DBA at the moment.

66:48Now we're back to being at weather. And so

66:51now we can just highlight this.

66:52And basically this is going to delete

66:54everything. So if you look here on the left

66:57hand side, this is going to reset

66:58everything.

66:59So all that data that I've uploaded from S3

67:01that isn't mine is all going to get deleted

67:03.

67:04The only things we should have left are my

67:05own databases, obviously, the TN media one,

67:08TNMD5 Tran, maybe the utility database, the

67:12shares.

67:13So you see, this is interesting. So you

67:16know how I said the data marketplace data

67:19wasn't a share?

67:21Actually, it turns out I'm incorrect. This

67:23is actually a share.

67:23So if you look at this here, it has a

67:25little icon, which means it is actually a

67:27share.

67:28So I have to dig into that. I don't know. I

67:30don't know enough about the marketplace,

67:32right, to really understand the dynamics of

67:34that.

67:34So like, you know, when you when you get

67:36data from the marketplace, whose credits

67:38are being used when you run queries on that

67:40sort of something I'm not clear about.

67:42You just buying data, but then you you're

67:44using someone else's credits. That doesn't

67:46seem to make sense.

67:47So maybe there's sort of a model around

67:49that that would then make sense.

67:51But let's go ahead and drop these tables.

67:54So let's go ahead and run these.

67:56And you should see this will start to clear

67:58everything.

67:59And the last thing you'll see is a junior D

68:01BA completed successfully, but it did

68:04actually run all the previous ones.

68:05I know that because if I hit refresh, you

68:07'll see that city bike and weather have

68:09disappeared.

68:10So just wiped everything that we did during

68:12this exercise. And yeah, we're pretty much

68:15back to normal now.

68:16The only thing I've got left is Snowflake,

68:18the Snowflake sample data and the Star

68:20schema, AWS, EU West COVID-19 data by Star

68:24schema.

68:24So that's really, really cool. It's really

68:26nice to sort of see that. And yeah, that's

68:28pretty much the end of this little series I

68:30've been doing.

68:31So if we go back here and actually we look

68:35at the last steps, they've got a conclusion

68:39and steps of what to do next.

68:40I've really enjoyed this. I've really

68:42enjoyed sort of going through this. I will

68:44definitely be doing this every week.

68:46Next week, I'll probably be taking

68:48something different on relation to Snow

68:49flake.

68:50But I think it's been a really nice way to

68:51sort of get familiar with the database.

68:53Like if you'd use Tableau and this is all

68:55you did, this would be enough to get you

68:57comfortable with Snowflake, like using it

68:59day to day.

69:00Because everything else you can kind of

69:01research after the fact.

69:02So you don't need to become like a database

69:05expert to understand what's going on.

69:07But if all you did was spend an hour and a

69:09half just going through this guide, you'd

69:11know what Snowflake is.

69:12You'd know the advantages. You'd be able to

69:14talk about it and you'd be able to at least

69:16engage someone who is a DBA on what should

69:19be done here and how you could use it in

69:20your day to day.

69:21So I think it's really, really cool. I

69:22think it's a really nice database.

69:24Something I have to learn more of, more

69:26about anyway. So I'll be pushing on with my

69:28learning.

69:29So, yeah, maybe we'll take on another Snow

69:31flake guide or maybe we'll sort of go off

69:33piece and just start making our own content

69:34around that.

69:35But yeah, I've really enjoyed it. If you've

69:37enjoyed this video, you know what to do.

69:39Hit the like button, subscribe, do whatever

69:41, share it with everyone.

69:42Really interested to know your thoughts

69:44about this. Should this be something I do

69:46every Sunday?

69:47Should it be something I continue to do?

69:49Are you learning stuff from it? Is it

69:50helpful to you?

69:51I'd be really keen to know that as well. So

69:53, yeah. Thank you for watching and I'll

69:55catch you in the next video.

69:56Bye.

69:58(upbeat music)