Venkata Gowri Sai Rakesh Kumar Varanasi vvgsrk

Data Platform Architect at Finnair

vvgsrk / central_repository_for_storing_the_tags.csv

Last active July 7, 2024 08:57

vvgsrk / task_error_notifications.sql

Created February 10, 2024 09:51

Snowflake task error notifications

	CREATE OR REPLACE NOTIFICATION INTEGRATION TASK_ERROR_NOTIFICATIONS
	ENABLED = true
	TYPE = QUEUE
	NOTIFICATION_PROVIDER = AWS_SNS
	DIRECTION = OUTBOUND
	AWS_SNS_TOPIC_ARN = 'arn:aws:sns:eu-central-1:123456789012345:dp-tools-slack-notifier-alarm'
	AWS_SNS_ROLE_ARN = 'arn:aws:iam::123456789012345:role/dp-tools-snowflake-tasks-error-notifications';

vvgsrk / serverless_task_batch_load.sql

Last active February 10, 2024 09:48

Snowflake serverless task batch load using schema detection and evolution

	USE ROLE data_engineer;

	-- Create external stage
	CREATE OR REPLACE STAGE DP_DEV.STAGE.DP_DEV_DATA_INBOUND_TEST
	STORAGE_INTEGRATION = DP_INGESTION_DEV
	URL = 's3://dp-dev-data-inbound-test/'
	;

	-- List files in external stage
	LIST @DP_DEV.STAGE.DP_DEV_DATA_INBOUND_TEST;

vvgsrk / Grant_OWNERSHIP_To_Rrole_AAD_PROVISIONER.sql

Last active August 29, 2022 18:32

grant OWNERSHIP on that user to the role AAD_PROVISIONER

	USE ROLE USERADMIN;

	GRANT OWNERSHIP ON USER USER_ID TO ROLE AAD_PROVISIONER;

vvgsrk / find_forbidden_ips.sql

Created August 10, 2022 19:31

Find forbidden IP's on Snowflake

	SELECT *
	FROM TABLE(rest_event_history(
	'scim',
	DATEADD('minutes',-3600,CURRENT_TIMESTAMP()),
	CURRENT_TIMESTAMP(),
	200))
	WHERE details like '%FORBIDDEN%'
	AND DATE_TRUNC('DAY', event_timestamp) = '2022-08-10'
	ORDER BY event_timestamp DESC;

vvgsrk / azure_ad_scim_integration_test_connection.log

Created August 10, 2022 19:21

	Testing connection to Data Platform - Snowflake
	You appear to have entered invalid credentials. Please confirm you are using the correct information for an administrative account.

	Error code: SystemForCrossDomainIdentityManagementCredentialValidationUnavailable
	Details: We received this unexpected response from your application:

	Received response from Web resource.
	Resource: https://XXXXXXXXX.eu-central-1.snowflakecomputing.com/scim/v2/Groups?excludedAttributes=members&filter=displayName+eq+"AzureAD_Test-7bfddc58-2c65-44a8-b556-3fd6bf9d7763"
	Operation: GET
	Response Status Code: Forbidden

vvgsrk / System_Function_To_Generate_Token.sql

Created August 10, 2022 18:55

System Function To Generate Token

SELECT SYSTEM$GENERATE_SCIM_ACCESS_TOKEN('AAD_PROVISIONING');

vvgsrk / Setup_Azure_SCIM_Integration_with_Snowflake.sql

Created August 10, 2022 18:50

Setup Azure SCIM Integration with Snowflake

	USE ROLE accountadmin;

	CREATE ROLE IF NOT EXISTS aad_provisioner;

	GRANT CREATE USER ON ACCOUNT TO ROLE aad_provisioner;
	GRANT CREATE ROLE ON ACCOUNT TO ROLE aad_provisioner;

	GRANT ROLE aad_provisioner TO ROLE accountadmin;

	CREATE OR REPLACE SECURITY INTEGRATION aad_provisioning

vvgsrk / check_list_of_valid_distributions.ps1

Created July 5, 2022 07:15

Check list of valid distributions that can be installed

wsl --list --online

vvgsrk / montior_replication_and_related_objects.sql

Last active September 15, 2022 06:27

SQL statements to monitor or troubleshoot replication and related objects

	-- Lists the streams for which you have access privileges
	SHOW STREAMS LIKE 'dp_logs_stream' IN ACCOUNT;

	-- Describes the columns in a stream
	DESCRIBE STREAM amadeus_dynamic_pricing.dynamic_pricing.dp_logs_stream;

	-- Lists the stored procedures that you have privileges to access
	SHOW PROCEDURES LIKE 'INSERT_DP_LOGS' IN ACCOUNT;

	-- Lists the tasks for which you have access privileges