Data Cleansing View in Snowflake

For part of one of my free ETLs I am using Zapps to transfer e-mails from Google Sheets and then Keboola to transfer the sheets into my Snowflake database. I am familiar with string searches and cleansing in Oracle and using python but have not had the chance to do this in Snowflake. I wanted to give it a go as a proof of concept if nothing else. There were some difference in functions between Oracle and Snowflake, no INSTR and using POSITION instead and some difference in working with dates / timestamps but overall it was very similar. 

The code below is what I ended up using: 

	CREATE OR REPLACE VIEW V_ETL_LOGS AS
	SELECT
	TO_TIMESTAMP_NTZ(
	substr(
	"Date",
	position(',', "Date") + 1,
	position('+', "Date") + 1 - position(',', "Date") -2
	),
	'DD MON YYYY HH24:MI:SS'
	) datetime_log,
	trim(
	REPLACE(
	REPLACE(
	REPLACE("Subject", '[KBC] Garymanley orchestrator'),
	' succeeded'
	),
	' error'
	)
	) ETL_GROUP,
	case
	when "Subject" like '%succeeded%' then 'SUCCESSFUL'
	when "Subject" like '%error%' then 'EROOR'
	else 'Unknown'
	end as ETL_STATUS,
	TO_TIMESTAMP_NTZ(
	substr(
	"EmailText",
	position('Start time', "EmailText") + 12,
	16
	),
	'YYYY-MM-DD HH24:MI'
	) start_time,
	TO_TIMESTAMP_NTZ(
	substr(
	"EmailText",
	position('End time', "EmailText") + 10,
	16
	),
	'YYYY-MM-DD HH24:MI'
	) end_time,
	substr(
	"EmailText",
	position('succeeded in', "EmailText") + 12,
	13
	) run_time
	FROM
	KEBOOLA_7127.WORKSPACE_15661914.ETL_LOGS

view raw snowflake_v_etl_logs.sql hosted with ❤ by GitHub

I think want to use this to create some overview graphics to allow me to track the success or failure of my ETLs. Assuming the aspects of Retool remain free you can see how much ETL is going on this link. 

In case things aren't working, here is a table of the output I am producing.