Defining and running conversions

Overview

The conversion is how the source data gets transformed and structured to fit the intake requirements of the target system. Source data is read in, transformed and outputted new data sets.

Conversions are defined and managed through the creation of Conversion Jobs, which can be run multiple times. A job consists of the following:

A Target and a set of its tables to generate the data for in this job. Those tables must have fields that have had mappings and transformations defined for them.
A set of JOIN statements (when required) defining the record level data relationships between the Source tables that are being used for each Target table.
An optional set of 'data filters' to be used to restrict what source records are used to create the data set, for each Target table.
The data to migrate: a set of data files to use for each of the Source tables used in the Mappings.
Runs: each time a job is run it creates logs, outputted data files or error reports that are stored against that run, allowing for iterative job definition and output comparison.

Defining a conversion job

Each job created must have a unique name and can modified and deleted, depending on the user's role and specific project restrictions. Each job is autosaved and does not need to be fully defined, it can be created and then worked as the project evolves. Defining a job consists of a series of steps.

Describe the conversion: name the job and add any useful meta data like tags or a description.
Set the scope, by defining the outcome of the job in terms of the Target:
1. Select the Target schema the job is for
2. Select which Target tables are to be outputted
3. Select which fields to include based on their mapping status
Configure the output:
1. Set data format - delimiter, encoding, type (extension)
2. Set where they should be outputted. You can set it to output inside the job (files need to be copied to reuse elsewhere in the project), or to a specific folder (outputted files can be accessed and used in the project alongside any other uploaded file asset)
Define the Source record relationships and any record filtering for each Target table selected to be outputted. For each Target table there are 2 parts:
1. A Source table JOIN statement, is required when 2 or more Source tables are used for the same Target table. This is a standard SQL statement describing how the Source table records should be related to create single records in the target. Note this is not a SELECT statement, it is only the JOIN part.
  Example: targetZ.tableX uses fields mapped from SourceA.table1, SourceA.table2, SourceB.table4. Where SourceA.table1 is the primary table and the common key for all is account_id. The JOIN would look like so:
2. ```
SourceA.table1 LEFT JOIN SourceA.table2
ON SourceA.table1.account_id = SourceA.table2.account_id
LEFT JOIN  SourceB.table4 
ON  SourceA.table1.account_id = SourceB.table4.account_id
```
3. A SQL based source record filtering statement (optional). This supports WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT, based on the Source table fields available.
  1. WHERE (must be first item if needed)
  2. GROUP BY (requires aggregate in the mapping for that target table - all used source fields must be referenced in the GROUP BY or be aggregated in the mappings)
  3. HAVING (can only be used in relation to a GROUP BY as a post grouping filter)
  4. ORDER BY (depending on data and complexity this will impact completion times)
  5. LIMIT (set the max number of rows to output, before an exit)
  Example: Only include records where SourceA.table1.account_status is 'active' and for testing purposes only give me 1000 records:
4. ```
WHERE SourceA.table1.account_status = 'active'
LIMIT 1000
```
Select the data to use in the conversion. Specify which data files to use for which Source tables. This can be done in a few ways:
1. Select from previously associated data files.
2. Select from any uploaded files.
3. Select a folder and auto match the files to each table at run time. This option allows you to rerun the job with different data, without having to edit this step.

Running a conversion job

A conversion job can be run multiple times, each run reflects the current definition in the job. A run is timestamped and previous runs are archived against the job and can be easily reviewed. Each run contains the following for each Target table it includes:

A data file named the same as the target table.
A process log txt file explaining the what was done.
If an error occurs, an error log txt file.

Chaining conversion jobs

For some migration projects you may need to create reference data from the source data you have, or some other pre or post processing requiring a sequence of conversions. This is best achieved using the folders for input data and output data. These allow you to chain jobs together where the output folder of one job is used as an input in the next job. Once this chain is defined, you can drop the appropriately named files into the first jobs input folder for the data files and then simply run each subsequent conversion in order.