Extraction, Transformation & Loading of Data
A common problem that companies face with moving data from one system to another is:
- How to gather data from multiple sources, in multiple formats.
- How to move that data to one or more data stores, when the destination might not be the same type of data store as the source.
- How to format, shape or clean the data before loading it into its final destination.
Various tools, services, and processes have been developed over the years to help address these challenges.
No matter the process used, there's a common need to coordinate the work and apply some level of data transformation within the data pipeline.
This help articles concentrates on the CX Index approach to this issue, namely the Extract, Transform, and Load (ETL) process.
Extract, transform, and load (ETL) process
Extract, transform, and load (ETL) is a data pipeline used to collect data from various sources.
- It then transforms the data according to business rules, and it loads the data into a destination data store.
- The transformation work in ETL takes place in a specialised engine, and it often involves using staging tables to temporarily hold data as it is being transformed and ultimately loaded to its destination.
- The data transformation that takes place usually involves various operations, such as filtering, sorting, aggregating, joining data, cleaning data, deduplicating, and validating data.
Often, the three ETL phases (Extract, Transform, Load) are run in parallel to save time.
For example, while data:
- is being extracted,
- a transformation process could be working on data already received and preparing it for loading, and
- a loading process can begin working on the prepared data, rather than waiting for the entire extraction process to complete.
How to load files into your CX Index database
In the ETL tab of App settings, select the Load button.
Fill in the fields as required.
Click the Select File button and select the relevant file from your computer. This should be a CSV file, delimited by semi-colons.
Note that ETL files should be uploaded in order as follows:
- Team Hierarchy
- Users
- Manager Access
- Surveys
- Questions
- Question Answer Options (Alternatives)
- Survey Channel (Recruitment Methods)
- Respondents
- Answers (Responses)
- Attributes
- Mail Texts
Click the Upload and Run button.
The file will appear in the list of import logs below. Ensure the file has a completed status before importing another file.
Click the Refresh button to refresh the import log list.
What happens if a file has missing data?
If a file is missing data on a row, the ETL tool will skip the row. This information is reported in the Logs section of App settings.