Liner Notes/Architecture

Architecture — Data Quality + Lineage

From API to static file. Every hop audited.

Two Fivetran connectors. Snowflake as the lakehouse. dbt-Snowflake shapes the gold layer. dbt-wizard run-time agents read the same gold marts. A Python snapshot writes static JSON to CloudFront.

11

raw tables

16

dbt models

76

dbt tests

1

warn

Stack powered by

→FivetranIngest→SnowflakeWarehouse→dbt LabsTransform

Fivetran lands the data. Snowflake stores and serves it. dbt governs the transform.

01

End-to-end lineage

Spotify and MusicBrainz raw → Fivetran connectors → Snowflake raw schemas → dbt-Snowflake staging views → Snowflake gold marts → static JSON on CloudFront.

SOURCE APISpotify Web APISpotify Web API — recently_played, top_tracks, top_artists, saved_albumsSOURCE APIMusicBrainz APIMusicBrainz API — releases, artists, recordings (essentials-scoped, 1 req/sec)FIVETRAN SDKspotifyOAuth · encrypted tokenFivetran Connector SDK — Spotify connector. OAuth refresh token in Fivetran encrypted store.FIVETRAN SDKmusicbrainzPublic · 1 req/secFivetran Connector SDK — MusicBrainz connector. Essentials-scoped. 1 req/sec rate limit.RAW_SPOTIFY · SNOWFLAKErecently_played1,240 rowsraw_spotify.recently_played — Snowflake table, landed by Fivetrantop_tracks (×3)150 rowsraw_spotify.top_tracks (×3) — Snowflake table, landed by Fivetrantop_artists (×3)150 rowsraw_spotify.top_artists (×3) — Snowflake table, landed by Fivetransaved_albums96 rowsraw_spotify.saved_albums — Snowflake table, landed by FivetranRAW_MB · SNOWFLAKEartists88 rowsraw_mb.artists — Snowflake table, essentials-scopedreleases96 rowsraw_mb.releases — Snowflake table, essentials-scopedrecordings1,180 rowsraw_mb.recordings — Snowflake table, essentials-scopeddbt-snowflakeSTAGING · VIEWSstg_spotify__recently_play…view · 5 testsstg_spotify__recently_played — dbt view, 5 tests definedstg_spotify__saved_albumsview · 4 testsstg_spotify__saved_albums — dbt view, 4 tests definedstg_spotify__top_tracksview · 4 testsstg_spotify__top_tracks — dbt view, 4 tests definedstg_spotify__top_artistsview · 4 testsstg_spotify__top_artists — dbt view, 4 tests definedstg_mb__releasesview · 5 testsstg_mb__releases — dbt view, 5 tests definedstg_mb__artistsview · 4 testsstg_mb__artists — dbt view, 4 tests definedstg_mb__recordingsview · 3 testsstg_mb__recordings — dbt view, 3 tests defineddbt martsCOREdim_albumssnowflakedim_albums — Snowflake table, marts/coredim_artistssnowflakedim_artists — Snowflake table, marts/coredim_trackssnowflakedim_tracks — Snowflake table, marts/coreLISTENINGfct_playssnowflakefct_plays — Snowflake table, marts/listeningagg_top_trackssnowflakeagg_top_tracks — Snowflake table, marts/listeningagg_top_artistssnowflakeagg_top_artists — Snowflake table, marts/listeningCURATIONcanon_vs_listeningsnowflakecanon_vs_listening — Snowflake table, marts/curationdecade_dist.snowflakedecade_dist. — Snowflake table, marts/curationdiscoveriessnowflakediscoveries — Snowflake table, marts/curationSnowflake WHXS · auto-suspend
API pullFivetran ingestSnowflake (raw schemas)dbt-Snowflake transform (view)Snowflake gold mart (table)
02

Source tables

11 raw tables in Snowflake — two schemas, raw_spotify and raw_mb. Landed by two Fivetran Connector SDK connectors on each sync.

raw_spotify — 8 tables

raw_spotify

recently_played

14m ago

Rows

1,240

Cols

9

PK

played_at, track_id

raw_spotify

top_tracks_short_term

14m ago

Rows

50

Cols

7

PK

track_id, time_range

raw_spotify

top_tracks_medium_term

14m ago

Rows

50

Cols

7

PK

track_id, time_range

raw_spotify

top_tracks_long_term

14m ago

Rows

50

Cols

7

PK

track_id, time_range

raw_spotify

top_artists_short_term

14m ago

Rows

50

Cols

6

PK

artist_id, time_range

raw_spotify

top_artists_medium_term

14m ago

Rows

50

Cols

6

PK

