solana-models/models/streamline/streamline__complete_block_txs.sql

{{ config (
    materialized = "incremental",
    unique_key = "block_id",
    cluster_by = "_partition_id",
    merge_update_columns = ["_partition_id"],
    tags = ['streamline'],
) }}

SELECT
    block_id,
    error,
    _partition_id
FROM
    {{ source(
        "bronze_streamline",
        "block_txs_api"
    ) }} AS s
WHERE
    s.block_id IS NOT NULL

{% if is_incremental() %}
AND s._partition_id > (
    select 
        coalesce(max(_partition_id),0)
    from
        {{ this }}
)
{% endif %}
group by 1,2,3
{% if not is_incremental() %}
qualify(ROW_NUMBER() over (PARTITION BY block_id
ORDER BY
_partition_id DESC)) = 1
{% endif %}