Managing & Validating ETL Automation Scripts for Data Templates

Audience: Power users, method owners, lab leads (not necessarily programmers) · Goal: Choose the right script, validate results, and communicate changes effectively.

This article explains how ETL automation scripts connect to Data Templates in Albert, and how non-coding “owners” can define requirements, validate behavior, and request changes safely.

Each ETL script is attached to one or more Data Templates. When you upload a file for that Template, the script runs automatically and produces the “File Fields” you see during import.

Every ETL script:

• Is linked to one or more Data Templates.

• Runs automatically when you upload a file for that Template.

• Outputs a DataFrame whose columns appear as “File Fields” on the Map fields to import data screen.

Important:

• Script name must match what the Data Template expects.
  If the Template is configured to use Rheometer_ETL_v2, that is the script that must be maintained under that name.

• Column names in the script output must match the Data Template exactly.
  This includes:

  • Spelling.

  • Upper/lower case.

  • Units and symbols (for example, (min), [%], µ).

If you change a column name, unit, or symbol in the Data Template, the script output must be updated to match — otherwise mapping breaks or creates “almost identical” fields.

You don’t need to write Python, but you are accountable for defining what “correct” looks like and validating that the script behaves accordingly.

1. Define the desired Data Template.
   Decide:

   • What each column should be called (exact text).

   • What the units are.

   • Which columns are mandatory.

2. Provide example files.
   Share raw instrument exports (the input of the script) and, ideally, multiple examples including edge cases.

3. Validate script behavior.
   Check that each column in the final Data Template is correctly populated for a few runs.

4. Request changes clearly.
   For example:

   • “Add a new column for X”.

   • “Change unit from seconds to minutes”.

   • “Ignore rows where ‘Sample Type’ = ‘Blank’”.

1. Prepare test files.
   Use at least 2–3 example exports:

   • A “normal” run.

   • A run with blanks or calibrations.

   • A slightly unusual one (missing values, extra comments).

2. Run the import.
   Open the Task / Block using the relevant Data Template, upload the file, and let the script run. Observe the mapping screen.

3. Verify column names.
   Confirm that the column names shown in the mapping correspond exactly to your Data Template. There should be no “almost the same” columns.

4. Verify values.
   Choose 3–5 rows and manually compare the original file with the imported data in Albert. Check:

   • Units are correct (for example, minutes vs seconds).

   • No decimal/comma issues (for example, 3,5 becomes 3.5, not 35).

5. Check inventory & lot mapping (if used).
   If the script uses inventory IDs and lot IDs:

   • Confirm that the correct inventory item(s) are linked.

   • Confirm that lot IDs match your rules (for example, LOTB56423).

6. Document approval.
   Once you’re satisfied, record:

   • Script name.

   • Version or date.

   • Who validated it.

   Store this in your internal method or SOP documentation.

Note: Treat ETL validation like a method qualification. Re-test when instruments, export formats, or Data Templates change.

When a script needs changes, send a concise brief to your ETL/script contact so they can update it without guesswork.

Include:

• Script name (exactly as in Albert).

• Data Template name.

• Description of the issue or new requirement.

• Example file(s).

• Optional: screenshot from “Map fields to import data”.

Example request:

Script: ColorBatch_CIE_Lab_ETL
Data Template: Color Batch CIE

Issue: The dE column is currently empty when CIE DE is present in the file.
Request: Map file column CIE DE to Data Template column dE.
Attached: 2 example files showing the issue.

The file likely changed (column names or positions) or comes from a different instrument. Check that you are using the correct export format.

The script may be reading the wrong sheet, the wrong header row, or the wrong delimiter. Share the file with your script developer.

The lot ID in the file does not match any lot in the task’s inventories, or the transformation rule for lots is wrong. Verify lot naming rules and inventory configuration.

Need more help with ETL automation scripts? Contact support.