I went through 80+ data engineering jobs and here's what I found 🧵

1.  SQL is the top in-demand skill.

2.  Python leads as the most popular programming language, followed by Scala.

3.  AWS is the preferred cloud platform followed by GCP, with Azure requested by only a few companies.

4.  Top keywords for data engineering roles include ETL, Data Warehousing, and Data Modeling.

5. Airflow is the only orchestration tool that companies are looking for.

6.  Snowflake is the most popular data warehousing solution, followed by Redshift and BigQuery.

7.  Kafka is the most in-demand messaging queue (Asked as a stream processing framework in some jobs)

8.  Hadoop and Data Lake are other frequently mentioned keywords in job descriptions.

9.  Terraform is the only IaaC tool demanded.

10. K8s and Docker are mentioned as good-to-haves in some jobs.

11.  Postgres is the preferred SQL database, while Cassandra and MongoDB are the preferred NoSQL databases. (mentioned in a few jobs)

Note:
• I found these jobs with a simple Google search of:
site:lever.co "data engineer" OR "junior data engineer"
• The experience ranges from 2-5 years.

Now, guess what'll be the tech stack for my next data engineering project? 😉

You can read the unrolled version of this thread here: https://typefully.com/itsadityagupta/HAL8vGv

Insights from 80+ Data Engineering Job Openings

YARN stands for Yet Another Resource Manager.

It performs resource management, along with job scheduling and monitoring.

The idea is to have a global resource manager (RM) and a per-application ApplicationMaster (AM).

ResourceManager and NodeManager form the data-computation framework.

RM is responsible for allocating resources among all the apps, and NodeManager is the per-machine framework agent that is responsible for monitoring resource usage and reporting them to RM.

The per-application ApplicationMaster is a framework-specific library and is tasked with negotiating resources from the ResourceManager and working with the NodeManager(s) to execute and monitor the tasks.

RM has 2 main components: Scheduler and ApplicationsManager

Scheduler only allocates resources to various running apps.

It provides no guarantees to restart failed tasks and doesn't monitor or track the status of the apps.

We have Capacity and Fair Scheduler as of now.

The ApplicationsManager is responsible for accepting job-submissions, negotiating the first container for executing the application-specific ApplicationMaster and provides the service for restarting the ApplicationMaster container on failure.

The per-application ApplicationMaster has the responsibility of negotiating appropriate resource containers from the Scheduler, tracking their status and monitoring for progress.

References:

- https://hadoop.apache.org/docs/stable/hadoop-yarn/hadoop-yarn-site/YARN.html

- https://stackoverflow.com/questions/30967247/difference-between-application-manager-and-application-master-in-yarn

Thanks for reading! That's it for today 👋

You can read the unrolled version of this thread here: https://typefully.com/itsadityagupta/YFmqpPT

YARN and its Architecture

Replica Placement and Replica Selection in HDFS 🧵 

1️⃣ Replica Placement in HDFS

HDFS clusters run on large no of computers spread across multiple racks. Network bandwidth between nodes in the same rack is greater than the bandwidth between the nodes in different racks.

Hence, a replica placement policy is rack-aware in HDFS.

This is done to improve data reliability, availability and network bandwidth utilization.

The Namenode determines the rack id each Datanode belongs to via the processed called "Hadoop Rack Awareness".

For default replication factor of 3, the replication policy is:

If the writer is on the datanode, place the replica in that same machine. Otherwise, place it in any random datanode in the same rack as that of the writer.

Now place another one in a node on a different (remote) rack and the last replica on a different node in the same remote rack.

This policy cuts the inter-rack write traffic which generally improves write performance without impacting data reliability and availability guarantees.

If the replication factor is greater than 3, the placement of the 4th and following replicas are determined randomly while keeping the number of replicas per rack below the upper limit (which is basically (replicas - 1) / racks + 2).

Because the NameNode does not allow DataNodes to have multiple replicas of the same block, maximum number of replicas created is the total number of DataNodes at that time.

(This is the default policy and along with this, HDFS supports 4 more Block Placement Policies)

2️⃣ Replica Selection in HDFS

To minimize global bandwidth consumption and read latency, HDFS tries to satisfy a read request from a replica that is closest to the reader. If a replica exists on the same rack as the reader node, then that is preferred to satisfy the read request.

If an HDFS cluster spans multiple data centres, then a replica that is resident in the local data centre is preferred over any remote replica.

That's it for today! Thanks for reading 👋 

You can read the unrolled version of this thread here: https://typefully.com/itsadityagupta/IsXGoXE

Replica Placement and Selection in HDFS

Let's understand Hadoop Distributed File System (HDFS).

