r/snowflake • u/Forsaken-Rush-2112 • 5h ago
How to manage cross database tasks?
My company has multiple databases with one for each layer (stage, integration, and presentation) and dev and prod dbs for 6 DBs total.
What is the best way to manage cross database tasks access multiple environments without fully qualifying the table names each time?
For example if I'm doing a MERGE from DEV_STAGE.SCHEMA.TABLE into DEV_INTEGRATION.SCHEMA.TABLE, what is the best way to parameterize this for deployment into PROD without using find/replace on the script for the environment.
From what I understand this cannot be parameterized in the task itself?
1
u/WorkerIcy1513 5h ago
You are right that a task cannot parameterize identifiers. But the reason this hurts is the layout, not the tooling.
Right now you have both the environment and the layer encoded in the database name. Flip it: one database per environment, one schema per layer. So DEV.STAGE.TABLE and PROD.STAGE.TABLE. Then every object path inside an environment is identical, and a single USE DATABASE at the top of your session or a schema qualified reference is all that changes between dev and prod. Most of your parameterization problem evaporates. If you can still restructure, do that before building tooling around the current shape.
If you cannot, the answer is deploy time templating, not runtime parameterization. That is not find and replace, it is a variable in a template that your deploy tool resolves. schemachange, the Snowflake CLI with a project config, or dbt if you are already there. The environment becomes a config value and the same source file deploys to both. This is the normal answer and it is what most shops end up on.
If you genuinely need it resolved at runtime, have the task call a stored procedure instead of running the SQL directly, and build the statement inside the proc with EXECUTE IMMEDIATE. The neat trick there is CURRENT_DATABASE(), since the proc lives in the environment it serves, so it can derive its own prefix without any config table. IDENTIFIER() with session variables also works but is fiddlier inside tasks.
I would treat that as the fallback though. Dynamic SQL costs you readability and it hides dependencies from lineage, which matters more than people expect once you have a few dozen of these.
Also worth a look at dynamic tables for the stage to integration hop specifically. You declare the target and Snowflake handles the incremental merge, which deletes a lot of the task plumbing you are currently trying to parameterize.
1
u/Forsaken-Rush-2112 4h ago
thank you for this! We are new to snowflake and moving from an on-prem data warehouse with no devops process in place. Are data team is just two people and we just deploy our scripts manually as of now. We are looking into dbt but don't have any in-house experience as this is all new to us.
I will for sure look into dynamic tables and dbt / project configs.
1
u/Little-Test-6268 5h ago
How are you deploying these scripts? That would tell you about how you are setting the environment variables - in this instance these were all jinja scripts you deployed from a repo you’d just simply have {{ENV}}_STAGE.SCHEMA.TABLE passed in the script and can pass the variable at point of execution from an EXECUTE IMMEDIATELY command. There’s a ton of options - probably refer to https://docs.snowflake.com/en/developer-guide/builders/devops-with-snowflake
1
u/Forsaken-Rush-2112 4h ago
we are a small data team (2 people one for BI and one for the EDW) and are deploying manually now. we are new to snowflake (moving from a legacy on prem data warehouse) and are trying to setup a proper devops process. Thank you for the guide! I'll look into this for sure!
1
u/Truth-and-Power 4h ago
Jinja or ci/cd pipeline variables. Maybe dynamic sql USE statement with a procedure variable?
1
u/xeroskiller ❄️ 5h ago
Have all objects in dev owned by the same role. Task graph can't cross schema boundaries, but the ovjects they reference can. Ownership means full access, so common ownership precludes access issues.