Building Simple and Scalable Data Pipelines

Welcome to this series of blog articles, focusing on Matillion features and design patterns that can be applied to many different data-oriented use cases and scenarios. As an example of an end-to-end data pipeline, I’ve used Matillion’s data platform to build the MatiHelper Slack AI app. Through this series of articles, I will walk through in detail the pipelines that comprise the MatiHelper Slack AI app. By doing so, I will show some real-world examples of using these Matillion features and design patterns.

Before diving into these blog articles, have a look at this video overview, which will help to set the stage for what’s to follow in this series of blog articles!

When building a new data pipeline, one often starts by thinking about the journey that the data will take from source to destination, accounting for any required stops along the way. The pipeline will need to account for the full lifecycle of that data, so it can be ushered along through the appropriate steps of its journey. Sometimes that data lifecycle can be as simple as ingesting data from a source system, applying some simple business rules to the data and exposing that transformed data via a report or dashboard. Often, that data lifecycle is more complex with additional data sources to incorporate, complex transformation requirements, external system dependencies or a near infinite number of variations and nuances around what to do with the data.  Irrespective of the specific data scenario, there are Matillion design patterns and features that can be applied to help encapsulate data journey requirements into Matillion Data Productivity Cloud Pipelines, built with simplicity, scalability and extensibility in mind.

To help set the stage for the other articles in this series, this article focuses on key Data Productivity Cloud features and design patterns that are foundational to simple, consistent and scalable data pipeline design. 

Pipeline Types

When using Data Productivity Cloud Designer, there are two primary types of pipelines that can be built. The first pipeline type, Orchestration pipelines, has two key roles.  The first role of an Orchestration pipeline is to ingest data from source systems, wherever that might be. Additionally, Orchestration pipelines play the role of the “orchestrator” (thus the name), allowing to define execution dependencies between different components and pipelines. 

Transformation pipelines provide the ability to define transformation logic to apply to data. An important aspect of the Matillion data platform architecture is that it leverages a cloud data platform as the main engine to power data transformations. Examples of data transformations include common tasks like aggregating values, flattening semi-structured data into more relational structures, applying business logic to calculate key performance indicators, enriching data with additional metadata, the list goes on… Transformation workloads can vary from just a few records to billions and trillions of rows of data. When using Matillion Data Productivity Cloud, because transformations are powered by the near infinite power provided by cloud data platforms, there will always be plenty of power to drive data transformations. 

Pipeline Dependencies

The simplest data pipeline one might build is to fetch a dataset from a source system to land in a cloud data warehouse table. The data in the target looks exactly like the data from the source when it was fetched. This is often what the “E-L” (Extract-Load) in “E-L-T” (Extract-Load-Transform represents). Using Data Productivity Cloud, the “E-L” can be done very easily with a single component in an orchestration pipeline. 

But, in real-world data scenarios, getting data from a source system is just the first step amongst many. The data journey often contains numerous dependencies that might involve things like timing of the data, multiple data system dependencies, business logic that needs to be applied to the data, additional metadata dependencies and any number of other data oriented dependencies. 

When designing an end-to-end data pipeline, one must first think about the data lifecycle that the data pipeline will represent. 

• Where does the data come from? 
• Where does the data need to end up? 
• What needs to happen to the data along the way? 
• What does the data need to look like when it arrives at its destination?
• What dependencies does the data have? 

Not only do the answers to these questions help to define the overall data lifecycle, they often help to categorize workloads into logical segments. Individually, each logical segment is often a good candidate to implement as an orchestration or transformation pipeline. Thinking in this way helps to break down the pipeline development into smaller workloads, which can then be stitched together with a main orchestration pipeline that serves the role of the coordinator, defining what pipelines to run and when. See here the main orchestration pipeline that represents the MatiHelper Slack AI app pipeline. The low-code graphical user interface of Data Productivity Cloud makes it very intuitive to define and understand dependencies in a pipeline.

Each nested pipeline represents one logical segment, but one can continue to nest orchestration and transformation pipeline executions within each level. The main orchestration pipeline is a parent, which might have one or more child orchestration or transformation pipelines that it executes. 

With the above considerations in mind, it’s often practical to begin the development of child pipelines, which you then orchestrate in a parent pipeline as a final development task. In this case, the main orchestration pipeline shown above was the last pipeline built, after completing the development of the child pipelines first.

Pipeline Organization

