Schemas should be first-class, versioned artifacts

[venkaa28]

schemas should be first-class, versioned artifacts

🧩 TL;DR

• Today: dbt Fusion relies on a hidden schema cache, local Parquet files of column names and data types, to validate SQL offline.

  It works, but it’s invisible, can go stale, and requires manual deletion or refreshes when schemas drift.

• Problem: this cache isn’t inspectable, versioned, or easily shareable.

  Teams can’t reproduce compiles, review schema changes, or work fully offline without live warehouse access or the necessary permissions to access source schemas.

• Proposal: make schemas first-class, version-able artifacts, “schema locks”, stored as human-readable YAML files under a directory.

  • New command: dbt sync → fetch, validate, and update schema locks.

    Think pip freeze but for your data schemas.

  • Benefits: predictable & faster compiles, transparent schema diffs in PRs, offline-ready workflows, and drift detection integrated with contracts.

  • Goal: turn schema metadata from an internal cache into an artifact dbt can both trust and verify.

• Ask: Give us feedback on the proposal and questions at the bottom

---

how we got here

The dbt Fusion engine radically improves developer experience with a new compiler toolchain that is not only faster but can understand your SQL.

This largely happens via local SQL validation, i.e. validating that any change you make to a model query does not have errors without needing to actually run that query against the data warehouse. To do this, dbt needs to know the names and data types of all your columns for all your models. How does dbt Fusion figure this out?

Enter the schema cache.

schema cache:
a local copy of all column names and types for models and sources in your dbt project. the schema cache is required to have Fusion understand your SQL

To populate the cache, Fusion makes metadata requests to your data warehouse to retrieve the column names and data types of your source tables.

💡 More on schema cache

If you understand a SQL dialect, you can derive all models’ column types from upstream.

