Skip to content

DBT: Postgres Traders

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

DBT model name: postgres_traders

Explore on Metabase: Full table; summary statistics

Explore dependencies/lineage: link

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

No data tests defined 🧐

Models

Macros

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