Model Table configuration

Download JSON Schema

Example

apiVersion: leapview.dev/v1
kind: ModelTable
metadata:
  workspace: movielens
  name: movies
  title: Movies Dimension
  description: Movie dimension with release year, decade, genres, and external identifiers.
spec:
  primaryKey: movie_id
  grain: movie_id
  sources:
    - movielens.movies
    - movielens.links
  fields:
    movie_id: {label: Movie ID, description: MovieLens movie identifier.}
    title: {label: Title, description: Clean movie title.}
    raw_title: {label: Raw title, description: Original MovieLens title value.}
    release_year: {label: Release year, description: Release year parsed from the title suffix.}
    release_decade: {label: Release decade, description: Release decade derived from release year.}
    genres: {label: Genres, description: Pipe-separated genre list.}
    imdb_id: {label: IMDb ID, description: IMDb title identifier with tt prefix.}
    tmdb_id: {label: TMDb ID, description: TMDb movie identifier.}
  transform:
    sql: |
      WITH parsed AS (
        SELECT
          try_cast(m.movieId AS BIGINT) AS movie_id,
          m.title AS raw_title,
          trim(regexp_replace(m.title, '\s*\(([0-9]{4})\)$', '')) AS title,
          try_cast(nullif(regexp_extract(m.title, '\(([0-9]{4})\)$', 1), '') AS INTEGER) AS release_year,
          nullif(m.genres, '') AS genres,
          nullif(trim(CAST(l.imdbId AS VARCHAR)), '') AS imdb_raw,
          nullif(trim(CAST(l.tmdbId AS VARCHAR)), '') AS tmdb_raw
        FROM source."movielens.movies" m
        LEFT JOIN source."movielens.links" l ON try_cast(l.movieId AS BIGINT) = try_cast(m.movieId AS BIGINT)
      )
      SELECT
        movie_id,
        title,
        raw_title,
        release_year,
        CASE
          WHEN release_year IS NULL THEN 'Unknown'
          ELSE concat(CAST(floor(release_year / 10) * 10 AS INTEGER), 's')
        END AS release_decade,
        COALESCE(genres, '(no genres listed)') AS genres,
        CASE
          WHEN imdb_raw IS NULL THEN NULL
          ELSE concat('tt', lpad(imdb_raw, 7, '0'))
        END AS imdb_id,
        try_cast(tmdb_raw AS BIGINT) AS tmdb_id
      FROM parsed
      WHERE movie_id IS NOT NULL

Fields

Nested definitions

APIVersion

Identifier

Metadata

ModelTable

ResourceID