Hi guys, I’ve generally got meltano working a treat for 73 tables, there is one table in this db that has a primary key made up of 8! columns. When I try to ELT the table I get

time=2021-04-14 23:13:06 name=target_postgres level=CRITICAL message=Primary key is set to mandatory but not defined in the

when trying to load. I guess I need to specify the primary key somehow?

Can you share more about which variant of target postgres you’re using and what commands you were using to run it? I suspect this is coming from a schema message.

Hi Taylor it’s the transferwise variant. I was just running

meltano elt tap-mysql target-postgres --job_id mysql-to-postgres

I tried with full-refresh too

Have you seen this configuration setting? https://meltano.com/plugins/loaders/postgres--transferwise.html#primary-key-required

Thanks @taylor really appreciate it. I had spotted that previously but the mysql table has a compound primary key of 7 columns, so I thought this would be ok. That does resolve it, but I also don’t want to end up with duplicates! 😄

I’m not super familiar with composite keys in postgres or mysql. Our general guidance for replicating data to a db is that some duplicates may happen and that there should be a dedupe step downstream. For analytics workflows this is fine b/c dbt provides a great place to setup that layer, but perhaps that isn’t your use case.

Thanks Taylor, I’ve got it running through DBT, what do you normally do to dedupe out of interetest?

It depends a lot on your database! Snowflake is great with the

QUALIFY

statement, but if you’re on postgres then setting up a view that dedupes based on primary key w/ an updated_at field would be ideal. https://gitlab.com/gitlab-data/analytics/-/blob/master/transform/snowflake-dbt/mod[…]tlab_dotcom/dedupe/gitlab_dotcom_audit_events_dedupe_source.sql is an example of how the gitlab data team deduped some of the gitlab.com we extract depends on your database - Snowflake is really nice with the

Qualify

Thanks @taylor