Models — The SQL File as the Atom

This lesson on Models — The SQL File as the Atom is hands-on and example-driven. You will design, configure, and execute dbt models to transform raw warehouse data into clean tables and views. By mastering the config macro and the ref function, you will control whether objects build as permanent tables, transient tables, standard views, or secure views while establishing automated lineage graphs.

What You'll Be Able To Do

• Write dbt SQL models using SELECT statements, CTEs, and aggregation logic.
• Execute single dbt models via the CLI using the -m selection flag.
• Reference upstream models dynamically using the ref() macro to generate fully qualified names.
• Configure model materializations using the config() macro for permanent tables and transient tables.
• Configure standard and secure view materializations tailored to target data warehouse capabilities.
• Inspect compiled DDL logs to verify generated CREATE OR REPLACE statements across different warehouse targets.

Detailed Concept Walkthrough

1. dbt Models as Atomic Transformations

A dbt model is a standalone .sql file containing a single SELECT query that encapsulates business logic. It represents the atomic unit of data transformation inside dbt.

• Mechanism: You write standard SQL queries utilizing Common Table Expressions (CTEs), joins, and aggregations inside .sql files in your project repository.
• Under the Hood: dbt wraps your SELECT statement with boilerplated DDL at compile time, eliminating the need to write manual DDL like CREATE, DROP, or INSERT statements.
• Execution Flow: When executed, dbt connects to your target data platform, compiles the model into dialect-specific SQL, and creates the requested relational object.

-- models/stg_customers.sql
with source_data as (
    select id, first_name, last_name, email
    from raw_data.customers
)
select * from source_data

Key Takeaway: Every dbt model is fundamentally a SELECT query that dbt automatically materializes as a database object.

2. Dependency Management with the ref Function

The ref() macro replaces hardcoded database references, enabling dbt to resolve object names dynamically and build a Directed Acyclic Graph (DAG).

• Mechanism: Placing {{ ref('model_name') }} inside the FROM clause tells dbt that the current model depends on the output of an upstream model.
• Under the Hood: At compilation, dbt evaluates the ref macro and replaces it with the target database's Fully Qualified Name (FQN), structured as database.schema.object_name.
• Best Practice: Never hardcode schema and table names in downstream transformation models; rely exclusively on ref() to guarantee execution order.

-- models/fct_orders.sql
select
    orders.order_id,
    customers.first_name,
    orders.amount
from {{ ref('stg_orders') }} as orders
left join {{ ref('stg_customers') }} as customers
    on orders.customer_id = customers.id

Key Takeaway: Using ref() establishes lineage relationships and dynamically compiles to the target warehouse's fully qualified object path.

3. Table Materialization: Permanent vs Transient

Table materializations persist transformation results physically on disk, with options to toggle persistence guarantees such as Snowflake transient tables.

• Mechanism: Setting materialized='table' instructs dbt to generate physical data tables rather than views during project execution.
• Under the Hood: Setting transient=False yields standard CREATE OR REPLACE TABLE DDL, retaining full fail-safe and time-travel storage capabilities.
• Syntax Rule: Setting transient=True produces CREATE OR REPLACE TRANSIENT TABLE DDL, which avoids long-term fail-safe storage costs in platforms like Snowflake.

-- models/marts/dim_permanent_users.sql
{{ config(materialized='table', transient=False) }}

select
    user_id,
    user_name,
    created_at
from {{ ref('stg_users') }}

Key Takeaway: Table materializations write query results to disk, and the transient parameter controls data retention features on supported warehouses.

4. View Materialization: Standard vs Secure

View materializations preserve logic without copying data, with the option to enforce secure view protections to hide internal SQL definitions.

• Mechanism: Setting materialized='view' instructs dbt to save the transformation as a virtual relation that re-executes its underlying query on demand.
• Under the Hood: Setting secure=False creates a standard CREATE OR REPLACE VIEW, prioritizing optimizer pushdowns and transparency.
• Security Nuance: Setting secure=True generates CREATE OR REPLACE SECURE VIEW, hiding view definitions and internal query optimizations from unauthorized users.

-- models/marts/v_secure_payroll.sql
{{ config(materialized='view', secure=True) }}

select
    employee_id,
    masked_ssn,
    salary
from {{ ref('stg_payroll') }}

Key Takeaway: Views build lightweight virtual objects, while secure views hide internal logic and column-level optimizations from unprivileged users.

Topics Covered in Models — The SQL File as the Atom

1. Defining DBT Models (0:00 - 0:52) — Introduces dbt models as SQL or Python files housing atomic business transformations.
2. Materialization Concepts (0:52 - 1:51) — Explains how dbt maps SQL SELECT files to physical tables and views.
3. Running Single Models (1:52 - 2:08) — Demonstrates executing an individual model using the dbt run command.
4. Referencing Models with ref() (2:08 - 2:22) — Covers using the ref macro to link models and establish execution dependencies.
5. Configuring Materializations (2:22 - 3:19) — Reviews the config macro syntax for controlling materialization types and attributes.
6. Creating Permanent Tables (3:56 - 5:54) — Walks through configuring and building permanent tables with full fail-safe storage.
7. Creating Transient Tables (5:54 - 7:16) — Shows how to configure transient tables to reduce warehouse storage overhead.
8. Creating Normal Views (7:16 - 8:31) — Demonstrates materializing a model as a standard SQL view.
9. Creating Secure Views (8:31 - 9:41) — Explains configuring secure views to restrict visibility of underlying SQL definitions.
10. Resolving Fully Qualified Names (9:41 - 10:11) — Validates the compilation of ref macros into complete database.schema.object identifiers.

dbt Cheat Sheet

• dbt run -m <model_name> — Executes transformation logic for a single target model

  dbt run -m stg_customers
  

• {{ ref('model_name') }} — References an upstream model and establishes DAG dependencies

  select * from {{ ref('stg_orders') }}
  

• {{ config(materialized='table', transient=False) }} — Configures model as a permanent physical database table

  {{ config(materialized='table', transient=False) }}
  

• {{ config(materialized='table', transient=True) }} — Configures model as a cost-efficient transient table

  {{ config(materialized='table', transient=True) }}
  

• {{ config(materialized='view', secure=False) }} — Configures model as a standard database view

  {{ config(materialized='view', secure=False) }}
  

• {{ config(materialized='view', secure=True) }} — Configures model as an access-protected secure view

  {{ config(materialized='view', secure=True) }}
  

Comparison Table

Common Pitfalls

• Mistake: Hardcoding database.schema.table paths in FROM clauses. Avoid: Use the ref() macro so dbt dynamically resolves environment paths and lineage.
• Mistake: Writing CREATE or INSERT DDL statements directly in model files. Avoid: Write only pure SELECT queries and let dbt manage table creation.
• Mistake: Using warehouse-specific config flags on unsupported database platforms. Avoid: Verify your target database adapter supports options like transient or secure before compiling.

FAQs

• What happens under the hood when I use the ref() function? dbt resolves the model dependency graph to ensure proper build order, then replaces the ref call with the target database's fully qualified name (database.schema.table).
• What is the primary difference between a permanent table and a transient table in dbt? Permanent tables retain fail-safe data recovery capabilities, while transient tables omit fail-safe to lower warehouse storage costs.
• Why would I materialize a model as a secure view instead of a standard view? Secure views prevent unauthorized users from viewing the underlying SQL logic or query plan optimizations that might expose sensitive data.