Is it possible to insert into Snowflake from Power Bi

Question

I'm trying to insert into a Snowflake table from Power Bi. Previously, I have leveraged the following YouTube video (Bulk Writeback to SQL from Power BI) from Bi Elite to writeback to a MySQL database. Now I'm trying to do the same with a Snowflake database, but I'm not having any success. The steps that I utilize for the MySQL writeback are (all detailed in the Bulk Writeeback Video):

1. Create a custom column in my table that mimics the insert statement that I want.
2. Create a function that connects to the database and uses the custom insert column as an input and performs the insert.

The function in step 2 relies upon the NativeQuery function in Power Bi to perform the insert. That function looks like this:

    (insert_statement as text)=>
let
    Source = MySQL.Database("localhost:3306", "mydatabase", [ReturnSingleDatabase=true]),
    insert = Value.NativeQuery(Source, insert_statement)
in
    insert

Then you simply create an INSERT column using invoke custom function feature. This column executes the insert statement in Step 1 and performs the insert.

When I try this with my Snowflake database it doesn't work. I believe the issue is related to the connector used to connect to Snowflake, Snowflake ODBC Connector. It appears that this connector does not support the Value.NativeQuery function that I use in my function to perform the insert. Is anyone aware of an alternative function that will work with SnowFlake.

Answer 1

It is possible to execute INSERT INTO query from Power BI via M-language function.

Power BI:

(insert_statement as text) =>
let
    source = Snowflake.Databases("<locator>.snowflakecomputing.com","<warehouse_name>"){[Name="<database_name>"]}[Data],
    insert = Value.NativeQuery(source, insert_statement, null, [EnableFolding=false])
in
    insert

---

Important [EnableFolding=false] otherwise every query will be wrapped with outer select query and cause SQL syntax error.

---

Snowflake Setup:

CREATE OR REPLACE TABLE TEST.TAB(col1 TEXT);

Direct insert SQL text will succeed in inserting value but it will error out:

---

Workaround - Stored procedure

Creation of stored procedure to be used for inserting new values:

CREATE OR REPLACE PROCEDURE TEST.INSERT_VALUES(input VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  INSERT INTO TEST.TAB(col1) VALUES(:input);
  RETURN 'Row successfully inserted';
END;

Call from Power BI:

CALL TEST.INSERT_VALUES('Test - insert from PowerBI');

Output:

State on Snowflake:

Warning! This approach should be thoroughly tested(it may not work if Power BI uses caching or reusing call for the same input).

---

Extras: Invoke INSERT INTO without stored procedure

Running an arbitrary code via CALL (with Anonymous Procedure):

WITH proc AS PROCEDURE(str TEXT)
RETURNS TEXT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
def main(session, str):
  return session.sql(f"{str}").collect()
$$
CALL proc('INSERT INTO TEST.TAB(col1) VALUES (''Test - insert from anonymous function - inline PowerBI'')');

Call from Power BI(one-liner):

Output: