All Microsoft Azure DP-203 certification exam dumps, study guide, training courses are Prepared by industry experts. PrepAway's ETE files povide the DP-203 Data Engineering on Microsoft Azure practice test questions and answers & exam dumps, study guide and training courses help you study and pass hassle-free!
Design and implement data storage - Overview on Transact-SQL
11. Lab - T-SQL - Creating Tables with Keys
Now I'll just go on to our next SQL statement. So just to kind of summarise the primary key and the foreign key constraints again, I'm going to go ahead with the creation of tables in Azure SQL Database. So I'll be creating a Customer table that has these particular columns, and I'm defining my Customer ID as the primary key. I am also creating a course table. Here. The primary key is the course ID. And then I'm creating an order table. The orders table will actually have foreign keys that reference my customer table and a foreign key that references my course table. Then I will insert data into my customer table, the course table, and the orders table. So we can go ahead and do that. I can go in and take all of these statements to, first of all, create my tables. So I can go on to the query window. I can add all of this and hit execute. It will create all of my tables. Next, I can insert the data into my table. So the first thing I'll do is add data first to my customer table, then to my course table. This is important. If I start adding information to my orders table without also having information in my custom table and my course table, it will fail because there is a dependency.
My orders table has a dependency on the data in the customer table, and it has a dependency on the information in the course table. So again, this is not supposed to be a full-fledged SQL development course, but I said the reason I'm going through all these important concepts is because I want students to be very well versed when we actually move on to Azure Synapse when we come on to the concepts of the SQL data warehouse. So first, I'll insert the data into my customer table. So I'll do more of this and insert the data. So that's done, and I'll now insert the data into my course table. So this is basically the customer ID and the customer name.
Here I have the course ID, the course name, and the price. So I'll execute this. Next, I'll insert it into my orders table. So in my orders table, I have my order ID. Here I am referencing the course ID, which is present in this table, and the customer ID, which is present in this table. I can go ahead and run this particular query, right? So I have all of the rows in place, and you can do a select statement to confirm all of this. I have a final insert statement, and in this insert statement, I'm adding an order where there is no customer defined. So if I go ahead and try to execute the statement, I get an error because of the foreign key constraint. So in this chapter, I just want to go through the process of creating tables. And how do we insert you when it comes to foreign key constraints?
12. Lab - T-SQL - Table Joins
Hi. Welcome to this section on Azure Synapse! Now, there is that is a Lowe are whereto cover aborting to COVID section. action’s the first thing we are go look at is building a workspace in Synapse. ynapse.
Now, the benefit of actually using this example is that I'm going to carry this same example forward when it comes to working with Azure Data Factory. So once you have your knowledge firm on building these fact and dimension tables, we'll see how to build them. Also, when we move on to Azure Data Factory, we'll also look at the different types of tables that are available in the dedicated sequel pool when we are looking at the hash distribute tables, the replicate tables, and the round robin tables. So we have a lot that we need to COVID in this particular section. So let's move ahead.
Design and implement data storage - Azure Synapse Analytics
1. Section Introduction
Now, the first thing we are going to tackle is: why do we need a sequel data warehouse in place? Well, this comes to the different needs of a transactional system, wherein you need to perform analysis. For example, earlier on, we had done a very simple demo on how we could use a Net Core application that was interacting with an Azure SQL database. This Azure SQL database was storing all of the application data. This was a very simple example of the storage of data on an Asia SQL database for a company. Let's say they have a production-based application, and they are storing all of their data in multiple tables in that SQL database.
So the application is performing a lot of select statements, you know, fetching data, performing inserts, performing updates, et cetera. So here, our SQL database is acting as the data store for our transactional system. By using the transaction system, we are seeing that our application is performing transactions at a rapid pace on the underlying tables that belong to the Azure SQL database. Even earlier on, we had looked at the Adventure Works database that had come into place. And again, let's assume that we had an application that was interacting with all of the tables that we had over here. So all of the application data, all of our transactions, are being logged in. Let's say these tables are part of this database. But now let's say that you want to perform analysis on the data. Let's take an example. Let's say that Urumi, right, the online platform for delivering courses, also has different tables for the storage of information.
Let's say that they have a separate tablet that is used for storing the student information. A separate table that has all of the course information allows us to search for a particular course based on the information that is stored in the table. Let's say they have another table that has all of the purchases that are made for a particular course. So this is the transactional data store. This could be stored in a SQL database. The SQL database system, the engine, is actually built and tuned to work as a transactional data store. It is tuned to take a lot of inserts, updates, and deletes from an application or from users. But as I mentioned before, what happens when you want to perform analysis on the underlying data? That's where you have a separate online analytical processing system. This actually helps the business users get a better understanding of the underlying data. Let's say in terms of uranium, they want to understand how many students are resting per day or which countries have the highest number of student registrations. And based on this information, they probably want to do a forecast of the future on how many students will actually come from certain countries.
This forecasting information will be based on the historical information that is already present, based on the purchases that have already been made by students. So you are doing an analysis of your historical data to predict an outcome and the future. What will the future be like when it comes to purchasing courses? And similarly, they might want to go ahead and perform more analysis based on the information that is available in different tables. Now, let's say that you try to perform analysis on the same transactional system. So let's say we have Udemy students, we are logging into the Udemy platform, we are searching for courses, we have bought a course, and now we are looking at videos that are part of a course. And let's say that you have another set of business users who are trying to perform analysis on the same data set. So the same data set is being used not only by us as students but is probably also being used by other users for analysis of data. This is where the problem will start occurring for students who are taking the courses. Because when you perform analysis, you are really putting a strain on the transactional processing system. So the database system is under pressure from both sides: from the students who are trying to access data and from the analysts who are trying to analyse the data.
This is the first problem. The next problem is that normally, when it comes to online transaction processing data, this is specially meant, as I said, for transactions and not for analysis. The SQL data warehouse is specially built so that you can actually perform analysis on your terabytes and petabytes of data. The underlying tables and the way they are distributed on the underlying hardware are actually specifically built for analysis itself.
The third point is that when you're looking at performing an analysis for the future, you are looking at historical data. And that historical data could be from years ago. So you might take three years' worth of data and then perform analysis. And normally, in your online transaction processing system—let's say a SQL database—you will not store this historical information. The main reason for this is that if you start storing all of your historical information—years of information from years ago—in these tables, when you're performing an operation right now on the tables, it might be slow if, for example, you want to fetch some information from the course table. If you have information about courses that aren't present anymore, they've been outdated, right? They are not present on the platform.
You are simply searching through so much invalid data that is not actually required. So in an online transaction processing system, you only have the data that is required. That is, you know, that is valid. Historical data should be in a different system altogether. So in this case, what you will do is actually take the data from your transactional processing system. You might perform steps such as cleansing your data. For example, you might have null values in some of the data columns. You might clean them, and you might transform your data in such a way that it could be built for a specific purpose in your data warehouse. Now again, a SQL data warehouse is used to store structured data. Again, you will use your traditional SQL statements to work with the data. But I say the underlying basic functionality of a SQL data warehouse is having the capability to work with large amounts of data and perform analysis on large amounts of data. And then you can use external tools—visualisation tools. It is PowerBI to perform analysis of data in your SQL data warehouse, right? So in this chapter, just to get the ball rolling, I wanted students to be well aware of the need for a SQL data warehouse.
2. Why do we need a data warehouse
So now we come on to Azure Synapse. Now Azure Synapse is much more than a sequel data warehouse. So initially, the product that was actually offered in Azure was only a sequel data warehouse. But then they expanded to much more. Now they have actually branded the product as having Azure Synapse Analytics. And over here, you can not only build a SQL data warehouse with the help of SQL pools, but you can also integrate your data into your SQL data warehouse with the help of pipelines. So Pipelines is actually part of another tool known as Azure Data Factory, but a part of that functionality is also made available as part of Azure Synapse. So if you want to load your data from a data source and if you want to perform transformations on the data before it is stored in a SQL data warehouse, you can actually make use of the pipelines that are available in Azure Synapse. You can also help to bring your daily much closer together and process them when it comes to Azure Deal Gen 2 storage accounts. You can also create Apache Spark pools when it comes to processing your data. And then Azure Synapse also integrates with other services that are available in Azure. So when it comes to monitoring, it integrates with Azure Monitor. When it comes to security, it integrates with Azure Active Directory. So this is an enterprise and a service that are actually available on Azure.
Microsoft Azure DP-203 practice test questions and answers, training course, study guide are uploaded in ETE Files format by real users. Study and Pass DP-203 Data Engineering on Microsoft Azure certification exam dumps & practice test questions and answers are to help students.
90.6% students found the test questions almost same
