Name: Understanding ETL (Matt Palmer)（Z-Library）
Author: Unknown Author

Page 1

Technical Guide Compliments of Understanding ETL Data Pipelines for Modern Data Architectures Pa lm er Pa lm er Matt Palmer

Page 2

@ databricks Delta Live Tables Reliable data pipelines made easy Delta Live Tables (DLT) is the first ETL framework that uses a simple declarative approach to building reliable data pipelines. DLT automatically manages your infrastructure at scale so data analysts and engineers can spend less time on tooling and focus on getting value from data. J c::;B @] Automatically Have confidence Simplify batch Accelerate ETL development manage your in your data and streaming infrastructure Learn more - -

Page 3

Matt Palmer Understanding ETL Data Pipelines for Modern Data Architectures Boston Farnham Sebastopol TokyoBeijing

Page 4

978-1-098-15923-8 [LSI] Understanding ETL by Matt Palmer Copyright © 2024 O’Reilly Media, Inc. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: Aaron Black Development Editor: Gary O’Brien Production Editor: Kristen Brown Copyeditor: nSight, Inc. Proofreader: M & R Consultants Corporation Interior Designer: David Futato Cover Designer: Ellie Volckhausen Illustrator: Kate Dullea March 2024: First Edition Revision History for the First Edition 2024-03-13: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781098159252 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Understanding ETL, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the author and do not represent the publisher’s views. While the publisher and the author have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the author disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights. This work is part of a collaboration between O’Reilly and Databricks. See our statement of editorial independence.

Page 5

Table of Contents Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii 1. Data Ingestion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Data Ingestion—Now Versus Then 2 Sources and Targets 2 Ingestion Considerations 10 Choosing a Solution 19 2. Data Transformation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 What Is Data Transformation? 25 Building a Transformation Solution 33 The Future of Data Transformation 40 3. Data Orchestration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 What Is Data Orchestration? 44 Data Orchestration Tools 47 Design Patterns and Best Practices 54 The Future of Data Orchestration 58 4. Pipeline Issues and Troubleshooting. . . . . . . . . . . . . . . . . . . . . . . . . . 61 Maintainability 62 Monitoring and Benchmarking 63 Errors 68 Improving Workflows 70 v

Page 6

5. Efficiency and Scalability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Efficiency and Scalability Defined 78 Understand Your Environment 79 Process Efficiency 88 Conclusion 92 Conclusion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 vi | Table of Contents

Page 7

Introduction Whether your title is data engineer or another data-oriented profes‐ sion (we see you, analysts and scientists), you’ve likely heard the term ETL. There’s a good chance ETL is a part of your life, even if you don’t know it! Short for extract, transform, load, ETL is used to describe the foun‐ dational workflow most data practitioners are tasked with—taking data from a source system, changing it to suit their needs, and loading it to a target. Want to help product leaders make data-driven decisions? ETL builds the critical tables for your reports. Want to train the next iteration of your team’s machine learning model? ETL creates qual‐ ity datasets. Are you trying to bring more structure and rigor to your company’s storage policies to meet compliance requirements? ETL will bring process, lineage, and observability to your workflows. If you want to do anything with data, you need a reliable process or pipeline. This fundamental truth holds true from classic business intelligence (BI) workloads to cutting-edge advancements, like large language models (LLMs) and AI. The Brave New World of AI The data world has seen many trends come and go; some have transformed the space, and some have turned out to be short-lived fads. The most recent is, without a doubt, generative AI. At every turn, there’s chatter about AI, LLMs, and chatbots. This recent fascination with AI, largely brought by the release of Open‐ AI’s ChatGPT, extends beyond the media’s interest and among vii

Page 8

