Export Templated

Description

Renders project tables through a template to produce a structured document, instead of the raw column dump the other export steps write. Use it when the receiving system requires a specific XML shape, or another text format, rather than a table.

Unique Configuration Items

Source Template

The Jinja template the output is rendered from. See What a Template Can Use.

Target Output

The document account, folder, and file name for the rendered result. The template decides what the file contains; a file name with no extension gets .xml.

Use Pandas Dataframes for Table Data changes how each source’s rows reach the template. See Source Rows as a Table.

Source Table

Name the tables explicitly, or match them by path and a search rule — exact, starts with, ends with, contains, or glob.

What a Template Can Use

The template renders in a sandbox: it reads the data the step gives it and nothing else.

• Each source, by its Name. Every source table you list is available to the template by the Name you give it, so use a name that works as a variable, such as invoices.
• The workflow’s variables, as variable — for example {{ variable.region }}.

<Invoices>
{% for row in invoices %}
  <Invoice id="{{ row.invoice_id }}" amount="{{ row.amount }}" />
{% endfor %}
</Invoices>

Source Rows as a List

With Use Pandas Dataframes for Table Data off, a source is a read-only list of rows. Loop over it, or use |length, keys(), first(), one(), all(), scalar(), scalars(), mappings(), fetchmany(n) and columns('a', 'b'). Read a value from a row as row.amount, row['amount'] or row[0]; row._mapping, row._asdict() and row._fields work too. Rows from columns('a', 'b') take row.amount or row[0], not row['amount'].

Source Rows as a Table

With Use Pandas Dataframes for Table Data on, a source is a read-only table. Looping over it gives each row as a dictionary, so row.amount and row['amount'] both work. You can also use:

• columns, empty, shape, values and index
• head(n) and tail(n)
• itertuples(), which puts the row position first as Index, so r[1] is the first column; itertuples(index=False) leaves it out
• iterrows(), which gives the row position and the row
• invoices['amount'], the values of one column, with sum(), min(), max(), mean(), unique() and tolist(); sum, min, max and mean skip nulls

Other pandas methods, such as loc and groupby, aren’t available. Shape the data in an earlier step and export the result.

What a Template Can’t Do

• Call a method whose name starts with to_, on any value — to_csv and to_json, and also harmless ones such as to_pydatetime.
• Read an attribute whose name starts with an underscore, other than a row’s _mapping, _asdict and _fields.
• Write or open files.

A to_ call or a file operation fails the step. An underscore attribute other than those three just renders as nothing.

Common Configuration Items

Data Mapper Configuration

The Data Mapper is used to map columns from the source data to the target data table.

Inspection and Populating the Mapper

Using the Inspect Source menu button provides additional ways to map columns from source to target:

• Populate Both Mapping Tables: Propagates all values from the source data table into the target data table. This is done by default.
• Populate Source Mapping Table Only: Maps all values in the source data table only. This is helpful when modifying an existing workflow when source column structure has changed.
• Populate Target Mapping Table Only: Propagates all values into the target data table only.

If the source and target column options aren’t enough, other columns can be added into the target data table in several different ways:

• Propagate All will insert all source columns into the target data table, whether they already existed or not.
• Propagate Selected will insert selected source column(s) only.
• Right click on target side and select Insert Row to insert a row immediately above the currently selected row.
• Right click on target side and select Append Row to insert a row at the bottom (far right) of the target data table.

Danger

Selecting Propagate All will duplicate columns if they already exist in the target list

Deleting Columns

To delete columns from the target data table, select the desired column(s), then right click and select Delete.

Source Columns That Are No Longer Listed

A target row can name a Source Column that the source data table no longer lists, for example after the source was inspected again. The step never removes such a row for you. Instead, the column name is shown in red behind a ⚠ warning sign, and hovering over it says the column is not in the source columns. In a step with several tables, such as a union, each table is checked against its own source columns.

A row with a Constant, an Expression or a Count (including nulls) summary is never flagged, because the step doesn’t read its source column. Nothing is flagged while the source data table lists no columns, for example before the source is first inspected.

When you save a step that still has flagged rows, a Stale Columns Found dialog lists them under the tab or table they are on, and offers three choices:

• Remove All and Save — remove the flagged rows, then save.
• Save Anyway — save the step as it is.
• Cancel — go back to the step without saving.

To keep a row and clear its warning, add the column back to the source data table, or pick a column the source data table lists.

Columns Missing From the Source Table

