SQL Transformations

transformations allow logic to be executed against a target connection based on a scheduled frequency or triggered event of new data on tables updated via datalakehouse io (dlh io) add a transformation pre sql transformations sql transformations add a transformation click on transformation from the header menu click on new transformation or add a transformation to show the transformation creation form update the form and determine what options to select overview adding a new transformation, be sure to complete all the required details pre sql transformations pre sql transformations are unique to dlh io where provide the ability to reference the in stream data (data streaming per each synchronization frequency) against the existing raw data landing zone this is a perfect use case for conceptual ideas such as refunds, chargebacks, employee punches, etc this option is available by selecting the transformation type in the transformation area see also employee pay summary docid\ paewz2e nk0z0okpzgxax for example of using pre sql logic sql transformations sql transformations are unique to dlh io where provide the ability to reference the in stream data (data streaming per each synchronization frequency) against the existing raw data landing zone this type of transformations runs as the step in the sync bridge execution while a tool such as dbt cloud, dataform or coalesce io can do these light transformations, datalakehouse io can as well in the example below, a sync bridge (data pipelines) docid\ e00kk6kz8klbthcdmpqpm is syncing a google sheet into snowflake in order to control the column data types in snowflake, a transformation is created to drop the contacts new table at the end of each sync bridge run and then a ctas is executed to create the contacts new table with the desired column data types the text of the script in the above image is below for easy reference \ \ drop the table \ drop table dlh demo sandbox peopleextract google sheets contacts new; \ \ create the table with the desired data types \ create table dlh demo sandbox peopleextract google sheets contacts new as select dob date as dob, first name varchar(25) as first name, middle name varchar(15) as middle name, last name varchar(50) as last name, prefix varchar(10) as prefix, suffix varchar(10) as suffix, title varchar(75) as title, details varchar(16777216) as details, company varchar(50) as company, company id number as company id, owned by varchar(50) as owned by, street varchar(100) as street, city varchar(30) as city, state varchar(2) as state, postal code varchar(10) as postal code, country varchar(50) as country, row md5 varchar(50) as row md5, dlh is deleted boolean as dlh is deleted, dlh sync ts timestamp tz(9) as dlh sync ts, dlh start ts timestamp tz(9) as dlh start ts, dlh finish ts timestamp tz(9) as dlh finish ts, dlh is active boolean as dlh is active from dlh demo sandbox peopleextract google sheets contacts;