Priyanshu Gandhi

Yeah. Hi. This is Priyanshu. So I have been in the data engineering industry for around four plus years now. Primarily, I have played a role of data engineer where I have worked on optimizing the pipelines, maintaining the existing pipelines. I have been part of large-scale data migration programs as well. Currently, I'm working at Groupon. At Groupon, we have a petabyte-scale warehouse, which is kind of spread across the GCV infrastructure, orchestrated by Airflow, and we have a data warehouse managed on BigQuery as well as Teradata. I am responsible for managing this petabyte-scale data warehouse as part of my day-to-day job. It revolves around optimizing my current pipelines that we have. We have a huge amount of data coming in from our SO systems, and I am responsible for making sure the pipelines are meeting their service level. Previously, as part of LDR Main Tree, Mindtree, at LTI, Mindtree, my primary role was around product development as well as data engineering. As a data engineer, I was responsible for designing and modeling the customer's target systems on the cloud. So we were essentially responsible for doing a lot of large-scale migrations for different customers across banking and financial industries, advertising, travel, and other industries. And then, we would essentially go about their complete system, understand the legacy, classify all things, and then create a roadmap for them to plan the full migrations across the cloud. Once that was done, we would lay the foundation for the migrations to the complete migration, starting from migrating objects, you know, the data, validating the data, making sure all aspects were taken care of. And once that was done, the most important part was around governance. So I was also responsible for helping multiple clients across governance, making sure that the queries were running as expected, not running into high costs, and the data quality was in control. So these are some of the primary roles I played across as part of my career.

Okay, so the way I understand this question is that if I have to create a system which can essentially take the data, use Hadoop and Hive for processing that particular data, right. So I would assume that we would have this data coming in from multiple source systems. The first and foremost thing while designing an ETL pipeline, which is essentially based on Hadoop or Hive infrastructure, we have to make sure that whatever data we are getting from sources, we are splitting that data well, right. So we don't want to make sure that we have cases where we are ingesting all the data at once and then loading it into a target table. That would be a totally misconfigured ETL system. I think this particular ETL system would involve during the extraction step, making sure that data is broken down into smaller components, which you could call partitions. And once that is done, during the transformation steps, you could store it into Hadoop. For transformation, you can use Hive queries. You can apply queries for analytics purposes, making sure you use the right partitions within your query, optimize your queries, and use the right kind of joins. And once that is done, you are giving the end result to your users. Again, this could be orchestrated through the Hadoop infrastructure itself. You could set up your orchestration service using any of the orchestration tools available, something like Airflow or a regular scheduler as well, right. So this is what I think an ideal pipeline would look like while we have a backend or infrastructure supported by Hadoop at Hive.

And the performance of the ETL pipeline. Okay. I mean, if I had to design a system that would essentially monitor the health and performance of my ideal pipeline. And I think the first thing we would need to decide is what metrics we're measuring for. So, for example, are we worried about throughput? Are we worried about latency? What are the things that we think about? And once we've decided on the metrics we're essentially worried about, the next step would be to make sure that we have implemented a system that can monitor the whole ETL pipeline. So, this could be something like a logging framework or integration with a monitoring tool. It just makes sure that we're monitoring the ETL pipeline because it can be a case where the pipelines might not be performing well. The data might be skewed. Your queries might be running longer. So, we do need to have a system that takes care of the monitoring of the pipeline. Once that's done, we should have a lot of audit checks on our system, which could make sure that we keep track of how much data we're processing. Is it beyond a certain range of data that we see every day? So, this can be achieved by having the logs from the ETL and making sure that our data quality checks are placed on top of those logs. Once that's done, you can set up an alerting-based mechanism to alert the user about this particular pipeline has reached a particular SLA or this particular pipeline has exceeded the amount of data that it sees through the particular platform. And then, I think the last step would be to make sure that we have a good security built around this particular system. When I say security, it might be the case where we are handling very sensitive data. So, you should make sure that we have a role-level security implemented for this particular database, and we make sure that we are masking the fields correctly. So, I think once we've taken care of all of these steps within our pipeline, we'll be on the right track.

