The problem I’m having
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
The context of why I’m trying to do this
What I’ve already tried
I have not tried anything as yet. I cannot figure out how to do this
Some example code or error messages
/* 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