dbt Conceptual

The reasoning

Three steps, and it is worth naming all three. First dbt parses the whole project and renders the Jinja: `{{ ref(...) }}` becomes a fully-qualified relation, `{% if %}` picks a branch, macros expand. The output is a plain SQL SELECT with no Jinja left in it, and it is written to `target/compiled/`.

Second, the materialization wraps that SELECT. This is the part people leave out. Your file never said CREATE — the `table` materialization is itself a macro that emits `create table ... as (<your select>)`, and it writes that to `target/run/`. Different materializations wrap the identical SELECT in completely different statements, which is why the same model can be a view, a table or an incremental merge without one character of the SELECT changing.

Third, dbt opens a connection to the warehouse and executes that statement. That is the whole of it. dbt has no execution engine, holds no data in memory, and does not stream rows anywhere — the warehouse does one hundred percent of the computation. This is why 'dbt is slow' is almost always 'my warehouse is doing something expensive', and why the fix is nearly always in the SQL rather than in dbt.

The practical payoff is knowing which file to open. If the numbers are wrong, read `target/compiled/` — that is your logic with the Jinja resolved. If the object in the warehouse is the wrong shape, or the run failed with a database error, read `target/run/` — that is the statement the warehouse actually received.

The reasoning

dbt only ever issues SQL against one connection: the warehouse in your profile. It has no source connectors, no scheduler for extraction and no way to hold a row in memory. If the data is not somewhere the warehouse can already query, dbt has nothing to select from.

The nuance worth adding, because interviewers like it, is that the boundary is fuzzier than the slogan. You can point a dbt source at an external table or a stage that the warehouse already knows how to read, and then dbt genuinely does select from S3 — but it is the warehouse doing the reading, not dbt. Similarly a `pre_hook` running a COPY command will load data, and people do it, but you are using dbt as a place to keep a string rather than as a loader: there is no incremental state, no retry semantics and no lineage on it.

The reason this matters beyond trivia is the failure mode. Treating dbt as an ingestion tool means the load and the transform share one run, so a source outage fails your marts, and a transform bug means re-extracting. Keeping the boundary means the raw layer is an immutable record you can rebuild from, which is the single thing that makes backfills survivable.

The honest one-line version: extract and load put rows in the warehouse; dbt turns those rows into models the business can use, in the warehouse, in SQL.

The reasoning

`dbt compile` is the debugging tool. It parses the project, resolves every ref and renders every macro, then writes the result to `target/compiled/` and stops. Nothing is created or dropped. When a number is wrong, this is the file to read — it is your logic with all the Jinja illusions removed, and you can paste it straight into a warehouse console and pick it apart.

`dbt run` does the same compile and then executes the materialized statement. It runs models only: no tests, no seeds, no snapshots.

`dbt build` is the one to use in production and the one candidates most often cannot distinguish. It runs models, tests, snapshots and seeds interleaved in DAG order, and the interleaving is the whole point: a model's tests run immediately after that model is built, and if they fail, everything downstream of it is skipped rather than built on top of data you have just proved is wrong. `dbt run && dbt test` gives you the opposite — the entire project is built first, so bad data has already propagated to your marts by the time anything fails.

The other reason to know compile exists: it is how you inspect an incremental model's second-run branch, an ephemeral model that has no warehouse object at all, and a macro whose output you cannot otherwise see.

The reasoning

Start by drawing the line: dbt Core is the open-source Python package that compiles and runs your project. dbt Cloud is a managed platform that runs that same package for you. No model compiles differently, no materialization behaves differently, and a project moves between them without a code change.

What Cloud adds is everything you would otherwise build. A scheduler with a UI, so you are not maintaining Airflow DAGs whose only job is to call dbt. A browser IDE with a compile preview and lineage, which is what makes Cloud popular with analysts who do not want a local Python environment. CI jobs wired to pull requests, including slim CI against deferred state. Hosted documentation that is always current. Alerting, run history and log retention. At the enterprise tier, RBAC, audit logs, the semantic layer and cross-project refs.

What you give up is control and money. Pricing is per developer seat plus consumption, and it becomes real at team size. Airflow-centric shops often prefer Core because their orchestration, secrets and observability already exist and adding a second scheduler means two places to look when something fails at 3am. The honest answer names that tension rather than picking a side.

The version worth saying out loud: if your bottleneck is orchestration and analyst onboarding, Cloud removes real work. If you already have a mature platform team, Cloud mostly duplicates it, and the money is better spent on warehouse compute.

The reasoning

