Data Governance & Script Generation Framework

Designed and delivered an end-to-end framework for generating and executing Snowflake and Databricks management scripts from a centralised template repository — eliminating hardcoded SQL scripts, reducing human error, and enabling non-privileged users to perform administrative operations safely.

Snowflake Implementation

• Designed the SCRIPT_TEMPLATE table in ADMINISTRATION.UTILS as the single source of truth for all script templates, storing SCRIPT_TYPE, ACCESS_TYPE, PARAMETER_NAMES, and parameterised TEMPLATE bodies.
• Authored the core stored procedure GENERATE_SCRIPT_FROM_TEMPLATE (VARCHAR returns) supporting three invocation modes: standalone in the Snowflake UI, inside Snowflake loops, embedded in parent procedures, and via external tools (dbt, SnowSQL).
• Implemented a self-documenting help system — passing empty strings to any argument returns contextual usage information, access type listings, or parameter schemas directly from the template table.
• Built procedures based on similar principles: CREATE_SCHEMAS, CHANGE_OWNERSHIP, GENERATE_TECHNICAL_ROLE, CREATE_SECURITY, as well as many others.
• As an example of a very complex procedure I built the procedure CREATE_STRUCTURES_WITH_ACCESSES — combining various other procs together. It executes in the background the following procs: CREATE_SCHEMAS, CHANGE_OWNERSHIP, GENERATE_TECHNICAL_ROLE and CREATE_SECURITY, and creates a DATABASE structure with SCHEMAS protected by appropriate access rights.
• Established a consistent error-handling contract: all procedures return a VARCHAR string; errors surface as strings prefixed with !Error, enabling clean handling in loops and parent procedures.

Databricks / Azure Extension

• Architected the equivalent Python-based framework for Databricks — translating the Snowflake procedural pattern into an object-oriented TemplateGenerator class backed by a Delta table in Unity Catalog (administration_se.utils.script_template).
• Stored the class in a Unity Catalog Volume, making it importable across notebooks, dbt Python models, Databricks Jobs, and external REST callers without code duplication.
• Replicated the three-argument help-mode interface identically — generate_script('','',''), generate_script('TYPE','',''), generate_script('TYPE','ACCESS','') — preserving the same developer experience across both platforms.
• Implemented the privilege-escalation layer using a Databricks Job configured with run_as: Service Principal — mirroring Snowflake's EXECUTE AS OWNER so that human callers with no GRANT or CREATE USER rights can trigger elevated operations safely.
• Implemented child methods generate_grant_role() and generate_user() as thin wrappers over the core method, maintaining the same single-responsibility design as the Snowflake procedures.
• Defined Unity Catalog RBAC grants — separating read access (SELECT on template table, READ VOLUME) for regular engineers from MANAGE GRANTS and CREATE USER rights held exclusively by the Service Principal.

Impact & Outcomes

• Reduced time to provision new users and roles from manual multi-step processes to a single parameterised procedure call.
• Eliminated direct privilege exposure — no human user holds GRANT or CREATE USER rights on either platform.
• Framework adopted across dbt pipelines, ADF orchestration flows, and Databricks notebook workflows with zero code changes to the core class.
• Template table acts as living documentation — all script patterns, their parameters, and descriptions are queryable directly from SQL or the help system.