and data quality. Okay, got it. So I think to make sure that we have data integrity and data quality throughout the ETL process, I think this is something that I would have covered in my previous steps as well. What you should make sure is that within your ETL pipeline, you have regulated checks after given transformation, right. So for example, let's say given an example where you get the data from a source, you apply some transformation, you load it to some dimension tables, and then you finally get the data to a final fact table, which could be essentially used to calculate a metric and the metric could be something like, you know, you're calculating the sales for every day, right? Now, what could happen is, you know, there are cases where you would essentially see more traffic, or there are cases where you would essentially find some things which you should not get into your data, right? So how do you make sure that you detect those patterns? And I think that's where your data quality checks come into picture. What you should do is once you have the data in your final target table, right, you should have different types of checks defined on the table. For example, a check could be around nulls, right? You should make sure that some business keys do not have a null value, right? That should not violate business rules. Some checks could be the number of counts that you see, right? Your count, for example, if you expect to receive sales for every day, right, your count should never go down or drop below a particular level, right? So you could essentially take a metric, an average metric that you define, that your average expected sales for a given particular order or for a particular day would be in this particular range, let's say 100k, right? So you define a check whether your count for that particular day is greater than that or within that particular range. Or you could also check whether the data you're getting in the final check is not zero, right? So that's at certain times what happens is there's some problem with the process and you don't get the data loaded into the final table. So this could be avoided by having all the data quality checks built in as part of your data pipeline, right. That makes sure you're covering your entire pipeline from start to end.

Data storage, okay. What I would say for this one. I mean, again, if I broadly think about this, if I'm talking about a relational database and I would have to optimize the data storage, right. One thing to make sure is you avoid a lot of data duplication. We see in relational databases, we might see a lot of redundant data stored within a particular table, right. So one way of doing that is applying normalization principles. You make sure that you avoid redundant data stored in a table, using multiple normalization methodologies, right. The next way I would think about making sure I optimize my storage is having proper indexes defined in my tables. I think that is one very important thing. Many developers miss out on defining an index because it could be essentially used for speeding up query retrieval, right. That I think becomes an important phenomenon. I think one more important phenomenon is, you know, making sure you are compressing the data while you are storing. Many relational database systems offer a feature where you can compress the data while you store it, right. The next thing that I can also think of is, you know, a lot of times, we don't need to store all the data in one system, right. What we can do is understand that there is some amount of data that is historical and that could be moved to an archival system, which would only be required when it is needed and there I think the latency could be less. So I think we would go into those kinds of first stages. I think these are a few steps I think on a high level I can take when I think of optimizing my data storage.

I would say that I have not worked much on the post request SQL. But, again, if I had to define, like, design a post request SQL for an optimal query. Right? I think one important thing here also becomes making sure that I avoid a lot of deduplication in the data. Again, I think repeating my earlier point, I think I make sure that I apply proper indexes, because that kind of ensures that I get the query that we will, you know, faster. I also understand what are the query patterns that I would be getting. Like, what are the frequently executing queries that would be fired on my database. Based on it, I should be able to decide what should be my indexes, across the table. Right? Also, I think one more important thing that, you know, you will not come into a very important category, but I think one thing that needs to be considered while designing the system is making sure to define the proper data type of the columns. A lot of time, we kind of make sure that we, when we're declaring that, we cannot declare it up to very high value. I think that doesn't make sense, so we should essentially define the right data types while choosing the database. Also, very important is to make sure that we have the right relationships. When I talk about relationship, I mean in terms of the primary care and foreign key, we should make sure that we define the right foreign key and the primary keys for the relationships between the tables, so that the joins can be optimal. There's also an option of having materialized views on top of the tables so that we essentially don't have to go back to the table to get the data and then apply the whole processing. And materialize, you can store that data, and then we can use that across. Right? So that could be one of the ways to do it.

