DuckDB SQL Macros: The Complete Guide to Reusable SQL Functions

TL;DR: DuckDB’s CREATE MACRO lets you package recurring SQL logic into parameterized, reusable functions. One-line calls replace entire CTE blocks. This guide covers syntax, default parameters, structured returns, view comparisons, and real monetization strategies.

1. Why You Need Macros

Have you ever encountered these scenarios while writing SQL:

• Writing the same “working days calculation” logic every month, copy-pasting it 5 times
• A complex date expression scattered across 10 queries—change one, update ten
• Every team member has their own “utility CTE,” versions are inconsistent, bugs appear

The traditional solution is Views, but views have two fatal flaws:

1. No parameters—you can’t write SELECT * FROM get_weekly_report('2026-08-01')
2. Cannot compose expressions—views only return whole tables, not computed values

DuckDB’s Macros solve both problems.

2. Basic Usage: Define Once, Call Everywhere

2.1 Simplest Macro

-- Define: Calculate working days between two dates
CREATE MACRO workdays(start_date, end_date) AS
  (end_date::DATE - start_date::DATE + 1)
  - (date_part('dow', start_date::DATE) + date_part('dow', end_date::DATE));

-- Call
SELECT workdays('2026-08-10', '2026-08-17') AS result;
-- Returns: 5 (weekends excluded)

Note: A macro body is an expression, not a complete SELECT statement. This allows macros to be embedded anywhere an expression is expected.

2.2 Multi-Expression Macro

-- Define: Check if a date is a holiday (simplified)
CREATE MACRO is_holiday(date_val) AS
  CASE date_val::DATE
    WHEN '2026-01-01' THEN true
    WHEN '2026-05-01' THEN true
    WHEN '2026-10-01' THEN true
    ELSE false
  END;

-- Use in query
SELECT
  order_date,
  amount,
  is_holiday(order_date) AS is_holiday_flag
FROM orders
WHERE is_holiday(order_date);

3. Advanced: Default Parameters & Structured Returns

3.1 Default Parameter Values

-- Safe division: return default when divisor is 0
CREATE MACRO safe_divide(a, b, default_val := 0) AS
  CASE WHEN b = 0 THEN default_val ELSE a / b END;

SELECT
  safe_divide(10, 3) AS normal,    -- 3.333...
  safe_divide(10, 0) AS zero;      -- 0

Default parameters make macros flexible—call them without providing all arguments.

3.2 Structured Returns (struct)

-- Split a date into year/month/day
CREATE MACRO parse_date(d) AS
  struct_pack(
    year  := extract('year'  FROM d),
    month := extract('month' FROM d),
    day   := extract('day'   FROM d)
  );

SELECT parse_date('2026-08-13'::DATE);
-- Returns: {'year': 2026, 'month': 8, 'day': 13}

-- Destructured usage
SELECT
  (parse_date(order_date)).year  AS order_year,
  (parse_date(order_date)).month AS order_month,
  SUM(amount) AS total
FROM orders
GROUP BY order_year, order_month;

3.3 List Macros

-- Generate N consecutive dates
CREATE MACRO date_range(start, count) AS
  generate_series(start, start + INTERVAL (count - 1) DAY, INTERVAL 1 DAY);

SELECT date_range('2026-08-01', 7);
-- Returns: [2026-08-01, 2026-08-02, ..., 2026-08-07]

4. Macro vs View vs CTE: Which to Choose?

Selection rules:

• Need parameterized expressions → use macros
• Need to return a whole table referenced in multiple places → use views
• Temporary logic within a single query → use CTE
• Need cross-session persistence for complex queries → use materialized views

5. Practical: Three Macros You Can Use Today

5.1 Money Formatting Macro

CREATE MACRO fmt_money(val, currency := '¥') AS
  currency || ROUND(val, 2);

SELECT fmt_money(1234.5), fmt_money(567.8, '$');
-- Returns: ¥1234.50 | $567.80

5.2 MoM Growth Calculation Macro

CREATE MACRO calc_mom(current_val, prev_val) AS
  ROUND(100.0 * (current_val - prev_val) / NULLIF(prev_val, 0), 2);

-- Usage in analysis query
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  calc_mom(revenue, LAG(revenue) OVER (ORDER BY month)) AS mom_pct
FROM monthly_sales;

5.3 Data Quality Check Macro

CREATE MACRO check_null_ratio(table_ref, col_name) AS
  ROUND(
    100.0 * SUM(CASE WHEN {col_name} IS NULL THEN 1 ELSE 0 END)
    / COUNT(*),
    2
  );

-- Check null ratio of user_id column in orders table
SELECT check_null_ratio(orders, user_id) AS null_ratio_pct;

6. Comparison with Traditional Tools

Core advantage: Macros give you functional programming capabilities without leaving SQL.

7. Monetization: How Much Can Macros Earn?

7.1 Personal Efficiency → Time Monetization

Package your repeatedly-used SQL logic into a macro library. If you save 3 hours/week at ¥200/hour, that’s ¥31,200 extra per year.

7.2 Macro Library Productization

Package your common macros into an open-source library on GitHub, then monetize:

• GitHub Sponsors: ¥100-500/month
• Paid macro packs: Sell on Gumroad or Xiaobotang (¥99-299)
• Training courses: Record “Advanced SQL Techniques” courses, macros as core chapters

7.3 Enterprise Data Products

Build configurable report templates using macros, charge SMEs:

7.4 Technical Blog Traffic

Write macro tutorial content for duckdblab.org, attract search traffic for “DuckDB macros.” A quality tutorial can bring 500-2000 monthly visitors, converting to ¥500-5000/month in paid users.

8. Summary

DuckDB’s macro feature is severely underrated. It fills the gap between “views can’t take parameters” and “CTEs can’t cross-query reuse,” letting you write reusable, configurable computation logic in pure SQL.

Key takeaways:

1. Macros are expression-level reuse, not table-level
2. Support default parameters for flexible calling
3. Can return scalars, structs, lists, and other types
4. Better than views for parameterized scenarios, better than CTEs for cross-query reuse

Next time you find yourself copy-pasting the same SQL more than 3 times, pause and ask: can this become a macro?

📖 More DuckDB macro case studies → duckdblab.org