Dataflow staging lakehouse table retention

Hi shaunmw 

Since you don't need to keep a staging table, you have several options:

Manual Delete: You can manually delete these tables when they are not in use. This is a straightforward approach, but can be time-consuming if there are many tables.

Automatic maintenance: You can set up an automated process to clean these tables. Since the built-in maintenance script doesn't work as expected, you might consider creating a custom script. For example:

from datetime import datetime, timedelta
from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("CleanupStagingTables").getOrCreate()

unused_threshold_days = 30
unused_threshold_date = datetime.now() - timedelta(days=unused_threshold_days)

all_tables = spark.catalog.listTables("your_database_name")

for table in all_tables:
    table_name = table.name
    table_metadata = spark.sql(f"DESCRIBE DETAIL your_database_name.{table_name}").collect()
    last_access_time = table_metadata['lastAccessTime']

    if last_access_time and datetime.strptime(last_access_time, '%Y-%m-%d %H:%M:%S') < unused_threshold_date:
        spark.sql(f"DROP TABLE your_database_name.{table_name}")
        print(f"Deleted table: {table_name}")

spark.stop()

The sudden increase in CU activity in temporary Lakehouse can be caused by a number of factors, you can try:

Use Azure Monitor to track resource usage in the staging Lakehouse. Look for any unusual patterns or spikes in activity.

Azure Monitor overview - Azure Monitor | Microsoft Learn

Look at the log of the data flow to see if there were any changes or anomalies during the peak of activity.

Since your data flow calls the REST API, check whether the number of API calls has increased or the API response time has changed. This may affect CU usage.

Even if you don't make any changes to your data flow or Azure SQL environment, consider external factors that may affect performance, such as network issues or changes in data volumes.

You mentioned that you submitted the support ticket, and I look forward to sharing with you if there are any answers.

Regards,

Nono Chen

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