Derived tables

Towards the beginning of these tutorials, we looked at loading data into our workflows, from Excel spreadsheets, CSV files and other similar sources. Sometimes however, instead of beginning a new table by loading in or creating a new set of data, we instead may wish to create one from an existing table in our workflow. We describe this process as deriving a table.

Derived table

In this scenario, the data from the end of one table (the source table) becomes the starting data of another (the derived table). As we can see above, a solid arrow indicates the direction in which the data flows in between the source table and the derived table. You can think of a derived table as creating a dynamic copy of another existing table in your workflow. It is dynamic because any changes to the source table are reflected in the starting point of the derived table.

Important: Derived tables are indicated using a solid arrow. Dashed arrows in our workflows instead indicate where data is merged between tables. See the introduction to tables and merging data tutorials for more details.

Below, as well as looking at how to create derived tables, we’ll also look at some of the scenarios where creating derived tables in our workflows is either helpful or even essential.

Why create derived tables?

There are many reasons to use derived tables in our EasyMorph workflows, from simple organization to building complex branching workflows. Let’s consider some of the most common:

Logical separation

The most simple reason to derive one table from another is to help us to organize our workflow into logical sections. For example, let’s consider a workflow which follows these high-level steps:

  1. Loads in, sorts, and filters the data
  2. Checks and cleanses any data quality problems
  3. Exports the data to a file

Rather than building this all in one single table of our workflow, we might wish to break it up into 3 tables instead, each of which contains the actions for one of the steps and derives its data from the previous.

Derived tables for logical separation of actions

Note: For more information on other ways to structure your workflow, see the Structuring workflows section later in these tutorials.

Branching workflows

It is also possible to derive more than one table from a single source table. This opens up the ability to load a set of data and then direct it down two or more different paths within our workflow, each of which does something different with it. For example, after loading and cleaning my data, I may wish to calculate two different aggregations of it.

Derived tables branching workflow

As you can see above, we have derived two different tables, one of which aggregates the data by City and the other by Year.

We could of course load in the same data file twice, but there are a couple of good reasons to use derived tables instead. Firstly, except in a few specific scenarios, the slowest part of the process is loading the data in to EasyMorph from wherever it is located (e.g. a file on the disk, a database, etc.). This means loading it once and deriving the necessary copies is usually more efficient and therefore faster.

Note: As well as being quicker, if our workflow tries to load in the same file twice at the same time, we can experience errors due to the file being locked by whichever load got to it first. See the Order of operations tutorial for more information on this problem and additional ways to prevent it.

Splitting a table into two or more parts

Another common reason to derive more than one table from a single source is when we want to split a single table into different subsets. For example, if I have table of unpaid invoices, I may wish to split them into two tables, one containing those that are overdue and another containing those which are not. We can then deal with them differently, such as emailing the two lists to different people.

Splitting a table into 2 or more parts

More advanced use cases for deriving tables

There are many more use cases where derived tables can be important. Just a few examples to consider are:

  • Sending different subsets of data to different people.
  • Filtering a dataset based on its aggregated metrics.
  • Monitoring data quality at certain points of a workflow.
  • Performing self-joins where a table is joined or merged with itself.
  • And many more…

How to create a derived table

Creating a derived table in EasyMorph couldn’t be any simpler. When we select any table in a workflow, just to the right-hand side we will see a + icon. To recreate a derived table from the selected table, we simply click on this icon.

Derive table icon

Alternatively, if we right-click a table’s heading we will see the option to derive a table in the context menu that appears.

The derive table action

You may have already noticed in the images above that all derived tables have the same icon at the beginning of their action chain. This icon represents a special action, the Derive table action. Other than the fact that they begin with the Derive table action, derived tables are no different to any other table.

Derive table action

Just like for all other actions, if we select a Derive table action in our workflow, the left-hand settings panel shows us the settings for the action. The settings are simple, allowing us to do two things:

  • Change the source table which this table is derived from.
  • Apply conditions to only derive the table under certain circumstances (more on this in the Topics for power users section at the bottom of this tutorial).
Derive table action settings

Recombining derived tables

Sometimes, after branching our workflow by deriving two tables from the same source, we wish to recombine the two copies. For example, we may need to identify which of our sales are above the average value. To be able to achieve this, we need to have the average sale value as a column in our sales data. We can then compare it with the individual sales value. To do this, we can use derived tables which we recombine as follows:

  1. Load in our sales data
  2. Derive a table from it, and use the Aggregate action to calculate the Average Price.
  3. Derive another table from the sales data in which we merge the average sales back into it, so that we have both the individual sales Total Price and the Average Price on the same table row.
Re-merging the branched data back together

Now we have our Average Price as a column in our sales data, we can use the Filter by condition action to keep only those sales above the average.

Filtering the data after re-merging the data

Tip: See the Filtering data and Merging data tutorial sections for more information on the actions and topics covered in this example.

Topics for power users

Conditional derivation

The derive table action has the option to either derive the table always or only on a condition. The latter allows us to define a condition under which the table should silently skip being derived.

Let’s consider our earlier example where we use derived tables to split unpaid invoices into those which are overdue and those which aren’t. If our workflow emails the list of overdue invoices to someone, instead of sending an empty list when there are no overdue invoices, we want to skip the actions which save the list of overdue invoices and send the email. Conditional derivation allows us to do just that.

Conditionally derived table skipping actions

To enable conditional derivation, select the Run actions → On condition option within the derive table action’s settings.

Once enabled, you will see 4 options for the condition:

  • Derive the table only if the source table is empty
  • Derive the table only if the source table is NOT empty
  • Derive the table only if a defined condition is met for EVERY row in the source table
  • Derive the table only if a defined condition is met for ANY row in the source table

When selecting either of the latter two options, we must enter a conditional expression which will be used.

Derived table conditional expression

When the derivation condition is not met, the derived table is effectively skipped, including all other actions in the table’s action chain.

Note: For more details, see the tutorial chapter on Conditional workflows.

Circular references

In the above example for Recombining derived tables we covered how it can be common to branch our workflow using combined tables and then to merge them back together again.

Sometimes a table needs to be transformed using a (possibly modified) copy of itself. When doing so, it is possible to create a situation where two or more dependencies cause a cyclic relationship between the tables in our workflow. If we consider the below example, our derived table is calculated only after the source table finishes being calculated. Because our source table also tries to append our derived table to our source table, a circular reference is formed and EasyMorph displays an error.

Cyclic dependency causing an error

To avoid such circular references, we can instead derive two tables from the source table, and then merge them together as we’ve seen earlier in this tutorial chapter. EasyMorph is now able to resolve the dependencies as no circular reference is formed. The key here is that all arrows indicating dependencies flow only down stream and there is no circular path through which the flow of data can cycle.

Same workflow with cyclic dependency avoided

Note: For more information on the order actions and tables are calculated in, see the Order of operations tutorial sections.

Parallel execution

A derived table is always executed after its source table has finished being calculated. However, when two or more tables are derived from the same source table they can both be executed in parallel (assuming no other dependency prevents it). Because table dependencies are always shown in EasyMorph as arrows between tables, looking at the arrows can help us to see and understand the order tables will be executed in.

For example, in the below diagram, the source table1 must finish calculating before the two tables derived from it can begin 2. However, both source tables are independent of each other and so can and will be executed in parallel.

Derived concurrent execution

Note: You can find more information on this topic in the Order of operations tutorial chapter.