So, we are essentially taking data from a table, we are creating a cursor connection, executing it while we have a row, we are fetching the first row, if the row is none, we break it, and then we are processing and closing it. I think one way to resolve this particular case is to avoid multiple iterations of opening and closing the cursor. I think this is one thing we should avoid, we could define a global variable that opposes the cursor and use it across the query, so we don't call close and open, which is an additional request on the server, so that could be avoided. The second thing is, once we are getting this stream data from the database, we are firing a select star. I think that's something we could avoid. We should only select the columns that are required for our use case and get the data required. If we have certain identifiers defined that could be used to identify the change data, we would only query the new data from the table rather than querying the complete table and then reading the row one by one. Also, rather than fetching row by row, we should batch all the data stream we are getting from the stream and process it as required rather than getting it one by one. I think these are a few ways that I think we could solve this particular problem.

Looking at this by the include bug in a way to and what might be the potential issue with the way exceptions are being handled. Okay? We are extracting the data, we are transforming the data, we are loading the data. Okay. I think what I see as the potential issue here is why we are logging in. Right? They'll do a fail due to this. Yeah. I think the way we are essentially handling this exception is the issue. What we do is whenever we see a failure within the pipeline, right, we are logging it with the logs log system, but we are also raising the custom exception in this particular case. So I think that could be avoided. I think what we could do here is have a function within that exception that calls the SMS service, which notifies the user about that particular block of code has failed. That would give an idea about what could've been done. Also, I think one problem I see with this is the particular function logic, right, it's very hard to detect at which particular step we had the failure. Right? So, we understand the retail job failed, but where did it fail? Did it fail at the extract step? Did it fail at the transform step? Did it fail at the load data step? Right? So I think that's not an ideal way. Ideally, what we should have is within each function, right, we would have a try-catch block, which could gather the exception. That way, we would understand from a deeper point of view, it would be easier for us to understand whether the exact step failed at when you need to see what's the problem there or the transform step has failed or the loading has failed. Right? So I think these two things are something that could be avoided with this, and I think that should work in the next step.

How would you employ Python to programmatically enforce asset properties on non-transactional data stores? Okay. To use Python for asset properties. This is a bit trickier. I've kind of not faced this scenario. Okay. I'll just probably think around it is that too, we have non-transactional data. Asset properties or transactional data still make sense. Having asset properties define non-transactional data. No. I think, again, in my view, implementing asset properties on a non-transactional data is something that looks a bit weird. Like, you generally don't kind of have these kind of use cases. But if I would have to think at a very high level, if something that comes to my mind that I could do is, for example, in a case where I have to handle atomicity, making sure that my transactions completely occur or they don't occur at all, I think we can use logs, basically, in Python, to make sure that the logs are defined on a certain function or data parameters that only when this kind of occurs, then only this completes. Otherwise, it just adds a lock. Right? If I think from a consistency standpoint, I think what we have to make sure is to apply some validation rules or checks to make sure that the data is consistent. From an isolation point of view, I think we can use a multi-processing kind of a library within Python to make sure that you're parallelly processing each block of the code, which is in isolation with each other. And from a durability standpoint, when we make sure that even in case of areas where the system is persistent, I think we write it to a persistent source so that we don't lose it. I think we would employ a similar thing in Python that whenever we are coming out of a function or writing out. We essentially write it to a particular file rather than holding those intermediates, you know, values in a Python variable. Yeah. I mean, this is what I would think about it, but this looks a bit odd for me, this kind of a use case.

