Skip to content

DBT: S3 Downloads Daily

DBT model name: s3_downloads_daily

Explore dependencies/lineage: link

Description

Per-(day, key) access rollup kept indefinitely — the history layer for usage. Raw events are pruned to a rolling window, but this table retains additive daily metrics forever, so you can rank top downloads over any window (past month, 2 months, a year) with a SUM over a date range. The requesters list enables exact distinct-requester counts over any window.

Details

Column Type Description
event_date `` UTC date of the requests.
download_requests `` REST.GET.OBJECT count for that day+key (additive across days).
requesters `` Distinct requesters that day; union across days for windowed distinct.

No data tests defined 🧐

Not referenced by any model or exposure.

Models

Macros

  • is_incremental

No called script or script source not found.

{{ config(
    materialized='incremental',
    unique_key=['event_date', 'key'],
    incremental_strategy='delete+insert',
    tags=['s3_access_logs']
) }}

-- Per-(day, key) access rollup, kept INDEFINITELY (unlike the raw
-- s3_access_log_events table, which is pruned to a rolling window). This is the
-- history layer: query it to rank top downloads over any window (past month,
-- 2 months, a year, …) without retaining every raw log line.
--
-- All metrics are additive across days, so a window query is just a SUM over a
-- date range (see trase/data_pipeline/README.md → "Historical usage"). The
-- `requesters` list lets you compute exact distinct requesters over any window
-- via length(list_distinct(flatten(list(requesters)))).
--
-- Incremental: each run recomputes the trailing `s3_monitor_rollup_reprocess_days`
-- days from raw (to absorb late-arriving logs) and upserts them by (event_date,
-- key); older days are immutable history. The reprocess window must stay well
-- below the raw retention window so the source rows still exist.

with raw as (
    select
        (request_time at time zone 'UTC')::date as event_date,
        key,
        operation,
        requester,
        coalesce(bytes_sent, 0) as bytes_sent
    from {{ ref('s3_access_log_events') }}
    where key is not null and request_time is not null
)

select
    event_date,
    key,
    count(*) filter (where operation = 'REST.GET.OBJECT') as download_requests,
    count(*) as total_requests,
    count(*) filter (where operation = 'REST.HEAD.OBJECT') as head_requests,
    sum(bytes_sent) as bytes_sent,
    round(sum(bytes_sent) / (1024 * 1024), 3) as mb_sent,
    count(distinct requester) as distinct_requesters,
    list(distinct requester) as requesters
from raw
{% if is_incremental() %}
where event_date >= (
    (select coalesce(max(event_date), DATE '1970-01-01') from {{ this }})
    - interval {{ var('s3_monitor_rollup_reprocess_days', 7) }} day
)::date
{% endif %}
group by event_date, key