Skip to content

DBT: S3 Top Keys

DBT model name: s3_top_keys

Explore dependencies/lineage: link

Description

The most-accessed files over the access-log window. download_requests (REST.GET.OBJECT) is the primary signal — total_requests also includes HEAD/metadata noise. Includes distinct_requesters, last_accessed_at / last_downloaded_at, bytes/MB sent, and (left-joined) size_mb / has_dbt_docs / still_present. Filter on rank_by_downloads (or rank_by_requests / rank_by_bytes) for a top-N.

Details

Column Type Description
download_requests `` Count of REST.GET.OBJECT requests (actual downloads).
rank_by_downloads `` 1 = most-downloaded file in the window.

No data tests defined 🧐

Not referenced by any model or exposure.

Models

No called script or script source not found.

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

-- "Most accessed files" over the access-log retention window. Access logs are
-- dominated by REST.HEAD.OBJECT metadata calls, so downloads (REST.GET.OBJECT)
-- are counted separately and used as the primary ranking — filter on
-- rank_by_downloads (or rank_by_requests / rank_by_bytes) for a top-N.
-- Left-joined to the current catalog for size and dbt-doc context (null for
-- keys no longer present, e.g. since-deleted files).

with events as (
    select
        key,
        operation,
        requester,
        request_time,
        coalesce(bytes_sent, 0) as bytes_sent
    from {{ ref('s3_access_log_events') }}
    where key is not null
),

agg as (
    select
        key,
        count(*) as total_requests,
        count(*) filter (where operation = 'REST.GET.OBJECT') as download_requests,
        count(*) filter (where operation = 'REST.HEAD.OBJECT') as head_requests,
        count(distinct requester) as distinct_requesters,
        sum(bytes_sent) as total_bytes_sent,
        max(request_time) as last_accessed_at,
        max(request_time) filter (where operation = 'REST.GET.OBJECT')
            as last_downloaded_at
    from events
    group by key
)

select
    a.key,
    a.download_requests,
    a.total_requests,
    a.head_requests,
    a.distinct_requesters,
    a.total_bytes_sent,
    round(a.total_bytes_sent / (1024 * 1024), 3) as total_mb_sent,
    a.last_accessed_at,
    a.last_downloaded_at,
    f.size_mb,
    f.has_dbt_docs,
    f.key is not null as still_present,
    rank() over (order by a.download_requests desc) as rank_by_downloads,
    rank() over (order by a.total_requests desc) as rank_by_requests,
    rank() over (order by a.total_bytes_sent desc) as rank_by_bytes
from agg a
left join {{ ref('s3_files_latest') }} f on a.key = f.key