AltaSQL Bulk Updating Expressions Across All View Definitions

Jeffrey Jacobs Jeffrey Jacobs

Jeffrey Jacobs

CTO/Founder of AltaSQL.io

发布日期: 2025年2月23日
+ 关注

Bulk Updating Expressions Across All View Definitions Using AltaSQL

AltaSQL allows users to efficiently apply changes to expressions across multiple View Definitions using a metadata-driven approach. By leveraging AltaSQL Tags, View Transformations, and bulk script execution, updates can be automated without manually modifying SQL statements.

1. Identifying All View Definitions Using an Expression

To apply a change across multiple views, first, identify all View Definitions that use the target expression. 

SELECT VIEW_NAME
FROM SBLDR_TEMPLATE_REPO
WHERE VIEW_COLUMN = '<column_name>'
AND SOURCE_COLUMN_DATA_TYPE = 'EXPR';

This retrieves the list of View Definitions using a specific expression.

2. Generating and Executing a Bulk Update Script

Instead of concatenating function calls into a string, you can directly execute the bulk update query using an outer SELECT that applies SBLD_ALTER_VIEW_COLUMN_EXPRESSION to each result. 

SELECT ASQL_DB.ASDF_SCHEMA.SBLD_ALTER_VIEW_COLUMN_EXPRESSION(
    REPO_VIEW_NAME => VIEW_NAME,
    VIEW_COLUMN => '<column_name>',
    NEW_EXPRESSION => '<new_expression>', -- See below for using AltaSQL Tag
    NEW_EXPR_COMMENT => 'Updated for all View Definitions'
)
FROM 
(SELECT VIEW_NAME FROM IDENTIFIER($SBLDR_META_REPOSITORY)
WHERE VIEW_COLUMN = '<column_name>'
AND SOURCE_COLUMN_DATA_TYPE = 'EXPR');

How It Works

? The outer SELECT directly executes SBLD_ALTER_VIEW_COLUMN_EXPRESSION on each View Definition, eliminating the need to manually construct and execute dynamic SQL. ? No need for LISTAGG() or procedural execution—updates are applied in one step. ? Any View Definition using the expression is instantly updated.

3. Storing Expressions as AltaSQL Tags for Future Updates

To ensure consistency and enable future updates without modifying SQL queries manually, store expressions as AltaSQL Tags (SBLDR_TAG).

Storing an Expression in an AltaSQL Tag

SELECT ASQL_DB.ASDF_SCHEMA.SBLD_ADD_TAG(
    REPO_VIEW_NAME => '<view_name>',
    SBLDR_TAG => 'TOTAL_REVENUE_EXPRESSION',
    SBLDR_TAG_VALUE => '"price" * "quantity"',
    TAG_CLASS => 'EXPRESSION',
    TAG_COMMENT => 'Revenue calculation for all reports',
    COPY_CONTROL => 'COPY'
);

Applying a Stored Expression

SELECT ASQL_DB.ASDF_SCHEMA.SBLD_ALTER_VIEW_COLUMN_EXPRESSION(
    REPO_VIEW_NAME => '<view_name>',
    VIEW_COLUMN => 'total_revenue',
    NEW_EXPRESSION => SBLD_GET_TAG_VALUE(
    REPO_VIEW_NAME =>'<view_name_with_tag>', 
   TAG =>'TOTAL_REVENUE_EXPRESSION',
   TAG-_CLASS => 'EXPRESSION'),
    NEW_EXPR_COMMENT => 'Updated using stored tag')
);

Now, when the expression needs to be updated, only the Tag value needs to be modified, and all View Definitions referencing it will automatically use the new expression.

4. Automating Expression Updates in CI/CD Pipelines

AltaSQL allows automated deployment of expression updates in CI/CD processes:

  • Run bulk updates as part of scheduled metadata updates.
  • Maintain a version history of expressions using Tags.
  • Ensure consistency across reports and queries.

Checking for Updated Expressions

To verify which expressions have been applied: 

SELECT VIEW_NAME, VIEW_COLUMN, NEW_EXPRESSION
FROM IDENTIFIER($SBLDR_META_REPOSITORY)
WHERE SOURCE_COLUMN_DATA_TYPE = 'EXPR';

Conclusion

? Automatically updates expressions across all View Definitions without dynamic SQL. ? Direct execution using an outer SELECT simplifies bulk updates. ? Metadata-driven approach eliminates manual query modifications. ? AltaSQL Tags enable easy, future-proof expression updates. ? Ideal for CI/CD integration and automated SQL governance.

By leveraging AltaSQL Tags and direct bulk execution, expression updates become faster, easier, and more maintainable, ensuring consistency across all dependent View Definitions.

#AltaSQL #Snowflake #SQLAutomation #DataProductivity #MetadataManagement

要查看或添加评论，请登录

Jeffrey Jacobs的更多文章