researchers—it is now seen by many as an essential strategic invest‐ ment…and who wants to be left behind? The true value in LLMs comes from embeddings or fine-tuning models on clean, curated datasets. These techniques allow for the creation of models with domain-specific knowledge, avoiding com‐ mon errors, like hallucination. Of course, meaningful embeddings are derived from, you guessed it—clean datasets. In that sense, AI is built on data transformation. Its success depends heavily on the ability to create consistent, high- quality datasets at scale. Data needs to be moved, mutated, and merged in a single location—one might say extracted, transformed, and loaded. That’s right—even the most cutting-edge tech has roots back to ETL. A Changing Data Landscape In addition to the recent surge in generative AI, other trends have reshaped the data landscape over the past decade. One such trend is the increasing prominence of streaming data. Companies are now generating vast quantities of real-time data through sensors, websites, mobile applications, and more. This shift necessitates the real-time ingestion and processing of data for immediate decision making. Data engineers are therefore challenged to extend beyond traditional batch processing to construct and manage continuous pipelines capable of handling large volumes of streaming data. Another noteworthy development is the emergence of data lake‐ house architectures. The data lakehouse represents a novel concept, seeking to merge the capabilities of data warehouses and data lakes. Leveraging new storage technologies like Delta Lake, which enhance the reliability and performance of data lakes, the lakehouse model combines the cost-effective, scalable storage of data lakes with the efficient transaction processing of data warehouses. This amalgama‐ tion enables the execution of both AI workloads (typically handled in data lakes) and analytics workloads (usually conducted in data warehouses) within a singular framework. This integration signifi‐ cantly reduces the complexities associated with maintaining parallel architectures, ensuring consistent data governance, and managing data duplication. viii | Introduction

Page 9

While ETL is a long-standing concept in data management, its rele‐ vance remains undiminished in the modern data landscape. A criti‐ cal consideration now is how ETL processes can adapt to encompass both batch and streaming data, and how they can be effectively integrated within a data lakehouse architecture. This guide aims to illuminate these aspects, helping you understand ETL in light of these evolving trends. What About ELT (and Other Flavors)? As you delve into data engineering, you may come across terms like ELT in addition to ETL. You might be thinking, “Wow, these guys should hire a proofreader,” but rest assured, they’re actually different terms. The key difference in ELT lies in sequence: in ELT, everything is loaded into a staging resource, then transformed downstream. ELT has increasingly become the norm, supplanting ETL in many sce‐ narios—as many say “storage is cheap.” The term “ETL” has been widely used for so long (since the creation of databases themselves) that it’s still commonly referred to, even when ELT is more accurate. We are now in an era of “store first, act later,” facilitated by decreas‐ ing costs of cloud storage and the ease of data generation. For analysis, retaining all potentially useful data is prevalent. Tech‐ nological advancements like the medallion architecture and data lakehouse support this approach with features like easy schema evo‐ lution and time travel. We’ll discuss those and more throughout this guide. Although we predominantly use the term “ETL,” it’s important to note that the principles and considerations discussed are applicable to both ETL and ELT, as well as other variations like reverse ETL— the practice of ingesting cleaned data back into business tools from the ware- or lakehouse. No, reverse ETL != LTE, and yes, this is confusing, but we digress. Whether the term “ETL” precisely describes your current process or not, comprehending the fundamentals of data ingestion, transfor‐ mation, and orchestration remains crucial. This also extends to best practices in areas like observability, troubleshooting, scaling, and optimization. We hope that this guide will be a valuable resource, regardless of the specific data processing methodology you employ. Introduction | ix

Page 10

O’Reilly Online Learning For more than 40 years, O’Reilly Media has provided technology and business training, knowledge, and insight to help companies succeed. Our unique network of experts and innovators share their knowl‐ edge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in-depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit https://oreilly.com. How to Contact Us Please address comments and questions concerning this book to the publisher: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 800-889-8969 (in the United States or Canada) 707-827-7019 (international or local) 707-829-0104 (fax) support@oreilly.com https://www.oreilly.com/about/contact.html We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at https:// oreil.ly/understandingETL. For news and information about our books and courses, visit https:// oreilly.com. Find us on LinkedIn: https://linkedin.com/company/oreilly-media. Watch us on YouTube: https://youtube.com/oreillymedia. x | Introduction

Page 11

