Troubleshooting Schemas

Find common issues and solutions when working with schemas and evolutions.

Why are my new columns showing NULL values?

When you add a new column to a source table, existing documents in Estuary collections were captured before that column existed. The new column will only have values for documents captured after the schema change.

Example:

1. You add column is_active to your source table
2. Estuary detects the change and updates the collection schema
3. New documents include is_active, but historical documents don't have it
4. When materialized, historical rows show NULL for is_active

Solution: Trigger a backfill to recapture historical data with the new schema.

• UI: Go to your capture → Click Backfill on the affected binding
• flowctl: Increment the backfill counter in your capture spec and republish

tip

For very large tables, consider whether you need historical values for the new column, or if NULL for older records is acceptable.

My data stopped flowing after a schema change

If data stops flowing after a schema change at your source, work through these checks:

1. Is the capture running?

   • Check the hourly data graph and logs in the UI
   • Most schema changes (like adding columns) are handled automatically—the capture briefly restarts to pick up the new schema

2. Is the materialization running?

   • Check the hourly data graph and logs in the UI
   • If stopped, look for schema validation errors in the logs

3. What is the value of the materialization's onIncompatibleSchemaChange setting?

   • Estuary's default response when schema changes would break downstream tasks is to backfill to automatically recover
   • If onIncompatibleSchemaChange is instead set to disable the binding or task or to abort, manual intervention is required
   • Manually update your schema and backfill

Common causes and solutions:

Symptom	Likely Cause	Solution
Transient "document failed validation" error	Schema inference racing with new records	See Transient validation errors section below
"Changes rejected due to incompatible collection updates"	Incompatible schema change detected	Change the onIncompatibleSchemaChange setting or manually perform a backfill
Data flows but destination unchanged	Processing delay or materialization paused	Check task status in UI

Transient validation errors during schema changes

When your source data changes shape, you may see errors like:

document failed validation against its collection JSON Schema

Why this happens:

Schema updates and new data records are processed through separate paths. When a source schema changes:

1. The capture detects the change and publishes an updated collection schema
2. New records (with the new shape) start flowing immediately
3. The materialization receives these new records
4. If a record arrives before the schema update is applied, validation fails

This is a side effect of a low-latency system—we don't artificially delay records, so the schema change can arrive slightly after the first records with the new shape. The system is designed to recover automatically.

How to identify transient vs. persistent failures:

Indicator	Transient (wait)	Persistent (action needed)
Timing	Started during/after a source schema change	No recent schema changes
Duration	Resolves within 5-10 minutes	Persists beyond 30 minutes
Error pattern	Intermittent, then stops	Continuous, every record fails
Materialization status	Restarts automatically	Stays failed or loops

What happens during recovery:

1. The materialization encounters validation errors and restarts
2. On restart, it picks up the updated collection schema
3. Processing resumes normally with the new schema
4. No data is lost—failed records are retried after the schema update

When to take action:

• Wait 15-30 minutes before investigating further
• Check the logs for the specific validation error—if it references a field that was just added/changed, it's likely transient
• If errors persist, verify your source schema change was compatible (see Auto-inference only widens)
• If you made an incompatible change, you may need to trigger a backfill or adjust your schema

tip

High-volume sources with frequent schema changes may see these transient errors more often. If this becomes disruptive, consider batching schema changes during low-traffic periods.

Configuring automatic schema change handling

You can configure how materializations respond to incompatible schema changes using the onIncompatibleSchemaChange setting.

In the web app:

1. Edit your materialization
2. Go to Collections
3. Click Config on a collection
4. Expand Advanced Options
5. Set Incompatible Schema Change to your preferred action

Available options:

Option	Behavior
backfill (default)	Automatically backfill and re-materialize the affected binding
abort	Fail the publication, preventing incompatible changes
disableBinding	Disable only the affected binding until manually re-enabled
disableTask	Disable the entire materialization until manually re-enabled

Using flowctl:

Set the field at the top level (applies to all bindings) or per-binding:

