Skip to main content

How to Delete Internal RelationalAI Stage Data

Genevieve LaLonde
Updated

What is Stage Data?

This includes RelationalAI stages, Snowflake stages, stages for other native apps. It also includes your own stage tables you have created for loading data from files into Snowflake tables and unloading data from tables into files. 

For example a named stage can be created in Snowflake with this statement:

CREATE STAGE MY_STAGE;

 

How does RAI use Stages?

A large portion of RelationalAI internal data gets stored in the stages. This can include the index on your source table that the stream creates. It also includes materialized data for cached query results. Additionally importing and exporting anything to/from RAI is often performed via converting to CSV table first, and the CSV table is stored as a stage. 

 

Check Stage Data in the UI

For an estimate on the internal data storage, check the Stages data in your account. Most of the RAI internal data gets stored here, however it also includes staged data from other apps in your Snowflake account, as well as any user/named/external stages.

 

Check the amount of staged data in the UI by going to:

Admin > Cost Management > Consumption > Usage Type: Storage

 

See an example below from the RAI Support account:

This page shows your storage usage over time, for each of your databases. It also shows internal data usage in the RelationalAI and Snowflake databases. Most importantly for us here, it shows how much data is used for Stages.

 

Check Stage Data in SQL

Query the Snowflake View on stage_storage_usage_history for the amount of staged data. 

select 
    usage_date,
    CASE
        WHEN average_stage_bytes >= POWER(1024,4) THEN ROUND(average_stage_bytes / POWER(1024,4), 1) || ' TB'
        WHEN average_stage_bytes >= POWER(1024,3) THEN ROUND(average_stage_bytes / POWER(1024,3), 1) || ' GB'
        WHEN average_stage_bytes >= POWER(1024,2) THEN ROUND(average_stage_bytes / POWER(1024,2), 1) || ' MB'
        WHEN average_stage_bytes >= POWER(1024,1) THEN ROUND(average_stage_bytes / POWER(1024,1), 1) || ' KB'
        ELSE average_stage_bytes || 'B' END as stage_size
from snowflake.account_usage.stage_storage_usage_history
ORDER  BY usage_date DESC
limit 30;

This query shows the date and the amount of stored stage data on that day. Note that the average_stage_bytes is in bytes, so this query performs some math to show this as B/KB/GB/TB as appropriate, for ease of reading. 

Example results:

 

Stage Data Measurement Frequency

For both of these methods, the amount of stage data is measured by Snowflake once per day. For this reason, changes in data size may not be reflected in Snowflake for 1-2 days. The timing depends on when the data was cleared out, and when Snowflake measured the storage volume.

 

What Can Cause High Internal Storage?

Long Running Transactions 

In the case of RelationalAI stage data, high storage is most often caused by a runaway transaction. As a transaction runs, it materializes partial results by creating views. These views are then used to return the final results. Next, we cache these views and reuse them to speed up future similar queries. 

However, if there is incorrect logic in a query, you may end up with a cross product or with unexpected recursion. While these can be appropriate components of some queries, they can make suddenly make a query a lot more expensive. 

Resolve by Managing Transactions

In that case, the query should be cancelled and the partial results should be cleared out since they won't be useful to future runs. See Cleaning Up Internal Data below for more information. 

To check for a long running transaction, cancel it, and prevent recurrence, see instructions in our article: Troubleshooting Transactions

 

Frequent Stream Recreation

If you have very large streams, and they are getting recreated frequently (daily), then this can cause high data volume due to the high rate of turnover. This is because the index for the previous stream goes through the regular process where it must wait a 2 day data retention grace period before it is ultimately removed. 

Streams are recreated if large changes are made to the source tables in Snowflake. For example you might refresh your data every day, by dropping and recreating the table. 

Resolve by Converting Workflow to Updates Instead of Replacement

For cases like this, consider whether the data could be kept up to date by performing a smaller UPDATE or INSERT statements instead of DROP/CREATE/REPLACE type of statements. When you perform writes to the same table, these are automatically ingested by the existing stream. This is faster for you, and writes less data in the app since the existing index can be updated instead of recreated. 

 

Cleaning Up Internal Data

What is a blob?

A blob is an internal storage file. In the case of a lot of data stored in the RelationalAI Stage, blobs are usually from materializing and caching query results. 

 

What is the BlobGC?

This is the RelationalAI garbage collector for files, aka blobs. Typically it runs as a background process on inactive engines. For example: engines that have completed all transactions and are awaiting their auto suspension timeout. Additionally, if no engines were inactive for long enough to complete a run of the blobGC on the account for one week, then an internal engine is spun up and performs one run.

 

What occurs in a BlobGC run?

  1. Storage files are identified and listed. 
  2. File state is determined to be active (associated with databases that were recently used) or inactive (likely will not be reused for any queries). 
  3. Files that have been inactive past their caching grace period are deleted. 

Usually it takes 3 runs for a file to get deleted, since a file can only change state once per run. On the first run, it is identified. The second run changes its state to inactive if appropriate. After the two day grace period has passed, the next blobGC run will delete the file. 

For this reason, a spike in data storage can persist for several days afterward, even if you already cancelled the transaction. 

 

What is the File Grace Period?

A file is not immediately deleted when it is deemed to be inactive. Each file is given a grace period of 2 days. In rare cases a file could be reused during that time, in which it would rotate back to an active state. If the 2 days passes and it is not used, then it will be deleted during the next run of the blobGC. 

 

Increasing the BlobGC Frequency

Background Process Frequency

Every 17 hours, the blobGC will try to run as a background process. However, it can only run in the background of inactive engines that are running. If none are available, it will wait until that is the case. This frequency cannot be adjusted.

Dedicated Internal Engine Frequency

If engines are too active for the blobGC to run on them as a background process, then once a week it will spin up an internal engine and use it for a blobGC run. This engine is in a dedicated compute pool on which customers do not pay the RAI premium. After it has completed 1 run, this internal engine is automatically deleted.

In some apps that have a high rate of data writing, you might need a greater frequency than once per week. In that case you can increase how often the blobGC runs on the dedicated internal engine. 

The command below would more than double the frequency by setting it to every 3 days (in seconds):

CALL relationalai.api.set_blobgc_background_trigger_threshold(259200);

 

Manual Intervention

If the blobGC still cannot keep up with the rate of data generation, or if there is a very large backlog of files that need to be deleted, reach out to us by emailing us at support@relational.ai for stronger intervention methods. Let us know your account, and what is your expected amount of data storage. 

 

Related to

Related articles