When you save, the source data table itself is checked as it is now. A column the step lists in its source columns but the table doesn’t have, for example one an earlier step renamed or dropped, means the step’s columns are out of date, and a step that reads such a column fails when it runs. Saving then opens a Missing Source Columns dialog that names each table and the columns it lacks, with two choices:

• Save Anyway — save the step as it is.
• Cancel — go back to the step without saving.

Nothing is removed for you, because the table may be what is wrong, and it can change again before the step runs. When rows are also flagged as no longer listed, both appear together in the Stale Columns Found dialog, and Remove All and Save removes only the flagged rows. While the source columns list is empty, the columns the step maps are checked instead, except rows with a Constant, an Expression or a Count (including nulls) summary, which read no column.

A source named with a variable, such as {year}/Sales, is checked as the variable stands when you save, and the dialog names the table it checked, such as 2026/Sales (from {year}/Sales). A variable that a loop sets holds the value from its last iteration, so a loop over tables of different shapes can warn about a table that the next run won’t read. A table that doesn’t exist yet, for example one an earlier step in the workflow creates, isn’t checked, and neither is a source whose variable has no value, a table the step finds by searching when it runs, or a table that can’t be read at the moment.

Column Data Type

Every target column carries a Type. A column propagated from the source keeps the source column’s type; a row you add by hand starts as Text, and you set Type to say what it should actually be — a column you intend to sum or sort needs its numeric or date type, because text sorts and sums as text.

A step saved with a column that has no type at all still runs. The type is worked out when the step starts, from the source column that column reads. Where there is nothing to read it from, it is treated as text: an Extract SQL step’s columns come from the query you wrote rather than from a mapped source, and where several sources carry the same column name under types that disagree, text is used rather than guessing one of them — a wrong guess produces a conversion the warehouse can reject, and anything converts to text.

Caution

Multi-Table Join is the exception, deliberately. It refuses to run and names the column instead, which is more useful than a guess in a step joining several tables. Set that column’s Type and re-save.

Setting Type yourself is still how you say what a column should be. Leaving it unset means accepting whatever the source column happens to be.

Changing Column Order

To rearrange columns in the target data table, select the desired column(s). You can use either:

• Bulk Move Arrows: Select the desired move option from the arrows in the upper right
• Context Menu: Right clikc and select Move to Top, Move Up, Move Down, or Move to Bottom.

Reduce Result to Distinct Records Only

To return only distinct options, select the Distinct menu option. This will toggle a set of checkboxes for each column in the source. Simply check any box next to the corresponding column to return only distinct results.

Depending on the situation, you may want to consider use of Summarization instead.

The distinct process retains the first unique record found and discards the rest. You may want to apply a sort on the data if it is important for consistency between runs.

Danger

Selecting all columns to determine distincriveness might make it appear as if it isn’t being applied. Select only the columns you feel define the distictivness of the data.

Aggregation and Grouping

To aggregate results, select the Summarize menu option. This will toggle a set of select boxes for each column in the target data table. Choose an appropriate summarization method for each column.

When Inspect Source fills the columns, a number column starts as Group By rather than Sum if its name reads as an identifier, such as id, customer_id, Order No or CustomerKey, if its table sets it as a key, or if the join uses it as a key.

• Group By
• Sum
• Min
• Max
• First
• Last
• Count
• Count (including nulls)
• Mean
• Standard Deviation
• Sample Standard Deviation
• Population Standard Deviation
• Variance
• Sample Variance
• Population Variance
• Advanced Non-Group_By

Note

When using aggregation, all columns must have a summarization type specified

For advanced data mapper usage such as expressions, cleaning, and constants, please see the Advanced Data Mapper Usage

To allow for maximum flexibility, data filters are available on the source data and the target data. For larger data sets, it can be especially beneficial to filter out rows on the source so the remaining operations are performed on a smaller data set.

Select Subset Of Data

This filter type provides a way to filter the inbound source data based on the specified conditions.

Apply Secondary Filter To Result Data

This filter type provides a way to apply a filter to the post-transformed result data based on the specified conditions. The ability to apply a filter on the post-transformed result allows for exclusions based on results of complex calcuations, summarizaitons, or window functions.

Final Data Table Slicing (Limit)

The row slicing capability provides the ability to limit the rows in the result set based on a range and starting point.

Note

For consistency, results that are sliced should have a sort specified

Filter Syntax

The filter syntax utilizes Python SQLAlchemy which is the same syntax as other expressions.

View examples and expression functions in the Expressions area.

Related

• Export steps
• Export To XML — for XML without a template
• Report steps — for PDF reports built from templates