Code Generation Macros

Relevant source files

The following files were used as context for generating this wiki page:

• dbt_packages/codegen/macros/generate_source.sql
• dbt_packages/codegen/macros/generate_model_yaml.sql
• dbt_packages/codegen/macros/generate_base_model.sql
• dbt_packages/codegen/macros/create_base_models.sql
• dbt_packages/codegen/macros/generate_model_import_ctes.sql
• dbt_packages/codegen/macros/helpers/helpers.sql

Introduction

The Code Generation Macros in the codegen package provide a suite of automated tools designed to accelerate dbt development by scaffolding sources, base models, and documentation. These macros interface directly with the database schema and dbt's internal project graph to generate boilerplate SQL and YAML configuration, ensuring consistency and reducing manual entry errors.

Within the flext-dbt-ldif project, these utilities are leveraged to handle the transformation of LDIF data by quickly generating foundational staging layers and documentation schemas. Sources: README.md, dbt_project.yml

Source and Model YAML Generation

The system provides macros to introspect existing database schemas or dbt models to produce sources.yml and schema.yml files.

generate_source

This macro scans a specific database schema and generates a complete YAML definition for a dbt source, including table names and column data types. It uses dbt_utils.get_relations_by_pattern to discover tables. Sources: dbt_packages/codegen/macros/generate_source.sql:1-18

Parameter	Type	Description
schema_name	String	The database schema to introspect.
database_name	String	The database name (defaults to target database).
generate_columns	Boolean	If true, identifies and lists columns for each table.
include_data_types	Boolean	Includes formatted data types for columns.

Sources: dbt_packages/codegen/macros/generate_source.sql:21-25

generate_model_yaml

This macro generates documentation YAML for existing dbt models. A key feature is upstream_descriptions, which allows the macro to inherit column descriptions from parent models or sources in the dbt DAG. Sources: dbt_packages/codegen/macros/generate_model_yaml.sql:35-50

The diagram above illustrates the logic flow for generating model documentation, highlighting the optional inheritance of metadata from upstream dependencies. Sources: dbt_packages/codegen/macros/generate_model_yaml.sql:35-68

Model Scaffolding

The package includes macros to generate the SQL required for dbt models, particularly the "Base" or "Staging" layer.

generate_base_model

Generates a SQL file for a base model using a source() reference. It automatically identifies columns from the source relation and provides a renamed CTE for standardization. Sources: dbt_packages/codegen/macros/generate_base_model.sql:1-45

create_base_models

This is a wrapper macro that outputs shell commands. It specifically generates a string that invokes a bash script (base_model_creation.sh) to physically create files in the project directory for multiple tables at once. Sources: dbt_packages/codegen/macros/create_base_models.sql:5-18

SQL Refactoring Utilities

The generate_model_import_ctes macro is a sophisticated refactoring tool that converts raw SQL queries into a modular CTE-first format.

CTE Generation Logic

The macro uses regular expressions to identify references to other tables, such as:

• {{ ref(...) }}
• {{ source(...) }}
• Hardcoded table names (e.g., schema.table)
• Variable references {{ var(...) }}

It سپس replaces these in-line references with CTE names and prepends a "With" block containing all identified dependencies. Sources: dbt_packages/codegen/macros/generate_model_import_ctes.sql:38-160

The sequence shows how the macro decomposes a monolithic SQL script into a structured format following dbt best practices. Sources: dbt_packages/codegen/macros/generate_model_import_ctes.sql:165-245

Helper Functions

The system relies on internal helper macros to navigate the dbt graph and format data.

• get_model_dependencies: Iterates through graph.nodes to find parents of a given model. Sources: dbt_packages/codegen/macros/helpers/helpers.sql:2-6
• add_model_column_descriptions_to_dict: Extracts descriptions from the manifest for either sources or models. Sources: dbt_packages/codegen/macros/helpers/helpers.sql:10-25
• get_models: Filters project models by directory or prefix to allow bulk operations. Sources: dbt_packages/codegen/macros/helpers/helpers.sql:46-77

Conclusion

Code Generation Macros serve as the primary automation layer for the flext-dbt-ldif project. By providing automated discovery of database structures and standardizing the SQL structure through import CTEs and base model scaffolding, they ensure that the LDIF transformation pipeline remains maintainable and well-documented. Sources: dbt_project.yml, README.md