vvgsrk

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

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

USE ROLE USERADMIN;

GRANT OWNERSHIP ON USER USER_ID TO ROLE AAD_PROVISIONER;

vvgsrk

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

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

SELECT SYSTEM$GENERATE_SCIM_ACCESS_TOKEN('AAD_PROVISIONING');

vvgsrk

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

wsl --list --online

vvgsrk

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

vvgsrk

-- A separate new database
CREATE DATABASE refresh_secondary_database;

-- Create a new schema
CREATE SCHEMA refresh_secondary_database.amadeus_dynamic_pricing;

-- Task that refreshes the secondary database on a schedule
CREATE OR REPLACE TASK refresh_secondary_database.amadeus_dynamic_pricing.dynamic_pricing_data_refresh_task
  WAREHOUSE = WH01_XS
  SCHEDULE = 'USING CRON 0 3 * * * UTC'