Airflow knows nothing about your SQL. If you write transformations as Airflow tasks, you declare every dependency by hand, you write the CREATE TABLE yourself, you have no lineage beyond the task graph you typed, and testing is whatever you build. That is the world dbt was built for.

dbt gives you four things Airflow does not: a dependency graph derived from your code rather than declared alongside it, so a ref you add is a dependency you get; materializations, so changing a model from a view to an incremental table is a config line rather than a rewrite; a testing framework that runs in the same graph as the models; and documentation and lineage generated from what actually ran.

What it does not give you is scheduling of anything that is not a model, cross-system orchestration, retries with backoff across heterogeneous tasks, sensors, or dependency on an external event. If your pipeline is 'wait for the vendor file, load it, transform it, refresh the BI extract, notify the team', dbt owns exactly one of those five steps.

The composition question that always follows is granularity: one Airflow task that shells out to `dbt build`, or one task per model. One task is simple and keeps dbt's DAG authoritative, but a failure gives you a coarse retry and Airflow's UI shows a single box. Per-model tasks give per-model retries and visibility at the cost of a generated DAG that has to stay in step with the manifest.

The reasoning

The environment job first. `ref` resolves against the target in your profile, so the same code compiles to your personal dev schema locally, to a temporary CI schema in a pull request, and to production in production. Hardcode `analytics.main.stg_orders` and your dev run silently reads production data, which is at best confusing and at worst a compliance problem — and if you write to a hardcoded name, your dev run overwrites the production table.

The ordering job is the one people forget, and it is the one that produces the confusing failure. dbt builds its DAG by collecting the ref calls it finds while parsing, and nothing else. A hardcoded name is invisible to the parser, so dbt does not know the dependency exists and is free to build your model before the thing it selects from. On a fresh run that fails with a missing relation; on an existing project it is worse, because the model builds successfully against yesterday's version of the upstream table and returns stale numbers with no error at all.

Two smaller consequences worth mentioning if you have room: lineage in the docs and in `dbt ls` is derived from the same graph, so hardcoded dependencies are missing from every impact analysis you ever do; and selection syntax stops working, so `dbt build --select +my_model` will not build the upstream model it actually needs.

The compiled output makes it concrete: one function call in the source becomes a fully-qualified, quoted, environment-correct relation in the compiled SQL, and dbt built the upstream model first without being asked.

The reasoning

The rule is mechanical: if dbt built it, ref it. If something else put it there — Fivetran, a COPY, an external table, another team — declare it as a source and use `source()`. Sources are the roots of the DAG; every model traces back to one.

What declaration buys you starts with freshness. A source with a `loaded_at_field` and `warn_after`/`error_after` thresholds gives you `dbt source freshness`, which turns 'the dashboard looks stale' into a check you run in seconds and can put in front of the pipeline so you fail before building on old data.

Then testing. You can attach `not_null`, `unique` and `accepted_values` to source columns, which is where they belong: catching a broken vendor feed at the boundary is far cheaper than discovering it three marts downstream. You also get one indirection point — when the loader moves `shopify.orders` to `shopify_v2.orders`, you change one YAML block instead of grepping fifty models.

Finally lineage and documentation. Sources appear in the DAG and the docs as green nodes with the system name attached, so 'what breaks if Shopify changes' is a selector — `dbt ls --select source:shopify+` — rather than an afternoon.

The reasoning

There is no dependencies file, no `depends_on` key you are expected to maintain, and nothing in `dbt_project.yml` that orders models. dbt parses the project, renders enough of each model's Jinja to find its ref and source calls, and builds a directed graph from those calls. The graph is stored in `manifest.json` and is what every selector, the docs lineage view and the build order all read from.

The consequence that matters is that the graph cannot drift from the code, because it *is* the code. There is no way to add a dependency and forget to declare it, and no way to remove a model and leave a stale edge behind. That is the property Airflow does not have, where the task graph and the SQL are two artifacts a human keeps in step.

The escape hatch exists and is worth knowing: `{{ config(depends_on=...) }}` does not, but `-- depends_on: {{ ref('x') }}` in a comment does, because dbt looks for ref calls in the rendered text regardless of whether the result is used. You need it when a macro references a relation dynamically — the classic case being a model that reads `{{ this }}` or builds a relation name from a variable, where the parser has nothing to see.

Selection syntax is the practical face of the graph. `stg_orders+` is everything downstream, `+tier_revenue` everything upstream, and both are answered from the same derived edges.

The reasoning

`this` is a Relation object for the model being built: database, schema and identifier for the current target. You can use its parts — `{{ this.schema }}`, `{{ this.identifier }}` — and it stringifies to the fully-qualified quoted name. In dev it points at your dev schema, in production at production, exactly like ref.

