Shodhan Pujari

Yeah, I'm Shudan Pujali. I have around 15 years of experience in SQL Server development. And, I have worked on various projects including banking, finance, and HR. And, currently, I'm working for Interpublic Group and in the middle of a team working on SSIS, EDL, as a SQL developer. And, I'm good on SQL and ETL, including SSIS packages. I also have experience with the cloud SAP ETL tool. And, I have worked on SSIS and Tableau reporting. So, in total, I rate myself around 9 out of 10 in SQL Server. Like, I started my career with SQL Server way back in 2005, and then I worked for Darren Broadstreet, then LED for Tech. After that, I worked for Deloitte, IBM, and this is my current company, which is part of the Interpublic Group. It's a major advertising group, wherein we have many agencies around the world. So, we cater to all the development activities, including creating middleware integrations, involving SS packages, and Birst. Currently, we are migrating to SAP Cloud. So, that's a brief background about me. Thank you.

Yeah, can you describe how to automate a data validation test after ETL process? Okay. Data validations might be like you can pass, for example, you can automate a job. Like, for example, if the ETL process is running via some agent or some control engine. So we can run that job by passing a test file, like the one used for regression testing. We pass around a large file, like 1 million rows, and see how the ETL package behaves with that file. And we also pass some generic characters, like Unicode characters and special characters. And if you are targeting any specific test cases for data validation, make sure to include more specific data, like Unicode characters with Chinese or special characters, so you can validate whether that data is getting inserted properly into the table and viewed in the report. So that is one automated way I can think of. And also, we can do some manual testing by debugging the ETL packages using a data viewer and checking what data is coming in the next flow. You can see what the data is after each flow. And, also, you can use a log file to capture events. For example, how many records have been processed in a particular transformation. So you can capture that using some log file. You can check the logs and the execution logs, which, for example, in SSIS, you have a monitor where you get to see all the execution, how many records are processed, what step, what warnings you get. For example, some cases have explicit or implicit conversion happening, so you get some warnings. So you can do a validation test.

Database transactions. Okay. So in SQL Server, the transactions are mostly about asset properties, atomicity, isolation, consistency, and durability. Okay? So the current isolation level has different isolation levels like read committed, read uncommitted, repeatable reads, and serializable. So the default isolation level is read committed. So if you open a transaction, it will wait for any other transaction that is open to get committed. So for that, we can use a try-catch block. We have a try-catch block. In that, you can try to begin a transaction, roll back, and commit. If there is an error, if the error count is greater than one, you can roll back that transaction. So in that way, you can manage the transactions in the database and also try to keep your transaction as small as possible. We shouldn't have multiple update statements; instead, try to make them smaller transactions. Don't keep your transaction waiting for more than a minute or two minutes. Because at the same time, there might be chances that some users might be accessing the same table or the same record. So they'll be like this: since you are holding a lock on those tables, it is better to use a transaction isolation level and close the transaction as soon as your operation is completed.

Next release. That must join several tables and function efficiently. Okay. So, for example, if you have multiple joins on several tables, the first thing you will do is use a temporary table. Create a smaller dataset in the temporary table and create some index on that so that it can do index seek when you use that temporary table in your next condition. For example, if you've done an inner join and a left outer join in your first step, and then stored the result set into a temporary table, you can use that temporary table in your second condition. If you've used one more table as a left outer join. If you want to add a function to a join, then you have to use a cross apply join or an outer apply. This is because normal joins won't support a function, like a table-valued function. You can use a table-valued function in a join as a cross apply. So, cross apply is like an inner join or an outer apply. My approach will be if your result set is very huge, try splitting it into multiple temporary tables, small temporary tables, and create indexes on that. Then work on that instead of writing everything in one statement. Try to split that out into multiple steps so that it will give a better performance as compared to several joins including a single select statement.

Slow running query without database access. Without database access, the run inquiry is slow. Okay. So for example, if someone is complaining about a slow run inquiry, first, I will ask them about the actual expected time. Like, what was the actual expected time? And then I will get the actual output and how much time it was used to take before. And then I will ask them for the stored procedure query. Stored procedure. Then, in the stored procedure query, without having access to the database, I will look into the code, what is written. So for example, if they have written some cursor logic, I will try to remove that using some lead lag function. And if they're using a scalar function within a cursor, we can try to convert that into a table-valued function and use that as a join. In my earlier experience, I worked on the performance unit of various stored procedures, queries, and all. So in one scenario, we had a stored procedure which used to take more than an hour to run. When it had multiple business logics, it was calling a scalar function, and then that scalar function was applied inside a cursor. It was looping 1 by 1 record, which was coming from the scalar function. So, we converted that scalar function to a table-valued function and used it as a cross apply join. And then we removed the cursor logic, which was taking the previous and the next record data from sales data. Instead of writing a cursor, we just used lead and lag functions, which is getting the previous and the next record value, which is available in the latest versions of SQL Server. So, we can rewrite the query if it is possible. And also, we can see if there is any implicit conversion happening. If you're matching with the integer and your back caps, that will always be an implicit conversion. So we should avoid that. And, any parameter sniffing. If you're passing any parameters, you have parameter sniffing. If you created a stored procedure and you are passing any input parameters, what is happening is, at the time of extracting data for last 1 year, SQL Server will store the plan based on that 1 year data. But the same plan won't work for the other. If you are taking more than 1 year data, then it won't work. So, that is called parameter sniffing. So we can avoid that using the option recompile.

