Pass Your Microsoft Certified: Azure Data Engineer Associate Certification Easy!

Prepare with top-notch Microsoft Certified: Azure Data Engineer Associate certification practice test questions and answers, vce exam dumps, study guide, video training course from ExamCollection. All Microsoft Certified: Azure Data Engineer Associate certification exam dumps & practice test questions and answers are uploaded by users who have passed the exam themselves and formatted them into vce file format.

Design and implement data storage - Overview on Transact-SQL

9. Lab - T-SQL - HAVING clause

So I'll go on to the next file. So this is just the "having" clause. So here the aggregate functions of the sum count cannot be used along with the where clause. So you can actually use it in the having clause here. The only difference is that I only want to havea result set wherein the number of products that actuallymatch a particular colour is greater than ten. That's it. If I take the statement, if I place it over here, and I hit execute, we are only getting those rows. Now where the number of products, the colours greaterthan ten, the count of the products based ona particular colour is greater than ten.

10. Quick Review on Primary and Foreign Keys

Now in this chapter, I just want to have a quick review when it comes to primary keys and when it comes to foreign keys. So, primary keys are used to uniquely identify rows within a particular table. So if you are sure that you don't have duplicate rows in place, you can go in and enforce the primary key constraint on a particular table. This is a feature that is available in most SQL database systems, and then you have the foreign key. So if you have one table that has a dependency on another table, you can have a foreign key constraint. If I go on to my Object Explorer, when you look at the sample tables that we have over here, I'm sure that there is a relationship between the tables over here. So if I consider the product, the product category, the description, and the model, I want to see what the relationship between these tables is. If I go on to the sales table or the keys, for example. So here I can see that there is a primary key that is based on the product ID. So this means that the ProductID value is unique for every row. That means you can't have two rows that have the same product ID. I can see that there are also foreign key constraints that are being mapped onto other tables. So there is a relationship between the product table and the product category table, and there is a relationship between the product table and the product model table. If you want to see a new visualisation of the relationships, you can actually do it from SQL Server Management Studio. You can right-click on database diagrams. You can click on "New Database Diagram" here. It will give you a message that one or more of these support objects is not in place. Do you want to create them? I'll click on yes. Then it should give me a screen where I can choose the tables I want to add onto my database diagram. So I'll choose my product table, my product category table, and my product model table, and I'll click on Add, and it will add all of these tables onto my database diagram. Once I have the tables in place, I'll close this. So here you can see that we have a primary key that is defined on the product table. There is also a primary key on the product category table, and there is a primary key on the product model table. There is also a relationship between the product table and the product category table, and there is a relationship between the product table and the product model table. Here are the columns that are actually mapping the tables, so we have the product category ID in the product table. This is actually being mapped onto the product category ID column that we have in the product category table. And then we have the Product Model ID column that will be mapped onto the Product ModelID column in the Product Model table. When it comes to understanding the relationships between tables, this is going to be a very important concept we will be learning in the sequel data warehouse. So based on relationships, based on the columns, you can actually construct something known as our Dimension Tables, which will be used in something known as a Star schema, and that's something that we're going to look at in detail at this point in time. Just a quick review: keep in mind that you have your primary keys and you have the foreign keys that are used to depict the relationships between the tables.

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 the 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 actually reference my customer table and a foreign key that references my course table. Then I will insert data into my customer table, into the course table, and into 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 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 onto 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, inserting the data. So that's done. 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 customerID, 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 wherein 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 the creation of tables. And how do we insert you when it comes to foreign key constraints?

12. Lab - T-SQL - Table Joins

