Can you wrap/extend the ref function in an include?

[benjikj-cera]

I'd like to create a helper function that overrides ref to point to a production database for specific sources. My use case is local development: I want to pull production data for upstream models I haven't modified, while still using dev data for models I'm actively working on.

I tried defining this in an include:

function ref_prod(table) {
  return ref(table).replace('dev', 'prod');
}

But this throws ref is not defined on compile.

Is it possible to call ref from within an include, or is there another approach to selectively override the database target per source?

[kolina]

ref can accept parameters for project and scheme, see this reference

Also you can use compilation variables to write a conditional logic for value of project / schema: see this guide

[benjikj-cera]

I think this could partly solve the requirement from the database side with quite a big potential change to have to specify the database for every single model and ref in order to get the desired easy change/switch of environment for only specific sources.

A bigger problem, though, is about the switch of the dataset/schema, which uses the compilation overrides to add a suffix; in this requirement we want to effectively halt the override (for the database/project as well really) and use the non-suffixed version. The complication where the suggestion won't work is the fact that we don't just have one schema for the whole of our data model, we separate and organise them into may different schemas. So we couldn't just use one default schema, we'd want the ref to evaluate depending on the exactly source model just without any dev overrides.

I don't think what you've suggested will be configurable for this, but maybe I'm misunderstanding?

[kolina]

ref is executed in a context of a specific action, so you'll need to pass it to your custom function.

Something like this in your javascript file

function customRef(ctx, name) {
    return ctx.ref("prod", ctx.schema(), name);
}

module.exports = { customRef };

And then in your SQLX file

config {
    type: "table"
}

SELECT *
FROM ${helpers.customRef(ctx, "daily_sales")}

[benjikj-cera]

OK, thanks, I think we're getting somewhere. However, this still seems to evaluate the schema with the compilation override suffix in some way. I get a couple of weird mismatching errors:

Any ideas?

[benjikj-cera]

FYI, I've created this new discussion, which I think is a bit clearer for exactly my situation and what I am wanting to achieve.

[kolina]

Responded there