The reason it exists is that ref creates a graph edge, and an edge from a model to itself is a cycle. dbt detects cycles at parse time and refuses to run, so `{{ ref('fct_orders') }}` inside `fct_orders.sql` is a hard error. `this` is deliberately outside the graph: it names a relation without asserting a dependency.

That is exactly what an incremental model needs — `where updated_at > (select max(updated_at) from {{ this }})` reads the table it is about to write. It is also what post-hooks use (`grant select on {{ this }}`), and what snapshot and audit macros use.

One trap goes with it. On the very first run the relation does not exist, so a bare reference to `{{ this }}` outside an `is_incremental()` guard fails with a missing-relation error. `is_incremental()` is false when the relation does not exist precisely so that branch never compiles on the first run — which is why the guard is not optional decoration.

The reasoning

`view` issues `create view ... as (select ...)`. Nothing is stored, so building is nearly free and always current, and every query against it re-runs your logic. `table` issues `create table ... as (select ...)`, replacing the previous one wholesale, so building costs a full recompute and querying is cheap.

`incremental` is the interesting one. On the first run it behaves like `table` and creates the relation. On later runs it does not recreate anything — it builds your select into a temporary relation and then merges, deletes-and-inserts, or appends into the existing table depending on the strategy. That is what makes it cheap and what makes it capable of being subtly wrong.

`ephemeral` writes nothing at all. dbt lifts the model's SQL into a CTE at the top of every model that refs it. Useful for a small piece of logic used by two or three models that nobody needs to query directly; painful to debug, because there is no relation to select from and errors surface inside the consumer's compiled SQL.

Most adapters add `materialized_view`, which hands ongoing refresh to the warehouse rather than to dbt, and Snowflake/BigQuery/Databricks each add their own. Worth naming to show you know the list is adapter-extensible rather than fixed at four.

One detail visible in the generated DDL that surprises people: dbt builds into a temporary relation and swaps, rather than dropping the live object first. That is deliberate — a failed build leaves the previous version in place instead of a hole where your table used to be.

The reasoning

An ephemeral model has no relation. It does not appear in the run summary, you cannot select from it in a console, and `dbt run --select int_shipped` builds nothing. What dbt does instead is textual: every model that refs it gets a `with __dbt__cte__int_shipped as (...)` prepended, and the ref is replaced by that CTE name.

The upside is that you avoid cluttering the warehouse with intermediate objects nobody queries, and you avoid the cost of materializing something used once. It suits a short piece of shared logic — a filter, a rename, a light join — sitting between staging and a mart.

The costs are real and are what interviewers probe. You cannot inspect it: when the numbers are wrong, there is no table to query, and you debug by reading the consumer's compiled SQL. Errors point at the consumer, not at the ephemeral model, which makes them confusing. And if five models ref it, that SQL is executed five times rather than once — an ephemeral model doing expensive work is a multiplier, not a saving.

Two practical rules: never make something ephemeral if more than a handful of models use it, and never make something ephemeral if you would want to test it — tests need a relation to run against, so a test on an ephemeral model has nothing to select from.

The reasoning

A view stores no rows, so building the whole staging layer is close to free and it is always in step with the source. But the logic runs on every read, and views stack: a mart selecting from a view that selects from a view is one query with all three bodies inlined, and warehouse optimizers stop coping somewhere around three or four levels of nesting or the first window function.

A table pays the full compute once per run and then reads are a scan. That is the right trade when a model is read many times between builds — every dashboard query, every downstream model, every ad-hoc analysis — or when it is expensive enough that you do not want it recomputed on demand.

The rule I actually apply: staging is views, because it is thin work (rename, cast, light filter) that runs at most once per downstream model and benefits from always being current. Intermediate models are views unless they are expensive or reused widely. Marts are tables, because they are the read-heavy end and their cost profile is many reads per build. Then override on evidence — a staging view over a billion-row source that four marts each scan is a table.

Two second-order points worth having ready. Views push the cost onto whoever queries them, which is your BI tool and your analysts, and that cost is invisible in the dbt run time — a project that 'runs in two minutes' can be very expensive. And a table is a consistency boundary: everything reading it sees the same snapshot between runs, where a view can return different numbers to two dashboards loaded a minute apart.

The reasoning

`is_incremental()` is a macro that returns true only when all of these hold: the model is materialized incremental, the target relation already exists, and the run is not a `--full-refresh`. On a fresh environment the second condition fails, so it returns false.

