如何在Snowflake中筛选含30天前数据的指定模式表

Step 1: Get List of Eligible Tables #

To filter tables matching DIM_NAMES_% that contain rows where time_id is older than 30 days, first generate check queries for each table:

SELECT CONCAT(
    'SELECT ''', table_name, ''' AS eligible_table WHERE EXISTS (SELECT 1 FROM ', table_name, ' WHERE time_id <= DATEADD(day, -30, CURRENT_DATE())) UNION ALL'
) AS check_sql
FROM INFORMATION_SCHEMA.tables 
WHERE TABLE_NAME LIKE 'DIM_NAMES_%';

Execute the generated SQL (remove the trailing UNION ALL from the output) to get your list of qualifying tables.

Step 2: Generate Migrate & Cleanup SQL #

Replace your_target_table with your destination table name, then run this query using your eligible tables list:

SELECT CONCAT(
    '-- Migrate old rows from ', table_name, '\n',
    'INSERT INTO your_target_table SELECT * FROM ', table_name, ' WHERE time_id <= DATEADD(day, -30, CURRENT_DATE());\n',
    'DELETE FROM ', table_name, ' WHERE time_id <= DATEADD(day, -30, CURRENT_DATE());\n'
) AS migrate_sql
FROM (
    -- Insert your eligible tables here
    SELECT 'DIM_NAMES_TABLE1' AS table_name UNION ALL
    SELECT 'DIM_NAMES_TABLE2' AS table_name
);

Copy the output and execute the statements. For safety, wrap them in a transaction:

BEGIN TRANSACTION;
-- Paste migrate/delete statements here
COMMIT;

Key Notes #

• Ensure the target table matches the schema of your source tables, or explicitly specify columns in the INSERT statement to avoid errors.
• For full automation, wrap this logic in a Snowflake stored procedure to loop through tables and execute commands programmatically.

内容的提问来源于stack exchange，提问作者Sara