Snowflake External Table with Auto Ingest

infare_external_table.sql

CREATE OR REPLACE EXTERNAL TABLE dp_prod.raw_rmp.infare_external_table (
        date_partition date as to_date(concat(substr(METADATA$FILENAME, 31, 4), '-', substr(METADATA$FILENAME, 39, 2), '-', substr(METADATA$FILENAME, 45, 2)), 'YYYY-MM-DD'),
        id int as (value:id::int),
        observation_date date as (TO_TIMESTAMP(value:observation_date)::date),
        observation_time varchar as (value:observation_time::varchar),
        pos varchar as (value:pos::varchar),
        origin varchar as (value:origin::varchar),
        destination varchar as (value:destination::varchar),
        is_one_way int as (value:is_one_way::int),
        outbound_travel_stop_over varchar as (value:outbound_travel_stop_over::varchar),
        inbound_travel_stop_over varchar as (value:inbound_travel_stop_over::varchar),
        carrier varchar as (value:carrier::varchar),
        outbound_flight_no varchar as (value:outbound_flight_no::varchar),
        inbound_flight_no varchar as (value:inbound_flight_no::varchar),
        outbound_departure_date date as (TO_TIMESTAMP(value:outbound_departure_date)::date),
        outbound_departure_time varchar as (value:outbound_departure_time::varchar),
        outbound_arrival_date date as (TO_TIMESTAMP(value:outbound_arrival_date)::date),
        outbound_arrival_time varchar as (value:outbound_arrival_time::varchar),
        inbound_departure_date date as (TO_TIMESTAMP(value:inbound_departure_date)::date),
        inbound_departure_time varchar as (value:inbound_departure_time::varchar),
        inbound_arrival_date date as (TO_TIMESTAMP(value:inbound_arrival_date)::date),
        inbound_arrival_time varchar as (value:inbound_arrival_time::varchar),
        outbound_fare_basis varchar as (value:outbound_fare_basis::varchar),
        inbound_fare_basis varchar as (value:inbound_fare_basis::varchar),
        outbound_booking_class varchar as (value:outbound_booking_class::varchar),
        inbound_booking_class varchar as (value:inbound_booking_class::varchar),
        price_exc float as (value:price_exc::float),
        price_inc float as (value:price_inc::float),
        tax float as (value:tax::float),
        currency varchar as (value:currency::varchar),
        source varchar as (value:source::varchar),
        price_outbound float as (value:price_outbound::float),
        price_inbound float as (value:price_inbound::float),
        is_tax_inc_outin int as (value:is_tax_inc_outin::int),
        search_class varchar as (value:search_class::varchar),
        outbound_fare_family varchar as (value:outbound_fare_family::varchar),
        inbound_fare_family varchar as (value:inbound_fare_family::varchar),
        outbound_seats varchar as (value:outbound_seats::varchar),
        inbound_seats varchar as (value:inbound_seats::varchar),
        min_stay int as (value:min_stay::int),
        outbound_flight_duration int as (value:outbound_flight_duration::int),
        inbound_flight_duration int as (value:inbound_flight_duration::int),
        observation_datetime datetime as (value:observation_datetime::datetime),
        outbound_departure_datetime datetime as (value:outbound_departure_datetime::datetime),
        outbound_arrival_datetime datetime as (value:outbound_arrival_datetime::datetime),
        inbound_departure_datetime datetime as (value:inbound_departure_datetime::datetime),
        inbound_arrival_datetime datetime as (value:inbound_arrival_datetime::datetime),
        observation_timeint int as (value:observation_timeint::int),
        outbound_departure_timeint int as (value:outbound_departure_timeint::int),
        outbound_arrival_timeint int as (value:outbound_arrival_timeint::int),
        inbound_departure_timeint int as (value:inbound_departure_timeint::int),
        inbound_arrival_timeint int as (value:inbound_arrival_timeint::int),
        type varchar as (value:type::varchar),
        subscription_type  varchar as (value:subscription_type::varchar),
        dow_outbound int as (value:dow_outbound::int),
        dow_name_outbound varchar as (value:dow_name_outbound::varchar),
        dow_inbound int as (value:dow_inbound::int),
        dow_name_inbound varchar as (value:dow_name_inbound::varchar),
        outbound_stopovers_count int as (value:outbound_stopovers_count::int),
        inbound_stopovers_count  int as (value:inbound_stopovers_count::int),
        outbound_departure_year_month varchar as (value:outbound_departure_year_month::varchar),
        inbound_departure_year_month varchar as (value:inbound_departure_year_month::varchar),
        days_before_departure int as (value:days_before_departure::int),
        airport_od varchar as (value:airport_od::varchar),
        source_file_name varchar as (value:source_file_name::varchar),
        source_file_date date as (TO_TIMESTAMP(value:source_file_date)::date),
        source_file_datetime datetime as (value:source_file_datetime::datetime)
    ) PARTITION BY (date_partition)
      LOCATION = @DP_PROD.STAGE.DP_PROD_DATA_LAKE/rmp/boomi/pricing/infare/
      AUTO_REFRESH = TRUE
      FILE_FORMAT = (TYPE = PARQUET);