context = {
    'destination_s3_key': 'soundscan_us_release/data/soundscan_us_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 ss_codes_sub.soundscan_code is not null
                THEN s.subaccount_name
                    WHEN length(v.name) <= 1 then v.company
                ELSE v.name
                END AS 'vendor_name',
            r.release_name,
            r.upc,
            r.display_upc,
            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_sub.soundscan_code is not null
                THEN ss_codes_sub.soundscan_code
                    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',
            COALESCE(ss_formats.format_code,'02') AS 'soundscan_format_code',
            IF(v.owner = 'RED','RED','IOD') AS 'distributor_code'
        FROM releases r
        LEFT JOIN soundscan_upc_exclusion sue ON sue.upc = r.upc
        LEFT JOIN release_territory_restriction rtr
            ON r.release_id = rtr.release_id AND rtr.country_id = 1
        LEFT JOIN subaccount s
            ON s.subaccount_id = r.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 OUTER JOIN (
                SELECT vendor_id, soundscan_code
                FROM soundscan_codes
                WHERE country_id = 1
                AND subaccount_id is null
                AND LENGTH(soundscan_code) >= 3) ss_codes
            ON ss_codes.vendor_id = v.vendor_id
        LEFT OUTER JOIN (
                SELECT subaccount_id, soundscan_code, vendor_id
                FROM soundscan_codes
                WHERE country_id = 1
                AND subaccount_id IS NOT NULL
                AND LENGTH(soundscan_code) >= 3) ss_codes_sub
            ON ss_codes_sub.vendor_id = v.vendor_id
            AND ss_codes_sub.subaccount_id = s.subaccount_id
        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 = 1
                AND genre_type = 'upc,isrc') ss_genres
            ON ss_genres.orchard_genre_id = r.genre_id
        LEFT OUTER JOIN (
                SELECT
                spfm.orchard_release_format ,spf.format_code
                FROM soundscan_product_format_mappings spfm
                INNER JOIN soundscan_product_formats spf
                    ON spfm.soundscan_product_format_id = spf.id
                WHERE spf.country_id = 1
            ) 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 = s.subaccount_id
        WHERE r.product_type_id = 1
        AND rtr.restriction_id IS NULL
        AND r.additional_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} '''
}