DBT: Postgres Ports

File location: s3://trase-storage/postgres_views/postgres_ports.parquet

DBT model name: postgres_ports

Explore on Metabase: Full table; summary statistics

DBT details

Lineage
Test results
Dbt path: trase_production.main.postgres_ports
Containing yaml link: trase/data_pipeline/models/postgres_views/_schema_postgres_tables.yml
Model file: trase/data_pipeline/models/postgres_views/postgres_ports.sql
Original dbt_docs
Tags: postgres, ports

Description

Queries the Postgres 'views.regions' view for region_type = 'PORT'

Details

ColumnsDepends OnModel code

Column	Type	Description
`name`	`VARCHAR`
`trase_id`	`VARCHAR`
`synonyms`	`VARCHAR[]`
`node_id`	`BIGINT`
`country`	`VARCHAR`
`_geometry`	`VARCHAR`
`biome`	`VARCHAR`
`brazil_port_urf_codes`	`VARCHAR[]`
`last_refresh_time`	`TIMESTAMP WITH TIME ZONE`
`parent_trase_id`	`VARCHAR`
`parent_name`	`VARCHAR`
`parent_node_type_slug`	`VARCHAR`
`parent_node_type_name`	`VARCHAR`
`longitude`	`DOUBLE`
`latitude`	`DOUBLE`

**Macros**

- `macro.trase_duckdb.attach_postgres`

- `macro.trase_duckdb.detach_postgres`

No called script or script source not found.

{{ config(materialized='external') }}

WITH
source_data AS (
    SELECT 
        * 
        EXCLUDE(
            region_type, 
            level, 
            node_type_slug, 
            year_start, 
            year_end,
            matopiba,
            node_type_name)
    FROM {{ this.identifier }}_postgres_db.views.regions
    WHERE region_type = 'PORT'
)

SELECT
    *,
    JSON_EXTRACT_STRING(_geometry, '$.coordinates[0]')::DOUBLE AS longitude,
    JSON_EXTRACT_STRING(_geometry, '$.coordinates[1]')::DOUBLE AS latitude
FROM source_data