Matillion’s Designer interface includes a Pipeline tab, which allows for the organization of orchestration and transformation pipelines within a folder structure. As a general rule, I use a folder hierarchy to help organize pipelines in a manner that reflects dependencies, with a top-level folder that the main orchestration pipeline might reside in and dependent child pipelines organized into their own sub-folders. See here how the folder structure of the MatiHelper Slack AI app pipelines aligns with the design of the main pipeline shown above.

Variables

Anyone with experience writing code, no matter the programming language, will recognize the importance of variables. Variables are used to store information to be referenced and manipulated throughout the execution of a block of code. There are many benefits of using variables when writing code.

• Variables allow you to assign descriptive names to data. This results in better code readability and clarity.
• Variables enable you to store data that can be used multiple times throughout your code without needing to rewrite or re-calculate the values. This leads to code reusability.
• Variables make it easier to update values in one place, as opposed to updating a value for every occurrence. This helps with code maintainability.
• Variables enable the dynamic storage of data, which can change throughout the code’s execution. This is especially useful in conditions like loops, iterations or when using metadata to drive code execution.

These are just a few of the many advantages that Variables provide when used in programming languages. Fortunately, with a platform like Data Productivity Cloud, users don’t need to rely on coding knowledge to build powerful data pipelines. However, variables still play an important role in helping to build scalable pipelines. In fact, Data Productivity Cloud has a few different types of variables that can be really helpful when developing data pipelines. In the following sections, I’m going to highlight some concepts around the use of variables that you will see used as we further decompose the pipelines in the MatiHelper Slack AI app. To learn even more about Variables in Data Productivity Cloud, see our official documentation here.

Variable Types

Data Productivity Cloud currently supports three types of variables; Project Variables, Pipeline Variables and Grid Variables. Project and Pipeline variables are considered scalar variables, which store a single value at any given point in time during a pipeline’s execution. Project and Pipeline variables differ in that a Project variable is accessible to all Pipelines within a Project, whereas Pipeline variables are only available to the Pipeline in which they are defined. You might think of a Project variable as a global variable and a Pipeline variable as a local variable. Project variables also allow one to define different default values per Environment. A common use-case for Project variables are to store commonly referenced values like source system hostnames, usernames and other things that might be referenced across multiple pipelines. Whereas, one would typically use Pipeline variables for things specific to the pipeline being developed.

Grid variables give the ability to store sets of data in columns and rows in a 2D array. I like to think of a grid variable like an in-memory table. Or, if familiar with Python programming, you can equate a Grid variable to a list object in Python. Grid variables become especially useful when starting to develop metadata-driven pipelines, where things like the list of columns from a source object or the schema metadata on a target table are defined at runtime.

Behavior

Variable behavior in Data Productivity Cloud is related to the ability to design branching behavior in a data pipeline. You can design a pipeline to execute multiple things concurrently. You might do that through an Iterator component or by visually connecting components such that more than one component is an output from another component in an orchestration pipeline. Each concurrently executed component runs as a separate independent thread.

Variables in Data Productivity can be defined as “copied” or “shared”. When a variable’s behavior is defined as shared, if the value of the variable changes during the pipeline’s execution, all threads executing in the pipeline will see the updated value. Conversely, if a variable’s behavior is defined as copied, each thread has its own value for the variable. If another thread updates the value of the variable, that will only affect the thread that updated the variable’s value.

Visibility

You can also define a variable’s visibility in Data Productivity Cloud. When a variable is defined as public, this allows the variable to be accessible by other pipelines that might reference that pipeline. When a variable is defined as private, it is only accessible within the pipeline it is defined. 

As a general rule, I like to set variables as private when the variable will be used to store a calculated value during the execution of the pipeline. The implication is that the value for the variable is not passed in at runtime, but calculated as part of the execution of the pipeline.

Naming Conventions

Another best practice around using variables is to standardize against a naming convention. This helps to make your pipelines more maintainable and easier to understand. The mechanics of the naming convention used matter less than just standardizing against a convention that is intuitive for you and your team. Some naming convention rules I like to use in Data Productivity Cloud are:

• Prefix the variable name with the variable type.
  • “pipe_” for Pipeline variables (i.e. pipe_channel_id)
  • “proj_” for Project variables (i.e. proj_microbatch)
  • “grid_” for Grid variables (i.e. grid_column_list)
• Define a descriptive name that reflects how the variable will be used.

Some examples of this naming convention could look like:

• pipe_channel_id - A pipeline variable that stores a “Channel ID” value.
• proj_microbatch - A project variable that defines if a pipeline should run in microbatch mode. See Part 6 of this blog series to see how this is used!
• grid_column_list - A grid variable that defines a list of column names and their data types.

