Auto Field
Detect what text columns really contain, such as numbers or dates, and convert them to that type. Whole-number columns are narrowed to BIGINT, and numeric 0 and 1 can optionally become booleans. Unchecked columns are left as they are.
Detect column types
Manual Input supplies this order table; Select converts its columns to text before Auto Field. Auto Field detects numbers, booleans, dates and timestamps, while the codes and mixed quoted amounts stay as text.
Auto Field interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| code | quantity | amount | paid | order_date | received_at | quoted_amount |
|---|---|---|---|---|---|---|
| 001 | 2 | 12.5 | true | 2026-09-07 | 2026-09-07 09:15:00 | 12.50 |
| 002 | 1 | 8 | false | 2026-09-08 | 2026-09-08 14:30:45.123 | pending |
| 003 | 3 | 6.25 | true | 2026-09-09 | 2026-09-09 00:00:00 | 6.25 |
- Clear the Infer type checkbox for
quantity. The column stays in the output, but its type changes fromBIGINTback toVARCHAR. Select Infer type again to restore numeric quantities. - Compare
order_datewithreceived_at. The first becomesDATE; the second becomesTIMESTAMPand keeps its time of day, including fractional seconds. - Look at
quoted_amount: it mixes numeric text withpending, so the entire column staysVARCHAR. Select the Manual Input block and replacependingwith8.00. Select Auto Field again:quoted_amountnow becomesDOUBLE. - Look at the
codecolumn. Values such as001stayVARCHARtext with their leading zeros, even with Infer type selected.
When to use
Use Auto Field when numbers and dates arrive as text and you want Strata to work out the types. To set a type yourself, use Select. Leave identifiers with leading zeros as text.
Read the output
The example starts with three orders. Select converts Manual Input's inferred columns to VARCHAR text before Auto Field. Auto Field keeps the same rows, column names and the configured grid order, and changes the types of selected text columns when their values match.
Each column shows a different result:
codestays VARCHAR text, preserving leading zeros in001,002and003.quantitybecomes BIGINT, with whole numbers2,1and3.amountbecomes DOUBLE, with decimal numbers12.5,8and6.25.paidbecomes BOOLEAN, withtrueandfalsevalues.order_datebecomes DATE, with dates from2026-09-07to2026-09-09.received_atbecomes TIMESTAMP, preserving times such as09:15:00, fractional seconds in14:30:45.123and midnight in00:00:00.quoted_amountstays VARCHAR because it mixes12.50and6.25with the wordpending.
The mixed quoted_amount column stays entirely as text; Auto Field doesn't convert just its numeric cells or replace pending with NULL. If you replace pending with 8.00, every value can become a number and the column becomes DOUBLE. Auto Field changes its own output; the values from Select stay text.
How Auto Field chooses a type
For each selected VARCHAR column, Auto Field checks all non-NULL values for BOOLEAN, then BIGINT, then DOUBLE, then DATE, then TIMESTAMP. It uses the first matching type.
Date-only values such as 2026-09-09 can match DATE. Values with an explicit time, such as 2026-09-09 09:15:00, are skipped for DATE so they can become TIMESTAMP and keep their time. A column mixing dates and timestamps becomes TIMESTAMP when all its non-NULL values can be converted; the date-only values use midnight. TIMESTAMP doesn't store a timezone offset.
If no candidate matches every non-NULL value, the column stays as text. NULL values don't block conversion and stay NULL. A column with no non-NULL values keeps its current type.
Integer-like strings with leading zeros, such as 001 or 007, are skipped for numbers so those codes stay text. Detection runs each time the block runs, so a change in the input can change the output type.
For numbers with a known locale or formatting, use Number. For dates with a known format, use DateTime.
To choose a specific output type yourself, use Select.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
Configure the block
The Fields grid lists the columns from the connected input. Choose which ones Auto Field should inspect. A chosen column missing from the input is skipped with a warning, and its settings are kept.
- Treat 0/1 as booleans
Convert selected numeric columns containing only 0 and 1 to booleans, including numeric text. This also affects binary counts and identifiers. NULLs are preserved.
- Column
Identifies the input column. Its name stays the same; list order determines output column order.
- Infer type
Select to infer text or numeric types. Clear to preserve the column's values and type. New input columns are selected automatically.
Auto Field FAQs
- Does Auto Field need an input connection?
- Yes. Connect the table to In. Auto Field keeps rows and column names, and the Fields grid sets the output column order.
- Which types can Auto Field detect?
- Selected text columns can become BOOLEAN, BIGINT, DOUBLE, DATE or TIMESTAMP. Whole-number FLOAT, DOUBLE and DECIMAL columns become BIGINT when conversion keeps every value exactly and fits its range. Other typed columns pass through unless numeric Boolean inference is on.
- Will timestamps keep their time of day?
- Yes. Text with a time in it, even midnight, is checked for TIMESTAMP rather than DATE. A column mixing dates and timestamps becomes TIMESTAMP if every non-NULL value can be converted; date-only values then use midnight. TIMESTAMP doesn't store a timezone offset.
- Why did my numeric-looking column stay as text?
- Every non-NULL value must fit the type. Mixed text, codes with leading zeros and whole numbers outside the BIGINT range all keep a column as text.
- Do missing values prevent conversion?
- NULL values are ignored when detecting types and stay NULL in the output. A column with only NULL values keeps its current type.
- Does Auto Field inspect just a sample?
- No. It checks all non-NULL values in each selected text column. If the input values change, the inferred type can change on a later run.
- Can zero and one become booleans?
- Turn on Treat 0/1 as booleans to read selected numeric columns and numeric text as true/false flags. Every non-NULL value must be exactly 0 or 1, and a column of all 0s or all 1s qualifies too. It's off by default because counts and IDs can also hold these values.
- Does numeric inference keep every value exact?
- Existing numeric columns narrow only when conversion is exact. Fractional text uses normal DOUBLE approximation. Empty and all-NULL columns keep their types; negative zero, NaN, infinities and fractional numeric values aren't narrowed to integers.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.