https://linen.dev logo
Join Slack
Powered by
<@U04S8PLJWMT> When there is a table called TEST, ...
# replication-ask-ai
j
@kapa.ai When there is a table called TEST, why does airbyte look for a stage with the pattern TESTe37642cdef432c2e7966e006370a7e26 stage in the airbyte_internal schema instead of looking for a stage file with the pattern airbyte_stage_TESTe37642cdef432c2e7966e006370a7e26 stage in snowflake?
k
Based on the provided documentation and GitHub issues, the knowledge sources do not explicitly state why Airbyte might search for a stage pattern without the 
airbyte_stage_
prefix in a specific instance. However, the logs and technical details offer context on the expected naming conventions and locations for Snowflake stages. ### Stage Naming Convention In Snowflake, Airbyte typically creates stages using the prefix 
airbyte_stage_
followed by the table or stream name. For example, logs show commands like: * 
CREATE STAGE IF NOT EXISTS "AIRBYTE_DB"."airbyte_internal"."airbyte_stage_PG_SIMULATIONSIMULATION_DATA..."
[[source-postgres sync issue](https://github.com/airbytehq/airbyte/issues/70921)] * 
CREATE STAGE IF NOT EXISTS "DEV"."DEFAULT"."airbyte_stage_MYTABLE1"
[[Snowflake future table grants issue](https://github.com/airbytehq/airbyte/issues/68681)] ### Why 
airbyte_internal
? With the transition to Destinations V2 (connector versions 4.x and higher), Airbyte changed its behavior to use a dedicated 
airbyte_internal
schema for temporary objects and raw staging [[Upgrading to Destinations V2](https://docs.airbyte.com/release_notes/upgrading_to_destinations_v2#upgrading-as-a-user-of-raw-tables)]. This was done to avoid cluttering the primary data schema with temporary tables and stages [[Snowflake future table grants issue](https://github.com/airbytehq/airbyte/issues/68681)]. ### Deterministic Suffixes The alphanumeric string at the end of the stage name (e.g., 
e37642c...
) is often a hash or identifier used to ensure uniqueness. This is similar to how Airbyte handles name truncation or uniqueness across different connections to prevent data clashes [[Postgres destination table name truncation rules](https://discuss.airbyte.io/t/2384); [Airbyte 1.6 release notes](https://docs.airbyte.com/release_notes/v-1.6#unique-stream-prefixes-or-namespaces-are-now-mandatory)]. If you are seeing Airbyte look for a stage name that lacks the 
airbyte_stage_
prefix, it is not a behavior explicitly described as a standard rule in the provided documentation. The documented standard remains 
airbyte_stage_<table_name_hash>
within the 
airbyte_internal
schema.
j
@kapa.ai I'm not sure if it's because of the version upgrade, but if there's a schema called SCHEMA_A under a DB called DW_A and a table called TEST under it, previously, a stage file was created under the SCHEMA_A schema, but now, it seems like a stage file is created under a schema called airbyte_internal under the DW_A DB. Is this the correct change?
k
A message from kapa.ai
Yes, this is the correct and expected change introduced with Destinations V2 and the Direct-Load paradigm for Snowflake. According to the documentation and technical issues provided: * Change in Schema Location: In older versions, Airbyte used to create temporary objects (like stages and raw tables) in the same schema as your final tables. Starting with Destinations V2, Airbyte now generates all raw tables and internal objects in a dedicated schema called 
airbyte_internal
to avoid cluttering your destination schema [[Upgrading to Destinations V2](https://docs.airbyte.com/release_notes/upgrading_to_destinations_v2); [Snowflake Migration Guide](https://docs.airbyte.com/integrations/destinations/snowflake-migrations)]. * Snowflake Specifics: For Snowflake specifically, versions 4.0.0 and higher use the "Direct-Load" paradigm. This behavior change means the connector now creates temporary tables and stages in the 
airbyte_internal
schema instead of the target schema (e.g., 
SCHEMA_A
) [[Snowflake future table grants issue](https://github.com/airbytehq/airbyte/issues/68681)]. * Why this happens: This was implemented in response to user feedback to keep the target schemas clean. During a sync, Airbyte may create a temporary table in 
airbyte_internal
and then use an 
ALTER TABLE ... SWAP WITH
command to replace the final table in your target schema [[Snowflake future table grants issue](https://github.com/airbytehq/airbyte/issues/68681)]. Important Note on Permissions: Because the stage and temporary tables are now created in 
airbyte_internal
rather than 
SCHEMA_A
, Snowflake's 
FUTURE GRANTS
on 
SCHEMA_A
will no longer automatically apply to these new tables. If you rely on specific permissions, you may need to apply grants to the 
airbyte_internal
schema as well [[Snowflake future table grants issue](https://github.com/airbytehq/airbyte/issues/68681)].
j
@kapa.ai When I look at snowflake's Query History, I keep seeing the error message "SQL compilation error: Object 'DW_A."airbyte_internal"."TESTe37642cdef432c2e7966e006370a7e26"' does not exist or is not authorized." Why is this happening?
k
A message from kapa.ai
The knowledge sources do not explicitly explain why Airbyte searches for that specific object name without the 
airbyte_stage_
prefix. However, based on the documented behavior of the Snowflake connector (version 4.x+), this error typically occurs due to how Destinations V2 handles temporary tables and schema permissions. Here are the likely reasons for this "Object does not exist or is not authorized" error: * Permissions in the 
airbyte_internal
Schema: Airbyte now creates temporary tables and stages in the 
airbyte_internal
schema instead of your final destination schema [[Snowflake future table grants issue](https://github.com/airbytehq/airbyte/issues/68681)]. If the Snowflake user used by Airbyte does not have 
USAGE
permissions on the 
airbyte_internal
schema or 
CREATE
permissions within it, the SQL compilation will fail because it cannot find or access the object [[Airbyte connection stopped working while nothing changes](https://discuss.airbyte.io/t/1178)]. * Table Swapping and Future Grants: For "Full Refresh | Overwrite" syncs, Airbyte creates a replacement table in 
airbyte_internal
and then executes an 
ALTER TABLE ... SWAP WITH
command. Because Snowflake does not apply 
FUTURE
grants when tables are moved across schemas, the newly swapped table may lose the privileges required for the operation to complete, leading to "not authorized" errors in subsequent steps [[Snowflake future table grants issue](https://github.com/airbytehq/airbyte/issues/68681)]. * Failed Cleanup or Race Conditions: In some cases, if a previous sync failed, Airbyte may attempt to reference or clean up temporary objects that were never successfully created or were partially dropped, resulting in an "Object does not exist" error [[Large sync fails but successfully loads data](https://discuss.airbyte.io/t/782)]. * Missing Schema Specification: If the schema or namespace was not explicitly defined in the source or destination configuration, Snowflake may fail to resolve the object's location correctly [[JSON schema validation failed (Redshift -> Snowflake)](https://discuss.airbyte.io/t/848)]. To resolve this, ensure that the Airbyte Snowflake user has full permissions (USAGE, CREATE TABLE, CREATE STAGE) on the 
airbyte_internal
schema [[Snowflake future table grants issue](https://github.com/airbytehq/airbyte/issues/68681)].
2 Views
Open in Slack
PreviousNext
Powered by