Madhan Lingareddy

So, I've been a data engineer. I started as a BI developer in my first year. I worked with tools like Tableau, Incorta, and data modeling. From there, I switched to become a data engineer. I started working with Python, SQL, and many relational databases, for example, Oracle, PostgreSQL, and MySQL. I began building data warehouses, most of which are also on Oracle, and some are file-based and others on cloud-based systems like AWS and GCP. Throughout my career, I've experienced working with multiple tools, multiple ETL tools, like Informatica and Pentaho. Recently, I've been experimenting with AWS Glue. I've also worked with PySpark, which was my first introduction to big data. So, I have good hands-on experience with PySpark. I've installed and maintained clusters for our organization. I've also worked with Google Cloud, where I experimented or worked hands-on with a data warehouse like BigQuery. I built data workflows using Airflow, maintained them, and helped clients build their own data ecosystem, showing them how to take advantage of in-house data and build their own analytics. Throughout my career, I've also worked with various BI tools like QuickSight, Google Data Studio, Tableau, Power BI, and Looker. That's pretty much it.

Alright, how would you design a system to monitor the health and performance of an ETL pipeline crossing daily data bytes of data? So in my latest experience, I work with an organization that deals with payments. So obviously, the data is huge, in petabyte scales somehow. And we use AWS Glue as our ETL tool, which can process large volumes of data depending on the configuration we provide it with, but it cannot scale. So that's our major detail tool. Our scale storage is on S3, which is also scalable, so there is no physical limit to it. We build workflows and we monitor everything through CloudWatch logs. We set up alerts using SNS topics that ping us directly on our team's channel. We do occasional monitoring and performance tuning, and we also implement data validation checks when we're not getting a key column populated or if a foreign key is not available under the table. So we do these kinds of checks if the large volume of data doesn't have data in it, like a column is coming up as blank for most of the records. We do automatic checks, and we ping either our Slack or Teams channels, and everything is automated. So it's all integrated. Also, the way we deploy things is also automated using GitLab CICD pipelines. So that's pretty much it. And we maintain most of our infrastructure on serverless code, which is easy to deploy and maintain.

Data consistency. Again, we get our data from our RDS instance, which we don't query the actual system, the actual production system, we touch the read replica. So we tend to do our checks, our validations against that. And we build a data lake on S3, as I said before. And we do this using an open-source format called Apache Hudi, which supports ACID transactions by managing a log-based file system. And also, we build an aggregated end-presentation layer. It's like three different layers of warehouses that we maintain. One, an exact copy of prod, and the second being an aggregated layer, which contains a denormalized version of tables and also an aggregated version of tables. And from there, we pick these tables up and build a final layer called the presentation layer, which most of the reporting and the end users use. And every step of this journey from the source to the presentation of the reporting layer that we build, we do validation checks, we do performance tuning, and we continuously monitor and make changes depending on what can be changed and what can be tweaked to achieve better performance and also better trust in the data. And yeah, I think that's it.

Tweaking a JSON data to load it into a relational database. A relational database is structured, so it has to have a fixed set of columns. And, you insert each row with all the values inside thereof. When you save JSON data, it can be semi-structured. So let's say a record comes with 3 different keys and the secondary card comes with 4 different keys. Our final goal is to produce a row that contains a dataset with 4 different columns. And wherever the missing key is there, we populate a blank or another. And in my experience, we can either do this with Python just using pandas. We use the JSON underscore normalize function, which will just split or explode all these JSON fields into a separate column. Or we can use PySpark to achieve the same effect. So we explore it, and then we just union depending on our schema based on how many columns we have. If the column is missing, we populate it with a blank, and then we dump it to JDBC. And there can be cases where your source dataset could contain more columns than you have on your final relational database. In those cases, we need to alter our final table, our target table a bit to accommodate these new columns or drop columns that are coming in.

