https://linen.dev logo
Join Slack
Powered by
Hi all I'm trying to upsert data into snowflake, y...
# troubleshooting
o
Hi all I'm trying to upsert data into snowflake, yet it seems that the "load_method" param isn't doing nothing Help will be highly appreciated
Copy code
version: 1
default_environment: dev
environments:
- name: dev
  config:
    plugins:
      loaders:
      - name: target-snowflake
        config:
          database: AIRFLOW_TASKS_DEV
          warehouse: dbt_dev_wh
- name: staging
- name: prod
  config:
    plugins:
      loaders:
      - name: target-snowflake
        config:
          database: AIRFLOW_TASKS
          warehouse: dbt_wh

plugins:
  - name: tap-smoke-test
    variant: meltano
    pip_url: git+<https://github.com/meltano/tap-smoke-test.git>
    config:
      streams:
      - stream_name: animals
        input_filename: <https://raw.githubusercontent.com/meltano/tap-smoke-test/main/demo-data/animals-data.jsonl>
  loaders:
  - name: target-snowflake
    variant: meltanolabs
    pip_url: meltanolabs-target-snowflake
    config:
      account: $SNOWFLAKE_ACCOUNT
      user: $SNOWFLAKE_USER
      role: ORCHESTRATOR_ROLE
      password: $SNOWFLAKE_PASSWORD

  - name: target-snowflake-test-upsert
    inherit_from: target-snowflake
    config:
      schema: test
      load_method: upsert
      key_properties:
        - id
e
Hi @Omri Antman! Can you create an issue in the repo? https://github.com/MeltanoLabs/target-snowflake/issues/
o
Sure mate Can you also please advice on the correct strategy when you want to load incremental data that should be upserted? I need to define the key_properties inside the extractor or loader? Is there an example I can look over?
I see this in my debug log: 2024-04-15T183033.209510Z [debug ] {"type":"SCHEMA","stream":"animals","schema":{"properties":{"id":{"type":"integer"},"description":{"type":"string"},"verified":{"type":"boolean"},"views":{"type":"integer"},"created_at":{"type":"string"}},"type":"object","required":["created_at","description","id","verified","views"]},"*`key_properties":[]}`* cmd_type=extractor name=tap-smoke-test (out) state_id=moki_shoki stdio=stdout I've tried numerous ways to try and update the "key_properties" but it doesn't work for some reason Example:
Copy code
- name: tap-smoke-test
    variant: meltano
    pip_url: git+<https://github.com/meltano/tap-smoke-test.git>
    config:
      streams:
      - stream_name: animals
        input_filename: <https://raw.githubusercontent.com/meltano/tap-smoke-test/main/demo-data/animals-data.jsonl>
        primary_keys:
        - id
e
I need to define the key_properties inside the extractor or loader?
Inside the extractor. You can use meltano's metadata extra. Something like this ought to work:
Copy code
plugins:
  extractors:
  - name: tap-smoke-test
    variant: meltano
    pip_url: git+<https://github.com/meltano/tap-smoke-test.git>
    config:
      schema_inference_record_count: 5
      streams:
      - stream_name: animals
        input_filename: ./animals-data.jsonl
    metadata:
      animals:
        key-properties: ["id"]  # <- custom metadata
o
Thank you @Edgar Ramírez (Arch.dev)! It worked. And also no need for the "upsert" load method, since I assume that if you define the key_properties, upsert is defined as the default. having said that, the load_method "append-only" doesn't work if the key_properties are set. Thanks again 🙏
🙌 1
2 Views
Open in Slack
PreviousNext
Powered by