artist_id, time_range

raw_spotify

top_artists_long_term

14m ago

Rows

50

Cols

6

PK

artist_id, time_range

raw_spotify

saved_albums

14m ago

Rows

96

Cols

8

PK

album_id

raw_mb — 3 tables

raw_mb

artists

1h 2m ago

Rows

88

Cols

10

PK

mbid

raw_mb

releases

1h 2m ago

Rows

96

Cols

12

PK

mbid

raw_mb

recordings

1h 2m ago

Rows

1,180

Cols

8

PK

mbid

03

dbt model lineage

7 staging views clean and cast raw columns. 9 Snowflake tables materialize the gold marts — core dims, listening facts, and curation aggregates. dbt-Snowflake adapter throughout.

Staging

stg_spotify__recently_played

view5 tests

stg_spotify__saved_albums

view4 tests

stg_spotify__top_tracks

view4 tests

stg_spotify__top_artists

view4 tests

stg_mb__releases

view5 tests

stg_mb__artists

view4 tests

stg_mb__recordings

view3 tests

Marts / Core

dim_albums

snowflake table8 tests

dim_artists

snowflake table6 tests

dim_tracks

snowflake table5 tests

Marts / Listening

fct_plays

snowflake table7 tests

agg_top_tracks_by_window

snowflake table4 tests

agg_top_artists_by_window

snowflake table4 tests

Marts / Curation

canon_vs_listening

snowflake table6 tests

decade_distribution

snowflake table3 tests

discoveries

snowflake table4 tests

7

Views (staging)

9

Snowflake tables (gold)

4

Layers

04

Data quality

dbt tests run on every build. Failures block promotion to the next layer. One warn on an accepted_values test — no failing tests in production.

76tests
75 passing
1 warn

Last run: dbt build · all layers · 8m ago

staging

all passing
29/29
  • not_null_stg_spotify__recently_pla…
  • unique_stg_mb__releases_mbid
  • not_null_stg_mb__artists_mbid

core

1 warn
18/19
  • not_null_dim_albums_album_id
  • unique_dim_albums_album_id
  • accepted_values_dim_tracks_source_…

listening

all passing
15/15
  • not_null_fct_plays_played_at
  • referential_fct_plays_track_id__di…
  • not_null_fct_plays_duration_ms

curation

all passing
13/13
  • not_null_canon_vs_listening_album_…
  • unique_decade_distribution_decade
  • not_null_discoveries_first_played_…
05

Operations

Personal dataset at personal scale. ~$10–20/month all-in (Snowflake XS warehouse, S3, CloudFront). Warehouse auto-suspends after 60s of inactivity.

Est. monthly cost

$10–20

Snowflake credits + S3 + CloudFront

p95 query time

0.8s

Snowflake XS warehouse, gold layer

Last full refresh

14m ago

Fivetran → dbt → snapshot

dbt build time

~40s

all 16 models, auto-suspend on

·

Trust boundaries

What is public, what is private, and where each credential lives.

Spotify credentials

User-bound OAuth. The connector holds a refresh token in Fivetran's encrypted secret store — never committed to this repo, never in .tfvars.

MusicBrainz access

Public API, no credentials required. The connector enforces MB's 1 req/sec policy via a configurable rate_limit_sleep parameter.

dbt Snowflake role

The dbt runner connects via a dedicated Snowflake role scoped to the LINER_NOTES database. Raw schema is readable; only the staging and marts schemas allow writes.

CloudFront isolation

The SPA is served from an S3 bucket that has no access to Snowflake. CloudFront serves the static export; Snowflake credentials are never embedded in the frontend.

Terraform IAM policies, Fivetran connector configs, and dbt profiles are all kept outside version control. .tfvars and profiles.yml.example contain no real values — only placeholder comments showing required keys.

·

dbt-wizard run-time agents

Natural-language queries over the Snowflake gold layer. Ask a question — four dbt-wizard sub-agents (Explorer, Summary, Worker, Verification) generate SQL, run it against the marts, and return a structured answer. Humans and agents read the same gold layer.

Input

Plain-English question against the semantic model defined on dim_albums, fct_plays, and the curation marts.

dbt-wizard agents

Four sub-agents — Explorer, Summary, Worker, Verification — generate SQL, execute it against the Snowflake gold layer, and return structured results. Same tested mart columns the rest of the site reads.

Output

Narrated answer rendered on the /ask page. Same gold tables the rest of the site reads — no separate analytical copy.

dbt-wizard sub-agents sit between the Snowflake gold marts and the app — the same position as the build_snapshot.py script, but interactive. They read the tested, versioned mart columns rather than raw API payloads.