Give me a detailed explanation with specific. examples on what happens with "Backfill new or renamed columns" setting turned on vs turned off

Backfill New or Renamed Columns: On vs. Off This setting controls what happens to existing rows in your destination when a new or renamed column is detected during an auto-propagated schema change. Prerequisites This setting only activates when both of the following are true: 1.

Detect and propagate schema changes

is set to either "Propagate all field and stream changes" or "Propagate field changes only" 2. Airbyte detects the schema change as part of an automatic sync (not from a manual schema refresh) [Schema Change Management] Setting Turned OFF (default behavior) When backfill is disabled, Airbyte propagates the new column to the destination but only populates it for rows that are updated in future syncs. Example: Suppose you have a

customers

table with 1,000 rows already synced to your destination. Your source adds a new column

loyalty_tier

. Only rows that happen to be updated (and thus re-synced) will have a value in

loyalty_tier

. All historical rows that weren't touched remain

NULL

. [Schema Change Management] Setting Turned ON When backfill is enabled, Airbyte re-syncs the entire stream whenever a new or renamed column is detected. This ensures every row — including historical ones — gets a value for the new column. Same example: After the

loyalty_tier

column is detected, Airbyte triggers a full re-sync of the

customers

stream: All 1,000 rows now have their correct

loyalty_tier

value populated. [Schema Change Management] Key Trade-off: Cost Enabling backfill comes with a cost implication. Because the entire stream is re-synced, destination costs increase (e.g., more data written to Snowflake, BigQuery, etc.). For Airbyte Cloud users, there is a billing consideration: any stream containing a new or renamed column will not be billed for that backfill sync, and the free usage will be noted on the billing page. However, other streams synced in the same job that do not contain schema changes will be billed as usual. [Schema Change Management] Summary Table 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help., with interactive elements

@kapa.ai I use the open source airbyte. If I had Detect and propagate schema changes set to "Approve all changes myself", then this auto backfill doesn't work? Give me a detailed explanation with specific examples

Yes, that's correct. The "Backfill new or renamed columns" feature does not work when

Detect and propagate schema changes

is set to "Approve all changes myself". Here's a detailed breakdown of why, with examples: Why Backfill Doesn't Trigger with "Approve All Changes Myself" The documentation is explicit: the backfill feature only performs the backfill when: 1.

Detect and propagate schema changes

is set to "Propagate all changes" or "Propagate column changes only", AND 2. Airbyte detects the schema change automatically as part of a sync Manually reviewing and applying schema changes (i.e., the "Approve all changes myself" flow) does not trigger the backfill, even if the backfill setting is enabled. [Schema Change Management] What Actually Happens with "Approve All Changes Myself" Example Scenario You have a

orders

table with 500 rows already synced to your Snowflake destination. Your Postgres source adds a new column

discount_code

. Step-by-step flow: 1. Airbyte detects the schema change (at most every 24 hours for self-managed/OSS users) [Schema Change Management] 2. A blue "i" icon appears next to the Schema tab in the UI, indicating a detected change. 3. Syncs continue running using the old schema — the

discount_code

column is not yet synced to the destination. 4. You navigate to the Schema tab → Review changes → OK → Save changes to manually approve and apply the new column. [Schema Change Management] 5. On the next sync, the

discount_code

column is added to the destination — but only rows updated in that sync will have a value. Historical rows remain

NULL

. No backfill occurs, regardless of whether the "Backfill new or renamed columns" toggle is on or off, because the change was applied via manual approval — not auto-propagation. Comparison Table Key Takeaway for OSS Users If you're on self-managed Airbyte with "Approve all changes myself", and you want all historical rows to have values for a newly added column, you would need to manually trigger a refresh (full re-sync) of the affected stream after approving the schema change. The automatic backfill mechanism simply does not apply in this workflow. [Schema Change Management] 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help., with interactive elements

@kapa.ai Okay, that means, if a table is set to full refresh, it's fine, the historical data would be loaded after you apply the schema changes. And for incremental dedup mode, what happens? I'd think any historical data that would have a value in the new/changed field would get updated, so it'd be fine? Also, for a cdc connector e.g postgres - you inherently don't touch historical data. Does this work the way incremental dedup mode syncs work? Again, give me a detailed explanation in simple terms with specific examples

Great questions! Let me break this down carefully for each sync mode. Full Refresh Modes (Overwrite / Overwrite + Deduped) You're right. With Full Refresh, every sync reads all data from the source. So after you manually approve a schema change (in "Approve all changes myself" mode), the next sync will: 1. Pull all rows from the source, including the new/changed column 2. Overwrite the destination table entirely Example: Your

products

table gets a new

category

column. After you approve the schema change, the next full refresh sync replaces the entire destination table with all 1,000 rows — each row now has its

category