So how do you optimize data storage and relational database for data intensive application? First off, if we can avoid using the relational database for data intensive application, at least for analytics purposes, we try to use warehouses like BigQuery or Snowflake and such tools. And if not, we have to understand the requirements here. Like, let's say an application is querying based on a particular key column. It's ideal to cluster or partition based on this column and also build indexes on these columns that can fasten up the querying on this. We're working with data warehouses, which support partitioning, clustering, these sorts of things. It's ideal to partition based on the column that you're filtering on or aggregating on. And you can sort or cluster the database on the key that you tend to join with. And these kinds of situations, clustering, partitioning, or building indexes, these are the things that come to mind.

Migrate an existing data process from an on-premise Hadoop cluster to BigQuery. The first step would be to export all existing data on your Hadoop system to a cloud storage that is scalable. So, that's Google Cloud Storage. You can write a Spark application that reads from Hadoop and pushes it to S3, either in CSV or Parquet format, preferably Parquet. And then there's the option of reading from external tables in BigQuery, which means you can directly connect to GCS, read the data from there, and sync it to an external table. Or you can even build a native table that keeps its storage on the BigQuery layer. Once the initial load is done, processing incremental data is going to be easy. So, you just have to make the connections. There's the concept of a collection of connections, which can connect to your relational databases or file systems and use that to query in SQL style to bring the incremental data in and merge it with your final native table. So, and from there, it's going to be easy. You either build scripts or you can just use a tool like Spark or even Airflow to run these incremental loads for you.

The only issue that I see here is using a generic exception SE, which is very generic. So we don't really know what's happening in there. It's always better to handle specific exceptions before going to a default exception case. So that's what I would change. Yeah. I think that's it. And a better user-readable error message before actually raising the issue, which just generally says data loading failed. We may want to give a better error-case scenario. So, deal with specific exceptions and write messages that are easily understandable, which also comes with the traceback, and then go to the very default scenario.

The thumb function is supposed to have seen data from the database. Alright. Alright. Looking at this, one thing I thought I would tweak is instead of using a while loop through, maybe we can use a while cursor dot next, which tells us if there is another one, then process it instead of just entering the loop and then fetching one, in which case at the end of the data, when the data is completely being processed out, we will still hit an error saying, you know, here is the diagnostic data, the next row does not exist.

On BigQuery, the data is pretty spread out, but also we need to understand if we're doing incremental loads based on the key that we are updating, is it really clustered on that column? Because that may prove very valuable when doing updates. And it's always better to partition based on a particular date column. Let's say we are pulling the incremental data based on an updated it or a created it column. It's ideal to partition our table based on this, which means that when updating or even when querying your data, the queries you run don't actually process the entire table, but only the partitions that you need to update. And if the table is too big, we can create cluster tables, and we can see if we can run parallel queries to update the table. Filter the data before we are processing it and select only what you need. Aggregate before joining if possible. Filter before joining if possible.

First up, I would understand what the requirements are here. Like, what is the source? What is the target? And is it okay to go with functional programming? Do I need to implement object-oriented programming here? And then, I'll start writing function classes, and I'll build the code for reusability. And I build the code for incremental and full loads for flexibility. I'll give enough room for user input but not clutter it so that we can tweak the code for specific use cases. And then, I'll use the docstrings. I'll use type hinting. I'll implement unit test cases, include data validation scenarios, and add enough comments to understand what's really happening inside. I'll push the code, get it reviewed, and then deploy it. And since we are working with cloud systems most of the time, it's also ideal to throw in a serverless or Terraform file that deploys stuff for you. And probably a shell script that automates things or easier to build your CI/CD pipeline on GitLab. So stuff like that.

Yeah. So that's an important one, I believe. So, in AWS, we use Blue Catalog combined with Lake Formation to manage the data governance most of the time. It's pretty easy there. So, we build roles around users and assign roles to these users. And then based on which role a user is assigned, they are able to access a specific set of data. In here, we can select either the databases, the tables. You can filter the data that they can access. You can select the columns that they can access. It's already visual the way that it is done on Lake Formation. But if we have to understand that there can be sensitive data that you don't want to expose, all this can be taken into consideration. You might want to do data checks. You may want to do key column comparisons. Make sure the data is trustworthy before we make it open to the end users, and write down the test cases, monitor, get user confirmation before things are deployed, and they will have access to. And thorough testing, everything comes into play.