How do you prevent SQL injection attacks in stored procedures? SQL injection. Yeah. So in that case, SQL injection attacks in stored procedures is like we can use sp_executesql, wherein it will pass your dynamic SQL query into that. It will compile your code, and it will compile your code before it runs. So that is one thing. And SQL injection is like a hacker will try to inject some incorrect values into your application, like some input parameters, which can hamper your application performance, like it can go down. So to avoid that, you declare the input variables properly and try to recompile that using sp_executesql. So that is what I currently can think of.

There is a potential performance issue when dealing with that reset. That's it. Can you explain what k. Here, this function gets the order total, order in returns money. We can think, declare a total, select sum of sum of in net into quantity from where order ID return total. Okay. This function is returning a value in the data type money. Like, it is selecting unit price. It is doing a sum of unit price into quantity. It is multiplying unit price into quantity and taking the sum from order details where order equals this. Sum of order okay. For that particular order from the order details, it is taking some into unit. Okay. So, since it is selecting the order ID from order details, we can check whether any index is present on that order ID. And because it is doing a seek operation on that order ID, we have to see whether unit price and quantity can be included in that particular index. For example, create a non-clustered index on order ID. Include unit price and quantity in that particular index. So this will improve the performance.

So what is the logic error present here and which might cause the impact on the rectified? Okay. So why is select from users where is that where is that typical to 1 greater than 0. And begin delete top 1 from delete top, one from here that are active, but top one from here. So here, we are writing a while statement, but we are missing the incremental logic. The increment should happen here. Delete top one from users where is at equal to 1. So the missing statement is, like, we need to increment that value, the count star. Count star from when delete. So we are begin and end. Yeah. If the count star is 0, then it should come to the it should stop the while loop. So we need to take a count of the users table here after deleting. And then, increment here. I need to check that actually. So basically to rectify it, we need to add a incrementing logic here. That is what is coming to my mind now.

End to end pipeline including error handling and data point of checks. Okay, so end to end pipeline. For example, first of all, we need to see the source and target. What is your source and target? We'll create the source and target destinations. Source connection and target connection destinations. Now, for example, you have created a control flow and data flow. So for example, in a control flow, you are doing some looping activity. Loop, for example, you're looping some files from a folder or from SFTP FTP folders. So, for example, there are multiple files. You're looping from the FTP folder and fetching them on an incremental basis. And then you're downloading them to a local path and then processing them based on the order of the sequence of the files. In that case, we'll add a four-hour container. Wherein we'll fetch the file from the SFTP. We'll drive the FTP task, and we'll fetch the file from the FTP. Whatever files are downloaded, we'll add another for loop, which will loop the files from that particular folder. Now, here, we'll do like this: if there is a failure, it should not fill the package. So we will propagate on propagate equal to true, wherein it will skip the error and go to the next file. For example, if there are 10 files, it processed the first five files, and the 6th file has some issue in the data quality. So what it will do is it will log that particular file records into one file or some table, which we can do using a data flow. And then it will continue with the next, 6th, 7th, 8th, and so on. Like this, we'll do error handling on that. After it completes the package execution, it will send an email alert that out of 10 files, these many files were successful, and out of them, one file was not successful. Please find the attached error records. So in that way, we can do the data quality checks and error handling.

Okay. So for example, in my current experience, we had a value-added apps, data database, which is like a CVG and Zomato-type order application. Wherein, in the PCs, the application was facing a huge pressure, like, a huge load, with the read and writes going very high and the CPU memory going very high. So, that is specifically on the orders table because on that table, all the orders and your audit are, like, your reporting orders. Like, if a customer wants to check their previous history, it will read from the orders table. If you want to place an order, it will read the order table again. Even the delivery guy, if you want to update anything, it'll read and write into the order table. So that was causing a lot of issues. So in that case, what we did is we checked the table design. They were storing address ID and address in the same table. And, we stored order quantity, order value, the total tax, and how many commissions and all in separate tables. All those columns were stored in separate tables, like, the commission table, your tax table, your order quantity table, and the total order value table. The address field was just the address ID in the orders table and the address text value was stored in the address table. So that moved the data. And, we also partitioned the orders table into range and list partitions. In SQL, we have MySQL. We were using MySQL in that project. The database was hosted in the AWS cloud. So we used range and list partitions in MySQL. We had a brand ID, wherein based on the brand ID and brand value, we partitioned the table based on the ranges. Like, the brand ID had some late ranges, such as 128, and the store location. Brand ID and store location, store ID. Sorry. So, brand ID based on the brand ID, the range and keys were based on the store. So, for example, every store had a store ID. So based on that store ID, for example, 1 to 10 stores will be stored in the partition range of 1. The 2nd partition will store from 11 to 20, like that. So, in that case, whenever there is a read and write happening, it will go to that particular partition and it will not search in the whole table. So, that helped the performance of our application.

So approach to documenting SQL code and database design, firstly, we use the Azure DevOps Git repository to store the SQL codes. And for the database design, we like to create a visual diagram, before starting any new development, with the high-level document and the in-depth document. Like, the high-level document is based on the requirement, specification which we get from the client. So based on that, we create a high-level document. And then we have a call with our architect. And then based on the discussion, we create a detailed data flow diagram. So in that, for example, in the video MSV geo, we create the data flows. We determine the source, how the source will be flowing. We also define the steps, error handling, email notifications, and the transformation. It's a detailed diagram. Based on that, we create the ADL packages and the SQL queries for the databases. So based on the visual diagram or the diagram for a new database, we create the diagram. Based on that, I document my SQL code and the database designs.