ETL: Defining Output Schemas

Save reusable output schemas in Integrate.io ETL by listing columns or uploading a sample file, then conform Select components to them with Smart Map.

A schema is a reusable column contract: an ordered list of column names and data types saved at the account level. A Select transformation conforms to a schema through Smart Map. A file storage destination using the fixed width format writes its columns in the schema’s order. Define the contract once and reference it from any pipeline instead of rebuilding the column list in each package.

Open Schemas from the main left menu. The entry appears when the ai_select_mapping account feature is enabled, the same feature that gates Smart Map. Contact your account owner or Integrate.io support if it is not visible.

When to use schemas

• Several pipelines write the same file or table layout and should agree on column names, types, and order.
• A partner or downstream system dictates a fixed delivery layout that your packages must match.
• You want Smart Map to conform a Select to a target that is not a downstream component or a live database table.

The schemas list

The list shows each schema’s Name, its column count, and Last edit. Twenty schemas load at a time; scrolling to the end of the list loads the next twenty. Click the Name or Last edit header to sort by that column. Clicking the active header again reverses the direction, and switching to the other header keeps the current direction.

Each row offers three actions:

• Edit schema opens the column grid for changes.
• Duplicate schema creates a copy named “Copy of [name]”. Schema names are unique per account, ignoring case.
• Delete schema removes the schema. Any pipeline conforming to it loses its target and needs a new one. Deletion cannot be undone.

Create a schema

Click Create schema and enter a schema name. Then list the columns by hand, or read them from a sample file.

Column properties

Each row in the column grid carries:

• Column name. Must be unique within the schema.
• Type. One of string, integer, long, float, double, boolean, or datetime.
• Order. Where the column sits in the output. Drag a row or type a new number to move it. For a file delivery the order is part of the contract.
• Required. Marks the column as expected to carry a value. A Select conforming to the schema asks for confirmation before saving with a required column unmapped. The flag is not enforced at run time.
• Blank filler. The column holds its position in the layout and always writes an empty value. Use it when a fixed layout reserves a slot that carries no data.

A schema stores names, types, and order only. Column width, padding, and alignment belong to a single delivery, so they are set in the fixed width layout of the file storage destination that writes it.

Reading columns from a sample file

Click Upload a sample file and choose a CSV, delimited text, or Excel (.xlsx) file. Integrate.io ETL reads the header row and up to 100 sample rows, adds a grid row per header, and infers a data type for each column from the sampled values. The From your sample column shows the first few values each type was read from. Nothing is saved until you click Create schema, so you can edit any name or type first.

Three controls above the grid re-read the same upload when the defaults guess wrong:

• Header row. The row that carries the column names, counted from 1 as you would read it in Excel or a text editor. Set it when the file has a preamble above the header.
• Separator. Comma, tab, pipe, or semicolon. Shown for delimited files. The separator is detected automatically on the first upload.
• Sheet. Which worksheet to read. Shown when the workbook has more than one sheet. The first sheet is read by default.

Type inference is conservative:

• Numbers with a leading zero, such as zip codes or padded identifiers, stay string so the zero is not lost.
• true, false, yes, and no become boolean. 0 and 1 stay integer.
• A value must be a complete, valid date to count as datetime.
• A column with mixed values falls back to string.

A column marked with an asterisk had little or no data to infer from. Check its type against the sampled values before saving.

For example, this sample:

order_id,customer_email,amount,ordered_at,zip
1001,[e-mail],19.99,2026-08-01 10:15:00,02134
1002,[e-mail],7.50,2026-08-02 14:02:00,10001

infers these columns:

Column	Inferred type
order_id	integer
customer_email	string
amount	float
ordered_at	datetime
zip	string

zip stays string because 02134 starts with a zero.

File size limits

• Excel uploads can be up to 10 MB. For plain-text samples, the first 1 MB is read.
• Up to 512 columns are profiled.
• Large Excel workbooks are accepted even when their internal text storage is bigger than the read limit. Column names sit at the start of that storage, so they are kept. Text values deeper in the workbook can appear empty, in which case the affected columns fall back to string. Review the types in the grid after uploading a very large workbook.

Use a schema in a package

• In a Select transformation, set the Smart Map Conform to picker to Saved schema and choose the schema. Smart Map then maps the Select’s output to the schema’s columns. See Smart Map.
• When a Select conforms to an existing database table, Save as schema in the Smart Map bar saves the table’s columns, in their current order, as a new schema that other pipelines can reference.
• When an upstream Select conforms to a saved schema, a fixed-width file storage destination writes its fields in that schema’s order.

Permissions

Schema access follows package permissions, so a schema is never more or less reachable than the packages that reference it:

• Viewing the schemas list and opening a schema requires listPackages or viewPackage.
• Creating a schema requires createPackage.
• Editing a schema requires updatePackage.
• Deleting a schema requires deletePackage.

Standard roles hold these permissions together. The split only matters for custom roles.

Related

Select Transformation

File Storage Destination

Custom Roles