materializations:
  myPrefix/my-materialization:
    onIncompatibleSchemaChange: disableTask  # or: backfill (default), abort, disableBinding
    bindings:
      - source: myPrefix/my-collection
        onIncompatibleSchemaChange: abort  # Override for this binding
        resource:
          table: my_table

Type inference issues with NoSQL sources

NoSQL databases like MongoDB allow flexible schemas where the same field can have different types across documents. Estuary infers a schema based on the data it observes, which can lead to unexpected type widening.

Common issue: "My date field is being stored as a string"

Why this happens:

1. The first documents captured had the field as a string value (e.g., "2024-01-15")
2. Estuary inferred the type as string
3. Later documents with proper Date types are converted to strings to match the inferred schema

Remember: auto-inference only widens, never narrows. Once a field is inferred as string, it won't narrow to date—even if all subsequent documents use the correct type.

Options:

1. Accept the widened type: In many cases, the widened type works fine downstream. String dates can still be parsed by your destination or BI tools. Evaluate whether this actually causes problems before investing effort in a fix.

2. Fix at the source, then backfill: The cleanest solution when the type matters:

   • Correct the inconsistent data types in your source database
   • Trigger a dataflow reset to re-infer the schema from scratch
   • The backfill recaptures all data with consistent types, and the schema is re-inferred correctly

3. Use a derivation (last resort): If you can't fix the source data, create a derivation to transform the field:

   SELECT
     $id,
     strftime('%Y-%m-%dT%H:%M:%SZ', $created_at) AS created_at;

   One caveat: strftime returns NULL rather than erroring if a value isn't in a format SQLite recognises, so check the output if your source mixes in formats like 01/15/2024. See the SQLite section of the derivations page for how named columns map to document properties.

   This adds complexity to your pipeline, so only use this when options 1 and 2 aren't viable.

tip

When setting up captures from schema-flexible sources, review the inferred schema before creating materializations. If types look incorrect, fix the source data and backfill before proceeding—it's much easier to get the schema right from the start.

See additional details on schema inference issues.

Schema not yet available for new collections

When creating a materialization for a newly captured collection, you may see a validation error referencing inferredSchemaIsNotAvailable. This comes from a placeholder in the collection's read schema, annotated:

An inferred schema is not yet available because no documents have been written to this collection.

Why this happens:

Before data starts flowing through a new capture, Estuary creates a placeholder schema. This gets overwritten on schema inference:

1. The capture starts and begins reading from the source
2. Documents are captured and schema inference analyzes them
3. The inferred schema is published to the collection
4. Only then can materializations validate against the schema

Solution:

1. Wait 5-10 minutes for the capture to process initial documents
2. Check capture logs to confirm data is flowing
3. The materialization is republished automatically once the inferred schema is updated

If the issue persists after 30 minutes:

• Verify your source has data to capture
• Check capture logs for connection or permission errors

Binding not found

When editing or publishing a capture, you may see errors that a table or stream cannot be found or that the user has insufficient permissions to discover it. These surface under a wrapper like the capture cannot run due to the following error(s):, followed by one of:

user "flow_capture" cannot read from table "public.orders"

stream "public.orders" is a configured binding of this capture, but doesn't exist or isn't visible with current permissions

SQL Server may instead report:

table "dbo.orders" has no capture instances and user "flow_capture" cannot create one

Postgres may instead report:

table "public.orders" is not in publication flow_publication and couldn't be added automatically

Common causes:

1. Source table was renamed or deleted

   • Check if the table still exists in your source system
   • If renamed, remove the old binding and add the new table name

2. Insufficient permissions

   • Verify the capture's credentials have access to the table
   • For databases, check SELECT permissions on the specific table/schema

3. Table not yet discovered

   • Click Refresh on your capture to re-run discovery
   • New tables may take a few minutes to appear

4. Case sensitivity

   • Some databases are case-sensitive for table names
   • Ensure the binding name matches the exact table name

To resolve:

1. Go to your capture and click Edit
2. Click Refresh to re-discover available tables
3. If the table appears, re-add it as a binding
4. If not, check source permissions and table existence