Documentation

    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.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    codequantityamountpaidorder_datereceived_atquoted_amount
    001212.5true2026-09-072026-09-07 09:15:0012.50
    00218false2026-09-082026-09-08 14:30:45.123pending
    00336.25true2026-09-092026-09-09 00:00:006.25
    1. Clear the Infer type checkbox for quantity. The column stays in the output, but its type changes from BIGINT back to VARCHAR. Select Infer type again to restore numeric quantities.
    2. Compare order_date with received_at. The first becomes DATE; the second becomes TIMESTAMP and keeps its time of day, including fractional seconds.
    3. Look at quoted_amount: it mixes numeric text with pending, so the entire column stays VARCHAR. Select the Manual Input block and replace pending with 8.00. Select Auto Field again: quoted_amount now becomes DOUBLE.
    4. Look at the code column. Values such as 001 stay VARCHAR text 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:

    • code stays VARCHAR text, preserving leading zeros in 001, 002 and 003.
    • quantity becomes BIGINT, with whole numbers 2, 1 and 3.
    • amount becomes DOUBLE, with decimal numbers 12.5, 8 and 6.25.
    • paid becomes BOOLEAN, with true and false values.
    • order_date becomes DATE, with dates from 2026-09-07 to 2026-09-09.
    • received_at becomes TIMESTAMP, preserving times such as 09:15:00, fractional seconds in 14:30:45.123 and midnight in 00:00:00.
    • quoted_amount stays VARCHAR because it mixes 12.50 and 6.25 with the word pending.

    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.