1️⃣ What is HDFS?
2️⃣ Assumptions and Goals of HDFS.
3️⃣ Namenode & Datanode.
4️⃣ Data Replication.

🧵 

1️⃣ What is HDFS?

It is one of the core Hadoop components for distributed storage.

It is highly fault-tolerant and is designed to be deployed on low-cost hardware, providing high throughput access to application data and is suitable for applications that have large data sets.

2️⃣ Assumptions and Goals of HDFS

⭐ Hardware failure is the norm rather than the exception.

Any running HDFS servers can go down at any time, making it an essential goal for HDFS to be fault tolerant and recover automatically and quickly.

⭐ Applications running on HDFS need streaming access to the data

HDFS is designed more for batch processing rather than interactive use by users. 

The emphasis is on the high throughput of data access rather than the low latency of data access.

⭐ Applications running on HDFS have large data sets.

It should provide high aggregate data bandwidth and scale to hundreds of nodes in a single cluster. 

It should support tens of millions of files in a single instance.

⭐ HDFS applications need a write-once-read-many access model for files

A file once created, written, and closed need not be changed except for appends and truncates. 

This simplifies data coherency issues and enables high throughput data access.

⭐ Moving Computation is Cheaper than Moving Data

It is often better to migrate the computation closer to where the data is located rather than moving the data to where the application is running.

This minimizes network congestion and increases overall throughput of the system.

⭐ Portability Across Heterogeneous Hardware and Software Platforms

HDFS has been designed to be easily portable from one platform to another. 

This facilitates the widespread adoption of HDFS as a platform of choice for a large set of applications.

3️⃣ Namenode and Datanodes

HDFS follows a master/slave architecture where a master (namenode) manages the slaves (datanode).

There is 1 namenode per HDFS cluster and usually, 1 datanode per node in the HDFS cluster.

Namenode manages the file system namespace and regulates access to files by clients.

Datanode manages the storage for the nodes that they run on.

Internally, each file in HDFS is split into blocks and these blocks are stored in a set of datanodes.

Namenode determines this mapping of blocks to the datanode. 

Datanodes serve read/write requests from the file system's client.

Datanodes also perform block creation/deletion and replication upon instruction from namenode.

Architecture is designed in such a way that any user data will never flow through namenode.

4️⃣ Data Replication

Each file is stored as a sequence of blocks and these blocks are replicated across the datanodes for fault tolerance. 

The default size of a block is 128 MB. (Hadoop 2.0+)

Both, the replication factor and the block size can be configured per file.

Namenode makes all decisions regarding the replication of blocks. It periodically receives heartbeats and blockreports from datanodes (default = 3 sec).

Here's a screenshot of a blockreport:

If heartbeats from a datanode are not received for up to 10 mins (by default) to the namenode, that datanode is assumed to be dead.

In such a case, namenode will remove the record of the dead datanode, leading to under-replicated blocks...

These blocks will be replicated in existing datanodes even if they already have that block, to maintain the replication factor for the block.

→ References:

• Apache Hadoop documentation: https://hadoop.apache.org/docs/current/hadoop-project-dist/hadoop-hdfs/HdfsDesign.html

• TrendyTech's Youtube Playlist: https://www.youtube.com/playlist?list=PLtgiThe4j67pWBYoV4PYbGc2MjGGPg2xo

That's it for today!

Here are a few questions that still remain:

How are the blocks assigned to the datanodes? and in what sequence are they written into them? What if the block gets corrupted?

Let's answer these questions in future threads.

Thanks for reading! 👋 

You can read the unrolled version of this thread here: https://typefully.com/itsadityagupta/35bAdZ7

HDFS Fundamentals

In my previous thread, I discussed ETL and ELT pipelines.

Today, let's see 2 variations of Loading the data:

• Full Load
• Incremental Load

🧵 

⭐ Full Load

When all the data coming in from the source systems is added to a data warehouse, it is a full load.

This is generally done for the first time after the data warehouse is created (initial load) or as per the use case.

⭐ Incremental Load

In this, we update/add only the new information from the source systems to a data warehouse.

Some of the incremental load patterns are:

1️⃣ Append

To append all the new information to a data warehouse, without modifying anything.

2️⃣ In-place update

Making changes to the existing data.

3️⃣ Rolling append

Maintaining a certain duration of history. 
Example: Past 3 years of the employment history of employees.

Incremental load can be performed in Streaming and Batches.

Streaming Incremental Load can be used for loading small volume of data.

Batch Incremental Load can be used for loading large volume of data at once.

Both full and incremental load approaches can be implemented by any data loading tool like Spark, SSIS, Stitch etc.