That is why the `{% if is_incremental() %}` guard is not optional. The whole point is that the filter — `where updated_at > (select max(updated_at) from {{ this }})` — references a relation that does not exist yet. Without the guard, the first run fails with a missing-relation error. With it, the first run compiles to a plain select over everything and creates the table.

The consequence for CI is the one interviewers push on. A pull-request run in a clean schema always takes the full-refresh path, so it exercises the branch your production run does *not* take. Your incremental filter logic is therefore untested by CI unless you deliberately run twice or use `--defer` against production state — which is exactly why incremental bugs reach production.

The other two ways to make it false are worth naming: `--full-refresh` on the command line, and the model not actually being incremental at all — a config typo that silently makes it a table means your model quietly rebuilds in full every night and nobody notices except the bill.

The reasoning

Before building, dbt looks up the existing relation in the warehouse and compares its type to the one the materialization wants. When they disagree it drops the old object and creates the new one. You do not need a manual migration, and this is why changing a materialization is genuinely a one-line change.

What is not free is what hangs off the object. Dropping and recreating loses grants unless you have dbt manage them with the `grants` config or a post-hook, and on some warehouses it also loses things like row access policies, masking policies, comments not managed by `persist_docs`, and any dependent views created outside dbt — which may fail or be invalidated.

There is also a visibility window. dbt builds a table into a temporary relation and swaps atomically where the adapter supports it, but a type change means an actual drop, so consumers querying at that moment can see the object disappear. On a heavily-read mart it is worth doing during a maintenance window or with a rename dance rather than at 9am.

The same logic applies going the other way, and to incremental: switching a table to incremental keeps the existing rows and starts merging into them, which sounds convenient and is a trap — the existing rows were built by different logic, so the first incremental run leaves you with a table that is half old-logic and half new. When you change an incremental model's logic, `--full-refresh` once.

The reasoning

The precedence order, weakest to strongest: `dbt_project.yml` (and within it, less specific paths lose to more specific ones), then the resource's YAML `config:` block, then the `{{ config() }}` call inside the model file. Command-line flags such as `--full-refresh` and `--vars` override at run time on top of all of it.

Within `dbt_project.yml` the same specificity idea applies down the folder tree: a setting on `models: shop:` is beaten by one on `models: shop: marts:` which is beaten by one naming the model directly. This is what makes the pattern of setting `+materialized: view` at the top and `+materialized: table` on `marts/` work.

Most configs override. A few merge instead, and knowing which is the senior half of the answer: `tags`, `meta`, `pre_hook`/`post_hook` and `grants` accumulate across levels rather than replacing, so a tag set at the folder level and another in the model file gives you both. That is usually what you want and occasionally a surprise.

The practical convention worth stating: put defaults in `dbt_project.yml` so a whole layer behaves consistently, and reserve the in-model `config()` block for things that are genuinely a property of that one model — the incremental strategy, its unique key, a partition column. Scattering materializations across all three files is how a project reaches the state where nobody can predict what a run will do.

The reasoning

`dbt_project.yml` names a profile; `profiles.yml` defines that profile with a set of targets — typically `dev`, `ci` and `prod` — each carrying host, database, schema, threads and credentials. `dbt run --target prod` selects one. Nothing about the environment lives in a model, which is what makes ref and source resolve differently without your SQL knowing.

The convention that makes dev safe is a per-developer schema: each engineer's dev target writes to `dbt_<name>`, so two people can build the same model simultaneously without colliding, and nobody can overwrite a production table by forgetting a flag. CI gets a schema derived from the pull request number and drops it afterwards.

Credentials belong in environment variables, not in the file: `password: "{{ env_var('DBT_PASSWORD') }}"` keeps secrets out of git and out of developer laptops. In dbt Cloud this is managed for you through environment settings, which is one of the concrete things you buy.

Two details that come up as follow-ups. `{{ target.name }}` is available in Jinja, so you can make a model behave differently in dev — the common use is `{% if target.name == 'dev' %} limit 1000 {% endif %}` to keep local runs cheap, and it is a legitimate pattern as long as it cannot change production results. And `generate_schema_name` is the macro that decides the final schema; by default dbt *concatenates* the target schema and any custom schema, which is why marts land in `dbt_vijay_marts` rather than `marts` and why most teams override it.

The reasoning

`dbt seed` reads CSV files from `seeds/` and inserts them into the warehouse. dbt reads the whole file and generates INSERT statements, so it is slow, memory-hungry and completely unsuited to volume. The files are tracked in git, which is the actual point: a seed is data that belongs to the code.

