source_db: art_relations
dim_table: dim_country
export_sql: >-
  SELECT
    id AS countryId,
    name AS countryName,
    abbrivation AS countryCode,
    continent,
    latitude,
    longitude,
    continent_id,
    iso3166a3
  FROM
    country
update_sql:
  - >-
    UPDATE
      {db}.{schema}.{dim_table}
    SET
      countryName = st.countryName,
      country_code = st.country_code,
      continent = st.continent,
      latitude = st.latitude,
      longitude = st.longitude,
      continent_id = st.continent_id,
      last_updated = '{current_timestamp}',
      iso3166a3 = st.iso3166a3
    FROM
      {db}.{schema}.{staging_table} st
    WHERE
      {db}.{schema}.{dim_table}.countryId = st.countryId AND
      (
        NOT (
          nvl({db}.{schema}.{dim_table}.countryName = st.countryName, false) OR
          ({db}.{schema}.{dim_table}.countryName is null and st.countryName is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.country_code = st.country_code, false) OR
          ({db}.{schema}.{dim_table}.country_code is null and st.country_code is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.continent = st.continent, false) OR
          ({db}.{schema}.{dim_table}.continent is null and st.continent is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.latitude = st.latitude, false) OR
          ({db}.{schema}.{dim_table}.latitude is null and st.latitude is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.longitude = st.longitude, false) OR
          ({db}.{schema}.{dim_table}.longitude is null and st.longitude is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.continent_id = st.continent_id, false) OR
          ({db}.{schema}.{dim_table}.continent_id is null and st.continent_id is null)
        ) OR
        NOT (
          nvl({db}.{schema}.{dim_table}.iso3166a3 = st.iso3166a3, false) OR
          ({db}.{schema}.{dim_table}.iso3166a3 is null and st.iso3166a3 is null)
        )
      )
insert_sql:
  - >-
    INSERT INTO
      {db}.{schema}.{dim_table}
    SELECT
      st.countryId,
      st.countryName,
      st.country_code,
      st.continent,
      st.latitude,
      st.longitude,
      '{current_timestamp}',
      '{current_timestamp}',
      st.continent_id,
      st.iso3166a3
    FROM
      {db}.{schema}.{staging_table} st
    LEFT JOIN
      {db}.{schema}.{dim_table} dc on dc.countryId = st.countryId
    WHERE
      dc.countryId is null