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, overlaid with the creates / overwrites / deletes seen in the access logs since that snapshot (rebuilt daily), 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, or its location is a glob matching it (see s3_dbt_documented_files).
changed_since_snapshot `` True if the access logs show a write after the latest inventory snapshot. For these rows last_modified / size come from the access log and etag is null until the next inventory.
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-10-26 01:12 pass
key unique_s3_files_latest_key — 2026-10-26 01:12 pass

Models

Models

Macros

No called script or script source not found.

-- Rebuilt by both workflows: weekly when a new inventory lands, and daily so
-- the access-log overlay below stays current.
{{ config(materialized='table', tags=['s3_inventory', 's3_access_logs']) }}

-- The CURRENT contents of the bucket: every object present in the most recent
-- inventory snapshot, overlaid with the creates / overwrites / deletes seen in
-- the access logs since that snapshot (s3_recent_object_changes).
--
-- Rows changed after the snapshot take last_modified / size from the access log
-- and have a null etag (the log doesn't record it); the next inventory replaces
-- them with the real values.

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

-- Net effect per key of the writes/deletes since the snapshot: the latest
-- event wins.
recent as (
    select
        key,
        arg_max(bucket, request_time) as bucket,
        arg_max(change_type, request_time) as last_change,
        max(request_time) as last_modified,
        arg_max(object_size, request_time) as size_bytes
    from {{ ref('s3_recent_object_changes') }}
    group by key
),

-- The inventory drops keys under any directory with more than
-- s3_monitor_max_direct_children files. Approximate the same rule for new keys
parent_counts as (
    select parent, count(distinct key) as n_children
    from (
        select key, regexp_extract(key, '^(.*)/', 1) as parent from latest
        union all
        select key, regexp_extract(key, '^(.*)/', 1) as parent
        from {{ ref('s3_access_log_events') }}
        where operation in (
            'REST.PUT.OBJECT', 'REST.POST.OBJECT', 'REST.COPY.OBJECT', 'REST.POST.UPLOAD'
        )
            and http_status >= 200 and http_status < 300
            and key is not null
    )
    group by parent
),

merged as (
    -- Inventory files, updated by any later overwrite; dropped if deleted since.
    select
        l.bucket,
        l.key,
        coalesce(r.size_bytes, l.size_bytes) as size_bytes,
        coalesce(r.last_modified, l.last_modified) as last_modified,
        case when r.key is null then l.etag end as etag,
        l.snapshot_ts,
        r.key is not null as changed_since_snapshot
    from latest l
    left join recent r on r.key = l.key
    where r.last_change is distinct from 'deleted'

    union all

    -- Files created after the snapshot.
    select
        r.bucket,
        r.key,
        r.size_bytes,
        r.last_modified,
        null as etag,
        (select max(snapshot_ts) from latest) as snapshot_ts,
        true as changed_since_snapshot
    from recent r
    join parent_counts pc on pc.parent = regexp_extract(r.key, '^(.*)/', 1)
    where r.last_change = 'created'
        and r.key not like '%/'
        and pc.n_children <= {{ var('s3_monitor_max_direct_children', 200) }}
        and not exists (select 1 from latest l where l.key = r.key)
),

documented as (
    select distinct s3_location
    from {{ ref('s3_dbt_documented_files') }}
    where not regexp_matches(s3_location, '[*?\[]')
),

-- Models whose location is a glob covering several files (e.g. one per year). There
-- are few, so matching every file against them, rather than joining on equality, stays
-- cheap.
documented_globs as (
    select distinct s3_location
    from {{ ref('s3_dbt_documented_files') }}
    where regexp_matches(s3_location, '[*?\[]')
)

select
    l.bucket,
    l.key,
    l.size_bytes,
    round(l.size_bytes / (1024 * 1024), 3) as size_mb,
    l.last_modified,
    l.etag,
    l.snapshot_ts,
    l.changed_since_snapshot,
    '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 or exists (
        select 1 from documented_globs g
        where ('s3://' || l.bucket || '/' || l.key) glob g.s3_location
    ) as has_dbt_docs,
    {{ metabase_question_url("'SELECT * FROM ''' || 's3://' || l.bucket || '/' || l.key || ''''") }} as metabase_url
from merged l
left join documented d
    on ('s3://' || l.bucket || '/' || l.key) = d.s3_location