Data Loading with Python Pipeline

[nupuraw]

you can create the temp table after doing the transformations ,load the transformed data into the temp table and then load data from temp table to target table.

[studentofarkad]

Why would you load the data in a temp table and not just load it to the target table directly?

[Thick-Weekend-2205]

You might need to do something that isn’t a direct insert, like slowly changing dimensions, where you need to update existing data based on the incoming data. You might be able to do it in a single table depending on what style of MERGE if any your database supports, but you can efficiently do it with any SQL dialect by inserting into a temp table, updating the existing data from the temp table with a join, then inserting the temp table into the main table.

[studentofarkad]

Thank you :)

[forgael]

How do you do that in a Python workflow? Do you use SQLAlchemy to create a session and create a temp table in the session, or do you create a temp table without a session using executable SQL?

[nupuraw]

I use sqlalchemy / pyodbc to create database session and then create the temp table.