• Level Up Coding
  • Posts
  • LUC #35: ETL Demystified: From Raw Data to Powerful Insights

LUC #35: ETL Demystified: From Raw Data to Powerful Insights

Plus, how to effectively use Big O in technical interviews, and the TCP handshake explained

This week’s issue brings you:


A big thank you to our partner Postman who keeps this newsletter free to the reader.

You've probably used Postman to send HTTP requests. But did you know you can also send GraphQL, gRPC, WebSocket, and MQTT requests? You can learn more here.

ETL Demystified: From Raw Data to Powerful Insights

Data is the new oil” - Clive Humby.

This has become a common saying in modern times and is particularly relevant in the context of tech-driven businesses.

Data management and business intelligence are growing fields that have huge impacts on businesses. Being able to leverage insights from data helps move companies away from bad decisions and towards good or profitable decisions.

And that’s where the ETL process comes into play.

The ETL process is fundamental in structuring a data pipeline that enables businesses to harvest diverse data, refine it into a practical format, and store it for insightful analysis.

As software engineers in a data-driven world, understanding the nuances of ETL is not just beneficial but essential for architecting robust, scalable, and efficient data systems. This guide breaks down ETL.

The Components of ETL


Extraction is the critical first step in the ETL process. It involves pulling data from various sources. This step determines the quality and scope of the data available for analysis.

Common data sources include databases, APIs, flat files, and more. The diversity of sources adds complexity but also richness to the data.

Some of the key challenges at this initial phase include ensuring data quality, handling large volumes efficiently, and dealing with diverse data formats.


Raw data is rarely in a perfect state. After raw data is extracted from a variety of sources, it undergoes significant refinement into a structured, clean, and enriched state.

This turns the raw data into a valuable asset for analysis.

Tools and techniques involved in this process vary widely, from SQL queries for database operations to Python or Scala scripts for more complex tasks, all the way to utilizing powerful ETL frameworks like Apache Nifi or Talend for handling intricate data structures.

Transforming data can get quite complex, needing to understand data anomalies, encoding schemes, dealing with hierarchical data, unstructured data, and data with incompatible formats.


In the loading phase, the transformed data finds its new home, typically in a data warehouse or database.

This stage typically involves batch loading, where data is moved at set intervals, or real-time streaming for continuous updates. The choice depends on the requirements of the business.

Ensuring data integrity, managing index strategies, and optimizing for quick retrieval are top concerns at this phase.

The ETL Process Flow

The ETL process is a tightly coupled sequence where each step's success influences the next.

Orchestrating this flow efficiently is key to a successful ETL process, involving not just connecting the steps but ensuring they work together optimally.

Consider, for example, a company analyzing customer buying patterns by consolidating sales data from multiple channels. Here, ETL transforms disparate data into a coherent format for strategic analysis, showcasing how a well-orchestrated data flow can drive informed decisions.

ETL Tools and Technologies

The tools and technologies for ETL range from traditional options like Informatica and SSIS to modern, cloud-based services such as AWS Glue and Google Cloud Dataflow.

While traditional tools are known for their robust feature sets and reliability, modern services excel in scalability and ease of infrastructure management.

Cloud-based ETL services also offer the flexibility to scale resources as needed and can be an ideal choice if your company’s infrastructure is already housed with a provider.

However, selecting the right tool involves balancing considerations like data security, transfer costs, scalability, and compatibility with existing systems against your organization's specific needs and data strategy.

Best Practices in ETL

Ensuring data quality from the start is paramount.

For software engineers, this involves implementing rigorous validation rules and checks at each stage.

Performance optimization might mean streamlining data processing by optimizing queries, efficiently managing resources, and utilizing parallel processing where possible.

Security considerations are also paramount, employing robust measures like data encryption, access controls, and secure connections.

Regular testing and validation of the ETL pipeline are critical to ensure it functions correctly and meets the required data quality standards.

ETL is rapidly evolving, embracing challenges and opportunities presented by big data, AI, and machine learning.

There's a noticeable shift towards ELT (Extract, Load, Transform) for certain use cases, particularly in cloud environments.

With ELT transformation occurs within the data warehouse after loading. Modern data warehouses are powerful enough to handle these transformations efficiently. Transforming after loading can often lead to significant benefits including increased performance, scalability, and flexibility, which stem from leveraging modern, high-performance data warehouses.

Meanwhile, the demand for real-time data processing is escalating. Modern ETL systems are adapting to provide immediate data integration and analysis, helping businesses react swiftly to changing conditions.

Wrapping Up

In the information age, data is opportunity.

ETL is a pivotal process for harnessing data.

By understanding and implementing a well-orchestrated ETL process, engineers can empower businesses to make confident, data-driven decisions.

Investing time and resources in properly planning and implementing ETL processes is crucial, as a well-executed strategy not only enhances data handling but also provides a solid foundation for future growth and adaptation.

How to Effectively Use Big O in Technical Interviews (Recap)

  • A few scenarios where Big O can be used: Live coding challenges, code walk-throughs, discussions about projects/solutions you've built, and discussions about your approach to programming & problem-solving.

  • When these scenarios come up, be sure to mention the Big O of your solution and how it compares to alternative approaches. Think out loud.

  • When comparing solutions, pay attention to the problem’s requirements. For example, linear time complexity may be completely fine when the input can never be too large. But if you’re dealing with big data, you’ll want to opt for something more efficient.

  • Of course, the goal is to get the correct Big O notation that applies to your solution. But don't worry about getting it wrong. The point is to show that you are thinking about the efficiency and performance of your solution. Do this and you’ll be able to showcase an important trait that technical hiring managers look for: The ability to consider a solution's viability beyond whether it works or not. This shows maturity in your decision-making and approach to programming.

How Does The TCP Handshake Work? (Recap)

  • Transmission Control Protocol (TCP) is a transport protocol that is used on top of Internet Protocol to ensure reliable transmission of packets. Essentially, it ensures that all the data you send over the internet reaches its destination correctly and in order.

  • For devices on a network to exchange data, a connection must first be established. That's where the TCP handshake comes in.

  • The TCP handshake follows a three-step process to establish a connection:
    1) SYN (Synchronize), 2) SYN-ACK (Synchronize-Acknowledge), 3) ACK (Acknowledge)

  • The TCP handshake uses a flag and sequence number at each step. The flag informs the receiving device of the segment's contents. The sequence number indicates the order of sent data, allowing the receiving end to reassemble data in the correct order.

That wraps up this week’s issue of Level Up Coding’s newsletter!

Join us again next week where we’ll explore architecting high-throughput real-time data pipelines, Event-driven Architecture, principles of object-oriented programming, and how data processing systems work.