dbt Metadata and Snowflake dbt Projects — Part 2
Introduction
Welcome to the second part in my dbt Metadata series. In the first part, I covered the essentials of dbt metadata, primarily focusing on dbt’s two main artifacts, the manifest.json and run_results.json. And in the next part of the series I will cover how we can use these important artifacts to perform state-aware operations with dbt. But before we can get there, we need to first go over how to work with dbt metadata in Snowflake’s exciting dbt Projects feature.
In this blog post, I will dive into the internals of Snowflake’s DBT PROJECT object, and explain how it stores dbt metadata and how we can work with that dbt metadata.
For reference, here’s the outline of the three-part series:
- dbt Metadata Essentials — Part 1
- dbt Metadata and Snowflake dbt Projects — Part 2 (this blog post)
- dbt Metadata and State-Aware Operations — Part 3
[This blog post was hand written.]
The DBT PROJECT Object
As part of Snowflake’s support for dbt, called dbt Projects on Snowflake, a dedicated object type named DBT PROJECT was created. You are no doubt familiar with different object types supported by enterprise database platforms, such as DATABASE, SCHEMA, TABLE, VIEW, PROCEDURE, and many more. The DBT PROJECT object in Snowflake is a native object type just like those others.
And the best way to understand what the DBT PROJECT object represents is to think of it as a file container. It contains all of the files that make up your dbt project, along with all of the artifacts and logs generated during an invocation/execution of dbt. Here’s a diagram that helps to explain the core structure of the DBT PROJECT object:
I mentioned above that the best way to understand the DBT PROJECT object is to think of it as a file container. Well, actually, it contains two separate file containers, the live stage and the results stage. The live stage is the active filesystem, which contains the current version of the dbt project files, artifacts and logs. And the results stage contains an archive of the result artifacts from the previous executions. We’ll take a closer look at each stage in the following sections.
Like all objects in Snowflake, the primary way to create, update and delete a DBT PROJECT is with SQL DDL. Here are those DDL statements. First to CREATE a new object:
CREATE [ OR REPLACE ] DBT PROJECT [ IF NOT EXISTS ] <name>
FROM '<source_location>'
[ DBT_VERSION = <version_number> ]
[ DEFAULT_TARGET = <default_target> ]
[ DEFAULT_ENVIRONMENT = '<environment_name>' ]
[ EXTERNAL_ACCESS_INTEGRATIONS = ( <integration_name> [ , ... ] ) ]
[ AUTO_COMPILE = { TRUE | FALSE } ]
[ DEFAULT_WRITEBACK = { TRUE | FALSE } ]
[ COMMENT = '<string_literal>' ]And next to ALTER an existing DBT PROJECT object:
ALTER DBT PROJECT [ IF EXISTS ] <name> RENAME TO <new_name>
ALTER DBT PROJECT <name> DEPLOY FROM '<source_location>'
ALTER DBT PROJECT [ IF EXISTS ] <name> SET
[ DBT_VERSION = '<version_number>' ]
[ DEFAULT_TARGET = '<default_target>' ]
[ DEFAULT_ENVIRONMENT = '<environment_name>' ]
[ EXTERNAL_ACCESS_INTEGRATIONS = ( <integration_name> [, ... ] ) ]
[ AUTO_COMPILE = { TRUE | FALSE } ]
[ DEFAULT_WRITEBACK = { TRUE | FALSE } ]
[ COMMENT = '<string_literal>' ]
ALTER DBT PROJECT [ IF EXISTS ] <name> UNSET
[ DBT_VERSION ]
[ DEFAULT_TARGET ]
[ DEFAULT_ENVIRONMENT ]
[ EXTERNAL_ACCESS_INTEGRATIONS ]
[ AUTO_COMPILE ]
[ DEFAULT_WRITEBACK ]
[ COMMENT ]There are additional DDL statements for working with DBT PROJECT objects that you can find on the dbt Projects on Snowflake commands page. And for more details on working with DBT PROJECT objects please check out my blog post Snowflake dbt Projects Operations Deep Dive.
NOTE: In September 2026 Snowflake released a major update to dbt Projects, which changed the core behavior of the DBT PROJECT object. The previous object model is referred to as “versioned,” while the new model is referred to as “live”. For details on migrating an existing versioned object see dbt Projects on Snowflake: dbt project objects migrate to a single mutable live version. This blog post will explain the newer, live object model.
The Live Stage
The live stage is where the current version of your dbt project files live, including the current version of your compiled artifacts and run results. You can think of it as the active (or live) filesystem for the DBT PROJECT object. When you actually run dbt in Snowflake, it’s the dbt project files in the live stage that are read by the dbt CLI.
There are 2 different ways to create/modify the files that exist in the live stage. The first, and primary way, is to run either the CREATE or ALTER … DEPLOY SQL DDL command. When you run that command the live stage in the target DBT PROJECT object is completely replaced with the new files you specified in the FROM clause. That is probably what you’d expect when creating a new object via CREATE, but it’s very important to note that when updating an existing object via ALTER … DEPLOY, Snowflake first removes all of the existing files from the live stage and then copies the new files you provide into the now empty live stage. It performs this process atomically, but that is what happens. Here’s a diagram to help you understand:
The key argument to either the CREATE or the ALTER … DEPLOY command is the FROM clause. The FROM clause identifies which source files will be copied into the new, empty live stage. And there are four different source locations where those source files can live – an internal STAGE, a GIT REPOSITORY, a WORKSPACE, or another DBT PROJECT object. Each of those four location types is essentially a file container in Snowflake, like our live stage. For more details on this process, and how the <source_location> path is defined, see my blog post Snowflake dbt Projects Operations Deep Dive.
The second way to interact with files in the live stage is to do so directly with the GET, PUT, or COPY FILES commands. These options are much less common, but possible with Snowflake DBT PROJECT objects. The COPY FILES command lets you copy files between different file stages in Snowflake (those listed above). It’s a very useful command in Snowflake to be aware of, and for more details check out the Snowflake COPY FILES documentation. Here’s an example of copying the /target folder from a previous execution into a stage:
COPY FILES
INTO @my_db.my_schema.my_stage/results/
FROM 'snow://dbt/my_db.my_schema.my_dbt_project/results/query_id_01bf51c1-010b-5676-0000-53493ae6db02/target/';Finally, because the live stage is the active filesystem, it also contains the most recent version of the artifacts and logs created during the previous execution (unless you’ve overridden the default WRITEBACK setting, more on that below). Those are created in the target/ and logs/ folders respectively by default. This is exactly what would happen if you were to run the dbt CLI yourself, locally on your laptop for example – dbt will write the artifacts and logs to the specified folders in the local filesystem. I’ll go into greater depths on working with dbt artifacts in the next blog post, but this is a good segue to our next topic, the results stage!
The Results Stage
The results stage is aptly named, and where the results of previous dbt executions are stored. As I covered in part 1 of this series on dbt metadata, each execution result includes the dbt artifacts and logs. Here’s a diagram to help you understand the structure of the results stage:
As you can see, the results stage is organized into top-level folders named by the Snowflake query id associated with the EXECUTE DBT PROJECT run. And each results stage query folder contains a snapshot of the results from the run. The contents of a particular query folder vary depending on the type of operation performed, but at a minimum each folder contains the following items:
- The key dbt artifacts in the
target/folder (manifest.json,run_results.json, andsources.json,semantic_manifest.jsonif present) - The
dbt.logfile in thelogs/folder (only the singledbt.logfile which contains logs for the related run only) - The
dbt_artifacts.zip, which contains at a minimum the complete copy of both thetarget/folder and thelogs/folder
In addition to those guaranteed items in a particular query folder, there can be additional items depending on the type of dbt run. And in particular, when running a CREATE or ALTER … DEPLOY command with AUTO_COMPILE=true, the query folder as well as the dbt_artifacts.zip will include the complete contents of your dbt project (the files from the live stage as well). More on AUTO_COMPILE later.
Get Jeremiah Hansen’s stories in your inbox
Join Medium for free to get updates from this writer.
This is such a powerful feature of Snowflake’s dbt Projects capability! As we’ll see in the third part of this blog series, the previous run results are critical to many important operational use cases (slim CI and state-aware operations). And the results stage makes it very easy to access and work with these previous results.
The last thing to point out here is that there is a data retention policy set on the results stage. Query results folders are auto deleted from the results stage after 14 days, which is a time window that is more than sufficient for most operational use cases. However, if you want to archive your dbt results for a period of time longer than the 14 days supported by the results stage, you can easily copy the files and archive them wherever you like. Here’s one way to do that, using the COPY FILES command I shared above, which copies all the artifact files from the /target folder of a particular dbt execution into a Snowflake stage:
COPY FILES
INTO @my_db.my_schema.my_stage/results/
FROM 'snow://dbt/my_db.my_schema.my_dbt_project/results/query_id_01bf51c1-010b-5676-0000-53493ae6db02/target/';Results Stage Helper Functions
As I showed in the last code example above, you can use the COPY FILES command (or GET and PUT) in Snowflake to copy files from anywhere in the DBT PROJECT object results stage to another DBT PROJECT object or file stage of your choice. With that capability alone you could accomplish just about anything operationally with dbt. But that is not the most user friendly approach, since it’s hard to reason over the top-level folder names in the results stage since they’re meaningless query UUIDs. Fortunately Snowflake provides a series of important helper functions, so you can quickly find what you’re looking for in the results stage.
There are two main approaches to finding the correct results, by DBT PROJECT object or by query id. Here’s the breakdown:
By DBT PROJECT object:
- SYSTEM$DBT_GET_LAST_RUN_TARGET()
- SYSTEM$DBT_GET_LAST_SUCCESSFUL_RUN_TARGET()
- SYSTEM$DBT_GET_LAST_FAILED_RUN_TARGET()
By Query ID:
The functions that work by query id are fairly straightforward, but take note of a couple things. First the query id used here is the query id of the EXECUTE DBT PROJECT command, in other words you can’t use these with the query id of a CREATE DBT PROJECT or an ALTER DBT PROJECT … DEPLOY command. And second, for help finding the query id of a previous EXECUTE DBT PROJECT command, check out the DBT_PROJECT_EXECUTION_HISTORY view in the information schema. Here’s an example:
--Look up the most recent dbt project object execution
SET latest_query_id = (SELECT query_id
FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.DBT_PROJECT_EXECUTION_HISTORY(
DATABASE => 'MY_DB',
SCHEMA => 'MY_SCHEMA',
OBJECT_NAME => 'MY_DBT_PROJECT'
))
ORDER BY query_end_time DESC LIMIT 1);
--Get the location of the dbt project object artifacts folder
SELECT SYSTEM$LOCATE_DBT_ARTIFACTS($latest_query_id);
-- Result
-- snow://dbt/MY_DB.MY_SCHEMA.MY_DBT_PROJECT/results/query_id_01c01096-010c-0ccb-0000-a99506bd199e/Note: For more details about the snow:// URI syntax, please check out my blog post Snowflake File-Based Entities (or FBEs). Also, please note that you can view (or list) the files in a DBT PROJECT object, just like you would those in a stage, with the LIST (or LS) command.
Finally, the functions that work by DBT PROJECT object are also easy to use. And these are going to be the most useful functions for you. Here’s a simple example with SYSTEM$DBT_GET_LAST_RUN_TARGET():
--Get the location of the dbt project object artifacts folder
SELECT SYSTEM$DBT_GET_LAST_RUN_TARGET(
'my_db.my_schema.my_dbt_project'
);
-- Result
-- snow://dbt/MY_DB.MY_SCHEMA.MY_DBT_PROJECT/results/query_id_01c01096-010c-0ccb-0000-a99506bd199e/target/As you can see, both groups of functions return a path to the correct dbt results folder in the results stage. The main thing to understand with these functions that work by DBT PROJECT object is how the arguments can be used to identify a particular DBT PROJECT object execution. Here’s the definition of the function:
SYSTEM$DBT_GET_LAST_RUN_TARGET (
'<object_name>'
[ , '<commands>' [ , '<target_path>' ] ]
)It wasn’t shown above, but you can pass two additional (optional) parameters after the object name which help you identify a particular type of execution. The <commands> argument lets you select executions of only a particular type, like only build commands or only run commands. And the <target_path> argument lets you only select executions against a particular/custom dbt target folder (corresponding to the value you specified in the --target-path dbt CLI argument).
Other Related Features
Now that you have a solid understanding of how the DBT PROJECT object works between the live stage and results stage, and how to locate any exact set of result artifacts, I want to touch on a few other related features here that are worth knowing about.
First, the AUTO_COMPILE flag. The AUTO_COMPILE flag affects the CREATE or ALTER … DEPLOY behavior. When it is set to true, Snowflake will automatically run a dbt compile whenever you create or deploy (via alter) the object. And more than that, if you have an EXTERNAL ACCESS INTEGRATION configured, it will also automatically run a dbt deps before doing the compile. This is the default behavior. When set to false, however, it skips these steps and just copies the files from the source location (specified in the FROM clause) to the live stage of the object, and nothing else. This flag can be set in the CREATE statement and updated later with the ALTER statement.
Second, the DEFAULT_WRITEBACK flag. The DEFAULT_WRITEBACK flag affects the EXECUTE behavior. When it is set to true, Snowflake will automatically copy (or write back) the artifacts and log files from the execution into the live stage. This is the default behavior. When set to false, however, it does not write any result files back to the live stage. This is generally useful for situations where you need to run concurrent EXECUTE DBT PROJECT commands but want to avoid a race condition where each concurrent dbt process can overwrite artifacts produced by the other in the live version (making downstream behavior unpredictable and failures difficult to reproduce). This is similar to the behavior of the --no-write-json flag in dbt, but excludes both artifacts (in the target/ folder) and logs (in the logs/ folder). This flag can be set in the CREATE statement, updated later with the ALTER statement and even overridden by the EXECUTE statement at runtime.
Third, and finally, Snowflake dbt Project specific metadata. In addition to capturing dbt’s core metadata (from the artifacts and logs), Snowflake also captures some of its own metadata. This final piece of metadata is captured in the Snowflake event table. The event table in Snowflake captures telemetry data for all services in Snowflake, including dbt Projects. This metadata is stored in the OpenTelemetry format and supports both system-generated traces, metrics and logs as well as custom user-defined ones. With dbt Projects in particular, Snowflake emits its own system-generated traces, but also captures all of the dbt console logs. So in addition to accessing the actual dbt log files directly (as discussed above), you can also access/query the dbt console logs via the event table.
Conclusion
Wow, we covered a lot of ground there, but those are critical details to understand in order to become proficient working with dbt metadata in Snowflake. In this second blog post, you learned how the Snowflake DBT PROJECT object works, what the live stage and results stage are, and how dbt metadata is stored and made available.
But other than giving a few examples of copying dbt artifacts around (or ingesting them like I discussed in the first blog post), I haven’t yet provided any practical examples or use cases for actually making use of the dbt artifacts. And that is exactly what I plan to do in the third and final post. Before discussing how to perform state-aware operations with dbt, we first had to lay the foundation of what dbt artifacts are and how Snowflake stores and makes them available.
So stay tuned for the third blog post in the series, on dbt Metadata and State-Aware Operations, it’s going to be very practical and tie this all together!
Blog posts in this series:
- dbt Metadata Essentials — Part 1
- dbt Metadata and Snowflake dbt Projects — Part 2 (this blog post)
- dbt Metadata and State-Aware Operations — Part 3
