I am a newbie in dbt and I am trying to use the existing database infrastructure to use dbt to replace existing process. The best practices is to do all writes ( INSERT/UPDATE/DELETES) via a write view in a schema/database which points to the target table in separate schema.
There is additional requirement that I may need to keep the existing table as is with existing “control columns” , that is effective_from_date , effective_to_date , job_id , run_id… etc. and processing pattern behavior ( model materialisation )
Is there a way of achieving this , I suspect that I need to go down the bespoke/custom materialisation
I have not tried anything as yet. I cannot figure out how to do this
/* Target table */ CREATE TABLE DWT01T_STORES.LOC ( STORE_NO VARCHAR(100) NOT NULL ,STORE_NAME VARCHAR(100) NOT NULL /* Control columns */ ,START_DATE DATE NOT NULL ,END_DATE DATE NOT NULL ,JOB_ID ... ) INDEX(STORE_NO) ; /* Write view */ /* All writes are via this view - user does not have access directly to table */ REPLACE VIEW DWT01V_STORES.LOC AS SELECT * /* usually columns explit */ FROM DWT01T_STORES.LOC