How to properly refresh Lakehouse SQL endpoint?

Instead of hardcoding table names, query the catalog for all tables in a given schema and loop through them.

from pyspark.sql import SparkSession

# create Spark session
spark = SparkSession.builder \
    .appName("Refresh Lakehouse SQL Endpoint") \
    .getOrCreate()

# define schema name
schema_name = "dbo"

# get all tables in schema
tables_df = spark.sql(f"SHOW TABLES IN {schema_name}")
tables = [row.tableName for row in tables_df.collect()]

# refresh each table
for table in tables:
    print(f"Refreshing table {schema_name}.{table} ...")
    spark.sql(f"REFRESH TABLE {schema_name}.{table}")

print(f"Metadata refresh completed for schema: {schema_name}")

If you are running this right after data ingestion, you need to wait until write jobs finish.
If ingestion is done within the same notebook, make sure to call spark.catalog.clearCache() to avoid stale metadata. But if its asynchronous consider implementing a checkpoint/audit table to track job completion and trigger refresh only after proper validation thtat the data has been landed. 

Pls note that the REFRESH TABLE refreshes only the metadata, it does not reload the data unless there are structural changes. You can also force query compilation reset if you want (spark.catalog.refreshTable) but for heavy pipelines, limit the refresh to only the tables that has been changed.

• Ingest data into Lakehouse.
• Validate ingestion completion (checkpoint or audit).
• Run REFRESH TABLE dynamically for all tables in schema
• Validate row counts.
• Trigger stored procedure executions.

Please 'Kudos' and 'Accept as Solution' if this answered your query.