Acknowledgments Though we all stand on the shoulders of giants, this guide in partic‐ ular would not have been possible without mentorship, help, and support from some very dedicated and caring individuals. First, thank you to my partners from O’Reilly and Databricks: Aaron Black, who gave me the opportunity to write; Gary O’Brien, who was a stellar development editor (and confidante!); Ori Zohar, who helped shape the guide as a whole; and both Sumit Makashir and Pier Paolo Ippolito for their excellent and attentive technical reviews. Thank you to Zander Matheson for your help in understanding streaming and stream processing. Along with developing an amaz‐ ing tool (Bytewax), Zander has been a great friend and a general data guru. Thank you to Aleks Tordova and the Coalesce team, who partnered to write my first guide and have provided me with ample opportuni‐ ties to learn and grow. Thanks to my family, who provided unconditional support for my journey—in data and life—despite my flaws, idiosyncrasies, and general tomfoolery. Thank you, Jasmine, Violet, and Paul (and pups Enzo and Rocky!) Next, I am blessed with some amazing friends who’ve supported me as I moved across the country, took a new job, wrote this guide, and continued my path of self-discovery. There were many texts, Slacks, phone calls, and memes that helped me through the tough times. In alphabetical order, thank you, JulieAnn, Kandace, Rob, Srini, and Tyson. Last, thank you to the data community. To the individuals that con‐ tribute to open source and present at conferences, the practitioners that wake up every day looking to improve, the educators/mentors that keep us moving forward as a field, and all of the authors whose texts, ideas, and content have helped us get to where we are today: I can’t wait to see what we accomplish next! Introduction | xi

Page 12

(This page has no text content)

Page 13

CHAPTER 1 Data Ingestion Data ingestion, in essence, involves transferring data from a source to a designated target. Its primary aim is to usher data into an envi‐ ronment primed for staging, processing, analysis, and artificial intel‐ ligence/machine learning (AI/ML). While massive organizations may focus on moving data internally (among teams), for most of us, data ingestion emphasizes pulling data from external sources and directing it to in-house targets. In an era where data holds central importance in both business and product development, the significance of accurate and timely data cannot be overstated. This heightened reliance on data has given rise to a multitude of “sources” from which teams extract information to refine decision-making processes, craft outstanding products, and conduct a multitude of other actions. For instance, a marketing team would need to retrieve data from several advertising and analytics platforms, such as Meta, Google (including Ads and Analytics), Snapchat, LinkedIn, and Mailchimp. However, as time marches on, APIs and data sources undergo modi‐ fications. Columns might be introduced or removed, fields could get renamed, and new versions might replace outdated ones. Handling changes from a single source might be feasible, but what about juggling alterations from multiple sources—five, ten, or even a hun‐ dred? The pressing challenge is this: “How can a budding data team efficiently handle these diverse sources in a consistent and expanda‐ ble way?” As data engineers, how do we ensure our reputation for 1

Page 14

providing reliable and straightforward data access, especially when every department’s demands are continuously escalating? Data Ingestion—Now Versus Then Though the principles of ingestion largely remain the same, much has changed. As the volume, velocity, and variety of data evolve, so too must our methods. We’ve had multiple industry changes to accommodate this—move‐ ment to the cloud, the warehouse to the data lake to the lakehouse, and the simplification of streaming technologies, to name a few. This has been manifested as a shift from extract-transform-load (ETL) workflows to extract-load-transform (ELT), the key difference being that all data is now loaded into a target system. We’ll discuss these environments in the context of transformation in Chapter 2. We refrain from being too pedantic about the terms ETL and ELT; however, we’d like to emphasize that almost every modern data engi‐ neering workflow will involve staging almost all data in the cloud. The notable exception is cases where hundreds of trillions of rows of highly granular data are processed daily (e.g., Internet of Things [IoT] or sensor data), where it makes sense to aggregate or discard data before staging. Despite constant changes, the fundamental truth of extraction is that data is pulled from a source and written to a target. Hence, the discussion around extraction must be centered on precisely that. Sources and Targets While most associate ingestion with extraction, it’s also tightly cou‐ pled with loading; after all, every source requires a destination. In this guide, we assume that you have an established warehouse or data lake; therefore, storage will not be a primary topic in this chapter. Instead, we’ll highlight both best practices for staging and the hallmarks of ideal storage implementations. 2 | Chapter 1: Data Ingestion

Page 15

