Rishabh Gaur

So good evening. I'm Resha Bort. Currently, I'm working with Celebill Technologies for the past 2.5 years. During this time, I have worked on multiple data engineering, data migration, and data warehousing projects on the technologies such as Databricks, PySpark, ADF, and SQL. And I have also worked on 2 clouds, that is Azure and AWS. My current working project is for Fashion Retail Limited company. This is basically a data engineering and migration project in which we have to migrate data from multiple sources like DB2, SAP BW, MySQL, Oracle, and multiple file-based integrations into a Databricks warehouse via ADF and Delta Live Tables. If I talk about my experience, other than the project, I have also completed Azure and Databricks certifications, such as data engineer associate, Databricks data engineer associate, Databricks solution architect, the AZ-900, and DP-900.

Okay, so if I talk about data bridge shredding. So, in Azure SQL, we can achieve database optimizations by applying partitioning and indexing on our Databricks database tables and all that. So if I talk about partitioning now, what partitioning does is redistribute data into a number of partitions on primary columns and incremental column basis. So if we have to get the data from the particular table, then with the partition data, the retrieval of the data would be a lot faster. And if we apply indexing on that particular data also, indexing basically means identifying the key columns or the high-order columns for that particular table. So if that particular table has all those important columns, the data, then it will be easier to fetch the data and easier to handle the large-scale dataset efficiently, which will not only optimize the time but also the space of the large datasets. So with this partitioning and indexing, we can implement database shredding and manage a large-scale dataset efficiently.

So, to deal with error handling routines in SQL and Python. We can use exception handling in Python, like the try and catch statement. In exception handling, we can apply all those scenarios which we feel can break our code. For example, in Python, if we divide a particular number by 0, then it will give a division by 0 error. Instead of getting our code failed, we can apply try catch. And inside the try statement, we can do whatever functionality we want. In the catch, if the particular try catch fails, then in catch, we can use exception as e, and we can print that particular error instead of our whole pipeline getting failed. We can use the exception statement to get what was the error, and solve it accordingly what type of error it was. In SQL, exception handling can also be done to check a few of the cases, like if the data compatibility is not matched between the columns or in the merge statement if the duplicates happen, then the data might fail because of that. We can handle all those case scenarios with the exception handling and get the data inconsistency treated before it provides an error in a daily pipeline or daily runs.

Okay, so if we talk about the Azure factory pipeline, so basically, in ADF, we have JSON scripts stored on whatever, like, we have done in that particular pipeline. Those values are stored. All the process is stored in the form of JSON. So, how can we deal with this kind of data with the help of Python's object-oriented programming? So, what we can do is create a Python class. Basically, we can create a Python class that will check the readability of that JSON data. If we can write a particular script, we can create a particular Python class that will parse the particular JSON data and check if it is in the correct format or not using pandas library, pandas import pandas as pd. And, you know, with that, we can import JSON. And with JSON dumps, we can extract all the data inside the ADF factory pipeline inside the JSON. Dumps data, and we can check if that particular JSON is correct, and then we can enhance our Azure data pipeline with the help of a Python class that will check the readability of that particular JSON data. In this form, we can maintain the or enhance the maintainability of the Azure data pipeline with the help of Python's script and all that.

Python Pandas library to streamline transformations of large data sets. So, basically, in pandas is not parallel, it does not use parallel processing. All computations and those which are happening are happening in that one worker node only. So, whatever, we have to if we want to optimize that particular large data set, then we can use Pandas cache library to cache that large data set and get our results stored quickly. So in that particular scenario, we can create our data and get the faster results from that particular data. So, this is how we can use it. So, basically, with pandas, I have not worked much yet. I have basically used the PySpark libraries to deal with large data sets. So, basically, in PySpark architecture, we have a parallel processing. So, basically, the large data sets are divided into multiple workers which are present, and those workers divide that particular data according to that execution plan and divide the data efficiently, and perform tasks on all the parallel workers in which the data is distributed parallelly, so that we can get all work on the large data set smoothly and efficiently. In this way, we can deal with the large data set with the help of PySpark. Pandas with PySpark, I haven't worked much yet, but we can use caching in the form of Pandas.

