CommencerCommencer gratuitement

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.

Cet exercice fait partie du cours

Data Manipulation in Snowflake

Afficher le cours

Instructions

  • 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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

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.___ = ___.___;
Modifier et exécuter le code