We’re currently running a Postgres=>Snowflake EL process using the

target-snowflake

loader, but it seems that for all of the tables that have been created they don’t have any primary keys set in Snowflake. Updated data is effectively gets UPSERTed on the primary key anyway, so we’re not ending up with duplicate data, but it seems likely that Snowflake may not be able to plan queries so effectively without a primary key set. Does anyone know if the lack of primary keys being set is a problem with the

target-snowflake

loader itself or if it is a common thing to Meltano?

your tap sets the pk for the stream. Reading this is worth it I think! https://hub.meltano.com/singer/spec#schemas Specifically the schemas section

Thanks. Seems surprising if by default

tap-postgres

isn’t handling primary keys. What I’m referring to is only really relevant when the schema is either being synced for the first time or has been updated, which seems like it might be responsibility of the target to handle?

Can you share your meltano.yml, you're saying

tap-postgres

doesn't pass primary keys over? Also can you share the command you're running I'm missing a bunch of context I thought we were just talking about

target-snowflake

and taps in general

In any case, I’ve since come across this Snowflake page which says

Snowflake supports defining and maintaining constraints, but does not enforce them, except for NOT NULL constraints, which are always enforced.

so perhaps the lack of constraints in Snowflake doesn’t really make any difference to query planning as I originally theorised!

OH man I forgot about that with OLAP dbs I use too much MSSQL!

Sorry. To clarify: 1. We had a Postgres DB with all tables having a primary key column 2. We had an empty database/schema in Snowflake 3. During an initial sync, Meltano both created the tables and populated the data 4. The tables created in Snowflake have all the right columns (including the primary key column), but it doesn’t actually set a primary key constraint on the primary key column