Inline Macro Support

[jaypeedevlin]

Describe the feature

The ability to define a macro inside a .sql file that is available only to that .sql file, like a function called inside the same .py file would work. The idea would be when you have repeated logic in a model that you want to abstract into a macro that won't be used in other models, so it doesn't feel right to have a 'single use' macro in the macros folder.

Describe alternatives you've considered

Just throwing the macro in the macros folder.

Additional context

Based on this conversation in Slack. This also seems to be supported in the dbt cloud IDE (but not via CLI)

Who will this benefit?

Likely this is for a medium to advanced dbt user.

Are you interested in contributing this feature?

If this is something within my grasp I'd definitely be up for contributing this.

[jtcohen6]

@jaypeedevlin Thanks for opening! I'm mostly for this, just want to register a few concerns that come to mind.

Pro: "Private" logic, unique to one model file, can already be expressed via most Jinja constructs within a model file (ifs, loops, etc). Sometimes, there's logic you just want to express functionally, with inputs and outputs. The ability to define a "private" macro at the top of a model, and use/reuse over the course of the file, keeps the logic close to home while also expressing it in the clearest way possible.

Con: Macros are resources in dbt. This "private" macro probably wouldn't work like other ("global") macros in a number of ways, because it wouldn't be a project resource: It couldn't be described, registered in the manifest, shown in the documentation site, or reused if imported from a package. (I say probably because anything is possible with enough code.) As long as those drawbacks don't feel surprising to you—it is a private macro, after all—then it may not be cause for concern.

In which types of resource files would you expect to be able to define and call inline macros? I'm thinking models, snapshots, analyses, data tests.

This also seems to be supported in the dbt cloud IDE (but not via CLI)

Yes! This is actually supported via the dbt RPC server, which has the ability to parse, compile, and execute an arbitrary blob of dbt-onic Jinja-SQL, via the compile_sql and run_sql methods only. It does this using the context of the project, but it doesn't actually register the new blob o' stuff within the project context. If that blob includes macros, it will parse them out here:

https://github.com/fishtown-analytics/dbt/blob/4d5d0e2150ff2776f5171eb229c367af839b9a35/core/dbt/parser/rpc.py#L50-L61

So if I've got a query, similar to the one linked in Slack:

{% macro test_func(column_name) %}
  {{ column_name }}
{% endmacro %}

SELECT
  {{ test_func('user_id') }}
FROM (
    SELECT 1 as user_id
) sbq

I can base64-encode that query, fire it off to a run_sql request, and see the results:

{
    "jsonrpc": "2.0",
    "method": "run_sql",
    "params": {
        "sql": "eyUgbWFjcm8gdGVzdF9mdW5jKGNvbHVtbl9uYW1lKSAlfQogIHt7IGNvbHVtbl9uYW1lIH19CnslIGVuZG1hY3JvICV9CgpTRUxFQ1QKICB7eyB0ZXN0X2Z1bmMoJ3VzZXJfaWQnKSB9fQpGUk9NICgKICAgIFNFTEVDVCAxIGFzIHVzZXJfaWQKKSBzYnE=",
        "name": "my_inline_macro_query"
    },
    "id": "2db9a2fe-9a39-41ef-828c-25e04dd6b07d"
}

"results": [
    {
        "logs": [],
        "raw_sql": "\n\nSELECT\n  {{ test_func('user_id') }}\nFROM (\n    SELECT 1 as user_id\n) sbq",
        "compiled_sql": "\n\nSELECT\n  \n  user_id\n\nFROM (\n    SELECT 1 as user_id\n) sbq",
        "node": {...},
        "timing": [...],
        "table": {
            "column_names": [
                "user_id"
            ],
            "rows": [
                [
                    1.0
                ]
            ]
        },
        "generated_at": "2021-05-25T12:26:22.403038Z"
    }
]

In any case, I do think the implementation of this could be tricky; we'd want to keep parse-time performance in mind. I'm open to continuing the conversation, and hearing what other folks think about the benefits, how they'd want to use this, or if there's any risk of confusing the interface to macros in dbt today.

[jaypeedevlin]

Hi Jeremy! Thanks for the thoughtful reply. A few responses:

As long as those drawbacks don't feel surprising to you—it is a private macro, after all—then it may not be cause for concern.

I didn't explicitly say it, but that would be my assumption — it would be scoped just to the file that it's contained in.

In which types of resource files would you expect to be able to define and call inline macros? I'm thinking models, snapshots, analyses, data tests

Agreed on those, I can't think of any others.

[pnadolny13]

...can already be expressed via most Jinja constructs within a model file (ifs, loops, etc). Sometimes, there's logic you just want to express functionally, with inputs and outputs. The ability to define a "private" macro at the top of a model, and use/reuse over the course of the file, keeps the logic close to home while also expressing it in the clearest way possible.

I think @jtcohen6 's message above says it all but I'd echo that I'd also like to see this as a feature - my use case is also more for code organization. If I have a macro that is very specific to a single model I would rather define it within the model file than out in a different directory. I could write the logic as plain jinja (no function organization) inside the model file but I find that hard to read so it would be nicer to have it pulled out and referenced a few times, like you'd do if you were writing it in python.

[NicholasCanova]

This would be a nice feature to have, as minor as it is on the surface. Would help with code organization.

[adamcunnington-mlg]

This would be really helpful. We have a model that needs to union 2 very similar SQL queries and I'm not sure how we could achieve this without a callable piece of jinja.

[davidborre]

On top this is quite disappointing, since if you define the macro inline on dbt cloud, it would preview it as it existed. however on the dbt run can't be find afterwards

[Luiscri]

Definitively this would be really helpful for keeping clean code and avoid code repetition on scenarios where the logic is not generic enough to be defined at project level

[fev4]

I thought this was the default behavior and just found out the requirements of the macro folder.

[noahjgreen295]

Checking in to see the status of this. This would be tremendously helpful and would increase the expressiveness and DRY-ness of the dbt language family by orders of magnitude. I would say the drawbacks mentioned do not matter to the average dbt developer. For example, in our projects, we have so many places where we have SQL duplication in our models because we don't want to cruft up the macros folder with macros that each pertain to only a single model. Fixing that is more important to us than registration in the manifest, etc.

Given the time span of this discussion, can you comment on whether you think there is a chance of it being implemented?

[dwl285]

Adding on here that I think this is a really sensible request, other competing frameworks support it, we've been waiting three years, can we at least get an update?

[sti0]

+1 for this request. This could be very useful to reduce code in a specific context where we won't make the macro available in the global context.

[johnny-lt]

+100 on this. I rarely use macros at the moment because the majority of my use cases are specific to a single model, and in this scenario the drawbacks of writing a macro in the macros folder outweigh the benefits of increased DRY-ness.