It’s our mission to arm you with a toolkit for architecture design, keeping in mind that a “perfect” solution might not exist. We’ll navigate a framework for appraising sources and untangling the unique knots of data ingestion. Our high-level approach is designed to give you a bird’s-eye view of the landscape, enabling you to make informed, appropriate decisions. The Source Our primary consideration for ingesting data is the source and its characteristics. Unless you’re extremely lucky, there will be many sources. Each must be separately assessed to ensure adequate resources and set the criteria for your ingestion solution(s). With the sheer volume of data sources and the nature of business requirements (I’ve seldom been asked to remove sources, but adding one is just another Thursday), it’s highly likely that you’ll encounter one or many sources that do not fit into a single solution. While building trust takes weeks, months, and years, it can be lost in a day. Reliable, timely ingestion is paramount. So, what’s important in choosing a source? Examining sources As a practical guide, we take the approach of presenting time-tested questions that will guide you toward understanding the source data, both its characteristics and how the business will get value. We recommend taking a highly critical stance: it is always possible that source data is not needed or a different source will better suit the business. You are your organization’s data expert, and it’s your job to check and double-check assumptions. It’s normal to bias for action and complexity, but imperative we consider essentialism and simplicity. When examining sources, keep in mind that you’ll likely be working with software engineers on upstream data, but downstream consid‐ erations are just as important. Neglecting these can be highly costly, since your error may not manifest itself until weeks of work have taken place. Sources and Targets | 3

Page 16

Questions to ask Who will we work with? In an age of artificial intelligence, we prioritize real intelligence. The most important part of any data pipeline is the people it will serve. Who are the stakeholders involved? What are their primary motives—OKRs (objectives and key results) or organ‐ izational mandates can be useful for aligning incentives and moving projects along quickly. How will the data be used? Closely tied to “who,” how the data will be used should largely guide subsequent decisions. This is a way for us to check our stakeholder requirements and learn the “problem behind the problem” that our stakeholders are trying to solve. We highly recommend a list of technical yes/no requirements to avoid ambiguity. What’s the frequency? As we’ll discuss in detail later, most practitioners immediately jump to batch versus streaming. Any data can be processed as a batch or stream, but we are commonly referring to the characteristics of data that we would like to stream. We advocate first considering if the data is bounded or unbounded—i.e., does it end (for example, the 2020 Census American Community Survey dataset), or is it continuous (for example, log data from a fiber cabinet). After bounds are considered, the minimum frequency available sets a hard limit for how often we can pull from the source. If an API only updates daily, there’s a hard limit on the frequency of your reporting. Bounds, velocity, and business requirements will inform the frequency at which we choose to extract data. What is the expected data volume? Data volume is no longer a limiter for the ability to store data— after all, “storage is cheap,'' and while compute can be costly, it’s less expensive than ever (by a factor of millions; see Figures 1-1 and 1-2). However, volume closely informs how we choose to write and process our data and the scalability of our desired solution. 4 | Chapter 1: Data Ingestion

Page 17

Figure 1-1. Hard drive costs per GB, 1980 to 2015 (Source: Matt Komorowski); the y-axis values are in log scale Figure 1-2. Cost of compute, millions of instructions per second (MIPS) (Source: Field Robotics Center); the y-axis values are in log scale What’s the format? While we will eventually choose a format for storage, the input format is an important consideration. How is the data being delivered? Is it via a JavaScript Object Notation (JSON) pay‐ load over an API? Perhaps an FTP server? If you’re lucky, it Sources and Targets | 5

Page 18

already lives in a relational database somewhere. What does the schema look like? Is there a schema? The endless number of data formats keeps our livelihoods interesting, but also presents a challenge. What’s the quality? The quality of the dataset will largely determine if any transfor‐ mation is necessary. As data engineers, it’s our job to ensure consistent datasets for our users. Data might need to be heavily processed or even enriched from external sources to supple‐ ment missing characteristics. We’ll use these characteristics to answer our final question: How will the data be stored? As we mentioned, this book assumes some fixed destination for your data. Even then, there are a few key considerations in data storage: to stage or not to stage (is it really a question?), business requirements, and stakeholder fit are the most important. Source checklist For every source you encounter, consider these guiding questions. Though it might seem daunting as the number of sources piles up, remember: this isn’t a writing task. It’s a framework to unpack the challenges of each source, helping you sketch out apt solutions that hit your targets. While it might feel repetitive, this groundwork is a long-term time and resource saver. Question Example Who will we collaborate with? Engineering (Payments) How will the data be used? Financial reporting and quarterly strategizing Are there multiple sources? Yes What’s the format? Semi-structured APIs (Stripe and Internal) What’s the frequency? Hourly What’s the volume? Approximately 1K new rows/day, with an existing pool of ~100K What processing is required? Data tidying, such as column renaming, and enrichment from supplementary sources How will the data be stored? Storing staged data in Delta tables via Databricks 6 | Chapter 1: Data Ingestion

