https://linen.dev logo
Join Slack
Powered by
<@U04S8PLJWMT> I have a pipeline in airbyte that h...
# ask-ai
j
@kapa.ai I have a pipeline in airbyte that has one stream with sync mode Full Refresh Overwrite. What does this sync mode do behind the scenes? What are the commands it does in Redshift? Mainly if I have the drop cascade option enabled for Redshift destination connector
k
Airbyte’s documentation explains the behavior of Full Refresh – Overwrite at a conceptual level, but does not list the exact SQL commands used for Redshift, nor how the Redshift “drop cascade” option is applied. I’ll summarize what is documented and where the gaps are. ### What Full Refresh – Overwrite does For a stream configured as Full Refresh – Overwrite: - Airbyte always reads all records from the source for that stream on every sync (no incremental cursor). [[Sync modes](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes)] - On each sync, it destroys all data in the existing destination table for that stream and then loads the new full dataset. [[Full refresh overwrite](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite)] - As a result, any rows that were deleted in the source since the last sync will also be deleted in the destination after the next sync. [[Full refresh overwrite](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite)] ### How this is implemented at the destination level The docs describe the mechanism in general terms (for “certified database and data warehouse destinations” like Redshift), but not with exact SQL: - For these destinations, Airbyte will recreate the final table each sync. [[Full refresh overwrite](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite)] - The pattern described is: - Write the new data to a temporary “final-table-tmp” location while the sync is running. - At the end of the sync, drop the old “final” table. - Rename the new temp table into place as the final table. [[Full refresh overwrite](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite)] This is the documented high‑level behavior; the exact SQL (e.g., 
DROP TABLE
, 
CREATE TABLE AS
, 
ALTER TABLE RENAME
) is not specified in the knowledge sources. ### About Redshift “drop cascade” option The documentation you provided does not mention: - The Redshift destination’s “drop cascade” option. - Whether Airbyte issues 
DROP TABLE ... CASCADE
vs 
DROP TABLE ... RESTRICT
or similar. - Any Redshift‑specific SQL examples. So I cannot reliably state the exact commands used in Redshift or precisely how the “drop cascade” flag changes them based on these docs. --- Summary: Behind the scenes, Full Refresh – Overwrite for a Redshift destination will (conceptually) load all data into a temp table, then drop the existing final table and replace it with the new one, so the destination table always reflects the full current dataset from the source. [[Full refresh overwrite](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite)] The knowledge sources do not provide the exact Redshift SQL or how the “drop cascade” option is applied.
j
@kapa.ai If I have a materialized view in Redshift that depends on tables that have sync mode Full Refresh Overwrite and drop cascade enabled, are they always deleted too?
k
A message from kapa.ai
The knowledge sources don’t explicitly document Redshift’s “drop cascade” option, so I can’t say with certainty what happens in Redshift specifically. I can only infer behavior from similar documented cases and general patterns. What is documented: - For Full Refresh – Overwrite, Airbyte (for certified databases/warehouses) typically recreates the final table each sync by writing to a 
final-table-tmp
, then dropping the old final table and renaming the tmp into place. [[Full refresh overwrite](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite)] - When a destination needs to recreate tables and there are dependent objects (like views) on those tables, the connector may need to run 
DROP ... CASCADE
. In the Postgres destination docs, Airbyte warns that enabling “Drop tables with CASCADE” will cause dependent objects to be dropped as well, and you must be able to recreate them. [[Postgres destination](https://docs.airbyte.com/integrations/destinations/postgres#creating-dependent-objects)] Given this: - In Postgres, if “Drop tables with CASCADE” is enabled and a table is recreated, dependent views are dropped. [[Postgres destination](https://docs.airbyte.com/integrations/destinations/postgres#creating-dependent-objects)] - The Redshift behavior is not documented in the provided sources, but if the Redshift connector behaves analogously (i.e., uses 
DROP TABLE ... CASCADE
when that option is enabled), then materialized views depending on those tables would also be dropped. Because there is no explicit Redshift documentation in the sources, I cannot confirm this as a documented fact for Redshift; I can only say that: - Airbyte’s pattern with “drop cascade” in Postgres is to drop dependent objects. - If Redshift’s option works the same way, your materialized views would be dropped whenever the underlying table is dropped and recreated. If preserving those materialized views is important, the Postgres docs’ guidance (use dbt or another tool to recreate dependent objects automatically after sync) is likely applicable in spirit, even though it’s not documented specifically for Redshift. [[Postgres destination](https://docs.airbyte.com/integrations/destinations/postgres#creating-dependent-objects)]
Open in Slack
PreviousNext
Powered by