That's it for today. 👋 Thanks for reading!

You can read the unrolled version of this thread here: https://typefully.com/itsadityagupta/imBdVp1

2 ways to load data in a data pipeline

1/15...Data Pipelines are traditionally built with ETL design. But with rising of the modern data stack, new data pipelines are moving towards  ELT design.

Let's understand the E, L & T steps, the 2 designs and everything in between: 🧵

2/15...⭐ Extract:

• Fetch data from different data sources.
• It can be from APIs, sensors, events, databases, raw files or any other source that generates data.
• Can be done in batches/streams.

3/15...⭐ Transform:

• Process the extracted data from different sources and make it ready for analytics.
• The goal is to clean the data of any junk/invalid values and to maintain data uniformity.
• Some common transformation models are:

4/15...a. Data Value Unification:

Transform data values from different sources to a single set of values. Example: One source can send city names as codes and the other can send them as the full name of the city.

5/15...b. Data Type and Size Unification:

Transform data types coming from different sources into one data type. Example: Age can come as an int from one source and a string from the other source.

6/15...c. Deduplication:

Remove duplicates from the data coming in from multiple sources.

d. Dropping Columns (Vertical Slicing) :

Removing columns from the data if not required.

7/15...e. Value Based Row-Filtering (Horizontal Scaling):

Based on some business rules, we can filter rows of data for some column values.

f. Correcting known errors:

Correcting known issues and inconsistencies in data.

8/15...⭐ Load:

• Loading the data in a data warehouse or a data mart where it can be used for analytics/BI/ML by the end users.

9/15...I hope you understood the 3 steps in a data pipeline. Let's look at the ELT design pattern now.


10/15...As explained in the image above:

Data is extracted and transformations are done on top of it. Finally, the transformed data is loaded into the final destination.

But a few things to note here:

11/15...1️⃣ Data is stored in a "Staging" layer before any transformations are done on it.

The staging layer can be persistent (new data is added here as and when received) or non-persistent (old data is removed and new data is added every time transformations are done)

12/15...2️⃣ Transformation and loading need to be performed in the same run as the data is stored in a temp staging layer after extraction.

Moving towards ELT design:


13/15...Here, we load data after extraction and perform transformations on it.

1️⃣ There is no staging layer here as the data is directly loaded to the "user-access" layer. (where end-users can access the data)

This is where data lakes can be used for loading the extracted data

14/15...2️⃣ The loading and transformations can be done independently of each other in separate runs.

As with the maturity of the modern data stack, we are seeing a rise in specialized tools for each of the 3 steps in a pipeline.

Ex: Fivetran, Stitch for E and L; dbt for T, etc.

15/15...PS:

Staging Layer is just a name given to a temp warehouse or a data mart that stores intermediate data.

User-Access Layer is a lake/warehouse/mart from where end-users can access the data.

That's it for today! Thanks for reading. 👋 

You can read the unrolled version of this thread here:
https://typefully.com/itsadityagupta/GA74s1q

ETL/ELT pattern in data pipelines

Generally, we learn about Data Warehouses then Data Marts and finally Data lakes. 

I think this order is messed up. 

Here’s how I think about it: 🧵

⭐Let’s start with Data Lakes (DL): 

• It’s a place to store all the data you want irrespective of its source, format or anything

• It’s schema-on-read i.e. we don’t worry about the data format unless we read from it.

• Teams can fetch data from here as per their use cases.

• It’s cost-effective as compared to Databases and Data Warehouses. 

• It gives us the most flexibility to store the data. 

• Some examples of data lakes are Amazon S3, Google Cloud Storage (GCS) and Hadoop Distributed File System (HDFS).

⭐ Then what’s a Data Warehouse?

• It contains structured data ready for analytics to gain business insights. (OLAP)

• It’s the central storage of structured data across business domains.

• It’s used for cross-domain analysis and reporting. 

• Data is never moved, always copied to a data warehouse from multiple sources.

• Every DW has to satisfy the following 4 rules:

   ・ Integrated: Contains data from multiple sources.

   ・ Subject-Oriented: The data should be organized by the subject/domain.

   ・ Time Variant: It contains historical data along with the current data. 

   ・ Non-Volatility: Data should not change in a DW except when new data is ingested.

• A DW is schema-on-write i.e. we need to be aware of the schema before writing data into a DW.

• Some examples of a data warehouse are Amazon Redshift and Google BigQuery.

⭐ Finally, Data Marts! 

• It’s similar to DW but focuses on one business domain only. It can be a subset of a DW as well.

• It is also schema-on-write and stores structured data. 

