Pizofreude

Posted on Feb 25

Study Notes 4.3.1 - Build the First dbt Models

#dataengineering #dezoomcamp #dbt #datamodeling

1. Introduction to dbt Models

dbt (Data Build Tool) sits on top of data platforms like BigQuery or Postgres.
Objective: Transform raw data (e.g., green and yellow taxi trip data) into structured, usable formats for BI tools.
Approach: Modular data modeling using fact tables and dimensional tables.

Process:
- Start with raw data (sources).
- Build SQL scripts (models) to clean, deduplicate, recast, and rename data.
- Apply business logic to create fact and dimensional tables.
- Aggregate data for stakeholders.

2. Anatomy of a dbt Model

Models are SQL files that contain SELECT statements.
Materialization: Determines how the model is stored in the database.
- Types of Materialization:
  1. Ephemeral: Not physically stored; exists only within other models (like a CTE).
  2. View: Materialized as a view in the database.
  3. Table: Materialized as a physical table.
  4. Incremental: Updates only new data in the table (avoids recreating the entire table).

File Structure:
- Each model is a .sql file.
- Example: model_name.sql contains a SELECT statement.
- dbt compiles the SQL into DDL/DML (e.g., CREATE TABLE AS SELECT).

3. Defining Sources

Sources: Raw data tables loaded into the database (e.g., green and yellow taxi trip data).

YAML Configuration:

Define sources in a schema.yml file.
Specify database, schema, and table names.

Example:

version: 2
sources:
  - name: taxi_trips
    database: my_database
    schema: trip_data
    tables:
      - name: green_trip_data
      - name: yellow_trip_data

Benefits:
- Abstracts the complexity of source locations.
- Allows for freshness testing to ensure data is up-to-date.

4. Using Seeds

Seeds: CSV files stored in the dbt repository (e.g., taxi_zone_lookup.csv).
Purpose: Used for small, static datasets (e.g., master data tables).
Benefits:
- Version-controlled.
- Can be documented and tested like other dbt models.
Example:
- Load taxi_zone_lookup.csv as a seed.
- Use it in transformations to enrich trip data with zone information.

5. Modular Data Modeling

Layers:
1. Staging: Clean and prepare raw data.
  - Example: stg_green_trip_data.sql and stg_yellow_trip_data.sql.
2. Core: Create fact and dimensional tables.
  - Example: fact_trips.sql and dim_zones.sql.

Ref Function:
- Use ref('model_name') to reference other dbt models.
- Automatically builds dependencies between models.
- Example:
```
SELECT * FROM {{ ref('stg_green_trip_data') }}
```

6. Macros in dbt

Macros: Reusable SQL code snippets (similar to functions in Python).
Purpose: Simplify repetitive tasks and dynamically generate SQL.

Example:

Macro to generate payment type descriptions:

{% macro get_payment_type_description(payment_type) %}
    CASE CAST({{ payment_type }} AS INTEGER)
        WHEN 1 THEN 'Credit Card'
        WHEN 2 THEN 'Cash'
        ELSE 'Unknown'
    END
{% endmacro %}

Usage:

Call the macro in a model:

SELECT {{ get_payment_type_description('payment_type') }} AS payment_description

7. Packages in dbt

Packages: Libraries of reusable macros and models.
Example: dbt_utils package provides common utilities (e.g., surrogate_key).

Installation:

Add to packages.yml:

packages:
  - package: dbt-labs/dbt_utils
    version: 0.8.0

Run dbt deps to install.

Usage:

Example: Generate a surrogate key:

SELECT {{ dbt_utils.surrogate_key(['vendor_id', 'pickup_datetime']) }} AS trip_key

8. Variables in dbt

Variables: Dynamic values that can be defined at the project level or passed during execution.

Usage:

Define in dbt_project.yml:

vars:
  payment_type_values: [1, 2, 3, 4, 5, 6]

Use in a model:

{% if var('is_test_run', true) %}
    LIMIT 100
{% endif %}

Execution:

Pass variables via CLI:

dbt run --vars '{"is_test_run": false}'

9. Building Fact and Dimensional Tables

Fact Table: Aggregates data from multiple sources.
- Example: fact_trips.sql combines green and yellow trip data.
- Union both datasets and join with dim_zones for zone information.
Dimensional Table: Provides context for fact tables.
- Example: dim_zones.sql contains zone details (e.g., borough, service zone).

10. Testing and Deployment

Testing:
- Use dbt's built-in tests (e.g., uniqueness, not null).
- Example:
```
tests:
  - unique
  - not_null
```
Deployment:
- Run dbt run to compile and execute models.
- Use dbt build to run models and tests together.
- Example:
```
dbt build --vars '{"is_test_run": false}'
```
- Best Practice to implement dev limit which can be toggle off when in production by simply setting default=false:
```
-- dbt build --select <model_name> --vars '{'is_test_run': 'false'}'
{% if var('is_test_run', default=true) %}

  limit 100

{% endif %}
```
  → faster, cheaper and can be made as macro to follow DRY ( Don’t Repeat Yourself) methodology.

11. Lineage and Dependency Management

Lineage: Visual representation of dependencies between models.
Benefits:
- Understand how data flows through the pipeline.
- Identify upstream and downstream impacts of changes.
Example:
- fact_trips depends on stg_green_trip_data, stg_yellow_trip_data, and dim_zones.

12. Best Practices

Modularity: Break down transformations into reusable models.
Documentation: Use YAML files to document sources, models, and tests.
Version Control: Store dbt projects in Git for collaboration and history.
Performance: Materialize large tables as tables (not views) for better performance.

13. Key Commands

dbt run: Compiles and runs models.
dbt test: Runs tests on models.
dbt build: Runs models and tests together.
dbt deps: Installs packages.
dbt compile: Compiles SQL without executing it.

14. Example Project Structure

dbt_project/
├── models/
│   ├── staging/
│   │   ├── stg_green_trip_data.sql
│   │   ├── stg_yellow_trip_data.sql
│   ├── core/
│   │   ├── fact_trips.sql
│   │   ├── dim_zones.sql
├── seeds/
│   ├── taxi_zone_lookup.csv
├── macros/
│   ├── get_payment_type_description.sql
├── schema.yml
├── dbt_project.yml
├── packages.yml