We do employ and will then go to play with the SOLID design. It's supposed to handle large complex data. That's the same task. SOLID in Python, right? So, if I look at SOLID at a very high level, right, I think the first part is, you know, making sure that you have the single responsibility principle. Right? So we make sure that every function is doing a particular task. We won't have functions that are doing multiple tasks at a time. So we have that predefined, right, in terms of that. So that is one thing I think we would make sure when you are writing the Python code. In terms of open/closed principle, right, I think what we should make sure is we have this thing which makes sure that your objects are not dependent on each other. Right? So we don't see this particular use case where your classes are basically functions, which can where we can easily add new functions or new functionalities rather than we but also, at the same time, we make sure that we don't allow users to make a lot of modifications to that particular class or function because that doesn't violate those particular principles. Right? So we have to make sure that if any new feature is required, we are able to incorporate within that particular class or function. But it shouldn't be modified much. Right? So that I think we make sure. In terms of the third part of the SOLID principle, which is the Liskov substitution principle. Right? I think the first and foremost thing there is, we make sure that the superclass we have, it should be and the object that we create with that prior you know, the parent class, it should not be essentially replaceable with the objects of the child classes. Right? It should not be a case where we are not able to use the parent class and the child class together. So I think that is where we are essentially able to substitute the parent and child class together. In terms of the fourth principle, which kind of talks about making sure that the interface is accreted, I think the one primary thing we should make within the Python code is that our clients that are using this particular function, right, they should not be forced to depend on the interfaces that they don't use. Right? So, basically, when I talk about interfaces, it could be something like a class or a function. So any entity. The client should not be dependent on the entity that they're not using. Right? So I think that we could avoid. We could essentially break down this entity into more smaller and specific needs so that this could be used by our clients. The last part, I think, where we talk about the dependency inversion. So we should avoid those cases where we have this high-level functions which are essentially dependent on the lower functions. Right? So we should not have cases where we have a broader function, which kind of still uses the results from the low-level functions or within functions across it. Right? So we decouple things across with that.

Yeah, I think this is something that I've worked across for the clients. When we talk about data governance, right, I think there are multiple pillars that we essentially take care of. I think the first pillar is making sure that the data is accurate. So, you make sure that whatever data you're getting into your target systems, right, it is as per the business requirement. It is as per the expected results. So, and in terms of the count, in terms of the values, they are essentially representing what is expected as part of the business process. Right? So that is the first thing. I think the second thing is when we talk about data governance. Right? We also think of it in terms of the overall cost of the system. It should not be a case where we are designing the data, you know, when we have this data. Right? Our queries that we are essentially firing on our systems, it is leading to a cost being very high, so we should have a proper governance around it as well. I think the third part of it is performance. So, you know, a lot of cases a lot of times what happens is when we don't have a proper data governance across the system, right, the queries tend to run very slowly, and you know, it is not performing as expected. Right? And that would essentially lead to your reports being refreshed a bit later. Right? So we would have to design a system which kind of meets all these three pillars, your cost, your data quality, and your performance. In terms of cost, I think you have to make sure you don't violate the principles. Specifically, if I talk about something like a cloud system, Adrian, you should not violate the principles of cloud, you're only processing the data that is required. So, you know, you avoid certain things like select star. You select the columns that are required. You have a proper partition defined on the table. You know? So you make these things, you take care of these things when you're talking about the cost. When you talk about data quality, you make sure you have the right checks. You're checking your counts. You're checking your nulls. You're making sure the values in the data tables are expected. You're capturing your data query patterns and understanding what kind of data patterns you generally query and the results are as expected to those patterns. You keep track of all the min max values that are coming into your data so that you make sure that these are the general values that you see across the systems. So you have those data quality checks applied to your processes. The last is in terms of performance. Right? So, a lot of times, the way you process data is can be a bit trickier. Like, a lot of times, what we do is we do a lot of go by or insert. So we should avoid those kinds of scenarios and have batch level inserts for most processes defined, which is much more optimized than single tenant inserts. Right? So, in terms of queries, we are essentially using the right join strategies other than doing a product join and essentially getting all the data. Right? So, and then applying the right filters based on the partition so that the data is pruned and you have that pruning happening, and you are essentially then seeing the more optimized and better query results as expected.