query to find Size of each FABRIC Warehouse table

[AJAJ]

Hi,

Currently on Azure SQL. At the end of ETL process, all SQL table sizes are recorded into a table by using a query.

When someone moves into Fabric warehouse or lakehouse, how to capture sizes of all warehouse tables or if lakehouse, file sizes using a query? 

Appreciate your thoughts.

[v-veshwara-msft]

Hi @AJAJ ,

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft · Accepted solution]

Hi @AJAJ ,

Thanks for reaching out to Microsoft Fabric Community.

In Microsoft Fabric, the approach depends on whether the data is in a Warehouse or a Lakehouse.

For Fabric Warehouse, you can run the query below.

SELECT
    t.name AS table_name,
    SUM(p.rows) AS row_count,
    CAST(SUM(a.total_pages) * 8 AS DECIMAL(18,2)) AS size_in_KB,
    CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(18,2)) AS size_in_MB,
    CAST(SUM(a.total_pages) * 8.0 / 1024 / 1024 AS DECIMAL(18,2)) AS size_in_GB
FROM sys.tables t
JOIN sys.indexes i 
    ON t.object_id = i.object_id
JOIN sys.partitions p 
    ON i.object_id = p.object_id 
   AND i.index_id = p.index_id
JOIN sys.allocation_units a 
    ON p.partition_id = a.container_id
WHERE t.name = 'SalesSample'
  AND SCHEMA_NAME(t.schema_id) = 'dbo'
GROUP BY t.name;

 Replace table name and schema with your table and schema names.

For Fabric Lakehouse, table sizes are stored as Delta metadata and can be retrieved using Spark SQL. From a notebook attached to the Lakehouse, run the following in a SQL cell:

DESCRIBE DETAIL SalesSample;

This returns sizeInBytes, row count, and other table metadata. The value can be converted to KB, MB, or GB and persisted as part of ETL logging if required.

For Lakehouse files (for example files under the Files area), size information can be viewed directly from the Lakehouse UI, where file and folder sizes are shown. This is currently the supported way to check file level sizes without using Spark.

Similar threads and useful resources: 

Solved: Re: Size table in workspace - Microsoft Fabric Community

How to check the size of data in lakehouse - Microsoft Fabric Community

Getting the size of OneLake data items or folders | Microsoft Fabric Blog | Microsoft Fabric

Hope this helps. Please reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @AJAJ ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.

[AJAJ]

Hi,

Currently on Azure SQL. At the end of ETL process, all SQL table sizes are recorded into a table by using a query.

When someone moves into Fabric lakehouse, how to capture sizes of ALL lakehouse file sizes using a SQL query (preferred) if not / python script in notebook ? 

Appreciate your thoughts.