vvgsrk

SHOW STAGES LIKE 'dev_data_outbound_fleetassigner' IN db_dev.stage;

DESCRIBE STAGE db_dev.stage.dev_data_outbound_fleetassigner;

vvgsrk

SHOW TASKS LIKE 'fa_unload_task' IN db_dev.revenue;

DESCRIBE TASK db_dev.revenue.fa_unload_task;

vvgsrk

SELECT scheduled_time, name, state
FROM TABLE(information_schema.task_history()) 
WHERE database_name = 'DB_DEV'
AND schema_name = 'REVENUE'
AND name = 'FA_UNLOAD_TASK'
ORDER BY completed_time desc;

vvgsrk

CREATE OR REPLACE TASK db_dev.revenue.fa_unload_task
  WAREHOUSE = LOAD_DEV  
  SCHEDULE  = 'USING CRON 0 18 * * * UTC'
WHEN
  SYSTEM$STREAM_HAS_DATA('OD_FORECAST_BOOKING_STREAM')
AS
  call db_dev.revenue.fa_unload_sp();
  
ALTER TASK IF EXISTS db_dev.revenue.fa_unload_task RESUME;

vvgsrk

create or replace procedure db_dev.revenue.fa_unload_sp()
  returns string not null
  language javascript
  as
  $$       
    // Get Current Date
    var date_now = new Date()
    
    // Get Year as YYYY
    var full_year = date_now.getFullYear()

vvgsrk

CREATE STAGE db_dev.stage.dev_data_outbound_fleetassigner
URL = 's3://dev-outbound-fleetassigner' 
CREDENTIALS = (AWS_KEY_ID = 'YOUR_ACCESS_KEY' AWS_SECRET_KEY = 'YOUR_SECRET_KEY') 
COMMENT = 'This stage is for Fleet Assigner data unload from Snowflake';

vvgsrk

AWSTemplateFormatVersion: 2010-09-09
Description: 'Cloudformation Stack to create a outbound bucket for Fleet Assigner Data'

Resources:

  DataBucketDev:
    Type: 'AWS::S3::Bucket'
    Properties:
      AccessControl: Private
      PublicAccessBlockConfiguration:

vvgsrk

CREATE OR REPLACE VIEW db_dev.revenue.fleet_assigner_vw
AS
SELECT
    airline_code || LPAD(flight_number, 4, 0) ||
    TO_CHAR(depature_date, 'ddMMyyyy') ||
    depature_airport ||
    arrival_airport ||
    flight_cabin ||
    booking_class
    AS FA_OUT

vvgsrk

SELECT * FROM db_dev.revenue.od_forecast_booking_stream;

vvgsrk

SHOW STREAMS LIKE 'od_forecast_booking_stream' IN db_dev.revenue;

DESCRIBE STREAM db_dev.revenue.od_forecast_booking_stream;