value populated. No backfill setting needed; it's naturally handled. [Full Refresh - Overwrite] Incremental Append + Deduped (Non-CDC, cursor-based) This is where it gets more nuanced. The knowledge sources actually highlight a known limitation here: > "The current behavior of Incremental is not able to handle source schema changes yet, for example, when a column is added, renamed or deleted from an existing table. It is recommended to trigger a Full Refresh - Overwrite to correctly replicate the data to the destination with the new schema changes." [Incremental Sync - Append] So your intuition — that historical rows with values in the new column would get updated — is not guaranteed with cursor-based incremental sync. Example: Your

orders

table has 500 rows already synced. A new

discount_code

column is added to the source. In incremental mode, Airbyte only picks up rows where

updated_at >= last_sync_cursor

. If a historical row (e.g.,

order_id = 1

from 2022) was never updated in the source after the column was added, it will never be re-synced, and

discount_code

will remain

NULL

in the destination for that row — even though the source has a value for it. The recommended fix is to trigger a Full Refresh on that stream to re-sync all historical data with the new schema. CDC Mode (e.g., Postgres with WAL) You're correct that CDC inherently doesn't touch historical data — it only captures changes going forward from the WAL logs. The documentation confirms this: > "If you create a new schema/column that is not yet being synced, it must first be snapshotted before CDC can begin tracking changes." [CDC Limitations] Example: Your Postgres CDC source has a

customers

table with 1,000 rows. You add a

loyalty_tier

column. CDC will only capture new WAL events (inserts/updates/deletes) going forward. Existing rows that are never updated will have

NULL

for

loyalty_tier

in the destination forever — because no WAL event was ever generated for them. To fix this, you need to trigger a Refresh Stream (snapshot) for the affected table after approving the schema change. This re-snapshots all current data, populating

loyalty_tier

for all rows. The docs explicitly recommend this: > "For new columns: Locate the schema containing the new column(s), click the three dots (⋮) next to it, and choose Refresh Stream." [CDC Limitations] The docs also recommend using "Approve all schema changes myself" specifically for CDC to avoid LSN issues and sync failures from auto-propagation. Summary Table 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help., with interactive elements

@kapa.ai I'm using "Incremental Sync - Append + Deduped" mode though? In the cdc set up as well as in the incremental sync tables

Good clarification! Let me address both cases specifically for Incremental Append + Deduped mode. Incremental Append + Deduped — Non-CDC (Cursor-based) The behavior here is the same as what I described for incremental modes generally. The known limitation from the docs applies: > "The current behavior of Incremental is not able to handle source schema changes yet, for example, when a column is added, renamed or deleted from an existing table. It is recommended to trigger a Full Refresh - Overwrite to correctly replicate the data to the destination with the new schema changes." [Incremental Sync - Append] Even though Append + Deduped keeps only the latest row per primary key in the final table, it still relies on the cursor to detect which rows to sync. So historical rows that were never updated after the new column was added won't be re-synced, and the new column will be

NULL

for them. Recommendation: Trigger a Refresh Stream on the affected table to re-sync all data with the new schema. Incremental Append + Deduped — CDC (e.g., Postgres WAL) With CDC, the situation is the same as I described: CDC only captures future WAL events. Even in Append + Deduped mode, Airbyte cannot go back and populate historical rows with a new column's values, because no WAL event was ever generated for those rows. The docs are explicit: [CDC Limitations] • New columns must first be snapshotted before CDC can track changes on them. • You need to trigger a Refresh Stream for the affected table after approving the schema change. The deduplication logic (keeping the latest row per primary key) doesn't help here — if a row was never re-emitted via CDC after the column was added, there's simply no new event for Airbyte to process and deduplicate. Bottom Line Regardless of whether you're using Append + Deduped in cursor-based or CDC mode, schema changes affecting historical rows require a manual Refresh Stream to ensure all rows are populated with the new column's values. The deduplication aspect of the sync mode doesn't change this fundamental limitation. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai So, in short, anything that uses either "Incremental Sync - Append + Deduped" or cdc with "Incremental Sync - Append + Deduped", we need to go to the status page and "refresh stream" on affected tables, yea?

Yes, that's correct. For both cases: • Cursor-based Incremental Append + Deduped: A Refresh Stream is needed because incremental mode cannot handle source schema changes on its own, and historical rows that were never updated won't be re-synced. [Incremental Sync - Append] • CDC with Incremental Append + Deduped: A Refresh Stream (snapshot) is needed because CDC only captures future WAL events — existing rows that were never updated after the new column was added will never generate a WAL event, so they'll remain

NULL

in the destination. [CDC Limitations] In both cases, the fix is the same: go to the Status page, find the affected stream, click the three dots (⋮), and choose Refresh Stream. The docs explicitly describe this step for CDC: > "For new columns: Locate the schema containing the new column(s), click the three dots (⋮) next to it, and choose Refresh Stream." [CDC Limitations] 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.