dbt macros are Jinja2 functions defined in `.sql` files in the `macros/` directory that can be called from any dbt model, test, or other macro. They enable code reuse — a `phase_label()` macro that converts over number to powerplay/middle/death can be called from every model that needs this logic instead of duplicating the CASE statement. Advanced macros accept arguments, call other macros, query the database at compile time using `run_query()`, and generate dynamic SQL based on metadata — enabling generic models that adapt to different tables without hardcoded column names.
dbt Hooks are SQL statements or macro calls that execute at specific points in the dbt model lifecycle: `pre-hook` runs before a model executes, `post-hook` runs after successful completion. Common hook patterns include granting permissions to BI tool service accounts, applying governance tags, and updating an audit log with the completion timestamp. Hooks can be defined at the model level in the config block or globally in `dbt_project.yml` to apply to all models matching a path pattern, eliminating repetitive config blocks across model files.
Analogy🏏Cricket
🏏 Think of it like cricket: OLTP is the IPL's live ticketing counter — it handles thousands of simultaneous seat reservations, each requiring a precise single-seat record update with immediate confirmation. Speed per transaction and data consistency under concurrent updates are everything. OLAP is the IPL's season statistics department — it runs complex analytical queries across every ball bowled in every match of every season to produce the published rankings, economy rates, and historical comparisons. No one books a seat through the statistics department, and no broadcaster calls the ticketing counter for Bumrah's career economy rate. The two workloads demand completely different systems. Just as the ticketing counter is built for speed and correctness on one seat at a time and would buckle if asked to tally a decade of attendance mid-sale, an OLTP row-store excels at single-record writes but chokes on full-table aggregation; and just as the statistics department pores over millions of past deliveries but would be hopeless at booking a live seat under contention, the OLAP columnar engine sweeps billions of rows yet is the wrong tool for a fast single-row update. The physical design of each — row-oriented for the counter, columnar for the stats desk — is what makes it superb at its own job and unfit for the other's.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.