How to get data in JSON format from Snowflake to Postgres using Terraform?

Slack Knowledge Base Q&A API, Terraform and Other Topics
, , , ,
1

Summary

The user is looking to understand how to configure their Terraform code to get data in JSON format from a Snowflake source to a Postgres destination.


Question

Hello team, if I want the data in the destination in json format provided my source is snowflake and destination is postgres and I am executing this code using terraform, can anyone please help me understand that what should I add in my code to get the data in destination in the json format

resource "airbyte_source_snowflake" "fix_airbyte" {
  name         = lower("${local.import_from_snowflake_db}.${local.import_from_snowflake_schema}")
  workspace_id = local.airbyte_workspace
  configuration = {
    connect_timeout = local.connection_snowflake.connect_timeout
    source_type     = "snowflake"
    credentials = {
      username_and_password = {
        username = local.connection_snowflake.username
        password = local.connection_snowflake.password
      }
    }

    host      = local.connection_snowflake.account
    warehouse = upper(local.connection_snowflake.warehouse)
    role      = upper(local.connection_snowflake.role)
    database  = upper("DEV")
    schema    = upper("abc")
  }
}

resource "airbyte_destination_postgres" "fix_airbyte" {
  name         = "${local.import_to_postgres_database}.${local.import_to_postgres_schema}"
  workspace_id = local.airbyte_workspace
  configuration = {
    connect_timeout  = local.postgres_connect_timeout
    destination_type = "postgres"
    host             = var.postgres.host
    port             = var.postgres.port
    username         = var.postgres.username
    password         = var.postgres.password
    database         = local.import_to_postgres_database
    schema           = local.import_to_postgres_schema

    ssl_mode = {
      allow = {}
    }
    tunnel_method = {
      no_tunnel = {}
    }
  }
}

resource "airbyte_connection" "snowflake_postgres" {
  source_id                            = airbyte_source_snowflake.fix_airbyte.source_id
  destination_id                       = airbyte_destination_postgres.fix_airbyte.destination_id
  depends_on                           = [airbyte_source_snowflake.fix_airbyte]
  namespace_definition                 = "custom_format"
  namespace_format                     = "emission_analytics_airbyte"
  name                                 = "${airbyte_source_snowflake.fix_airbyte.name} | ${airbyte_destination_postgres.fix_airbyte.name}"
  status                               = "active"
  prefix                               = "fix_airbyte_"
  non_breaking_schema_updates_behavior = "propagate_fully"
  configurations = {
    streams = [
      {
        name      = upper("REPORTS")
        sync_mode = "full_refresh_overwrite"
      },
    ]
  }
  schedule = {
    schedule_type = "manual"
  }
}```


<br>

---

This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. [Click here](https://airbytehq.slack.com/archives/C021JANJ6TY/p1721810865185339) if you want 
to access the original thread.

[Join the conversation on Slack](https://slack.airbyte.com)

<sub>
["json-format", "snowflake", "postgres", "terraform", "airbyte-connection"]
</sub>