Turn one-off Snowflake SQL into reusable templates by abstracting constants into named parameters, storing the query in a version-controlled repository (e.g., a Galaxy Collection), and adding clear defaults and documentation so any teammate can slot in values and run it instantly.
Hard-coded filters slow teams down. By turning a solid one-off query into a template with parameters, you ① eliminate copy-paste errors, ② enable non-experts to self-serve, and ③ keep a single source of truth that’s easy to audit.
Scan the WHERE and LIMIT clauses for values that change by run (e.g., customer_id
, dates, region). Replace each with a clear token such as {{customer_id}}
or a session variable like :customer_id
.
Parameter defaults ({{start_date="2024-01-01"}}
) let teammates test instantly and understand the data shape.
A final SELECT that references earlier CTEs keeps the template tidy and makes parameter placement obvious.
In Galaxy, save the SQL to a Collection and flag it as Endorsed. Galaxy surfaces the template in AI autocomplete, injects inline docs, and lets viewers supply parameter values from a sidebar-no edits required.
Sync Galaxy with GitHub or rely on its built-in history so changes are reviewed and rolled back quickly.
Session variables (SET customer_id = 'ABC';
) and table functions let you pass arguments natively. For frequent jobs, convert the template into a CREATE OR REPLACE TASK
or PROCEDURE
.
\ship
and Galaxy inserts your endorsed query with placeholders.• Use consistent parameter names (start_date
, end_date
).
• Document each parameter’s purpose in a header comment.
• Add a validation SELECT that errors when required params are NULL.
• Schedule quarterly reviews of templates to remove stale logic.
✅ Abstract constants → ✅ Provide defaults → ✅ Store in Galaxy Collection → ✅ Document → ✅ Version-control.
How to parameterize SQL in Snowflake; Best way to reuse queries across data teams; Snowflake session variables tutorial; SQL template best practices
Check out the hottest SQL, data engineer, and data roles at the fastest growing startups.
Check outCheck out our resources for beginners with practice exercises and more
Check outCheck out a curated list of the most common errors we see teams make!
Check out