Including logic in ETL pipelines

Not everything in life is simple. While we might start off thinking a data pipeline only consists of Reading from a data source, Loading It into a Warehouse, Running transforms, and Displaying It in a dashboard, there’s a lot more to it.  

Even a “simple” pipeline often includes complexity and requires logic to guide the pipeline to a successful conclusion.

When you filter the components in the Matillion Designer interface by Logic, you’ll see a list of the specific logic components.

This article will guide you through a number of the most widely used logic operators used in Matillion Orchestration pipelines. Specifically, we’ll focus on those with the "Flow" label: And, If, Or, Retry, End Failure, and End Success.

And

Let’s start with the simple but powerful And. This component synchronizes parallel tasks by waiting for all of its inputs to complete before continuing.

The And component has no properties and is controlled entirely via the inputs and output connections.

In this example, all three inputs are “Success” connections from the previous components.  The And component must succeed in getting data from our sources before using that data.

“Extract Salesforce Accounts” And “Load Customer 360 Data” And “Extract Point-of-Sale Data” components must all complete successfully for the Pipeline to continue.

With And, there is only one output, “Unconditional”. If the And logic is satisfied, we continue the pipeline from there.

If

The If component lets us plan for multiple scenarios and tell part of our pipeline to run only If a condition is true. For my example, let’s say we only want to update a calculated forecast If the day is Sunday.

To do this, I would store the required value in a variable and use the If component to get a “true/false” response.

In my example, I’m getting the current day of the week from Snowflake and loading that into a variable called “dayOfWeek” and included the rule I want to apply in the component properties.

From there, I used the “True” connection from the If component to connect to the next component that will run my weekly forecast.

I’ll also have to create a path to skip the forecast on days it isn’t Sunday. For that task, Or comes in handy.

Or

Like And, there are no settings for Or. If any paths connected to the Or happen, then Or will allow the Pipeline to continue to whatever component is connected after the Or. Where And was used to combine multiple successful paths into a single path, the Or will have a similar effect for the false side of If.

In the example pipeline above, regardless of whether I have run the Forecast Profit or not, the next thing I want to happen is the Alter Warehouse component. By connecting the false result from the If and the Success from the Forecast into the Or, the Warehouse is returned to my desired size, regardless of the path the orchestration takes.

End on Failure / Success

Using logic to trap a problem has shortcomings For example, zero rows returned. A connector didn’t fail to work, but it didn’t get any data, so we’d rather “End on Failure” in that scenario. 

For our example, if we have one or more rows, the pipeline continues(true).  if we have less than one row (false) we’ll send a message. 

The False path from the If will not register as a failure, and neither will sending the message (assuming it happens successfully). So, to force a failure outcome and register the pipeline as Failed, place an “End Failure” at the end.

A use for “End Success” might be that you have used the Assert Table component to verify if an unwanted table exists. In that case, the component would fail because the table wasn’t there, and that’s a success for the process.

Retry

Occasionally, a system you want to get data from isn’t able to respond. Perhaps it takes a while to be ready after you call on it, or suffers from inexplicable timeouts. When that happens you might want a component connecting to that source to try again rather than stop the pipeline.

This is where Retry comes in. While it can be attached to most components, I’ve added a Retry component to the Salesforce connector in my example job

In the properties for the component you can select various retry strategies, or specify your own. I’ll set it to try three times with a 30-second interval between each, before failing.

The resulting Pipeline looks like:

Retry will try three times to get data from Salesforce. If I succeed in connecting to Salesforce but get zero rows back, I’ll send a webhook message and end in failure. The transformation pipelines will only run if I successfully get data from my three sources and the Forecast pipeline will only run on Sunday.

Conclusion

Matillion data pipeline platform empowers data teams to build and manage pipelines faster for AI and analytics while solving multiple problems at scale.

Matillion Orchestration pipelines include multiple options for incorporating logic operators to handle the complexity found in normal data handling processes. Matillion’s UI enables the use of pre-built components while supporting SQL, Python, and DBT coding.

The platform’s full Git integration, no-code connectors, variable parameterization, and AI capabilities enhance productivity, collaboration, and flexibility. Matillion also democratizes AI access and accommodates hybrid SaaS deployments for easy scalability.

Ready to streamline your data management and integration processes? Start your data journey with Matillion today and see how our platform can transform your data workflows. Get started for free, or book a demo to explore Matillion’s powerful solutions.