Learn Snowflake In 45 Mins | Snowflake Tutorial | What Is Snowflake | Snowflake Explained

Key Takeaways

Welcome to this course on Snowflake, the modern data platform built for the cloud. In today's datadriven world, organization needs solutions that are fast, scalable, and easy to manage. That's exactly where Snowflake comes in. It's not just a data warehouse. It's a powerful platform that combines data storage, processing, and analytics in a single unified system. Whether you're a data analyst, engineer, developer, or a business user, this course is designed to give you a solid foundation on using Snowflake effectively. We'll begin with the basics understanding what Snowflake is and how it architecture works and then dive into hands-on topics like setting up databases, running queries, working with virtual warehouse, and handling structured and semistructured data. By the end of this course, you'll not only be comfortable using Snowflake, but also confident in applying it to solve real world data challenges. Let's get started. So, what is Snowflake? Snowflake is a cloudnative data platform delivered as software as service or SAS. It's designed to be highly scalable, flexible, and cost effective allowing organization to consolidate, integrate, and analyze all their data in one place. Now we are looking at architecture of Snowflake. A modern cloud-based data platform. Snowflake is built on a unique multi-layer architecture that separates storage, compute, and services, allowing it to scale efficiently and independently. Let's start from the bottom layer, the database storage. This is where all the structured and semi-structured data is stored in a centralized repository. Snowflake automatically manages data organization, file size, compression, and indexing behind the scenes. Next, we have query processing layer. This is handled by multiple virtual warehouses. Each virtual warehouse is an independent compute cluster that can process queries without affecting others. This means multiple users and workloads can run at the same time without performance issue. At the top, we have cloud service layer. This layer coordinates the entire system. It handles authentication and access control query optimization through optimizer infrastructure management, metadata handling and security services. All of these work together to provide a seamless user experience while ensuring performance and data governance. This architecture makes Snowflake highly scalable, secure and ideal for modern data workloads in the cloud. Now let's explore why Snowflakes has become such a prominent player in a cloud data warehousing space. It offers several compelling advantages that set it apart. First, we have performance and concurrency. Snowflake's unique multiclustered shared data architecture allows for incredible performance. Even with thousands of concurrent users and complex queries, workloads don't contend for resources and ensuring consistent speed. Next, scalability or elasticity. This is a huge benefit. Snowflakes allow you to independently scale compute and storage. You can instantly resize your virtual warehouses up or down to match your workload or even spin up multiple warehouses for different teams, all without downtime or data mitigation. You only pay for what you use. A major advantage is zero management. Snowflake handles almost all traditional data warehouse management task for you including hardware provisioning, software installation and upgrades, backups and data encryption. This frees up your data teams to focus on analytics not administration. Then data sharing. Snowflake offers a robust secure way to share live governed data with other Snowflake accounts or even with non Snowflake users via reader account features. This simplifies data collaboration with partners, customers or even within different departments of your own organization. Finally, its cost effectiveness. While it might seem premium software, Snowflake pay as you go model and ability to pause compute resources when not in use can lead to significant costsaving compared to traditional solution where you're paying for idle capacity. You're charged purely for compute usage and storage consume. These advantages collectively make Snowflake a powerful, flexible and efficient platform for modern data warehousing and analytics. Now what are the services provided by Snowflake? We are looking into wide range of services provided by Snowflake which make it much more than just a data warehouse. First data warehousing. This is a core service. Snowflake provides scalable and high performance storage and quering for structured data. It is also functions as a data lake allowing you to store and process raw semistructured and unstructured data. Data engineering workflows are supported using snowflakes compute power and features like snow park and pipelines. Data sharing enables secure and seamless sharing of data across department, organization or even external players. With built-in support for data science and machine learning, Snowflake integrates well with tools like Python, R and various ML frameworks. Developers can build data application directly on Snowflake using APIs and native connectors. Snowflake also promotes collaboration through Snowflake marketplace where users can access share and data sets and applications. Cyber security and compliance analytics help organization monitor risks and meet regulatory requirements using Snowflake scalable platform. One of the powerful features is unis store which supports both transactional and analytical workloads on a single platform. Lastly, Snowflake supports streaming and real-time analytics, allowing businesses to react instantly to live data. All these services together make Snowflake a comprehensive platform for modern data needs. Now, before getting in, let's have a basic understanding of what is data warehousing. A data warehouse is a centralized repository of integrated data from one or more desperate sources. It stores current and historical data designs for reporting and data analysis and is a core component of business intelligence. So coming to what are what is cloud computing? Snowflake operates entirely in the cloud. So basic understanding of cloud computing is helpful. Cloud computing delivers on demand computing services including servers, stoages, database networking, software analytics and intelligence over the internet. It's typically pay as you go offering massive scalability and flexible and this comes in models like infrastructure platform and software as a service. Now let's move on and check what Snowflake UI is all about. So to create a free account in Snowflake you just need to Google Snowflake and open the first link. It'll take us to a sign up page. So here you can see they have mentioned that you can start your free 30-day trial which will be including 400 worth free usage. That means you don't need to pay for 30 days and you can use up to $400 worth free usage. So quickly fill your name, your last name, your work email and why are you signing up? You can just say I'm just a student or other and give a mail so that the authentication code you'll be able to access. So after filling those details you'll get a window of asking for a company name. You can just give a dummy name and job title. Just give data engineer. And then here in versions I would prefer enterprises because it provides us a longer usage of the storage. And also here you have to choose your cloud services. I would go with Amazon. See which and all you are familiar with. And then also select your region which is the closest to you which is Mumbai for me. And we'll just say get started. And here there are a few questions. So what will be the snowflake used for? We'll say run analysis. Then we can load the data. I'm going to teach you loading the data and continue. Else you can just skip if you don't want to. So here majorly I'll be using Python and SQL. So here you will get an authentication code or activation code for your email that you have provided before. So you can just go there and confirm it. So if you click on the activation link from your email, you'll have a window here to enter your username and uh enter your password and confirm your password. Fill all these details up. So after you enter, here's the first look of the UI of the snowflake. So you can take a tour of or just skip it. This is the homepage of snowflake where you can see different options on the left side where you have homepage and then search you can search anything that you have saved that you have misplaced. Then we have projects is a place where you can write your SQL queries create notebooks create your streamlit applications also and then you can create dashboards apps etc. So here you can see this is the worksheet place where you can create a worksheet by just clicking on the plus symbol. So you need to set your account. That's what they're trying to tell. And you can just say next, next, done. And here you can choose your language also. You can either do it in SQL or Python. So by default you have few of the notebooks which they have given which is sample data. They've given uh some few sample data from the snowflake itself and some tables also inside that. And then uh here you can see the data one where you can see all the databases that is already created by you and uh again there are few which are already provided by the snowflake and then we have cloud and which marketplace is also there. So snowflake also has marketplace which is an advantage where you can directly work on data deploying from here itself. So you don't need to just externally uh download the data from somewhere else and do it. You can just practice in the same marketplace where you can you have any number of things to practice on any number of datas to practice on from here itself. And then we have E IML studio where all the models and features and documentation should be available here. If you want to monitor what you have been doing you have the monitoring query history. So you can see any statement that I've just executed here will be displayed here. And then we have copy history. Any copy command which is run by here is also displayed. We also have task history. Since we have not performed any task, there are no tasks and then other things which you can monitor. Next here we have the admin where all the costs is also displayed. You can track all the cost related things. Here you can track your credits used here for the past 7 days also. Then you can track the anomalies, the budgets also. We have 26 days of remaining and data latency is up to 6 hours. We'll come to the latency part later. And then uh here you can see I have used zero because I've not used any uh SOS as now. So now let's write a sample code using SQL. So I'm going to just run a small code. We'll see how that goes. I'll teach you how to set up this which is users and schemas. So before that we'll just write a small code. We'll use account admin. So this will reset to account admin and then use sorry use warehouse and the name of the warehouse will be compute warehouse. So there is a running process here too. So if you want one sentence to run, you just need to select the sentence and click on run here. So statement executed successfully and then we have the second statement here. You can just run it the next time or if you want it to run together you can just select both of them and run it. So here you can see what are the information provided what is the duration and other things you can see in the results also. So in snowflake before executing any code we need to set users and roles. So what is this users and roles? In Snowflake, access and permissions are managed through a system called rolebased access control or RBAC. Let me break this down. First, we have users. These are individual accounts created for people who need to work within Snowflake such as analyst, engineer or administrator. Then we have roles. A role is a collection of privileges or permission. Instead of assigning permissions directly to each other, we assign them to roles which simplifies management. Finally, we grant roles to users. This means each user gets access only to what their role allows. Nothing more, nothing less. This makes Snowflake secure, organized and easy to manage at the scale. So with RBAC Snowflake ensures that the right people have access to the right data and capabilities keeping both security and efficiency in balance. Now let's see how to set up users and responsibilities. Now if you want to create users and roles in Snowflake, there's a pretty easy method. You just need to go to admin and then here you can see users and roles option. So you can see uh my name is already there as a default user. Now how to create a user? So you can just click on the option of create user. Mention their name, their email, set a password only for them so that only they get access to this and then you just if you want to comment just comment and uh you can just create users. Now how to initiate roles? How to give roles? Just go to the roles option and by default you can see so many other roles which are already predefined. So suppose I'm choosing account this is all the things which is about the account admin and it is granted to one user and if you click on that that username will be here. Now how do you grant this to other people? So here when you go down we have granted roles and then we have grant role. So you can just grant role just click on and if you want any other role to be granted for this user you can just select that role and just click on grant it will be granted. Now let's clarify the foundation concepts of databases and schemas. It's essential to understand their distinct roles and data management. At the highest level, a database is a collection of schemas. This is your entire data environment housing all your structured information. A schema then is a logical group of objects within that database. It acts as a distinct namespace helping us organize and manage related components. These objects typically include tables, views, and stages among other schemes. Some are crucial for managing complexity, preventing naming conflicts and structure your data effectively. Now let's see how to create a database and a schema. So go to the database option and uh here you can see different databases and you can search for anything which you want here too. So here when you come to database option there is a plus database symbol where you can just click on it give a name for it and comment if you want to and create. So here you can see it's already been created. Now under this we need to create schema but by default we have two schemas which is provided. One is information schema and one is public schema. Beyond this if you want to create any schemas just click on the database and you can add your schema uh name it trial one and you can comment also and create the schema. You can create this via code also but a much easier way is to create via the UI. So here you can see we have trial one schema also which is set. So under this you'll be creating tables and other uploading data or whatever it is. Moving on to the table type. Not all tables function the same. We have distinct categories based on their persistence and storage. First we have permanent tables. These are your standard persistent tables storing data indefinitely until explicitly removed. Next we have temporary table. These exist only for a specific session or transaction automatically disappearing afterwards. Ideal for intermediate results. Then transient tables often found in cloud platforms. These have reduced data retention or recovery suitable for easily recreating data. Finally, external tables. These define the structure of data residing outside the database allowing you to query external files directly without loading them in. Choosing the right table type is key for optimizing data storage and processing. Moving on from tables, we look at views which are virtual tables offering different perspective in our data. First we have standard view. This is stored query. It doesn't store data but dynamically fetches the latest info from underlying tables when queried. Next, we have the secure view. This provides enhanced security performing additional permission checks and all underlying data sources to prevent unauthorized access. Finally, the materialized view. Unlike others, the view actually stores the premputed query results. It's excellent for boosting performance especially for complex analytic queries as the data is readily available. These views helps us optimize for dynamic access security and query performances. So let's see how to create a table first. So you can create table in two ways from the UI itself and from the worksheet. First we'll see how to create it with the worksheet. So go to worksheet and open the latest one here. And then here we can start from fresh. So how to create it's a basic SQL code. But before that as mentioned before you have to create the role and the schema also. So here what are you going to do? First we'll use you can set it here by the way. Whatever role that you want you can create it. And whatever the warehouse that you are using you can create it here. You can just click and you're good to go. But then I let me show you via code also. So you'll be informed. So I'm setting the role to account admin. And then also I'm using warehouse which is the warehouse I'm using compute dot wh and then also I'm going to create a schema. So the schema I'll put it as my while creating a schema we have to mention under which data house it is. So what is the data house that I'm having? Database that I'm having it is practice. So it's going to be practice dot the name of the schema that you want to set it up. So we have my schema. Now let's run this. So here you can see no database selected but after we'll see what will be the conditions. So there is an error object doesn't exist. So I've not created my schema. So let's create my schema. Instead of that I've created trial. So let's use trial and the trial one. So now let's run it and see. And you can see here the database and the schema both are set. Make sure you create the schema before. And now how do you create a permanent table? So let's see how to create a permanent. It's a basic SQL code. So what will be the code for it? We have create or replace the table. Here we have not even create the table but we are going to create the table. And the name will be uh let it be permanent one permanent sorry permanent table and then what are the things inside that should be mentioned. So I'm giving ID which is int data type and then what else name I'll give which is a string data type. And I think that's all. So let's run this and see. So you can just arrange it like this as per your wish and then we can click on result and then you can run. There is a syntax error. So let's figure it out what it is. Okay, I'm not. And also the symbols that I've used is wrong. So let's change that. And now you can run it. Now you can see table permanent table seted and uh the end time scan account type warehouse everything is mentioned. So here you can see the query history also. Now uh we'll just see monitoring query history here how it works. So here you can see what are the exact code that is executed and if it is a success if it is failed what is a warehouse what is the duration extra. Now if you go to task history there are no task yet performed copy history also have not performed and then traces and logs also not found. So we'll go back and uh we'll continue now how to create a transient table. So let's see that. Let's create a transient table. So we can hide the results for now. And then so what is the process of creating a transient table? It is the same but then the keyword here differs. So it's the same create or replace transient is the keyword and then you have to give the name of the table. I'm going to give transient itself. transient table as the name and then again what are the things that should be mentioned here or what are the columns to be mentioned I'm going to give the same ID as integer sorry ID as integer and then name as a string and then let's close it and run this. There is another syntax error. Let's rectify what is it. Transient table. I have missed the table keyword. Okay. Now I think we are fine. So let's just execute this. And then here you can see table transient table successfully created. So now the last part is a temporary table. So let's create that to create a temporary table. So as usual it is create or replace temporary table and again I'm going to give the name as temporary table itself. Make sure there are no spaces and let's give the same. So id as int and then name as string. Let's close it up and let's run this. So again there is a syntax error. Let's figure it out. What is it? Okay, there is this comma here. So, it should not be the case. And now let's run it. So, you can see a temporary table successfully is created. Now, under trial one, we have created three tables which should be showing here. So, under tables, we have temp created permanent table, temporary table and transient table. Now let's check on with the code how to check all the tables that are provided in that particular database. So the simple command here is show tables and uh let's just run it. So here you can see all the three tables at what time it is created and the name of it and also the schema under which database under which and the kind here. So if it is a permanent one it is showing the kind as stable temporary as temporary and transient as transient. And here you can if you move ahead you can see the retention time that means how many days it's going to stay until it is automatically removed off. So by default for permanent it is given as one and for other two also we have given it as one. Now can we change this? So as per the snowflake for a permanent one it's up to 90 days for enterprises and other two it is up to a day. So now since you're familiar with creating different types of tables now let's see how to give values for those. So I'm just clearing this out and I'm going to create a table with entering the values. So create a table and enter the values. It's SQL code. You can also change the worksheet to Python too. So as usual the first statement be create or replace table which I'll give it as employees and what should be added here sorry the braces are wrong so it should be this. So what are the columns that you want to insert it? uh ID and I want the type of ID to be a integer and then I would say give the name of the employee which is as a character with maximum as 10 as the length. So let me give name as a small letter and then I would choose department. What will be the type of this? It's also going to be a character also. I'm going to give the length as 10. And then let's say salary which is integer and let's close it up. So this will create a table with the columns. So let's run it and see if there are any errors. So table employee is successfully created. Now how to insert the values? So here we have insert to what is the table name employee and what are the columns ID we have name and then department and also salary is also there And then we going to enter the values for it. So it's going to be values. So I'm going to give the first one as ID. So ID 1 and the name should be let's say Aron and the department HR and then the salary let the salary be 50,000 and then let's create another one which has ID as two and name being shwa and in department of ID and salary being 75,000. Let's create another one. So easier way to do this is it. So just change the number so that you don't have to waste time. So the name now is Susan and the department being sales and the salary being 50,000 again and then again let's say the fourth one name is Jennifer and uh he belongs to marketing and the salary being 50,000. Again, let's enter the last one which is five and let's say the name as J let it be it. And then let's give 65,000 as the salary. And let's run this. So the values are inserted. So number of rows inserted we have 100% fulfilled. So you can see five rows are added. So let's check on the table. So let's just let let's just run it. And here you can see the table name where is employee here here first one. So let's show the query for us to filter out only IT employees. Okay. So how do you do that in SQL code? We use us use usual select star from which table is it employee and there first let us run this so that we know what is there in the table. So when you run it here you can see the name has been all entered and the departments being HR, IT, sales, marketing and the salary is also being updated. Now I want a separate table only for IT people. So let's create a query for that query for only IT people. So for that we have to create another table. So create or replace view. Uh let's keep the name as ID employee and uh as where I should select it from select sorry select so it'll be select ID ID in small select and caps select ID name and what else is there salary and then from employees with what and all you have to say select we need only the condition B where department is Let's run this and see if there are any errors. So it is done. IT employees is created successfully. Now let's see what is stored in IT employees. So we have the same sentence just add let's run it. So initially there should be two of them who are from IT department which is SA and AI with the ID S2 and PI with their salaries also displayed. So this is a how you can create a table and you can push in the table content. There is another way to push in the content where you just can upload it. We'll get back to that. Let's move on to understanding data loading which refers to the methods we use to bring data into our database systems. There are distinct strategies each with its own advantages. First bulking loading. This is ideal for large historical data set or significant data migrations. You process huge volumes of data in one go very efficiently. Next we have continuous loading. This method handles real time or near realtime data streams. As data arrives, it's immediately ingested crucial for application needing up tothe- minute information. Finally, and very importantly, staging data first. This approaches evolves loading raw data into temporary area or stage before moving it to its final destination. This allows for critical steps like validation, cleansing and transformation to happen safely ensuring data quality before it impacts your core tables. The choice among these method depends on the data volume, velocity and quality requirements. Now let's look at specific methods used to implement these data loading strategies particularly focusing on two two common approaches. One on the left is having using the copy command. This is often primary loading mechanism for bulk loading. It is designed to effectively load large volumes of data. The copy command typically loads data from the stage to table. As we discussed, a stage is that temporary landing area of files. Crucially, it supports various formats like CSV and JSON and more making it very flexible for different data sources. On the right, we have using Snowpipe. Snowpipe is specially used for continuous loading. It provides automated continuous ingestion. You configure it once and it automatically loads data as soon as new files appear in your stack location, often in the cloud storage. This results in low latency data availability meaning your data is ready for querying almost immediately after it lands in the source stage. So while copy is excellent for scheduled bulk load snowpipe is go to for near realtime automated data injection. So before uploading any external or internal data you will be needing to create a stage which is like the middle layer before uploading which will the data will stay for a few time. So how do you create that? So go to your database and open the schema and you can see a create button and an arrow over there. So you can drop down and there is stage. So here there are two types. For internal we have snowflake manage whereas for external stage we can connect Amazon x3 uh Microsoft Azure and even Google cloud platform. So the link should be attached here for the external storage since uh we are going to create something internal stage. So first of all we have to give a stage name. So let's give it as customer since the file that I'm uploading is regarding a customer data. And then you can comment what kind of information is present in this. And there is an option of client side encryption or serverside encryption. You have to provide any one of these. See the terms and conditions over here. So select if you plan to use snowark or snowflake. And then server side if there is any document AI cortex AI functions you can use the server side. And I'll be using uh the client side encryption. And there is a code. So if you run copy this code and run it in the worksheet it it is going to upload the same way. So then you can just create it and here you can see as of now there are zero files. So stage customer was successfully created just refresh it for once and then go back and here again you'll have an option of plus files. So you can add your files directly over here. So there is a drag and drop option and then there is browse. So you can browse whatever file you want to go for it. And uh the limit is 250 MB and then you can upload it and use it. There's a code that will be provided here also. You can copy and paste it. So that's how you upload your data set as of now. And then there is a UI version. The cloud version is also there where you need to get the link from your cloud platform and link it to your snowflake and there on you can use the cloud platform as well. So that was it about snowflake the cloud platform. In this video we not only understood what snowflake is and how it architecture is designed for the cloud but we also got hands on other things such as exploring the UI tables, what are schemas, what are other things which are related to the database set. We explored the Snowflake user interface, learned how to create database and schemas, how to build tables, and even saw how to upload data into the Snowflake step by step. This foundation sets you up perfectly for everything that's coming next. Whether that's running your SQL queries, exploring data, or working with more advanced feature like data sharing, role based access, or building pipelines. If you're new to Snowflake, you are now well equipped to start building and exploring on your own. Thanks for joining me in this session. Staying ahead in your career requires continuous learning and upskilling. Whether you're a student aiming to learn today's top skills or a working professional looking to advance your career, we've got you covered. Explore our impressive catalog of certification programs in cuttingedge domains, including data science, cloud computing, cyber security, AI, machine learning, or digital marketing. Designed in collaboration with leading universities and top corporations, and delivered by industry experts. Choose any of our programs and set yourself on the path to career success. Click the link in the description to know more. Hi there. If you like this video, subscribe to the SimplyLearn YouTube channel and click here to watch similar videos. To nerd up and get certified, click here.