https://linen.dev logo
Join Slack
Powered by
<@U04SE163WC8> Why does Airbyte recreate the strea...
# ask-ai
a
@kapa.ai Why does Airbyte recreate the stream/table on the destination even when incremental method is selected ? Shouldn't be similar to how DBT does the incremental load? By creating a staging table/Temp table and do a insert/merge on the destination table?
k
Airbyte's behavior regarding the recreation of tables during incremental syncs has evolved over time. Initially, Airbyte did not support incremental updates in normalization, which led to the recreation of Slowly Changing Dimension (SCD) and final tables every time normalization was run. However, this has changed with recent updates. ### Historical Context A few versions ago, Airbyte did not support incremental updates in normalization. This caused the normalization process to recreate the SCD and final tables every time. With the implementation of incremental updates, normalization now generates staging (STG) tables that store only the new records. This approach aligns more closely with how dbt handles incremental loads. ### Current Behavior When using the incremental deduped history mode, Airbyte creates an additional table suffixed with 
_stg
for every stream replicated. This staging table is used to handle incremental transformations. The process involves: 1. Initial Synchronization: Airbyte replicates all records from the source table into a raw table in the destination database. Then, dbt-generated SQL commands are executed to normalize the raw data into the final destination table ([source](https://airbyte.com/tutorials/incremental-data-synchronization#step-6)). 2. Subsequent Synchronizations: For incremental history + dedupe replication, records are inserted into an existing table rather than creating a new destination table. The process involves copying records from a temporary table into the staging table and ensuring no duplicates by removing existing records before the insertion ([source](https://airbyte.com/tutorials/incremental-data-synchronization#step-7)). ### Example SQL for Incremental Updates Here is an example of the SQL used for incremental history + dedupe replication:
Copy code
sql
delete from "postgres"._airbyte_public."table_two_stg"
where (_airbyte_ab_id) in (
    select (_airbyte_ab_id)
    from "table_two_stg__dbt_tmp140757456406"
);

insert into "postgres"._airbyte_public."table_two_stg" ("_airbyte_table_two_hashid", "id", "name", "updated_at", "_airbyte_ab_id", "_airbyte_emitted_at", "_airbyte_normalized_at")
(
    select "_airbyte_table_two_hashid", "id", "name", "updated_at", "_airbyte_ab_id", "_airbyte_emitted_at", "_airbyte_normalized_at"
    from "table_two_stg__dbt_tmp140757456406"
);
This SQL ensures that records are copied from a temporary table into the staging table, avoiding duplicates by removing existing records before the insertion. ### Conclusion Airbyte's current approach to incremental updates should not recreate the final tables every time, provided you are using the incremental deduped history mode. If you are experiencing issues with table recreation, ensure that you are not using the Full Refresh sync mode, as this mode will always perform a full refresh and recreate the tables ([source](https://discuss.airbyte.io/t/76)).
Airbyte's behavior regarding the recreation of tables during incremental syncs has evolved over time. Initially, Airbyte did not support incremental updates in normalization, which led to the recreation of Slowly Changing Dimension (SCD) and final tables every time normalization was run. However, this has changed with recent updates. ### Historical Context A few versions ago, Airbyte did not support incremental updates in normalization. This caused the normalization process to recreate the SCD and final tables every time. With the implementation of incremental updates, normalization now generates staging (STG) tables that store only the new records. This approach aligns more closely with how dbt handles incremental loads. ### Current Behavior When using the incremental deduped history mode, Airbyte creates an additional table suffixed with 
_stg
for every stream replicated. This staging table is used to handle incremental transformations. The process involves: 1. Initial Synchronization: Airbyte replicates all records from the source table into a raw table in the destination database. Then, dbt-generated SQL commands are executed to normalize the raw data into the final destination table ([source](https://airbyte.com/tutorials/incremental-data-synchronization#step-6)). 2. Subsequent Synchronizations: For incremental history + dedupe replication, records are inserted into an existing table rather than creating a new destination table. The process involves copying records from a temporary table into the staging table and ensuring no duplicates by removing existing records before the insertion ([source](https://airbyte.com/tutorials/incremental-data-synchronization#step-7)). ### Example SQL for Incremental Updates Here is an example of the SQL used for incremental history + dedupe replication:
Copy code
sql delete from "postgres"._airbyte_public."table_two_stg" where (_airbyte_ab_id) in (     select (_airbyte_ab_id)     from "table_two_stg__dbt_tmp140757456406" );  insert into "postgres"._airbyte_public."table_two_stg" ("_airbyte_table_two_hashid", "id", "name", "updated_at", "_airbyte_ab_id", "_airbyte_emitted_at", "_airbyte_normalized_at") (     select "_airbyte_table_two_hashid", "id", "name", "updated_at", "_airbyte_ab_id", "_airbyte_emitted_at", "_airbyte_normalized_at"     from "table_two_stg__dbt_tmp140757456406" );
This SQL ensures that records are copied from a temporary table into the staging table, avoiding duplicates by removing existing records before the insertion. ### Conclusion Airbyte's current approach to incremental updates should not recreate the final tables every time, provided you are using the incremental deduped history mode. If you are experiencing issues with table recreation, ensure that you are not using the Full Refresh sync mode, as this mode will always perform a full refresh and recreate the tables ([source](https://discuss.airbyte.io/t/76)). For more detailed information, you can refer to the [Airbyte documentation on incremental synchronization](https://airbyte.com/tutorials/incremental-data-synchronization#step-6) and the [discussion on normalization models](https://discuss.airbyte.io/t/2259).
Open in Slack
PreviousNext
Powered by