Skip to main content

How to Correctly Add/Delete Columns

Background

After extraction datasets or extraction datasets are created, sometimes you need to add/delete fields or modify field names and types.

Wrong Method ❌

Click the "Update" button on the dataset overview page, replace the old SQL with new SQL in the popup box, and change the update method to "Overwrite Old Data".

Reason: This update entry is only suitable for data updates where the data model structure remains unchanged and only query conditions change. Modifying the dataset structure here will not take effect and will cause update failure errors.

Correct Method ✔️

  1. Enter the dataset details page, click the "Model Structure" tab.

image.png

  1. Click "Edit" to enter the SQL query editing page, modify the SQL query statement, click "Preview" to ensure the SQL statement can query normally.

image.png

  1. After previewing, click the "OK" button in the bottom right corner of the page. If fields have changed, a field association confirmation window will pop up. You need to manually click the dropdown menu to match the original field and new field names for the yellow highlighted prompts, then continue clicking "OK";

image.png

  1. If there are new fields, you will continue to be prompted to rename. Click "OK". (Whether to set the rename here doesn't matter, because the data structure can be modified later)

image.png

  1. If deduplication primary key was set before, you can re-select the deduplication primary key at this step, then continue clicking "OK". After completion, automatic update will be triggered, and old data will be overwritten.

image.png

  1. After the data update is complete, check the overview page and you will find that it is already the new data model structure.

If incremental updates are set, after the dataset update is complete, you need to go to the "Data Update" tab to promptly modify the SQL query statement used for incremental updates and save it.

The data structure should be consistent with the main structure of the SQL statement in the model structure, with only different query conditions, otherwise subsequent incremental updates will fail due to inconsistent data structures.

image.png

  1. Modify field types and names: Dataset Overview — Data Structure interface, you can manually modify the display names and types of fields. This page can also see all new fields and the formulas used.

Note: After field renaming, new fields still reference old fields, and you need to manually replace them with new field names in the data details.

image.png

Notes

Not recommended method: For extraction datasets, when modifying SQL statements in the data model, use a lot of "as" for field renaming, use functions for new fields or calculations, and multiple nested subqueries. Reason: Data update speed is slow; error-prone, subsequent modifications are troublesome, not conducive to maintenance.

Recommended method: Keep SQL statements as simple as possible, only extract original table data. Type modification and renaming can be done in the final step of creating a new dataset or in the "Data Structure" tab of the overview page after creation. New fields are recommended to be added in the overview page or ETL.