Page 19

The Destination While end-to-end systems require hypothetical considerations that are just that, we assume most readers will be tasked with building a pipeline into an existing system, where the storage technology is already chosen. Choosing data storage technology is beyond the scope of this guide’s focus, but we will briefly consider destinations, as they are highly important to the total value created by a data system. Thus, when analyzing (or considering) a destination, we recommend using a similar checklist to that of a source. Usually, there are far fewer destinations than sources, so this should be a much simpler exercise. Examining destinations A key differentiator in destinations is that the stakeholder is priori‐ tized. Destinations have a unique trait: they pivot around stakehold‐ ers. These destinations either directly fuel BI, analytics, and AI/ML applications or indirectly power them when dealing with staged data, not to mention user-oriented apps. Though we recommend the same checklist, we suggest framing it slightly toward the stakeholder to be sure it meets their requirements, while working toward engi‐ neering goals. We fully recognize this is not always possible. As an engineer, your role is to craft the most fitting solution, even if it means settling on a middle ground or admitting there’s no clear-cut answer. Despite technology’s incredible strides, certain logical dilemmas do not have straightforward solutions. Staging ingested data We advocate for a data lake approach to data ingestion. This entails ingesting most data into cloud storage systems, such as S3, Google Cloud Platform, or Azure, before loading it into a data warehouse for analysis. One step further is a lakehouse—leveraging data storage protocols, like Delta Lake, which use metadata to add performance, reliability, and expanded capability. Lakehouses can even replicate some ware‐ house functionality; this means avoiding the need to load data to a separate warehouse system. Adding in a data governance layer, like Databricks’ Unity Catalog, can provide better discoverability, Sources and Targets | 7

Page 20

access management, and collaboration for all data assets across an organization. A prevailing and effective practice for staging data is utilizing metadata-centric Parquet-based file formats, including Delta Lake, Apache Iceberg, or Apache Hudi. Grounded in Parquet—a com‐ pressed, columnar format designed for large datasets—these formats incorporate a metadata layer, offering features such as time travel, ACID (atomicity, consistency, isolation, and durability) compliance, and more. Integrating these formats with the medallion architecture, which processes staged data in three distinct quality layers, ensures the preservation of the entire data history. This facilitates adding new columns, retrieving lost data, and backfilling historical data. The nuances of the medallion architecture will be elaborated upon in our chapter on data transformation (Chapter 2). For the current discussion, it’s pertinent to consider the viability of directing all data to a “staging layer” within your chosen cloud storage provider. OLAP Versus OLTP Databases The biggest choice in data warehousing is whether to use a cloud native database or a cloud-hosted traditional database—the main difference being the distributed nature and column-oriented architecture of cloud native solutions. These are more commonly referred to as OLAP (online analytical processing) and OLTP (online transaction processing): • OLAP systems are designed to process large amounts of data quickly. This is commonly accomplished via distributed processing and a column-oriented architecture. Newer, cloud native databases are OLAP systems; the big three OLAP solu‐ tions are Amazon Redshift, Google BigQuery, and Snowflake. • OLTP systems are engineered to handle large amounts of transactional data originating from multiple users. This usually takes the form of a row-oriented database. Many traditional database systems are OLTP: Postgres, MySQL, etc. OLAP systems are most commonly used by analytics and data science teams for their speed, stability, and low maintenance cost. Here are some considerations for data warehouse selection: 8 | Chapter 1: Data Ingestion

Tip the Site

Understanding ETL (Matt Palmer)（Z-Library）

Understanding ETL (Matt Palmer)（Z-Library）

Registered users can read the full content for free

AI Reading Assistant

Passage locations

Recommended for You

Tip the Site