Venkata Gowri Sai Rakesh Kumar Varanasi vvgsrk

Data Platform Architect at Finnair

vvgsrk / one_hour_interval_average_load_with_credits_and_query_count.sql

Created April 10, 2020 08:08

1 Hour Interval Average Load with Credits and Query count

	WITH wlh as (
	SELECT DATE_TRUNC('hour', wl.start_time) start_time_trunced_at_hour, HOUR(wl.start_time) start_time_hour,
	AVG(avg_running) avg_running, AVG(avg_queued_load) avg_queued_load,
	AVG(avg_queued_provisioning) avg_queued_provisioning, AVG(avg_blocked) avg_blocked
	FROM snowflake.account_usage.warehouse_load_history wl
	WHERE DATE_TRUNC('DAY', wl.start_time) = '2020-03-26'
	AND wl.warehouse_name = 'PUT_YOUR_WAREHOUSE_NAME'
	GROUP BY start_time_trunced_at_hour, start_time_hour
	ORDER BY start_time_trunced_at_hour asc
	),

vvgsrk / Parse_Fixed_Width_Text_File_Using_Pandas.ipynb

Created April 12, 2020 09:52

Parse fixed width text file using Pandas library read_fwf method

Sorry, something went wrong. Reload?

Sorry, we cannot display this file.

Sorry, this file is invalid so it cannot be displayed.

vvgsrk / snowflake_stream_for_booking_table.sql

Last active June 25, 2020 15:09

	CREATE OR REPLACE STREAM db_dev.revenue.od_forecast_booking_stream
	ON TABLE db_dev.revenue.od_forecast_booking
	APPEND_ONLY = TRUE;

vvgsrk / check_stream_info.sql

Last active June 28, 2020 18:03

	SHOW STREAMS LIKE 'od_forecast_booking_stream' IN db_dev.revenue;

	DESCRIBE STREAM db_dev.revenue.od_forecast_booking_stream;

vvgsrk / query_stream_object.sql

Created June 25, 2020 15:08

SELECT * FROM db_dev.revenue.od_forecast_booking_stream;

vvgsrk / fleet_assigner_query_as_view.sql

Last active June 29, 2020 12:53

	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 / create_s3_bucket_for_fleet_assigner_output.yml

Created June 25, 2020 16:59

	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 / snowflake_stage_for_fleet_assigner.sql

Last active June 27, 2020 14:22

Snowflake Stage to unload fleet assigner output CSV to S3 bucket

	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 / snowflake_java_script_stored_procedure_to_unload_csv_to_s3.sql

Last active June 29, 2020 12:56

	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()

Last active June 28, 2020 14:29

	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;