How-To: Add new dimensions to an existing datasource without losing historical data

Overview

Adding new dimensions to an existing datasource may require dropping and recreating the table due to potential changes in the granularity and/or the primary key of the data. This guide explains how to back up your datasource beforehand, and avoid losing any historical data.

Instructions

Backing up your datasource using Data Explorer

1.) Navigate to Data Explorer by selecting Reporting → Explorer in Alli

2.) Create a new report by clicking on New Data Report. Select the SQL - Edit Custom SQL option.

3.) Enter the following query:

select *
from {alli_client}.{datasource}
where {date_column} < {minimum_backfill_date} --OPTIONAL

Legend (excluding the brackets):

• {datasource} - The name of the datasource

• {date_column} - The column name for date

• {minimum_backfill_date} - The minimum backfill date allowed by a platform API, e.g., today’s date two years ago for DV360

  • This minimizes the size of the backup, and ensures that the API is used as much as possible to backfill the updated datasource

4.) Click on Save and name it as {datasource}__bak to save your report.

5.) Export your report by clicking on the three dots next to the Publish button, and selecting Export. Click on Okay in the warning if prompted.

6.) If successful, you should see a the following success message.

Success!

Your Export was added successfully. You will receive an email with a link when it is ready to download.

Once you receive the email, download the CSV file – this is your backup data for the datasource.

Modifying the datasource

7.) Navigate to the datasource in question and add the necessary field(s) via the Define your data tab

8.) Save the datasource. You may see a warning message about having to delete the data - click on the red Save and Delete Current Data in Table button.

9.) Backfill the updated datasource as far back as possible.

Manually inserting the backup to the datasource

10.) When the backfill has finished, query the table in and observe the order of the columns. Take note of the position of the newly added column(s).

11.) Open the CSV file downloaded from step #6 in Excel or Google Sheets, and insert a dummy column(s) in the same position as the newly added column(s) from the previous step. Make sure to populate the header row. Save the CSV file.

12.) Manually upload the CSV file to the updated datasource by selecting Upload File under the Load Data tab. This should succeed assuming your backup and datasource schemas match (previous step).

Related articles