Transformation queries

Network can transform data so you can pre-process the data before loading it or post-process the data after exporting it.

In this release, enhancements have been made to help you transform additional file names and types.

File options

22R3.1

Administrators can now process the following types of files names and file types in transformation queries.

Inbound queries

  • non-static files names

  • delimited text files with any file extension

Previously, only .csv files with static file names were supported for inbound queries.

Outbound queries

  • DCR Export files

Previously, outbound queries supported files for Data type target subscriptions only.

These enhancements are available by default in your Network instance.

Inbound queries

Inbound transformation queries can now process files using the file name alias that you define in the source subscription configuration. This enables you to query input files that have non-static file names and delimited text files with any file extension.

For best results, reference input files by their alias for all new inbound transformation queries. Existing inbound queries that use a static file name are still supported.

Example - Non-static file names

The HCP source file that you want to process has a timestamp (for example, HCP_2023-01-30_04-45-07.csv) in its file name. In the source subscription configuration, define the file name as HCP* and define the alias as HCP_INPUT_FILE.

Aliases must be defined with uppercase letters.

In the transformation query, refer to the source file by its alias (HCP_INPUT_FILE) instead of its file name. Notice that the __csv file extension is required to distinguish the file from other regular table names in reporting.

Tip: File names and file aliases are not case-sensitive in transformation queries.

Link the transformation query to the source subscription. When the subscription runs, the transformation query will process the input file by its file alias.

Example - Delimited files with any file extension

The HCP source file has a .txt extension (for example, HCP.txt).

In the source subscription configuration, define the file name as HCP and define the alias as HCP_INPUT_FILE.

In the transformation query, refer to the source file by its alias (HCP_INPUT_FILE). Notice that the __csv file extension is required to distinguish this as source file; otherwise, it will be considered a reporting table name.

Link the transformation query to the source subscription. When the subscription runs, the transformation query will process the .txt input file by its file alias.

Outbound queries

Transformation queries can now post-process DCR export files (third-party owned DCRs) so you can export it in the format that third party systems expect to receive.

Example

To transform third-party owned DCRs after the files are exported, create a transformation query that references the DCR export files:

  • DCR_BATCH (header file)

  • DCR_FIELD_BATCH (line item file)

In the transformation query, omit the UNIX timestamp that is automatically applied to the export files in the archive (for example, a file name is DCR_BATCH_1669990415331.csv).

Add the __csv extension to each file name in the query.

This query joins the header file with the field file to produce a single export file instead of two.

In the DCR type target subscription, add the transformation query to the configuration.

After the job runs, you can view the Job Details to see the records that were exported and the transformation queries that were applied.

Job details

Note: The Data Flow Diagram that displays on Data type target subscriptions is not available on DCR type subscriptions.