Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
I am currently working on a historization SQL Recipe in which I am trying to insert data from one table to another.
The source table has a different connection that the destination table, but both connections have the same database.
My question: If I want to create the historization table, to have the source table in another database(ODS) than the destination (DWH), to insert only the new rows, what is the best practice?
Is a temporary table and use of the same database, then a sync recipe to get data from ODS database to DWH database the best way?
(Topic title edited by moderator to be more descriptive. Original title "SQL Recipe")
Operating system used: Virtual Machine
It really depends on many factors:
Sorry for the confusion!
I need to create the historization table,I have the source table in another server(ODS) than the destination server (DWH), I need to insert only the new rows.
I am processing data in ODS from a SQL table, but I am trying to get to DWH, and never delete data in DWH if errors as it is used for reporting in Power BI.
In the attached picture I have the process, so I will now need to have 2 tables in ODS, and will have to use a Prepare recipe to get data from ODS to DWH.
I would like to know what is the best practice for this process!
Thank you for the assistance!
ODS represents a database in server 1 in Microsoft SQL Server
DWH represent a database in server 2 in Microsoft SQL Server
The Historization is made by SQL Recipe script, which inserts if not exists, and update cases.
My plan is to use a Prepare Recipe to get ODS data to a temporary table in DWH, with no modifications, then create historization (SQL Recipe script) from the temp table(DWH) to destination DWH(final table, also used for reporting)
- DWH destination should not have data removed
- ODS table will have new rows to insert, received every day by an excel
Let me know if more information should be provided!
Why can't you not use Linked Servers in SQL Server and do all the ETL in SQL Server? See this post:
Doesn't seem like using Dataiku for this ETL is the best idea. Maybe if your DWH was on a Cloud DWH engine like GCP BigQuery or Snowflake it might make sense to run it via Dataiku.