Generating Child Tests from an Import File

What the import file does

A template test generates one child test per metadata object. With Import File as the generation source, you decide which objects those are, and what each child test should be given, by uploading a spreadsheet. Every data row in the file becomes one child test.

Use it when the catalog alone cannot describe the test population:

• The mapping between objects lives outside Validatar. A source table and its target table, a foreign key and the parent it must resolve to, a table and the query that reconciles it.
• Each object needs its own parameters. A tolerance per table, a threshold per column, a lookback window per metric.
• Someone else owns the list. A QA lead or data steward maintains the spreadsheet and you materialize from it.

The catalog still matters. Every row must name a schema and table (and a column for column-level templates) that already exists in the metadata of the template's data source. The file adds information to catalogued objects. It does not add objects.

For the other two generation sources, see Generating Child Tests for Specific Metadata Objects and Generating Child Tests using a Metadata Script. Worked examples of import-file templates, with downloadable files, are in Template Test Examples: Import File.

Choose Import File as the generation source

1. Open the template test. On the Test Definition tab, find Object Selection and Definition.
2. Select Import File.
3. Save the test. A File Selection tab appears. The file cannot be uploaded until the test has been saved once.

Selecting Import File also adds a From File checkbox next to most settings on the Test Definition and Child Test Configuration tabs. Ticking one tells Validatar that the setting varies per child test and will be read from a column in the file. The next section explains which columns those checkboxes create.

File layout

The file is a .xlsx workbook. Validatar reads the first sheet only, treats the first row as the header row, and matches columns by header text. Column order does not matter. Header matching ignores case, with one exception noted below.

Columns Validatar always understands

The three required headers are checked case-sensitively. Write them in lower case exactly as shown. Other headers are matched without regard to case.

Any var. column whose reference key does not match a declared variable is ignored. Declare the variable on the template first, then add the column.

Columns created by From File

Each setting you tick as From File adds one expected column. The header is the setting's display name.

Every ticked setting must have its column present, or upload validation fails with Missing column.

Variables or From File?

Both put a per-row value into a child test. They differ in where the value can land and what happens when a cell is blank.

• A variable renders anywhere Handlebars is accepted: SQL, names, descriptions, folder paths, tolerance fields, control values such as a numeric target or a window size. Declare it once, reference it as {{var.key}}, add a var.key column. The declared default applies when the file has no such column.
• A From File setting only exists for the fields that offer the checkbox, and the value must be one of that field's accepted values. Nothing else can read it.

Prefer variables. Use From File for the few settings that have no variable form, such as switching the control data source or the success criteria type per row. The shipped examples in Template Test Examples: Import File carry every per-row value, tolerances included, as variables.

Rules the parser applies

• Blank rows are skipped and do not count toward the record count.
• A header cell left blank drops that whole column.
• Two columns with the same header are renamed name and name1, and the second one then fails as an invalid column. Keep headers unique.
• Formula cells are read as the formula text, not the computed value. Paste values before uploading.
• Date cells are converted using the server's short date format. Store dates as text if the format matters.
• Numbers may be stored as numbers or text. Both parse.
• Extra sheets are ignored. Extra columns that Validatar does not recognise fail validation as Invalid column.
• Headers such as column.data_type or table.type are accepted for compatibility but their values are ignored. Those attributes always come from the catalog.
• The request limit is 100 MB. There is no row limit.

Generate a starter file

Validatar builds the header row for you so the column names match the template exactly.

1. On the File Selection tab, click Generate Import File.
2. Choose Headers Only for a blank file, or Previous Version to download a file that was uploaded earlier (the grid shows date, uploader, record count, and file name).
3. Click Download File.

The generated header row contains schema.name, table.name, column.name when the template is column level, one var. column per declared variable, and one column per setting ticked From File. It does not contain test.key. Add that header yourself if any object needs more than one child test.

If you add a variable or tick another From File box later, generate the file again or add the new header by hand.

Upload and validate

1. On the File Selection tab, click Upload Import File.
2. In Import Template Test File, choose the .xlsx file. Only that extension is accepted.
3. Validatar parses the workbook, then validates every row against the template and the catalog.

If validation passes, the tab shows Using file uploaded on {date} with {n} records. The previous file stays in the version history.

If rows reference metadata links that do not exist yet, the dialog lists them and asks Do you want to proceed? Confirm to store the file anyway. Materialization treats a missing link on the control data set as an error for that child, so fix the link or the row before materializing.

If validation fails, the dialog shows a grid with Row, Column, and Description. Row numbers are spreadsheet rows, so the first data row is 2.

Materialize

Click Materialize Child Tests. Validatar reads the current file and creates, updates, or deletes children according to the options you pick in the dialog.

How rows turn into child tests:

• The child's identity is its key: test.key if present, otherwise schema.table or schema.table.column. Keys are stored in lower case. Re-uploading a file with the same keys updates the existing children in place; rows that disappear are candidates for deletion.
• The child test name, description, folder path, and every SQL script are Handlebars templates rendered per row. {{schema.name}}, {{table.name}}, {{column.name}}, and the catalog attributes such as {{column.data_type}} come from the catalog. {{var.x}} comes from the row. {{test.key}} is the key.
• Because {{var.x}} is plain text substitution with no escaping, a variable can carry an entire SQL statement, a column list, or a predicate. A blank cell in a var. column renders as empty text. The variable's default value applies only when the file has no column for that variable at all.
• The test data set always runs on the template's data source. The control data set runs on the template's control data source unless Target Data Source is ticked From File.
• Severity and quality dimension are copied from the template. A row cannot change them.

If a child needed a default because a value did not parse, the materialization review lists it as a warning on that child.

Import files in exported test packs

When you export a template test whose generation source is Import File, the export includes the current file when you select the include-file option for that test. The workbook travels inside the XML as an ImportFile element. Importing that pack recreates the template with its file attached, ready to materialize.

A pack exported without the file imports as a template with no file. Materializing it fails with Upload a file on the File Selection tab before materializing child tests. Upload the file first.

Related

• Template Tests
• Configuring a Template Test
• Understanding Child Test Materialization
• Template Test Functions
• Template Test Examples: Import File