Fivetran Community

george_durzi · ‎02-12-2024

Hi Community,

I'm using Fivetran to sync data from my Snowflake data warehouse back into our Postgres application database - here's the complete life cycle of the data:

Postgres -> Fivetran -> Snowflake (ingestion db) -> dbt -> Snowflake (reporting db) -> Fivetran -> Postgres (different/dedicated schema)

The use case here is that we're cleaning up and aggregating data from various sources (including our Postgres database) using dbt, and then pushing it back into Postgres purely for reporting.

The data in my destination tables appears to be always replaced by fresh data vs. the rows being updated. I end up with a very large number of rows with `_fivetran_deleted = false`.

I also noticed that my destination tables all have a `_fivetran_id` column, which is strange because the source dbt models have a `unique_key` configured in their configuration.

Can you please point me in the right direction to troubleshoot?

jacklowery · ‎02-12-2024

Hi @george_durzi ,

I think I am tracking. Here is what I would do if I was you.

Once your Postgres data lands in Snowflake (ingest db), make sure to use dbt's contracts (https://docs.getdbt.com/reference/resource-configs/contract) and constraints (https://docs.getdbt.com/reference/resource-properties/constraints) in order to actually transform your raw data and assign primary keys to the tables that land in your reporting db.

Fivetran is assigning a unique key b/c it doesn't see one (https://fivetran.com/docs/databases/snowflake#tableswithaprimarykey). You can confirm if there is actually a key or not by running a describe query on your output models

desc table database.schema_something.table_name;

Here is an example config you can add to a schema.yml file:

  - name: pk_table_test_two
    config:
      contract:
        enforced: true
    columns:
      - name: id
        data_type: integer
        description: hello
        constraints:
          - type: not_null
          - type: primary_key
        tests:
          - unique
          - not_null
      - name: other_column_name
        data_type: date

View solution in original post

Toby-Metcalf · ‎02-13-2024

Thank you for the answer @jacklowery - thank you for the question @george_durzi AND for accepting Jack's solution.
Keep the questions coming.

Fivetran Community

dbt models have unique_key configured but destination tables have _fivetran_id and many deleted rows