topic Lakehouse backup table in Pipelines

Lakehouse backup table

Soobramoney — Thu, 28 Nov 2024 11:10:40 GMT

Hi,

I have a data pipeline that loads data into a staging table with the destination setting set to "Overwrite." This seems to create a _backup_XXXXXXXXXX table each time I load data. Prior to using "Overwrite," I had it set to "Overwrite Schema," which did not create backup tables. However, this setting introduced datatype issues on the table, so I switched to "Overwrite."

Do you have any advice on how to prevent these backup tables from being created, or should I create a process to delete them?

Best regards,

Re: Lakehouse backup table

Anonymous — Fri, 29 Nov 2024 02:33:20 GMT

Hi Soobramoney

According to the official documentation, you can copy previous Version tables from Lakehouse by specifying version in the copy activity source. Then, I suggest you consider custom code in the pipeline to remove these tables.

https://learn.microsoft.com/en-us/fabric/data-factory/connector-lakehouse-copy-activity#destination

Specific steps you can consider:

First, get a list of all the tables that need to be deleted.

Write a script to delete these tables. You can write code in a language you are familiar with, here is an example using Python:

import pyodbc # Connect to database conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_database;UID=your_username;PWD=your_password') cursor = conn.cursor() # Gets a list of tables to delete tables_to_delete = ["backup_table1", "backup_table2", "backup_table3"] # Delete list for table in tables_to_delete: cursor.execute(f"DROP TABLE IF EXISTS {table}") print(f"Deleted table: {table}") conn.commit() cursor.close() conn.close()

Integrate this script into your data pipeline. You can run this script after the data is loaded to ensure that the backup table is deleted.

I hope you found this idea helpful.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Lakehouse backup table

Soobramoney — Wed, 22 Jan 2025 07:56:24 GMT

It seems you cannot prevent the backup tables when you are using the Overwrite setting. I had to create a clean job that runs once a day.

import logging logging.basicConfig(level=logging.INFO) logger = logging.getLogger("BackupTableCleanup") backup_tables = spark.sql("SHOW TABLES") drop_backup_tables = backup_tables.filter( (backup_tables["tableName"].like('%_backup_%')) & ) drop_statements = drop_backup_tables.rdd.map(lambda row: f"DROP TABLE {row['tableName']};").collect() for statement in drop_statements: try: logger.info(f"Executing: {statement}") print(f"Executing: {statement}") spark.sql(statement) logger.info(f"Successfully executed: {statement}") except Exception as e: logger.error(f"Failed to execute: {statement}. Error: {e}")

Re: Lakehouse backup table

ToddChitt — Tue, 14 Jul 2026 21:59:06 GMT

I know this post is over a year old, but is this really an acceptable solution? Seems more like "Defect support system" (meaning we have to add extra coding and stuff to 'fix' stuff that Microsoft won't address.