That gives you the right use cases. Lookup tables that a human maintains and a reviewer should see change — currency codes, a mapping from raw status strings to business ones, a list of internal accounts to exclude, revenue targets by quarter. These are small, they change through a pull request, and having them in git means a change to a mapping shows up in the same diff as the model that uses it.

The wrong uses all share one property: the data does not belong to the codebase. A daily export is operational data — it changes without a commit, it is too big, and putting it in git means a repository that grows forever and a diff nobody can review. That is a loader's job, and dbt should see the loaded table through a `source`.

Two practical notes. Seeds are refed like models — `{{ ref('country_codes') }}` — so they sit in the DAG properly. And dbt infers column types from the CSV, which will eventually guess wrong on a zero-padded code or a date format; pin them with `column_types` in the seed's YAML rather than discovering it in a mart.

The reasoning

`manifest.json` is the complete parsed project: every model, test, source, snapshot and macro, with its config, its raw and compiled SQL, its checksum and its edges. It is produced by any command that parses the project, and it is the input to everything clever dbt does afterwards — the docs site, `dbt ls`, and state selection.

`run_results.json` is written after an execution and describes what happened: per-node status (`success`, `error`, `skipped`, `fail`), execution time, rows affected where the adapter reports it, and the message on failure. That is your source for durations, and it is what `--select result:error` reads to retry just the failures.

Alongside them: `target/compiled/` and `target/run/` with the SQL, which is what you open when debugging; `graph.gpickle`, the serialized DAG; and `catalog.json` from `dbt docs generate`, which holds warehouse metadata — column types, row counts, sizes — collected by querying the information schema rather than by parsing.

The reason this is an interview question rather than trivia is state comparison. Slim CI works by storing a production `manifest.json` somewhere and running `dbt build --select state:modified+ --defer --state path/to/manifest`. dbt compares each node's checksum and config against the stored manifest and builds only what changed plus its children. No artifacts, no slim CI — which is why 'where do you store the manifest' is the next question.

The reasoning

A dbt package is just a dbt project. `dbt deps` fetches it — from the hub, git or a local path — into `dbt_packages/`, and from then on its macros are callable as `{{ dbt_utils.star(...) }}`, its generic tests are usable in your YAML, and any models it defines are part of *your* DAG and will be built by your runs. That last part surprises people: installing a package like `dbt_project_evaluator` or a source package adds real models to your project.

Version pinning matters more than in most ecosystems because a package's macros are executed inside your compilation. `version: [">=1.0.0", "<2.0.0"]` is the usual form; `revision:` for git. Never leave it unpinned — a macro changing shape upstream breaks your compile, not your runtime, and it breaks it for everyone at once.

What to check before adding: does it support your adapter, since cross-database macros rely on `adapter.dispatch` and coverage varies; how much of it will you actually use, because a package for one macro is a dependency for one macro; and is it maintained. The usual shortlist is `dbt_utils` for the cross-database helpers and extra generic tests, `dbt_expectations` for a wider assertion vocabulary, `codegen` for scaffolding, and `audit_helper` for comparing two relations during a migration.

The two macros people cite in interviews: `dbt_utils.star(from=ref('x'), except=['a','b'])` to select all-but-some columns, and `dbt_utils.generate_surrogate_key([...])` for hashed keys — which is worth using rather than hand-rolling because it handles nulls and type coercion consistently across adapters, and inconsistent surrogate keys are a genuinely nasty bug.

The reasoning

The 3% gap is structural, not a bug: the same metric is defined twice, in two languages, by two teams. Pre-aggregating it into a mart does not fix it either — it fixes one grain, and the moment someone needs revenue by region-and-month rather than by month you get another mart with another definition.

dbt's semantic layer separates the two halves. You define semantic models over your marts — entities, dimensions and measures — and then metrics on top of them in YAML. MetricFlow compiles a request for 'revenue by region for last quarter' into SQL against those models at query time. The definition lives once, in git, reviewed like code, and Looker, a notebook and an API client all get the same number because they all ask the same service.

The costs are honest ones. It is another layer with its own concepts to learn and its own failure modes. Query performance is now MetricFlow's generated SQL rather than your hand-tuned mart, so you trade control for consistency. The pieces you would actually use it for — the API, caching — are the paid tier, so the business case has to be real. And your BI tool has to speak to it, or you have gained nothing.

The pragmatic middle ground worth mentioning, because most teams are here: define metrics once in a dbt model that BI reads and forbid metric logic in the BI layer by convention. It gets you most of the consistency with none of the new machinery, and it fails the moment somebody needs a grain you did not build.