Transforming and enriching data

Learn how to apply transformations, customize columns, and organize your datasets for clarity and efficiency, along with tips and best practices.

Once you've connected a data source to Drivetrain, it is easy to prepare, transform and shape the data to make it suitable for modeling, reporting and analysis.

You can easily manipulate your data tables by adding custom columns, modifying existing columns including renaming, reordering, or hiding columns. This is quite useful for preparing, cleansing and transforming your data for more accurate and insightful analysis.

The following topics describe how you can customize and transform your data tables in Drivetrain:

• How to access data transformation tools

• List of transformations

  • Adding a custom column

  • Editing an existing column

  • Renaming a column

• Other actions

  • Reordering a column

  • Hiding a column

• How to save changes

• Tips & best practices

How to access data transformation tools

To go into Edit mode:

Go to a dataset. Click on the kebab menu (⋮) and select Edit.

You are now in Edit mode and can start making changes to your data table.

All transformation actions can be performed only in Edit mode.

List of transformations

Drivetrain helps users prepare easy-to-use data sets with the following transformation tools to prepare, blend, and analyze data.

• Add custom columns: Create new data points derived from existing ones.

• Change existing columns: Update the values or logic behind a current column.

• Rename columns: Rename columns to improve clarity and organization.

You can define these transformation using either:

• Natural Language (NL) prompt: Use natural language to define your transformation (e.g., “Sum of Sales and Tax”), or

• SQL-like syntax for defining advanced transformation logic.

By default, the input is set to Write with AI, which is the NL mode.

To switch to SQL-mode, click on Write with AI.

We recommend writing your desired transformation in NL mode. Drivetrain will automatically translate that into SQL-like statement which you can then edit as needed.

Adding a custom column

To add a new custom column, follow these steps:

1. Click on the + Add Column button, or on an existing column, and hover your mouse. Click on the kebab menu (⋮) and select Insert column left or Insert column right.

2. Provide a name for the new column.

3. Define the transformation using either NL prompts or SQL-like syntax.

Examples

• SQL-like syntax: CONCAT(first_name, ' ', last_name).

• Natural Language Prompt: "Calculate the difference between Order Date and Delivery Date".

Editing an existing column

On the desired column header, click on kebab menu (︙) and click Edit.

You can now update the column’s formula using either:

• SQL-like syntax: Write the formula in SQL for advanced logic.

• Natural Language (NL) Prompts: Use natural language to define your transformation (e.g., “Sum of Sales and Tax”).

Examples

• Transforming a date column: Convert "Order date" to a different date format.

  • SQL-like syntax: FORMAT_DATE('%Y-%m-%d', order_date)

• Update price values: Increase all prices by 10%.

  • SQL-like syntax: sales * 1.10

• Replacing some values in the column with another

  • NL Prompt: Replace all occurrences of “Exp” with “Expense”.

Renaming a column

On the desired column header, click on vertical ellipsis icon (︙) and click Edit column.

Renaming a column impacts dimensions created from that column. It may take some time for the change to be reflected across all your models and reports where that dataset is used.

To check all the places a dimension is used, go to Settings > Dimensions page. Select the desired dimension, click on the vertical ellipsis (⋮) on the top right and select Show usage.

Other actions

• Reorder Columns: Change the order of columns for better readability.

• Hide Columns: Remove irrelevant columns from view for cleaner datasets.

Reordering a column

Click on the desired column name and hold down on the mouse button to move the column to the left or right.

This affects the way columns are presented in:

• Metric creation views.

• Underlying data tables when reviewing reports.

Hiding a column

Click on the Columns button and on the dropdown list, uncheck the columns you want to hide.

This is useful for hiding irrelevant or unused columns to maintain dataset hygiene without deleting them.

How to save changes

Once you have made your transformations, click Save to apply your changes. When you save:

• The data model is recomputed to reflect your transformations.

• All dependent models and reports are updated.

Saving is a resource-intensive operation and may take several minutes to a few hours depending on the dataset size.

You can also Save a Copy if you don't want to save the changes to the current data table.

Tips and best practices

Every transformation applied to a dataset is logged in the Changelog shown on the right pane. This log is accessible in Edit mode, allowing you to:

• Review past changes.

• Modify previous transformations.

• Remove transformations altogether.

The changelog provides a comprehensive history of dataset modifications for easier auditing and management.