Skip to content

Postgres Traders

s3://trase-storage/postgres_views/postgres_traders.parquet

Dbt path: trase_production.main_postgres_tables.postgres_traders

Explore on Metabase: Full table; summary statistics

Containing yaml file link: trase/data_pipeline/models/postgres_tables/_schema_postgres_tables.yml

Model file link: trase/data_pipeline/models/postgres_tables/postgres_traders.sql

Dbt test runs & lineage: Test results ยท Lineage

Full dbt_docs page: Open in dbt docs (includes lineage graph -at the bottom right-, tests, and downstream dependencies)

Tags: postgres, traders

postgres_traders

Description

Queries the Postgres 'views.traders_basic_info' view and brings the traders names, array of labels, array of groups (each a json containing group name, start and end time), and the current time to know when it was updated.

Details

Column Type Description
name VARCHAR
trader_node_id BIGINT
trase_id VARCHAR
labels VARCHAR[]
groups JSON[]
updated_at TIMESTAMP WITH TIME ZONE 
**Macros**

- `macro.trase_duckdb.attach_postgres`

- `macro.trase_duckdb.detach_postgres`

No called script or script source not found.

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

WITH 
-- Filter out records without labels, or without name + trase_id
original_table AS (
    SELECT
        name,
        trader_node_id,
        trase_id,
        labels::VARCHAR[] AS labels,
        groups::JSON[] AS groups,
    FROM 
        {{ this.identifier }}_postgres_db.views.traders_basic_info
    WHERE 
        labels[1] IS NOT NULL
    AND 
        NOT (name IS NULL AND trase_id IS NULL)
),
-- There are 3 records with repeated trase_id. Keep the one with the most groups 
ranked_records AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY trase_id
            ORDER BY array_length(groups, 1) DESC
        ) AS rn
    FROM original_table
),
without_repeated_trase_id AS (
    SELECT 
        name,
        trader_node_id,
        trase_id,
        labels,
        groups,
        NOW() AS updated_at
    FROM ranked_records
    WHERE rn = 1
    OR trase_id IS NULL
)
SELECT * FROM without_repeated_trase_id