Hi all ! We are using Meltano in our team to import data from our postgres backend to Snowflake for analytics. Some data we'll move from one schema to another in our backend. We'd like to migrate, it will be fairly easy except for the LOG_BASED. We are facing two problems: we can't create new taps for LOG_BASED job (it would force us to create a new replication slot in postgres and it is very costly). So we'll need to add the new tables to an existing tap. Is there a way with the same tap to write in two different schemas ? Thanks a lot 🙏

If you don't set

default_target_schema

on

target-snowflake

, the loader respects the informal Singer convention of

<schema>-<table>

for stream names output by

tap-postgres

. That way, you can replicate your Postgres db across into Snowflake retaining the existing schema structure.

thanks ! is it possible to use the informal Singer convention for some streams and a

default_target_schema

for the others ? To be more precise we have a tap for LOG_BASED tables that import data from

public

schema (postgres DB) into a

backend

schema (Snowflake DB). We'd like to keep these streams and add new ones that follows the Singer convention without messing with our WAL in postgres, would it be possible ?

Good question - I think

default_target_schema

acts like a hard override for all streams, so not using that. Somewhat confusingly, there is another schema setting

schema

, which I believe sets the default schema for the session - with that, in theory you'd be able to remove the schema prefix from the incoming stream name using

stream_maps

and have it load into

schema

instead:

config:
      schema: backend
      stream_maps:
        public-*:  # all tables in public schema
          __alias__: __stream_name__.partition(".")[-1]

You could also explicitly map to

backend

in a similar way:

config:
      stream_maps:
        public-*:  # all tables in public schema
          __alias__: '"backend." + __stream_name__.partition(".")[-1]'

https://sdk.meltano.com/en/latest/stream_maps.html#inline-stream-maps

Thanks ! If I drop

load_schema

from tap, would it mess with the state (for LOG_BASED streams) ?

No, that's just used to pass a schema to the target via the exposed

MELTANO_EXTRACT__LOAD_SCHEMA

env var - there is no interaction with state. > The value of this extra can be referenced from a loader's configuration using the

MELTANO_EXTRACT__LOAD_SCHEMA

pipeline environment variable. It is used as the default value for the target-postgres and target-snowflake schema settings. https://docs.meltano.com/concepts/plugins/#load_schema-extra

Thanks a lot !

We do have a feature request for mapping streams to specific schemas: https://github.com/meltano/sdk/issues/1086. It might need some refinement to, for example, request that it supports wildcards like

public-*

->

backend

. For the time being though, Reuben is right that stream maps is the best way to accomplish this 👍

Hi ! It worked. I had also to: • replace

.

with

-

(

public-*

) • add a

schema_mapping

to the

target-snowflake

:

schema_mapping:
        backend:
          target_schema: backend

Thanks !