DBT: Postgres Commodity Equivalence Factors

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

DBT model name: postgres_commodity_equivalence_factors

Explore on Metabase: Full table; summary statistics

DBT details

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

Description

Extracts commodity equivalence factors from the Postgres 'main' schema. This table was made quickly to be able to run the Brazil beef SEI-PCS update: probably it can be made more user-friendly.

Details

ColumnsDepends OnModel code

Column	Type	Description
`time_start`	`TIMESTAMP`
`time_end`	`TIMESTAMP`
`eq_factor`	`DOUBLE`
`commodity_equivalence_factors_ref_id`	`INTEGER`
`com_id`	`INTEGER`
`node_id`	`BIGINT`
`unit`	`VARCHAR`
`com_eq_group_id`	`INTEGER`
`com_eq_factor_id`	`INTEGER`
`commodity_equivalence_group_id`	`INTEGER`
`description`	`VARCHAR`
`is_default`	`BOOLEAN`
`commodity_equivalence_group_name`	`VARCHAR`
`commodity_code`	`VARCHAR`
`com_code_id`	`INTEGER`
`commodity_code_name`	`VARCHAR`
`commodity_code_description`	`VARCHAR`

**Macros**

- `macro.trase_duckdb.attach_postgres`

- `macro.trase_duckdb.detach_postgres`

No called script or script source not found.

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

-- DuckDB can't seem to SELECT tables from the "main" schema, but all other schemas
-- work fine. Maybe it is a reserved word or something? So using "postgres_query" as a
-- workaround
SELECT * FROM postgres_query(
    '{{ this.identifier }}_postgres_db',
    'SELECT
          commodity_equivalence_factors.time_start
        , commodity_equivalence_factors.time_end
        , commodity_equivalence_factors.eq_factor
        , commodity_equivalence_factors.ref_id AS commodity_equivalence_factors_ref_id
        , commodity_equivalence_factors.com_id
        , commodity_equivalence_factors.node_id
        , commodities.unit
        , commodity_equivalence_group_factors.com_eq_group_id
        , commodity_equivalence_group_factors.com_eq_factor_id
        , commodity_equivalence_groups.id AS commodity_equivalence_group_id
        , commodity_equivalence_groups.description
        , commodity_equivalence_groups.is_default
        , commodity_equivalence_groups.name AS commodity_equivalence_group_name
        , commodity_code_values.value AS commodity_code
        , commodity_code_values.com_code_id
        , commodity_codes.name AS commodity_code_name
        , commodity_codes.description AS commodity_code_description
    FROM      main.commodity_equivalence_factors
    JOIN      main.commodities                          ON                 commodity_equivalence_factors.com_id = commodities.id
    JOIN      main.commodity_equivalence_group_factors  ON commodity_equivalence_group_factors.com_eq_factor_id = commodity_equivalence_factors.id
    JOIN      main.commodity_equivalence_groups         ON  commodity_equivalence_group_factors.com_eq_group_id = commodity_equivalence_groups.id
    LEFT JOIN main.commodity_code_values                ON                         commodity_code_values.com_id = commodity_equivalence_factors.com_id
    LEFT JOIN main.commodity_codes                      ON                                   commodity_codes.id = commodity_code_values.com_code_id
    '
)