context = {
    'destination_s3_key': 'soundscan_canada_release/data/soundscan_canada_release.txt.gz',
    'chunk_size': 100000,
    'table': 'releases',
    'primary_key': 'release_id',
    'bucket': '{env}-db-data',
    'query': '''
            SELECT
                r.release_id,
                ai.vendor_id,
                CASE
                    WHEN r.subaccount_id IS NOT NULL THEN sub.`subaccount_name`
                    ELSE v.name
                END AS 'vendor_name',
                r.release_name,
                r.upc,
                r.display_upc,
                r.listprice,
                IF(r.original_release_date IS NULL OR r.original_release_date = '0000-00-00',
                    DATE_FORMAT(r.sale_start_date,'%m%d%y'),
                    DATE_FORMAT(r.original_release_date,'%m%d%y')
                ) AS 'release_sale_date',
                ai.name AS 'artist_name',
                CASE
                    WHEN ss_codes.soundscan_code IS NOT NULL THEN ss_codes.soundscan_code
                    WHEN ss_codes.soundscan_code IS NULL AND v.owner = 'RED' THEN 'RDIK'
                    ELSE 'ORCH'
                END AS 'soundscan_label_code',
                COALESCE(ss_genres.genre,'00') AS 'soundscan_genre_id',
                ss_formats.format_code AS 'soundscan_configuration_code',
                ss_formats.format_name AS 'soundscan_configuration_code_name',
                IF(v.owner = 'RED','RED','IOD') AS 'distributor_code'
            FROM releases r
            LEFT JOIN subaccount sub
                ON r.`subaccount_id` = sub.`subaccount_id`
            INNER JOIN artist_info ai
                ON ai.artist_id = r.artist_id
            INNER JOIN vendor v
                ON v.vendor_id = ai.vendor_id
            LEFT JOIN release_territory_restriction rtr
                ON rtr.release_id = r.release_id AND rtr.country_id = 2
            LEFT OUTER JOIN (
                    SELECT
                        vendor_id,
                        IF(LENGTH(soundscan_code) < 1,NULL, soundscan_code) AS 'soundscan_code',
                        subaccount_id
                    FROM soundscan_codes
                    WHERE country_id = 2) ss_codes
                ON ss_codes.vendor_id = v.vendor_id
                AND IFNULL(ss_codes.subaccount_id,0) = IFNULL(r.subaccount_id,0)
            LEFT OUTER JOIN (
                    SELECT
                        sgm.orchard_genre_id,
                        sgm.soundscan_genre_id,sg.genre
                    FROM soundscan_genre_mappings sgm
                    INNER JOIN soundscan_genres sg
                        ON sgm.soundscan_genre_id = sg.id
                    WHERE sg.orchard_country_id = 2 AND genre_type = 'upc' ) ss_genres
                ON ss_genres.orchard_genre_id = r.genre_id
            LEFT OUTER JOIN (
                    SELECT
                        spfm.orchard_release_format,
                        spf.format_code,
                        spf.format_name
                    FROM soundscan_product_format_mappings spfm
                    INNER JOIN soundscan_product_formats spf
                        ON spfm.soundscan_product_format_id = spf.id
                    WHERE spf.country_id = 2
                ) ss_formats
                ON ss_formats.orchard_release_format = r.format
            LEFT OUTER JOIN `soundscan_vendor_exclusion` sve
                ON sve.vendor_id = v.vendor_id
            LEFT OUTER JOIN `soundscan_subaccount_exclusion` sse
                ON sse.subaccount_id = sub.subaccount_id
            LEFT JOIN soundscan_upc_exclusion sue ON sue.upc = r.upc
            WHERE r.product_type_id = 1 AND rtr.restriction_id IS NULL
            AND r.display_upc IS NOT NULL
            AND sve.vendor_id IS NULL
            AND sse.subaccount_id IS NULL
            AND sue.upc IS NULL
        AND r.release_id BETWEEN {min_id} AND {max_id} '''
}