Skip to content

DBT: S3 Files Latest

DBT model name: s3_files_latest

Explore dependencies/lineage: link

Description

The current S3 catalog: latest inventory row per object with derived prefix_l1/prefix_l2/extension, size_mb, the s3:// URI, a has_dbt_docs flag, and a Metabase explore link (metabase_url). Materialized as a table for fast Metabase exploration. Join s3_dbt_documented_files on s3_uri = s3_location for the dbt model name / catalog URL / description of the few documented files.

Details

Column Type Description
key ``
size_mb `` Object size in MB (size_bytes / 1024^2).
has_dbt_docs `` True if a dbt model writes this object (see s3_dbt_documented_files).
metabase_url `` Metabase native-query deep link to SELECT * FROM '<s3_uri>'.

Review full report including sample errors records if they exist (link)

Test column Test name Failing rows Last test run Last status Query in Metabase
key not_null_s3_files_latest_key — 2026-07-19 06:51 pass
key unique_s3_files_latest_key — 2026-07-19 06:51 pass

Models

Models

Macros

No called script or script source not found.

{{ config(materialized='table', tags=['s3_inventory']) }}

-- The CURRENT contents of the bucket: every object present in the most recent
-- inventory snapshot, with derived prefix/extension columns, the full s3:// URI,
-- a `has_dbt_docs` flag, and a ready-to-click Metabase "explore this file" link.
-- Materialized as a table so Metabase dashboards read precomputed rows (DuckLake
-- has no materialized views; we rebuild on each run).
--
-- We filter to the latest snapshot (rather than "last-seen row per key") so that
-- once the full snapshot history is backfilled, files deleted in earlier
-- snapshots don't linger here as if they still existed. Deleted-file history
-- remains queryable in s3_inventory_snapshots.

with latest as (
    select *
    from {{ ref('s3_inventory_snapshots') }}
    where snapshot_ts = (
        select max(snapshot_ts) from {{ ref('s3_inventory_snapshots') }}
    )
),

documented as (
    select distinct s3_location
    from {{ ref('s3_dbt_documented_files') }}
)

select
    l.bucket,
    l.key,
    l.size_bytes,
    l.size_mb,
    l.last_modified,
    l.etag,
    l.snapshot_ts,
    's3://' || l.bucket || '/' || l.key as s3_uri,
    split_part(l.key, '/', 1) as prefix_l1,
    case
        when position('/' in l.key) > 0
            then split_part(l.key, '/', 1) || '/' || split_part(l.key, '/', 2)
        else null
    end as prefix_l2,
    nullif(lower(regexp_extract(l.key, '\.([^./]+)$', 1)), '') as extension,
    d.s3_location is not null as has_dbt_docs,
    {{ metabase_question_url("'SELECT * FROM ''' || 's3://' || l.bucket || '/' || l.key || ''''") }} as metabase_url
from latest l
left join documented d
    on ('s3://' || l.bucket || '/' || l.key) = d.s3_location