Aan de slagGa gratis aan de slag

Validating data quality

Data isn't always very clean. Both the composer field in the track table and the name in the artist table contain information about a song's author. You'd like to validate the data quality in these fields for each track. To do this, you'll use a CASE statement and the columns from JOIN'ed tables. Go get 'em.

Deze oefening maakt deel uit van de cursus

Data Manipulation in Snowflake

Cursus bekijken

Oefeninstructies

  • When the track.composer field is NULL, then label as 'Track Lacks Detail'.
  • If the track.composer field matches the artist.name, then return 'Matching Artist'.
  • Finally, LEFT JOIN the artist table to album using the field artist_id; note that track and album have already been joined for you.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

SELECT
    track.name,
    track.composer,
    artist.name,
    CASE
    	-- A 'Track Lacks Detail' if the composer field is NULL
        WHEN track.composer ___ ___ THEN 'Track Lacks Detail'
        -- Use the composer and artist name to determine if a match exists
        ___ track.composer = ___.name ___ '___'
        ELSE 'Inconsistent Data'
    END AS data_quality
FROM store.track AS track
LEFT JOIN store.album AS album ON track.album_id = album.album_id
-- Join the album table to artist using the artist_id field
___ JOIN store.___ AS artist ON album.___ = ___.___;
Code bewerken en uitvoeren