Forum Discussion
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.
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.
How can I check if table is being refrshed successfully after running the refresh table command, I have seen data not reflecting in the lakehouse even if I run the refrshe table command for that table, is there any time delay between this? How can I keep running the refresh until a particular table has a date, for example checking count of records and refreshing again if count is 0.
Also we dont have any notebooks for data load, we use ADF executions after lakehouse data populates, we need to add some interim step to run refresh of all the tables corresponding to a schema or lets say a paritcular query that gives table output and keep checking the counts and run table refresh again if any table has still 0 count?
