schema-custom-types-ddl

Custom Column Types

Override default column types in materializations using castToString or custom DDL.

Docs: https://docs.estuary.dev/guides/advanced-usage/custom-column-types/

When to Use

• Integer overflow or value out of range on large IDs → castToString or DDL: "NUMERIC(...)".
• Monetary values lose precision as DOUBLE → DDL: "NUMERIC(22,6)".
• Multi-type unions ([integer, null, string]) mapped to JSON when you want a string → castToString.
• Need a specific destination type (e.g., JSONB instead of JSON, custom VARCHAR length).

Two Options

castToString — Convert to String (Simpler)

Converts the value to its string representation before writing — numbers become "3", booleans "true", objects stringified JSON. More robust than DDL: "STRING" because it actually transforms the data.

fields:
  recommended: true
  require:
    large_id: { castToString: true }

Field configs go under fields.require. include is accepted as a legacy alias but is normalized to require on publish — if you write include, your next pull-specs will show require. Use require to avoid confusion.

DDL — Custom Column Definition (Advanced)

Specifies the exact SQL column type for the destination. The DDL string is passed verbatim to the database.

DDL only changes the column definition — it does NOT transform the data. The burden is on you to ensure the field's data is compatible with the specified type. When DDL is set, the connector disables its normal type validation for that field.

fields:
  recommended: true
  require:
    amount: { DDL: "NUMERIC(22,6)" }
    json_payload: { DDL: "JSONB" }

You can combine both: { castToString: true, DDL: "VARCHAR(100)" } — value is converted to string, then stored in the custom column type.

Prerequisites

• Existing materialization to modify
• For DDL: knowledge of destination database SQL types
• flowctl authenticated

Procedure

Step 1: Pull the Materialization Spec