Note that you can define a Project variable and a Pipeline variable with the same name. If that is the case, the Pipeline variable will take precedence over the Project Variable of the same name. 

Metadata

Common Use Cases

The Merriam-Webster dictionary definition of metadata is “data that provides information about other data”. When designing data pipelines, metadata is king. There are many uses of metadata that help enable scalable and efficient data pipeline design. Some of the most common use cases for metadata in data pipelines include:

• Dynamically defining column information
  • In a data pipeline, you might have a scalar variable that dynamically defines a database table name and a grid variable that stores the table’s column information. This can then be used to build a generic pipeline that moves data from the database source, which you then execute repeatedly for different tables.
• Incremental Loading
  • Incremental loading of data from a source based on a high watermark is a really common data ingestion pattern. Typically, the dataset will include a date timestamp field that can be used as a high watermark. In this scenario, the metadata is the max timestamp value that has been ingested so far.
• Iterating
  • Iterating over a list of values is a frequent task that metadata is used to support. This can take many shapes and forms, something as simple as a Google Sheet list of values to iterate over or maybe something more involved like an Excel spreadsheet that defines data transformation mapping specifications. Irrespective of complexity, the common trait is that in each, there is some “values” that you want to iterate over. 

Component Highlight

The few scenarios mentioned above are great examples of how metadata can help with efficient and scalable pipeline design. To help support implementing these patterns using Data Productivity Cloud, here are some really useful Components to call attention to.

Design Patterns

When I think about what “the art of the possible” means to me, it is the idea of recognizing common patterns that can be applied to solve data problems and show how those patterns can be supported using Data Productivity Cloud features and components. In the following blog articles in this series, I will highlight key concepts within the pipelines that comprise the MatiHelper Slack AI app, so you can see some of these patterns in action.  

Before diving into those pipelines, I’ll finish things off by highlighting some common data pipeline design concepts and patterns that center around using metadata.

Tables vs Views

When building a metadata driven pipeline, you need to store your metadata somewhere. Very often, that will be in a table in your cloud data warehouse. You might have a table that just contains metadata by itself. Or, you might have a table of “real” data that is additionally supplemented with metadata stored in specific columns. 

If you are not familiar with the concept of “views”, they are like “virtual tables” that derive data dynamically. Think of a view as a lens that is focused on a specific area of data stored in one or more tables. This helps to simplify complex logic and prevents the need for duplicating (and maintaining) data. If the underlying data in a table is changed, a view that uses that data will immediately show those changes.

To help keep my data pipeline design simple, I like to create views on top of metadata. And, use those views as the drivers for logic within my data pipelines. Additionally, I like to use Data Productivity Cloud Transformation pipelines to create those views, which helps to make the logic behind these views easy to understand. Stay tuned for the next article, where I will break this down even further!

Iterating over Metadata

As discussed earlier, when thinking about pipeline dependencies, you break out the different parts of the data lifecycle into logical segments. Very often, there will be hierarchical dependencies in some of these logical segments. This concept is frequently referred to as a parent/child relationship, where you might nest the execution of one or more “child” pipelines within a “parent” pipeline. A common scenario when executing a nested pipeline is the need to repeatedly call a nested pipeline and provide different data input per execution. A common pattern is to use views as the basis of iterations, using the Table Iterator component to set the value of variables per iteration. The Table Iterator can then be linked to execute another pipeline or component for every iteration.

We’ll see this exact concept in action in Part 5 of this blog series!

Incremental Loading

Incremental loading is the concept of loading data that has changed since the last successful load. This is a well known pattern used by data engineering teams all over the world. In Part 3 of this series, we’ll see an example of loading incremental data from a Slack channel in action! 

For now, if you want to learn more about the concept of incremental loading, take a look at our blog on DataOps Harmony: Incremental Loading for Optimal Data Engineering Performance!

Conclusion

So, here concludes the first part of this blog series, which focused on some high-level pipeline design concepts. Hopefully, you found some of the items discussed helpful. You might even recognize you’re doing some of these things today! Here’s a quick peek into the upcoming parts in this blog series, where we’ll start to take a deep dive into the pipelines that comprise the MatiHelper Slack AI app and see these concepts in action.

• Part 2: The Data Lifecycle
• Part 3: Integrating with Slack
• Part 4: The Power of GenAI
• Part 5: Webhooks and Pushdown Python
• Part 6: Microbatching for Continuously Running Pipelines

Downloads

You can find the MatiHelper Slack AI App pipelines available for download on the Matillion Exchange here!