• It is of 2 types: 

   ・ Independent Data Mart: When it gets data directly from the source systems.

   ・ Dependent Data Mart: When it gets data from a data warehouse.

You can understand all 3 of them from the below image:

That's it for today! Thanks for reading 👋 

You can read the unrolled version of this thread here: https://typefully.com/itsadityagupta/SVbDgUM

Data Lakes, Data Warehouses and Data Marts

💡 Indexes in SQL

• Indexes are generally B-Trees that store the keys that help in fetching the records easier without the entire table scan.

• Indexes are of 2 types: Clustered and Non-Clustered

1️⃣ Clustered Index

• This index sorts the data in the actual table based on the values, hence reducing search times.

• Only 1 clustered index can be created for each table as the rows can be sorted only in one order

2️⃣ Non-Clustered Index

• These indexes have a separate structure (lookup table) to store the key as well as the pointer to the actual row in the table as values.
• Hence, 2 reads are required here: lookup + table
• We can have more than 1 non-clustered index.

⭐ Clustered and Non-Clustered indexes are created by default as soon as PRIMARY KEY or UNIQUE constraints are applied.

⭐ For the PRIMARY KEY constraint, a clustered index is created. Whereas for a UNIQUE constraint, a non-clustered index is created.

⭐ Seek operation is performed on queries using an index i.e. only the required rows will be read.

⭐ Otherwise, a Scan operation will be performed i.e. all the rows of a table will be scanned and results will be fetched.

Indexes in SQL

What is Predicate Pushdown in SQL? 🤔 

Predicates are statements that result in True or False.
In SQL, you can think of WHERE/HAVING as a predicate.

Now, let's see what is the predicate pushdown 🧵 

Consider a schema as shown in the image.

We want to find the total_amount spent by customers whose name starts with 'A'.

So, here's the query along with its execution plan:

As you can see, the WHERE clause is executed as the first step, while reading the customers from the disk. Even before the JOIN.

This helps in reducing the amount of data to be processed at a later stage of the query execution.

Let's take another example.

We want to find the total_amount of customers having odd customer ids.

Here's the query and the execution plan for the same:

Even though HAVING is used to perform filtering after the aggregation, SQL is smart enough to realize that filtering the records before the aggregation will not be a problem in this case. 

Hence, the records are filtered while reading from the disk.

However, if you have a condition on aggregation like sum(total_amount) > 50 or likewise, the filtering will be done after the aggregation.

I hope you get the idea. Let's put it more formally.

Predicate Pushdown is a behaviour according to which certain predicates/filters in a SQL query can be performed as early as possible so that further processing can be reduced.

That's it for predicate pushdowns!

💡 Bonus Question:

If you look at the execution plan of the first query, you'll find that it is sorting data on cust_id before aggregation even when we're not using GROUP BY.

Can you find out why? ⚡ 

That's it for today! 👋  I hope you learned something new. 

Thanks!

You can read the unrolled version of this thread here: https://typefully.com/itsadityagupta/or9f2Tw

Predicate Pushdown in SQL

According to a 2018 poll by @stripe, $85 billion per year are spent on dealing with "bad code".

Bad quality incurs a technical debt every time the code is altered. And this debt does not stay intangible for long.

Here are 5 things about code quality that you should know!
🧵

1️⃣ Expressing empathy to the reader.

Writing high-quality code is expressing empathy to the readers.

Thinking about how our code affects humans is a powerful form of empathy in practice, one that no organization can afford to ignore.

2️⃣ Software Quality Attributes

⭐ External: such as UI
⭐ Internal: architecture

External quality enhances user experience.

Internal quality enhances the software.

3️⃣ Low internal quality

Low internal quality can start with high productivity but eventually slow it down.

4️⃣ High Internal Quality

High internal quality poses an initial cost but accelerates productivity moving forward.

5️⃣ Cruft

Cruft is the difference between the current code and how it would ideally be.

More cruft 🡪 Less productivity.
Less cruft 🡪 More productivity.

This thread is highly inspired by below articles:
• https://martinfowler.com/articles/is-quality-worth-cost.html
• https://stackoverflow.blog/2021/10/18/code-quality-a-concern-for-businesses-bottom-lines-and-empathetic-programmers/

You can read the unrolled version of this thread here:
https://typefully.com/u/itsadityagupta/t/T76R7A8N57Kd

➼ TL;DR

1️⃣ High code quality shows empathy for the reader.
2️⃣ Software Quality Attributes
3️⃣ Low Internal Quality means low productivity.
4️⃣ High Internal quality means high productivity but comes with an initial cost.
5️⃣ Cruft: Diff. b/w the ideal code and current code.