So now I'll go on to our final SQL file. So, this is table joining. Again, this is a very important concept when you are working with a SQL data warehouse. This is used to combine rules from two or more tables based on the related column between them. So here I'm again making a reference to the tables that we have in our Adventure Works database. I'll copy the first statement and I'll place it over here. I am selecting the Product ID, the list price, the order quantity, and the line total. I am actually performing a join now on two tables. I want to see the products that actually have a defining record in the Sales Order Detail table. So the Sales Order Detail table has a Product ID, which maps to or matches the ProductID in our actual product table. And if you look at my select clause now, all of these columns are not just part of one table. So my product ID, yes, is part of my product table. But what about the order quantity? So if I go onto the Object Explorer, if I go on to the columns in my product table, do I have the order quantity over here? No, but if I go onto my Sales Order Detail Table, if I go on to my columns, I can see my order quantity column over here. So now I'm performing a join on two tables. I want to get the matching rows of both tables based on the product ID. And I want some information from both of the tables. I'll go ahead and execute the statement over here. It's giving me an ambiguous column name. So for that, what we can do is that we can take our sales table and add it over here because the approximate ID column is in both tables, in our Sales Product table and in our Sales Order Detail table, as an execute. And I get the results as desired. When we actually move on to the SQL data warehouse and when we are building our dimension tables at that time, we'll also be using the joint statement. And when you are building reports that are based on your fact and your dimension tables, or when you're looking at data from both your fact and your dimension tables in a SQL data warehouse, that time comes. Also, you'll be making use of joins in the SQL Data Warehouse implementation. In Azure Synapse, you don't have the concept of foreign keys. So you can't define foreign keys in the SQL data warehouse in Azure Synapse. Yes, some SQL data warehouse implementations make use of foreign keys, but when it comes to Azure Synapse, you don't have any foreign keys in place at that time. You will actually be performing joins on your tables based on matching columns in the tables themselves. However, there will be no foreign key constraints. Just keep this in mind. So, returning to my statements, if I now take the third statement and add it over here, I'm just performing an extra activity of calculating the discount amount. So this is based on the data that I have in my tables. If I hit execute, I can see the values over here. So when you start performing joins on your tables, you start performing operations on those tables to get meaningful results from your data or a meaningful representation of your data. And then finally, I can also go ahead and use the "where clause" where the order quantity is greater than ten. And let me go ahead and hit on execute. So we have that in place, right? So this concludes this section on a quick review of transaction SQL, which is essentially just basic sequel statements. And I said the main purpose for this is for students to have a quick refresher on SQL statements, because this will be important in our SQL data warehousing section. When it comes to Azure Synapse, it kind of builds a foundation for you all when you're moving on to that particular section.

Design and implement data storage - Azure Synapse Analytics

1. Section Introduction

Hi. Welcome to this section on Azure Synapse. Now, there is a lot that we are going to cover in this section. So the first thing we are going to look at is building a workspace in Azure Synapse. Then we'll look at the two major compute options. You have the server SQL pool, out of which we'll build external tables. Then we are going to look at the decade-SQL pool wherein you will build your SQL data warehouse. Now, I'll also be discussing how to build your dimension and fact tables with the help of an example. I'll take a simple example where I have data in place, and we'll build dimension and fact tables out of the data. 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 a firm grasp 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 cover in this particular section. So let's move ahead.

2. Why do we need a data warehouse

Now, the first thing we are going to tackle is: what is the need for having 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 ASQL 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 behaving as the data store for our transactional system. By 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, which 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, 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 fine-tuned to actually work as a transactional data store. It is fine 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're analysing your historical data to forecast 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. As a result, the same data set is being used not only by us as students, but also by other users for data analysis. 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 getting 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 as an analyst 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, 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 for 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 for 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—visualization tools. Isn't PowerBI used to analyse data in your SQL data warehouse? 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.

3. Welcome to Azure Synapse Analytics

So, now, we come on to Azure Synapse. Azure Synapse is now much more than a follow-up data warehouse. So, at first, the only product available in Azure was a sequel data warehouse. But then they expanded much more. Now they have actually branded the product with Azure Synapse Analytics. And over here, you can not only build a SQL data warehouse with the help of SQL pools, 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. When it comes to Azure Deal Gen 2 storage accounts, you can also help to bring your daily much closer together and process them. 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.

ExamCollection provides the complete prep materials in vce files format which include Microsoft Certified: Azure Data Engineer Associate certification exam dumps, practice test questions and answers, video training course and study guide which help the exam candidates to pass the exams quickly. Fast updates to Microsoft Certified: Azure Data Engineer Associate certification exam dumps, practice test questions and accurate answers vce verified by industry experts are taken from the latest pool of questions.