For example, imagine the below scenario with models: fct_orders and report_orders.
We know that COUNT() returns an integer, and that information can be used in downstream models like report_orders.

    -- fct_orders.sql
    SELECT
     user_id,
     COUNT(order_id) as total_orders
    FROM raw_table_orders
    -- report_orders.sql
    SELECT
     total_orders
    FROM {{ ref('fct_orders')

But the buck stops at the top of your DAG: your source tables. If we fetch the column types from the warehouse on the sources, it is enough information to understand all type of your downstream models. So one of the first things that Fusion does is download the source table schemas (read: the source table’s column names and types)

To accomplish this, the first thing Fusion does is make metadata queries to your data platform to get all the column names and datatypes of all of the relevant source tables. On most data platforms, this does not spin up a warehouse and drawdown credits.

The result is saved to target/db/ as parquet files.

• For the curious
  The parquet files have no rows, but the columns and datatypes do correspond to those of the source table in the data warehouse.

To see the information here’s two ways:

1. Use duckdb to select * from parquet_metadata(filepath) (docs)
2. Uploading it to this Parquet Visualizer to see the column schema

Once populated, the schema cache ensures that fusion doesn't need to re-fetch this metadata every invocation, making validation faster.

original design principles

The schema cache has been a part of dbt Fusion since launch in May this year. Since then it has improved greatly, works smoothly for many, and has even been reverse-engineered by several early adopters of Fusion.

We wanted new users to try out static analysis, which is a huge part of Fusion’s promise as the new engine for dbt. To that end, we originally treated the source schema cache as an implementation detail of static analysis, rather than a user-facing feature in its own right. Accordingly, we’ve documented a lot about static analysis but very little about the source schema cache. (static analysis Config, New Concepts)

Therefore the schema cache today is largely invisible to end users. Today, a user will only see a single line in stdout that it is happening: Downloading Relations ⠖ [6s]. Hardly anyone was aware of the actual schema cache: all the parquet files in target/db/.

🚨 The Problem

While the schema cache has been a solid foundation for enabling offline SQL validation in dbt Fusion, it’s also shown clear limits in practice.

The schema cache today is a hidden implementation detail, local Parquet files of column names and data types stored under target/db/. While it enables Fusion to understand your SQL without hitting the warehouse, it’s hard to inspect, can easily go stale, and often requires manual deletion or refreshes when things drift.

For example, one of the most common issues we’ve seen (dbt-fusion#615) looks like this:

• COLUMN_THAT_EXISTS is a newly added column.

• Fusion says it can’t find it, because it’s still looking at the outdated schema cache.

  error: dbt0227: No column CUSTOMERID found. Available are 
    --> models/stg/stg_customers.sql:2:1 (target/compiled/models/stg/stg_customers.sql:2:1)

• The only fix is to manually delete the local cache and re-download all your schemas.

Beyond staleness, several other pain points have emerged:

• Security constraints: some teams don't grant all users access to every source table, meaning Fusion can't populate the cache for the entire project. Bonus: for teams that want to work completely offline (e.g., pulling down schemas before getting on a flight), you'll be able to do so.r.
• Hidden complexity: because the cache is invisible, it’s not versioned, reviewable, or reproducible, making schema drift difficult to detect to accurately invalidate the cache.
• Static analysis gaps: models that depend on unanalyzable SQL or Python models can’t propagate type information downstream, since Fusion can’t infer their structure from code alone.

Together, these issues make the schema cache feel fragile and opaque, a powerful but “magical” mechanism that works until it doesn’t.

💡where we’re headed

👉 We think managing this metadata could look like introducing a new command — dbt sync — to fetch, lock, and update schema metadata, turning today’s hidden cache into an explicit, reviewable “schema lock.”

We want to make schema metadata a first-class, versioned artifact that dbt can both trust and verify, one that users can see, review, and manage directly, not invisible cache files that only Fusion knows about.

Still early thinking, but below is the direction we want to validate.

Does this feel like the right shape of the solution?

🧭 goal

Enable dbt projects to:

• Run static analysis and compilation without relying on live warehouse introspection
• Maintain a self-contained, versioned record of schemas for all nodes (including root nodes like sources, external tables, cross-project refs; or nodes that can't be analyzed likePython models, incremental models, unrecognized SQL, etc.)
• Detect and manage schema drift deterministically and review schema changes like code

This turns schema metadata into a declarative artifact that dbt can both trust and verify.

🧱 core concept: schema locks

Schema locks evolve the existing Fusion source schema cache into something explicit and version-controlled, part of your project’s source of truth.

Current State	Proposed Behavior
Parquet cache files in target/db/schemas/	Human-readable YAML lockfiles in schemas/
Git-ignored cache	Optionally Version-controlled artifact
Hidden implementation detail	User-visible, reviewable artifact
Cache used implicitly	Lockfiles managed via dbt sync

With this, dbt would:

• Use schema locks for static analysis, even for Python models or unanalyzable SQL
• Validate all schema locks for drift at run/build time
• Let users explicitly sync and review schema metadata via a new dbt sync command, think npm install, but for schemas

🧩 what this unlocks

• Predictable compiles: no more “works on my machine” moments
• Transparent diffs: schema changes show up in PRs
• Offline-ready workflows: compile without warehouse access (unless nodes introspect live)

✍️ authoring ergonomics we want feedback on

Hopefully you now have a big picture understanding of this proposal. You may already have some questions like: what will these lock file look like? where will they live? how does one create or update them?

Let’s go through them one at a time

where do these files live?

We propose that they live in their own schemas/ folder, and not in properties.yml files (more on that later).

The folder would be organized in the same folder structure as your project like below

schemas/
	moms_flower_shop/sources/sources__ad_reporting.yml
	moms_flower_shop/staging/python__orders.yml
	moms_flower_shop/analytics/external__metrics_layer.yml

what goes in a lock file (minimal example)?

# sources__ad_reporting.lock.yml

version: 1
node: sources.ad_reporting
warehouse: snowflake
columns:
  - name: campaign_id
    type: number(38,0)
  - name: spend
    type: number(18,2)
  - name: dt
    type: date

in this directory, there’s a small manifest file to indicate freshness and provenance

# schemas/_sync.yml
version: 1
last_synced: 2025-11-08T15:12Z
items:
  - fqn: sources.ad_reporting
    hash: abc123
    last_fetched: 2025-11-03T00:00:00Z

🧩 Managing Schema Locks with dbt sync

With a new command dbt sync and configuration in dbt_project.yml

It fetches schema metadata, stores it locally, and verifies it against the warehouse when needed.

dbt sync

Command	Intent	Behavior
dbt sync	Initialize or update missing schema lockfiles	Reads existing schema.yml definitions and warehouse metadata to populate missing or outdated locks under target/schemas/.
dbt sync --check	Check for schema drift	Compares the local lockfiles to live warehouse schemas and reports any drift.
dbt sync --update	Refresh outdated locks	Re-fetches schema metadata for all nodes or selected nodes.
dbt sync --from-remote	Generate lockfiles from warehouse definitions only	Ignores any local schema info and rebuilds all lockfiles directly from live warehouse metadata.
dbt sync --from-local	Generate lockfiels from local definitions only	Uses only schema.yml model definitions, never querying the warehouse. Useful for offline runs.

It would also support:

• selectors (--select / --exclude )
  • No selectors will generate lock files for all nodes that need schemas (i.e. sources, incremental models, python models, and any nodes with unanalyzable sql)
• a flag for a custom directory (--schema-lockfiles-dir)

configuration

There are also sync-specific configurations that, like other dbt configurations, could be set at the folder- or model-level

# dbt_project.yml
		  
flags:
  schema_lockfiles_dir: schemas/          # default: target/schemas/
  prefer_declared_schemas: true           # use lockfiles during analysis
  completeness: partial                   # or "strict"

[models | sources | seeds | snapshots]:
  +lockfiles:
    +completeness: strict                 # enforce schema exactness

Note:

By default, schema locks are written to target/schemas/, a directory typically ignored by Git.

Teams that want to version-control and review these files can opt in by setting schema_lockfiles_dir to a path inside their project (for example, schemas/). This provides flexibility between ephemeral local locks and persistent, reviewable artifacts.

🧩 Workflow

1. Initialize:

   # dbt_project.yml
   flags:
     schema_lockfiles_dir: schemas/

   dbt sync

   This creates human-readable YAML lockfiles that mirror your project’s folder structure under schemas/.
   Commit these files to your repo to make schema metadata reproducible and reviewable.

2. Check Drift Before Deployment

   dbt sync --check

   This command is ideal for CI/CD pipelines or pre-deploy hooks.

   It reports schema drift (e.g., added, removed, or type-changed columns) before merging or releasing changes.

3. Refresh as needed:

   dbt sync --update --select sources.*

4. Review schema changes:

   If version-controlled under schemas/, PR diffs show column-level changes directly.

   #example lockfile diff
   -  - name: spend
   -    type: number(18,2)
   +  - name: spend
   +    type: number(38,4)
   +  - name: campaign_name
   +    type: varchar(255)

5. (Optional) Define Schemas Locally—Skip the Remote

   If you’d rather declare the schema in schema.yml and have dbt build the lock from that (no warehouse calls), author columns and types in your properties:

   # models/staging/schema.yml
   models:
     - name: stg_orders
       columns:
         - name: order_id
           data_type: number(38,0)
         - name: order_total
           data_type: number(18,2)
         - name: created_at
           data_type: timestamp_ntz

   Then: dbt sync --select stg_orders

   dbt will generate the lockfile from your declared schema, without contacting the warehouse.

   If you explicitly want to ignore local declarations and pull from the warehouse instead, use:

   dbt sync --from-remote --select stg_orders

How strict are they?

What if your schemas drift over time?

Drift happens when new columns are added to your table. dbt already has a concept of model contracts to ensure the underlying table matches your expectations

We could reuse contracts to enforce schema drift:

• If a contract is enforced, Fusion errors when your schema has drifted
• Otherwise, it warns

What if you don't necessarily care about new columns, but need to ensure the columns you depend on exist?

We've introduce a new completeness mode:

• strict: schemas must match exactly
• partial: the columns your model depends on must exist, but extra columns are allowed
  • Useful for wide or dynamic tables (like event logs or Salesforce objects) where new fields appear often. Your model stays stable as long as required columns don’t change; when a new column matters, add it and run dbt sync.

escape hatches

Sometimes you need flexibility beyond the default behavior. We've included two escape hatches:

• dbt compile --prefer-remote: Use this flag when you want to bypass the local schema locks and fetch schemas directly from the warehouse for a one-off compile.
• dbt compile --no-auto-sync: Use this flag to disable automatic schema syncing and fail immediately if any required schema lockfile is missing.

what about Python models and unanalyzable SQL?

For nodes we can’t statically infer (Python models, or SQL with unknown functions), their schema locks are authoritative, they tell Fusion what the shape of the data is at compile time.

This means static analysis can continue downstream of those nodes, even when we can’t parse their SQL or inspect their runtime code.

At run/build time, dbt can validate those declared schemas (and any others) against the live warehouse to check for drift, using the same enforcement rules as other locks.

---

why do we need schema locks — aren’t schema.yml files enough?

A common question we’ve already heard:

Why introduce schema locks when schema.yml files already define columns and data types?

It’s a fair question, and one we’ve debated internally. While schema.yml files already describe models and sources, they serve a very different purpose from schema locks.

Type	Author	Purpose
schema.yml files	Human	Contain rich context: configs, tests, descriptions, and other metadata. These are human-authored inputs to dbt.
Schema lockfiles	Machine	Contain only column names and data types. These are machine-generated artifacts that capture the exact schema state dbt will rely on during analysis or execution.

Keeping these two artifacts separate is intentional and valuable:

• Maintains a clear boundary between authored definitions and generated metadata
• Prevents noisy diffs, schema locks change only when the actual underlying schema changes
• Improves safety, editing model configs or tests won’t accidentally alter schema metadata
• For teams that check schema locks into version control, it enables highly reviewable, deterministic diffs that show only real schema changes

In short, schema.yml files tell dbt what you intend, while schema locks record what actually exists, giving you the benefits of both declarative intent and verifiable reality.

questions for y’all

• do you agree that schema locks need to be distinct from the existing schemas.yml convention for configuring and documenting models and sources?
• default completeness: should we default to strict or partial?
• default directory for schema locks: should dbt write schema lockfiles to target/schemas/ by default (ignored by Git), allowing teams to opt in to version-controlling them by changing schema_dir to a path like schemas/ in dbt_project.yml?
• contracts reuse: is “drift = contract enforcement” intuitive, or do you want a separate setting?
• naming: “schema locks” vs something else?

[ttusing]

I think this is a very important feature and any implementation would be a big improvement. I think the idea of schema locks is a really good system for handling this. A lot of the configs makes sense to me.

do you agree that schema locks need to be distinct from the existing schemas.yml convention for configuring and documenting models and sources?

As long as dbt sync supports pulling the data types from our schemas.yml files into the lockfiles, I think its a pretty clean implementation.

I wonder how to keep these concepts intuitive for users

• dbt sync could have a slightly more intuitive name - like dbt lock-schemas.

default completeness

I think partial. In my experience new columns don't often critically require this level of enforcement

[sophieml]

As I told Anders last week, this is an extremely cool and necessary idea that I personally will never use!

TL;DR:

• I think that schema lockfiles should not be version controlled by default.
• I'd like some more details on how this will be used in practice to dbt0227: No column found less painful for users who are generally unaware of the schema cache!

Broadly, I see the remote source / schema cache relationship as follows:

• The data warehouse contains source (and model) DDL as it is in reality
• Fusion's schema cache contains a simulation of source/model DDL, which may or may not align with reality.

Currently, Fusion's schema cache is a 1:1 representation of the DWH's reality at a given point in time. The ability to alter this representation into one that is ungrounded from the DWH's state is very empowering for end users, but it should not be taken lightly. Similarly, the move from Parquet to YAML seems necessary, but also introduces lots of potential for user error.

Responses to posed questions

Default directory for schema locks?

I have a strong preference for schema lockfiles to be ignored by Git by default, for two reasons:

1. Since dbt does not manage DDL for sources in our project, I see no reason to have schema lockfiles version controlled here. I would also argue that most dbt projects fall in this bucket, unless they're managing sources through hooks or run-operations.

2. Schema lockfile generation is particular to the environment that dbt is running in. I wouldn't want myself or my team members inadvertently committing schema locks that are unfaithful to our staging and production environments.

   • Since dbt is also unaware of Git state, versioning also seems a little meaningless here: a user's dev environment could be ahead or behind the version in the main branch (or staging, or production, etc).
   • Yes, this can (and should be) accounted for by CI/CD checks or pre-commit hooks for both extraneous columns and missing columns in higher environments, but I do not think it's a good idea to introduce a behavior change to check in schema lockfiles by default when we can't implement these guardrails until said change goes live.

do you agree that schema locks need to be distinct from the existing schemas.yml convention for configuring and documenting models and sources?

Yes. but I do think it's important that the authoring interface for overriding the remote DWH state be independent from schema lockfiles. The proposal to define these in schema.yml files and syncing with dbt sync --from-local seems reasonable to me.

A question of my own

If my understanding is correct, this architecture change is attempting to address two user archetypes:

• The user that doesn't understand why Fusion doesn't "just work" like Core did when the schema cache differs from remote.
• The user who is maintaining synthetic Parquet files just to achieve a clean compile, for the reasons described in the proposal.

First, I think the introduction of an explicit dbt sync is a great improvement, and that a method to generate schema cache artifacts is necessary for User 2 to continue using Fusion. I also agree that making the schema cache readable will help User 1 with understanding the schema cache's purpose.

My question is: what is the intended path for User 1 to resolve their dbt0227: No column found error?

Are they supposed to know that they should run dbt sync? What if they edit the machine-generated lockfile, or add data types to the corresponding schema.yml configuration instead?

I think the pain points are actually best addressed through other ergonomic improvements in the CLI/VSCode Extension/dbt Studio: mainly, hinting towards refreshing the schema cache and restarting the LSP, if necessary. My concern is that without a better UX, users will end up introducing bad information into schema lockfiles that will result in confusion when attempting to run models. There's some amount of RTFM involved in Fusion already, and I'm not sure that the new YAML format will provide sufficient education on its own.

Two sidebars

1. Does this mean that the LSP and CLI will share a cache in dbt Studio?

2. Columns have other properties besides data type: nullability and collation, for example. I know this information is currently present in the schema cache as part of the typing system; will this be going away with schema lockfiles?

   • not that this is super relevant in a world where the DataFusion execution engine lies dormant, especially since most of these column properties are not settable through CTAS statements. Just curious!

[venkaa28]

I'm going to respond to each of your questions in different responses!

My question is: what is the intended path for User 1 to resolve their dbt0227: No column found error?

This is a great question, and you’re absolutely right that this remains a gap in the discussion today.

The intended path for resolving dbt0227: No column found is going to change meaningfully once schema lockfiles exist. Instead of the opaque “no column found” message we have today, users will see something like:

No column found in table.
If this column is new, please run dbt sync to refresh your schema locks.

That makes the error actionable, it directly points users toward the mechanism that resolves it, without requiring them to know about the schema cache internals or dig into documentation. The goal is to replace “delete the Parquet cache and hope it works” with a single, guided step.

We also plan for the CLI and VS Code extension to surface gentle hints when a schema lock is stale, e.g. a notification suggesting dbt sync --update if Fusion detects missing columns during static analysis. This complements the clearer error messaging and should make the entire workflow self-explanatory for most users.

Longer-term, the intention is that User 1 never needs to think about lockfiles directly. They’ll interact only through familiar commands and UI cues (dbt sync, auto-refresh buttons in Studio, etc.), while advanced users can still inspect or version-control the YAML artifacts if they want determinism or CI drift checks.

[jecolvin]

Definitely think this will be a very valuable feature! I agree with others that partial should be the default, and users opt into strict checking.

I see @sophieml 's points on defaulting to lockfiles not being version controlled, but if that's the case I almost wouldn't want to call them lockfiles (because in all other uses lock files are meant to be version-controlled to make environments reproducible).

To safely have them be version-controlled, I think being able to declare source environments would be necessary (almost like deferral environments but in reverse). Then perhaps you can choose to version control specific source environments (just prod, prod+staging, whatever folks decide).

This would also make it easier in the cases where there's a planned change to a source table, and you want to test the change's effects before they hit production. If you're syncing a lower-level source environment, you could temporarily switch to that and see what does / doesn't break, and update your models in preparation for the change.

[sophieml]

This is a nice suggestion! (and I agree with you about the semantics around "lock" file). I'm still skeptical of allowing dbt to be the source of truth for objects that it does not manage from a philosophical standpoint: going back to semantics, all the lockfiles I can think of are generated by the tool that performs the dependency management, not an observing party. But an environment spec would make the operational consequences much easier to deal with.

[venkaa28]

Thanks both, this is super helpful context.

The point about the semantics of “lockfiles” is spot-on. I realized I might be unintentionally conflating two separate ideas:

1. A true lockfile, which would imply dbt is the system of record for upstream objects (and should therefore be version-controlled), and
2. A richer cache context, which simply records where metadata came from and when it was fetched.

For an MVP, the second concept feels like the minimum requirement. Even without adopting lockfile semantics, adding provenance to errors meaningfully improves the experience, for example:

Column "foo" was not found in source "bar".
Cache origin: remote_warehouse
Cache fetched_at: 2025-11-25T10:14:21Z
Hint: run `dbt sync --select source:bar` to refresh from remote (or update your YAML if using declared schemas).

In this sort of MVP design, users would explicitly choose the source of truth per source via something like use_declared_schemas (trust YAML) or a remote-based mode with optional autosync (trust the warehouse). That keeps the behavior predictable. Regardless of which path users choose, selective cache invalidation feels important, being able to refresh one source/table without wiping everything. A dbt sync --select-style operation seems like a natural fit.

On the “source environments” idea: it’s a compelling longer-term direction, since syncing against different upstream environments (e.g. staging vs. prod) would help teams preview upstream changes before they land. It also introduces flexibility in which schemas teams might want to version-control, without making dbt the system of record for objects it doesn’t manage. But for an MVP, sticking to the two mutually exclusive schema sources (declared YAML vs. remote) plus provenance + sync seems like a clear, understandable starting point.

[jecolvin]

Oh I 100% didn't expect y'all to have environment-aware sources on the first release! I just want it on the roadmap 😎

[Simonot]

For me, this is a game breaking feature for the future of Fusion DBT !

I also think that the idea of schema locks is really elegant and is very well though (with the dbt sync (or whatever command name, I am very very bad at naming so never trust my propositions on naming 😅) etc.)

I think it will help for some DBT projects with specificities that make the hidden cache buggy (like wrongly interpret the type of a column with BigQuery TIMESTAMP vs DATETIME type for my project 😅) without breaking/changing anything for more standard DBT projects where the hidden cache works.

Regarding the questions asked, here is my inputs:

do you agree that schema locks need to be distinct from the existing schemas.yml convention for configuring and documenting models and sources?

Yes, I totally agreed with the reasoning describe in the proposal

default completeness: should we default to strict or partial?

I think we should go for partial as not all column addition are a must have immediately

default directory for schema locks: should dbt write schema lockfiles to target/schemas/ by default (ignored by Git), allowing teams to opt in to version-controlling them by changing schema_dir to a path like schemas/ in dbt_project.yml?

I think we should put it in a schema_dire path to being able to Git control it form the beginning. Because we want cache to be a real citizen of Fusion, we should not hide it from Git.

naming: “schema locks” vs something else?

I would go for dbt sync, but I am very very bad at naming so never trust my propositions on naming 😅

[VDFaller]

Opinions/Questions

• would dbt sync have an auto run/retry path for select failures? specifically column is missing
  • even if it's buried behind a flag or something
  • Just from a DX persepective
• how many people don't start with dbt-codegen or somthing similar for their sources to start? If it's machine generated to start anyway, don't know that column intentions is that much different. Our source files are definitely messy because of all the machine generated columns we use but then we have enforcements through dbt-checkpoint /dbt_project_evaluator
• partial strictness default
• agree with @sophieml on pretty much every point they made.

Edit
One thing I didn't understand was it seems that dbt sync will default to using the local if the local schema.yml has columns defined in source (like we would need to do if we want to test the source columns)? I would expect a sync to default to the remote first, but allow a local. And I would expect it to tell me if my local and the remote differ on either columns or their data types.

I say this because we sometimes (rarely, but it happens) only add columns that we have to test in our source yamls, but we still stage other columns. So if it were to be default local, we'd basically crash every time.

[eakubilo]

Huge props, to me this is both the technical and philosophical correct direction for DBT fusion to head towards. It is undeniable, really, that the lockfiles should be version-controlled by default. It both doesn't degrade the end user experience and provides an improvement on the maintainer's experience. I will start with the end user experience portion.

We should remember that although the schema-cache allows us to statically validate models without hitting the warehouse, data developers should (hopefully) actually still be testing their models by running them locally during the development process. I say this The antecedent to this being that users have access to the actual database objects that dependent sources of their particular dbtf run invocation correspond to. Let's think through just one of these database object during compile time. If the developer does have access to the database object, we can move to question whether there exists a schema lockfile.

If there is a schema lockfile, we use that metadata to statically verify the model. If the model depends on a column that does not exist in the schema lockfile, we can say this situation corresponds with the DBT fusion behavior when a column is referenced that does not exist in the schema cache. Today, fusion fails with e.g.

error: dbt0227: No column CUSTOMERID found. Available are 
  --> models/stg/stg_customers.sql:2:1 (target/compiled/models/stg/stg_customers.sql:2:1)

and requires flushing and recreating the entire cache to resolve. The proposed behavior of the source schema lockfile will allow me to run dbtf sync --select models/stg/stg_customers.sql to refresh the lockfile. This is strictly better behavior. Both need to first refresh something, but proposed lockfiles provide the ability to target specific schema metadata to invalidate. It would be nice if there was a flag that could tell Fusion to retry once after resyncing. If the model succeeds to validate, update the lockfile. If the model still fails, report to the user that the column cannot be found locally nor remotely and report the remote failure.

If the model depends on a column that does exist in the schema lockfile, all is well in both the current day fusion allegory and fusion post-lockfile.

If there is no schema lockfile, this is analogous to the cache being empty. In such a case, we should generate the lockfile.

This presents no issue at all, as well, if the lockfiles are tied to an actual database object, rather than the source name. This would completely abstract away the actual environment your commands are run in and solve the problem of unfaithful lockfiles. It would be impossible for the lockfile to be unfaithful to the environment with such an implementation. If the lockfile differs, then it represents genuine drift that should be dealt with.

The overall thrust of the argument on the user side is that schema lockfiles ostensibly seem to bring us closer to a reality where development on DBT just "works".

For maintainers, the real value here lies in dbt sync --check. Let's say we have a healthy DAG, all models can run successfully. It is important to recognize that this is just a point in time and is a function of your data warehouse. If the data in the warehouse updates in an incompatible way, your DAG will be unhealthy. The big issue is that you have no idea at any given point how unhealthy it is until a user reports that models are failing to materialize. We can know if our DAG is in an unhealthy state if we start monitoring the outputs of the check sync. Then, we can shift left and tackle it before it impacts user workstreams.

As well, consider sources whose schemas are programmatically generated. Today, if you want to check whether a schema migration on the producer side breaks your DAG, you need to manually twiddle with parquet files. With this proposed idea, the schema lockfiles as yaml would be easy enough to generate that we could just generate them with a simple Python script, then statically verify there are no structural failures in our DAG.

A good architecture and implementation for schema lockfiles would honestly provide close to a step-function change for the maintainability of a DBT project.

My hot take as well is that the manifest should get the same treatment and that would round out the step change. I elide that from this already large message, but can expound if anyone is curious.

[eakubilo]

To answer the questions:

1. I believe that schema locks should be distinct, but related to schemas.yml files. That is, it would be very nice behavior if schemas.yml files could only derive their columns from schema locks.
2. Strict completeness - in my opinion schema lockfiles should completely represent the database object and should thus represent the object exactly at a given point in time. Partial completeness is much more valuable forschemas.yml files, see my answer to the first question.
3. default directory - schema lockfiles shouldn't really be tied to a particular profile or target. I like the Terraform .terraform.lock.hcl style, where lockfiles live in the same directory as the source schemas. As a second option, I support separating the lockfiles from the DBT project an having them live in their own schema_dir
4. I don't think it's very intuitive actually. I do not think of source schemas as prescriptive at all, but rather descriptive. We are not making a promise of anything when we write the schema, rather we are recording an observation of database objects. OTOH, the model schema is prescriptive. It is saying that this is the shape that the code of the model takes in the warehouse. The observed source schemas are inputs of contracts. There doesn't seem to be a strong philosophical basis to reuse the contracts
5. To be honest, I think the lockfile name doesn't properly represent the full thrust of the concept. I think something like "meta sources" would be proper

[jsnb-devoted]

I've thought about this a bit longer since we met and I've swung back all the way to this engine doesn't need a dbt sync command or schema lock files at all. I fully accept that might just be a product of how my particular dbt deployment works but I tried to address this in a way that covered multiple flavors of dbt users.

To enumerate the issues with the cache that you listed above

• it’s invisible (this cache isn’t inspectable)
• can go stale
• sometimes requires manual deletion or refreshes when schemas drift
• not versioned
• not easily shareable.
• can’t reproduce compiles
• can't review schema changes
• or work fully offline without live warehouse access or the necessary permissions to access source schemas

I largely agree with all of these. I'm particularly interested in how this gets users to an offline static analysis of the sql. My gut reaction here is that it seems like this solution is to shift the burden of responsibility of maintaining the cache off the engine and to user via the dbt sync command.

I've jotted a lot of thoughts down below but if I can sum it up in a tl;dr I would say that: If its even possible I would prefer a solution that makes users think less about the cache rather than more. For users coming from dbt-core that just want it to work, it should just work even if it is more computationally expensive (more round trips to the db). For users like us with a giant project that are trying to emulate the cache by generating our own parquet files -- we want to bring our own table metadata (aka cache) so we aren't making any db round trips to get metadata.

What is stopping the engine from just keeping better track of cache? Why does the cache get stale?

The cache stores a list of tables and the table column names/data types. The engine today effectively does some kind of dbt sync like operation today to go fetch the DDL definitions from the database and populate that cache every time it runs a command. That gets pretty expensive so the cache must have some mechanism for determining when it needs to refetch that metadata. I suspect that right now the engine is leaning too hard on the cache when it actually should be fetching from the db more often. What actually is that determination today?

What is stopping the engine from looking at table x in the cache and comparing the cache time to the time the DDL for that table changed? I accept this could be tricky and a number of factors can get in the way. While Snowflake reliably keeps track of DDL changes with a LAST_DDL flag in the tables view -- I did a quick search and it doesn't look like BigQuery or Redshift do expose that metadata. Even if your database does support that, what if your source table is populated via CREATE OR REPLACE? Then you are invalidating that cache every time that operation happens even if the underlying metadata doesn't change.

I'm sure I missed a more sophisticated strategy -- but assuming the above is the best that can be done -- where does that leave users? You likely would find the engine fetching table metadata definitions from the database more often than is actually needed but slightly less than every table every run.

My suspicion is that for a majority of projects and user profiles this is actually more acceptable than is assumed by this doc. Small to medium size projects can probably even just survive fetching the table definitions every time a command is run. How many sources does a project need to have before that actually becomes prohibitive? 100? 200? 1000? dbt-core/cloud users today accept an enormous overhead before every command even with partial parsing.

How does dbt sync actually help maintain the cache better than the engine?

To me dbt sync addresses the problem by making it the users problem. It's not unreasonable -- the user actually has a better idea of when the cache has been invalidated because they typically have access and insight into how the source tables are being created/altered/populated with data. I think @sophieml's response is very telling -- most users just want this to work and want their database to be the source of truth for objects managed outside of dbt. If you just improve the caching mechanism and improve ergonomics (ala "hinting towards refreshing the schema cache") then most users are probably satisfied. Put another way -- most users probably don't want the cache to be more transparent -- they want it to just work and they will probably accept occasionally having to clear their cache every now and then. If you really want to get fancy, before the engine reports a missing column/mismatched datatype that might be from a stale cache it could always reinspect the metadata for that table on the fly.

What about really large projects? and projects that want offline compilation?

This is me. My project has >8000 sources and I have been waiting (somewhat) patiently for real static analysis checks in my project. Does having a new yaml file that describes my sources help me? I don't think so. We code generate 99% of the sources and our project lives in a mono repo that is shared with the source system that keeps track of the DDL for all the tables that could be sources in our project. Not everyone has this luxury but many people have turned to dbt-codegen for similar reasons. I don't want dbt fusion to ever inspect my database for source table columns/types. I've declared my types in my project and if that has gotten out of sync then that is on us. If the proposed solution is implemented we will likely also gitignore the lock files and we will probably implement some kind of hook that runs dbt sync --from-local before every command.

Source schema.yml or separate schema lock files

At first I felt like this was justified because there can be human-authored values (is it more than tests, descriptions, freshness?) in the source schema.yml files and its hard to mix human-authored and generated files. I've come back around on this and I think that sources shouldn't have any human-authored information. I suspect that isn't going to be popular but I'd be interested to see what people think about that.

description

(I think) all db engines support comments on their tables/columns so what are we putting in the description that wouldn't be just as suited for the comments in the database? If you want descriptions why not just put in them in the comments and let whatever tool your are using to generate your sources pull those into yaml files?

data_tests

I'd be interested to know how many deployments really use data_tests on sources. We have considered it at different times but we never have justified doing it. The jaffle shop doesn't have data_tests on its sources. dbt's best practices state that you should build intermediate tables on top of sources rather than having models query them directly (project evaluator). If that is really best practice isn't easier to just put your tests on the intermediate model rather than directly on the source?

freshness

also another feature I've never used but unless you are defining a unique source freshness for every source you have then I don't see why this can't just be defined somewhere away from your source definition. I get it if someone takes issue with this but I don't think freshness is enough to warrant keeping 2 separate yaml files that have nearly identical information in it.

[VDFaller]

For your comments on data_tests, description, and freshness. Where I am, we don't own our sources, they come from a different team that

• doesn't test their data at all
• won't put comments in for columns or tables
• has freshness failures every day

Putting these in helps tell if it's an us problem or an upstream problem. It's definitely not ideal, but I've fought for three years to change it and it's not going to change.

So gonna respectfully disagree on those points.

[jsnb-devoted]

Wouldn't the "staging" layer described in the dbt best practices satisfy your situation?
https://docs.getdbt.com/best-practices/how-we-structure/2-staging#staging-models
You have a staging model for every source. You put your descriptions and data tests in the schema.yml. Freshness checks for sources are easily translated into a data_test on the staging model (check if x field max is older than y time). You effectively get the same functionality no?

I doubt I'm going to convince anyone of anything here because I know its not practical to wind back concepts that people have grown accustomed to from dbt-core.

The issue is that we have always been using sources as "schema lock files" in dbt-core but we are in a small minority. I think if this feature is rolled out as described we just need to refactor our code generation pipeline and I think we will be fine. I appreciate the replies. I think that solidifies my answers to questions in the initial post:

do you agree that schema locks need to be distinct from the existing schemas.yml convention for configuring and documenting models and sources?

• as stated we would prefer it be the same as schemas.yml but we will survive if its not. it probably makes more sense for most users for them to be separate

default directory for schema locks: should dbt write schema lockfiles to target/schemas/ by default (ignored by Git), allowing teams to opt in to version-controlling them by changing schema_dir to a path like schemas/ in dbt_project.yml?

• we need these files version controlled and putting these files in the target/ directory makes the gitignore more complicated. either defaulting to a new directory outside of target or giving us the ability to override that dir works for us.

contracts reuse: is “drift = contract enforcement” intuitive, or do you want a separate setting?

• I actually don't think we will ever check for drift so maybe we don't have an opinion on this. If there is drift between the db and the files we don't actually care unless it breaks the sql.

default completeness: should we default to strict or partial?

• similar to the answer above on contracts -- we only care if there is drift in so far as it impacts the sql in the project. if a source adds a column that we don't use and a developer hasn't updated their branch from master that is fine. So I think we vote for partial by default.

naming: “schema locks” vs something else?

• no strong opinion here and my position is still that these are "sources" and we will probably treat them like sources -- so I think my vote shouldn't really count here.

[VDFaller]

@jsnb-devoted, we have a staging layer, and we do 1:1, but we also add calculated columns and other things that are not associated with source. This gives a clean, "It's definitely not something we did" partitioning of responsibilities. It just feels much cleaner, and easier to monitor this way.

It sounds like if you want your schema.yml files to be your source of truth it can be with --from-local and from what I can tell, that's the default if it's available. Though I'd argue it should be --from-remote.

[venkaa28]

Thanks everyone, this is super helpful context and an awesome thread to see!

We’ve actually started shaping an MVP that doesn’t commit fully to one side or the other, and it ends up looking very similar to what’s outlined in this comment:
https://github.com/dbt-labs/dbt-fusion/discussions/1042#discussioncomment-15080339

At a high level, our POC treats this as a “schema origin + cache provenance + sync” problem rather than a full lockfile system, which aligns with many of the concerns raised here.

What we’re implementing for the MVP (sources-only)

1. A per-source schema_origin flag

•	local → Fusion uses only YAML-declared schemas
•	remote → Fusion fetches schema from the warehouse (with an hourly auto-refresh)

This gives users an explicit, predictable “source of truth” without requiring lockfiles by default.
⸻

2. Cache auto-invalidation on YAML edits

Any edit to source.yml schema definitions marks the cache stale; Fusion re-hydrates from YAML on the next sync/compile.

3. A new dbt sync command (chained before compile)

dbt sync builds the source schema cache from either:
• local YAML (schema_origin: local), or
• the warehouse (schema_origin: remote)

We’re also aiming to support:

dbt sync --select source:<name>

so users can refresh a single source/table instead of the entire project.

4. A provenance index for errors and inspection

For each source/table we persist:
• origin: local_yaml | remote_warehouse
• fetched_at: ISO timestamp
• remote_last_modified_at (when available)

These fields are used directly in error messages and debugging context.
Error behavior example

Column "foo" was not found in source "bar".
Cache origin: remote_warehouse
Cache fetched_at: 2025-11-25T10:14:21Z
Hint: run `dbt sync --select source:bar` to refresh from remote
(or update your YAML if using declared schemas).

This keeps the distinction clear between:
• a true lockfile (system of record, version-controlled), and
• a richer cache context (provenance + refreshable metadata).

For the MVP, we’re intentionally targeting the second.

How this fits into the discussion

This design tries to meet the needs of both ends of the spectrum:
• If teams want fully declared, version-controlled schemas, they can use schema_origin: local and never hit the warehouse.
• If teams want warehouse-sourced schemas, remote + sync gives them a predictable, inspectable flow.
• Selective refresh helps large projects avoid “wipe everything” invalidations.
• Provenance improves errors without forcing the complexity of full lockfile semantics.

The “source environments” direction is still a compelling long-term extension, but for now, this feels like the smallest coherent set of behaviors that solves the major pain points.

Before we build further, does this meet everyone’s minimum requirements?

This MVP is intentionally scoped to the smallest set of features that:
• let local-schema users work offline,
• let remote-schema users sync predictably, and
• give all users clearer provenance and better errors.

[jsnb-devoted]

Awesome -- thanks @venkaa28. That all sounds great 👏

[pempey]

Here are my first round of thoughts. There may be more to some, sorry I was late to the discussion.

Original questions:

do you agree that schema locks need to be distinct from the existing schemas.yml convention for configuring and documenting models and sources?

Yes I think that the thing that the engine is using the validate the schema should be different from the thing that developers are using the define and configure the models. The are both models, in the simple definition of a thing that approximated reality, but they server different purposes and are interacted with in very different ways.

default completeness: should we default to strict or partial?

I would want partial by default, I am a little sensitive to dbt telling me when what I am doing is 'wrong' and I do not want to be yelled at just because a source system added a column.

default directory for schema locks: should dbt write schema lockfiles to target/schemas/ by default (ignored by Git), allowing teams to opt in to version-controlling them by changing schema_dir to a path like schemas/ in dbt_project.yml?

While I am big on version control and 'business logic in code', this is not a time where I would want the thing version controlled. In our project these would not be a source of truth, not a definition of anything so having them as part of the version controlled code would just add process without value.

contracts reuse: is “drift = contract enforcement” intuitive, or do you want a separate setting?

We do not currently use contracts so I do not have a strong opinion here.

naming: “schema locks” vs something else?

Introspected schemas maybe? I agree that these are not lock files in the traditional sense so calling them such might be confusing.

Current MVP status

The outlined changes will still require manual intervention and manually overwriting files for our project. This is no different than the current schema parquet files for us we just would not have to maintain code to generate the parquet files so that would be a plus.

My thoughts

The first thing I think of is that I would have the command be introspect, that is what it is called in database IDEs. The commands seem good, I get a little lost still in the remote vs local naming; maybe the inclusion of environments would clarify it for me. I do not really like the idea of maintaining the data types in schema files for sources or for models: the source data types are defined elsewhere and should be read in and model data types are defined within the model code. The developers having an other place to maintain the data types is another palace to introduce errors.

• Would sync part of compile? independent? What about run and build?
  • is it compile(sync->render->analyze) or is it sync -> compile(render->analyze)?
  • run(sync->render->analyze->execute)? or sync -> run(render->analyze->execute)
• I am sensitive to static analysis being baked into compile so I question any proposal to group tasks for me.
• I like the node selection aspect of the proposal
• I love the idea of the check I see that as being part of the static analysis that we would do in out CI jobs

Second thing I think of is that the assumption that we would not want a stale schema is not always valid. There are times in our development process where we want the schema to remain static regardless of changes to any actual database. I think that the developers should be able to control any refresh frequency that is implemented and it should include the ability to tun it off. If I run the analysis on my code with out of date schema definitions that is on me.

• I think that the proposed --no-auto-sync would disable it for a run, but I think there should be a config for it as well.

I would want to be able to use --defer or something like to use a specific schema definition without interfering with any local defined schemas. Our project falls into the group where not every developer has access to all of the tables, but there are some that do. Being able to use the schema definitions from a service account that has access to all of the tables, just like we can with the manifest.json, would give us the control that we need without having to manually maintain the definitions in the schema.yml files.

[lbk-fishtown]

Custom Materialization Feedback

It would be valuable if had a way to define Columns that a materialization will add to a table, while not needing to define the columns in the model itself.

An example would be:

--- product.sql
{{ config( 
      materialization = "my_custom_materialization" 
) }}

SELECT
    ProductID,
    ProductName,
    ProductCategory,
    ProductListPrice
FROM
    some_product_table

And the materialization adds a set of columns (e.g. for audit purposes) such that we have columns derived from the Model & from the Materialization.

Column Name	Column Definition Source
ProductID	Model
ProductName	Model
ProductCategory	Model
ProductListPrice	Model
run_date	Materialization
job_id	Materialization
column_hash	Materialization
valid_from	Materialization
valid_to	Materialization

Ideally we would have a way to define somewhere that a materialisation with a given name will generate a set of columns, and have Fusion append those to the columns it parses out of the model on its on.

# schema.yml
# these columns will be inherited by models using this materilization type

materializations:
    - name: my_custom_materialization
      columns:
        - name: run_date
          data_type: timestamp 
        - name: job_id
          data_type: integer   
        - name: column_hash
          data_type: varchar   
        - name: valid_from
          data_type: timestamp 
        - name: valid_to
          data_type: timestamp 

Maybe in an future / ideal state this is something that materialisations can inform Fusion about ahead of time in the definition.

[graciegoheen]

Should we have source schemas for UDFs too?

There's a use case described by @eliasdefaria here:

Users sometimes have UDFs that already exist in the warehouse and are managed outside of dbt. They want Fusion to know the function signature so that models calling the UDF directly (without the {{ function() }} macro) compile correctly — without dbt building, deploying, or otherwise managing the function.

Let's say you're using UDFs in your dbt project, but you can't use dbt-managed functions because another team manages the UDFs (and that team doesn't use dbt). So, you're referencing those UDFs with a hard-coded-reference:

select
    maybe_positive_int_column,
    udf_db.udf_schema.is_positive_int(maybe_positive_int_column) as is_positive_int
from {{ ref('a_model_i_like') }}

Could there be a way to declare your function source schema definitions to inform Fusion compiler context? And then dbt would produce a lock file like:

# is_positive_int.lock.yml

version: 1
fully_qualified_identifier: udf_db.udf_schema.is_positive_int
warehouse: snowflake
arguments:
  - name: a_string
    type: string
returns:
   type: integer

[eliasdefaria]

@graciegoheen I like where your head's at. Another reason to extend this spec to support more than just source schemas is Snowflake's newly introduced user defined types. These will fully fail strict compilation, as they are custom types defined as objects in Snowflake by the user. We could extend the spec in the same way you're thinking:

# campaign_financials.lock.yml
version: 1
fully_qualified_identifier: type_db.type_schema.campaign_financials
warehouse: snowflake
definition:
      - type: object
         fields:
             - name: total_spend
                type: float
             - name: total_revenue
                type: float