So how can we optimize the execution plan? So, first, what we can do is after running the query, we can check the execution plan of that particular query and see at what statement it is taking a lot of time to get executed. Let's suppose there are multiple join conditions and multiple CPs are used in that particular query. So, if we check the execution plan of that query, it will show at which point of time how much time it has taken for which query. So, basically, what we can do instead of normal joins to optimize those normal joins, we can use broadcast joins to optimize those particular tasks. And instead of using subqueries and nested queries, we can convert those particular nested queries and subqueries to CTs, which can improve our time and optimize the execution plan more efficiently. So, these are a few of the ways through which we can optimize our SQL queries efficiently. Or, if these were not working, then what we can do is if the data is very large, then we can partition our particular data and use indexing on that particular partition for faster retrieval of queries and faster working of queries. In that sense, we can work or optimize those particular SQL queries and those particular scenarios.

In this particular order, if we like to try to run this order, it might give an error in that particular function, get the titles, which is taking URL as a parameter. Inside this function, we have the second line as soap is equal to beautiful soap and base dot content, comma, HTML dot parser. So for beautiful sofa, instead of taking two parameters, which usually takes three parameters, to, like, whatever API data which we get in the page variable, it will first take the content of that variable, use an HTML parser, and we can specify the third argument in the format in which we want to store the data. This is how we can add the third parameter and then store it in the soap variable. Then, I think adding that parameter will work to solve this issue.

So here in this particular SQL, select the statement, This where condition, what we are using, is actually wrong. We cannot use where condition on a calculated column. So particularly, what is happening inside this data, we are taking the count of order IDs on the basis of first name and last name. How many orders were done by a particular customer. And basically, then we are using their condition to filter the data where it should be greater than 5. So instead of their condition, we can use having condition here, and then we can take that as order count should be greater than 5 like that. So in that case, this will work. This weird condition here, it will give an error. We can replace this weird condition with the having condition, and then we can utilize this particular SQL command and user.

In online analytical processing cubes, what we can do to reduce the query load on operational databases is use Azure Synapse serverless computation. If we don't want our database to take a load on those particular SQL queries, then we can use Azure Synapse serverless computation. Azure Synapse serverless computation automatically increases or decreases its number of worker nodes to deal with the queries. If the operation is taking too much load, it will increase the number of workers. And if the query has less load, it will reduce the number of workers accordingly. We can use a serverless compute from Azure Synapse to deal with these kinds of query loads. This will also help to reduce the cost of whatever operations we are doing and whatever results we are getting. It will be helpful to reduce those particular costs and all those things. So, basically, we can use an Azure Synapse serverless cluster in these kinds of cases.

So, basically, to deal with the API data, I have not used the.NET as a programming language. I'm currently using Python, and to deal with the API based data and all that. So, basically, in Python, what we can do is integrate a secure connection. We can either use Azure Key Vault or we can create the secret keys for PII data. With the help of these, we can set up a secure connection using the Python API request library. We can use that particular thing to secure our API basis data and ensure our data consistency in that case. I have not used the.NET as a programming language till now. So, I'm currently using Python to deal with these kinds of cases.

Capabilities of PHP backend in data processor. If we are using Python for data processing purposes, basically, we have two things in Python. One is multiprocessing, and one is multithreading. So, basically, we can use multiprocessing to process our data parallelly using Python. This can be done to reduce time and costing efforts. And also, if we are fetching the data and have particular loops and all those things to deal with our data, then in that case, we can use multithreading to deal with those kinds of data. Multithreading will basically parallelize the loops and provide or run those loops parallelly to get faster running of the queries. Generally, we run a full loop of a list of size and then it will take an amount of time. But if we apply multithreading, we can apply it on how many levels we can use multithreading. We can take a maximum of eight threads. So, it will process eight rows simultaneously at the same time in that particular scenario. So, this can be used to process our data in a much faster way using Python. That's it. Thank you.