Manually Update Schemas

If you do not opt to automatically evolve schemas, you can still manually update specifications as necessary. This can be useful when you want tight type checking and want to be alerted when data does not conform to the existing schema.

warning

For most use cases, Estuary's automatic schema evolution is the preferred method of keeping schemas up to date. Manual updates should be reserved for more advanced configurations.

For manual updates, ensure all auto-discover options are toggled off in the capture or remove the autoDiscover stanza from the specification file. This stops Estuary from updating the flow://connector-schema in collection write schemas.

To stop relying on sourced schemas or continuous schema inference, you will also need to ensure collection read schemas do not reference the flow://inferred-schema.

You can then choose how your materialization should behave when encountering an incompatible schema change, such as setting onIncompatibleSchemaChange to abort or disableBinding to wait for manual review.

Either fix malformed data in the source or manually update the specification to accept the irregular document.

To manually edit collection specifications, see guides on using flowctl and the collection concept docs. Common scenarios are provided below.

Add a field

You can update the collection specification to support new fields. Simply add new properties to the spec, such as describing a new location field:

collections:
  acmeCo/inventory:
    key: [/sku]
    schema:
      type: object
      properties:
        name: { type: string }
        sku: { type: string }
        quantity: { type: integer }
        location: { type: string }
      required: [sku, quantity]

To then add a field to a materialization:

• In the dashboard, edit the materialization, find the affected binding, and require desired fields with field selection.
• Using flowctl, add the field to fields.include in the materialization specification as shown here.

info

Newly added fields will not be set for rows that have already been materialized. If you want to ensure that all rows have the new field, just increment the backfill counter in the affected binding to have it re-start from the beginning.

Update a field's data type

When a field's data type has changed, the effect on your materialization depends on the specific connector you're using.

warning

Note that these restrictions only apply to fields that are actively being materialized. If a field is excluded from your materialization, either explicitly or because it's not recommended, then the data types may change in any way.

Regardless of whether the field is materialized or not, it must still pass schema validation tests. Therefore, you must still make sure existing data remains valid against the new schema. For example, if you changed excluded_field: { type: string } to type: integer while there was existing data with string values, your materialization would fail due to a schema validation error.

Database and data warehouse materializations tend to be somewhat restrictive about changing column types. They typically only allow dropping NOT NULL constraints. This means that you can safely change a schema to make a required field optional, or to add null as a possible type, and the materialization will continue to work normally. Most other types of changes will require materializing into a new table.

The best way to find out whether a change is acceptable to a given connector is to run a test or attempt to re-publish. Failed attempts to publish won't affect any tasks that are already running.

Dashboard workflow

If you're working in Estuary's dashboard, publishing a change will apply the materialization's onIncompatibleSchemaChange setting. If the publish fails (if the setting is abort), you can fix it by manually updating the collection schema and triggering a backfill.

flowctl workflow

If you test or attempt to publish a change that's unacceptable to the connector, you'll see an error message pointing to the field that's changed. In most cases, you can work around the issue by manually updating the materialization to materialize into a new table.

For example, say you have a data flow defined by the following specs:

collections:
  acmeCo/inventory/anvils:
    key: [/sku]
    schema:
      type: object
      properties:
        sku: { type: string }
        quantity: { type: integer }
        description: { type: string }
      required: [sku, quantity]

materializations:
  acmeCo/data-warehouse/materialize-snowflake:
    endpoint:
      connector:
        image: ghcr.io/estuary/materialize-snowflake:v1
        config: encrypted-snowflake-config.sops.yaml
    bindings:
      - source: acmeCo/inventory/anvils
        backfill: 3
        resource:
          table: anvils
          schema: inventory

Let's say the type of description was broadened to allow object values in addition to string. You'd update your specs as follows:

collections:
  acmeCo/inventory/anvils:
    key: [/sku]
    schema:
      type: object
      properties:
        sku: { type: string }
        quantity: { type: integer }
        description: { type: [string, object] }
      required: [sku, quantity]

materializations:
  acmeCo/data-warehouse/materialize-snowflake:
    endpoint:
      connector:
        image: ghcr.io/estuary/materialize-snowflake:v1
        config: encrypted-snowflake-config.sops.yaml
    bindings:
      - source: acmeCo/inventory/anvils
        backfill: 4
        resource:
          table: anvils
          schema: inventory

Note that the only change was to increment the backfill counter. If the previous binding spec did not specify backfill, then just add backfill: 1.

This works because the type is broadened, so existing values will still validate against the new schema. If this were not the case, then you'd likely need to perform a dataflow reset.

Collection key changes

You can update the collection key in your collection spec:

collections:
  acmeCo/inventory:
    key: [/uuid]
    schema: {...}

This counts as an incompatible change and you may need to run a dataflow reset to recover.

Materializations can also specify separate group-by keys from the collection key structure.

If set, the group-by key is the final reflection of what the key structure should look like in the destination. You can update the group-by key if you wish to propagate any collection key changes downstream.

If the group-by key is unset, you may face different scenarios:

• You can preventatively set the group-by key to avoid propagating key changes.
• Set the group-by key after a collection key change to migrate back to the previous key.
• Leave the group-by key unset and let the collection key change propagate downstream with a dataflow reset.

Change logical partitioning

Changes to a collection's logical partitioning require a dataflow reset to reset the collection.

If you need to change logical partitions, you can control the partition projections and reset from the collection specification file.

For example, your flow.yaml file might look like:

collections:
  acmeCo/inventory/anvils:
    key: [/sku]
    schema:
      type: object
      properties:
        sku: { type: string }
        warehouse_id: { type: string }
        quantity: { type: integer }
      required: [sku, warehouse_id, quantity]
    projections:
      warehouse_id:
        location: /warehouse_id
        partition: true
    reset: true

The dataflow reset will automatically update associated capture and materialization bindings and kick off a backfill.