Hey there! I’m trying to set up a simple workflow to upgrade the warehouse size before a prod run, using a webhook generated from a job start in DBT, but I’m noticing there isn’t just a simple action to “run snowflake SQL”. I’ve asked the AI code generator but seems like a lot of code just to run a simple select. Anyone here done something similar and can advise?
Thanks!
Mike
Hi @mike.melanson,
To set up a workflow in Pipedream that upgrades the warehouse size in Snowflake before a production run, triggered by a webhook from a DBT job start, you can use a custom code step in Pipedream. While it’s true that there isn’t a pre-built “run Snowflake SQL” action, you can achieve this by writing a small amount of Node.js code within a Pipedream workflow. This code will connect to Snowflake, run the SQL command to resize the warehouse, and then proceed with the rest of your workflow.
Here’s an example of how you could write this code using the Snowflake SDK in a Pipedream workflow:
import { promisify } from 'util'
import snowflake from 'snowflake-sdk'
export default defineComponent({
props: {
snowflake: {
type: "app",
app: "snowflake",
}
},
async run({steps, $}) {
const connection = snowflake.createConnection({
username: `${this.snowflake.$auth.username}`,
password: `${this.snowflake.$auth.password}`,
account: `${this.snowflake.$auth.account}`,
warehouse: `${this.snowflake.$auth.warehouse}`,
database: `${this.snowflake.$auth.database}`,
schema: `${this.snowflake.$auth.schema}`,
role: `${this.snowflake.$auth.role}`,
application: "PIPEDREAM_PIPEDREAM",
})
const connectAsync = promisify(connection.connect)
await connectAsync()
async function connExecuteAsync(options) {
return new Promise((resolve, reject) => {
connection.execute({
...options,
complete: function(err, stmt, rows) {
if (err) {
reject(err)
} else {
resolve({stmt, rows})
}
}
})
})
}
// Example SQL to resize the warehouse
const sqlText = `ALTER WAREHOUSE YOUR_WAREHOUSE_NAME SET WAREHOUSE_SIZE = 'X-LARGE';`
const { rows } = await connExecuteAsync({
sqlText: sqlText,
})
return rows
},
})
Replace YOUR_WAREHOUSE_NAME with the actual name of your warehouse and adjust the WAREHOUSE_SIZE as needed.
This code does the following:
I’m a bot powered by Pipedream and GPT-4. I’m still learning, so please double-check my answers and code! Learn how to build your own.
ok— this worked quite well! 
Struggling to publish to my actions though so I can just reuse this in other steps… getting this
There are a few metadata required to publish components, check out this documentation.
snowflake_action0.0.1Hi @mike.melanson , not sure if this would be helpful for you, but we have a Snowflake Trigger which allows you to run a query, and this emits the results when the query completes:
Hey @michael — I notice that we definitely have a lot more snowflake functionality as a trigger, and a lot less actions “mid workflow”. I added a step in my workflow to alter the warehouse size to XL before a DBT job runs, and then drop it back down to Large after the DBT job. Ended up saving them to my actions so they are recyclable, but was just curious that there are so few “Actions” outside of the initial triggers!
Hey @mike.melanson, if there are any discrete Snowflake actions that you would find useful, we’d be more than happy to get these on our radar to our component developers to build - just let us know!
hey @michael – I’d say the main use cases I can think of right now would be :
)Also, Looks like execute query was added in! Thanks!
That’s right – that’s an early version of much deeper database support we’re rolling out within the builder, and across MySQL and PostgreSQL in addition to Snowflake (with more database types coming soon). Here are the docs for it, please let us know if you have any feedback!
hey @danny ! I had a question. Trying to use the sql query function to execute a stored proc. I am triggering the workflow by slack— " Trigger Clone Odin_Mike " — I want to take this “Odin Mike Text” to then pass into the variable for the stored proc : “call odin.public.Odin_clone(‘odin_mike’)” — but when I try and use the step variable in the snowflake query I get errors. Am I missing something simple, or is this potentially outside the scope of the simple SQL query? Thanks!
