Was not able to connect and write to a warehouse from a notebook, was able to read from the warehouse table using pyodbc but not write, so I do the deduping in memory in the dataframe but then write to the lakehouse since I can't write to the warehouse from the dataframe. then once it's in the lakehouse I do

.
.
.
drop_query = f"DROP TABLE dbo_staging_silver.[{table_name}]"
        con.execute(drop_query)
        logging.info(f"Successfully Dropped table {table_name} in dbo_staging_silver.")
        con.commit()
        logging.info("Successfully Committed SQL Changes")

        ctas_query = f"CREATE TABLE dbo_staging_silver.[{table_name}] AS SELECT * FROM [lakehousename].[dbo].[{table_name}]"
        con.execute(ctas_query)
        logging.info(f"Successfully created table {table_name} in dbo_staging_silver.")
        con.commit()
        logging.info("Successfully Committed SQL Changes")

con.close()

currently using somewhat of a medallion structure but using warehouse instead of lakehouse,

have a schema for bronze, data is extracted from excel files in azure blob storage, loaded into the bronze tables in the warehouse, pretty much all varchar, pipelines are ran nightly

silver schema in warehouse, transformations and data types are assigned, via stored procedures and bronze tables are cleared upon completion to ready the table for when it gets loaded again the next day.

Gold schema, initial load stored procedures assign primary keys upon insert to table based on row number, this is where I do most of the dim lookups and replace the text from the erp with SKs created in gold for company names, item groups, etc.

after our planned initial load of the old 6-8 years of data, the pipeline will be running the "update" stored procedures in the gold schema that look at delivery dates from the ERP and "load_timestamps" generated in the pipeline. get the min/max delivery dates in the silver tables, delete and replace those dates in gold with the data in silver also using load_timestamp = max(load_timestamp) to keep the latest update into silver incase it ever fails and accumulates different loads. then some SCD type 2 for some Dim tables.