Verify Macro Variable Resolution

Oct 13, 2025 by pynkocean.com 84 views

Understanding the Issue

When running unit tests using SQLMesh, it's essential to ensure that macro variables are respected. However, in this scenario, we're experiencing an issue where unit tests do not respect macro variables, leading to unexpected behavior and errors. In this article, we'll delve into the problem, explore possible causes, and provide a solution to resolve the issue.

Problem Description

We're using SQLMesh version 0.164.0 and have a test of the form:

test_claim_model:
    model: "{{ var('silver') }}.client_x.claim"
    inputs:
      "BRONZE.client_x.CLAIM":
        file: tests/silver/client_x/data/bronze_claim.csv
      "@{SILVER}.client_x.INSURANCE_PLAN":
        file: tests/silver/client_x/data/silver_insurance_plan.csv
    outputs:
      query:
        file: tests/silver/client_x/data/silver_claim.csv

The silver variable is well-defined in our config.py file, and we can verify this by checking the database parameterization on a variable such as silver in every single model. Moreover, sqlmesh plan and sqlmesh run have worked fine.

However, when running sqlmesh test, we consistently get the following error:

[WARNING] Model '"NONE"."client_x"."CLAIM"' was not found at /Users/gesus/Repos/core/adonis-core/adonis_sqlmesh/integrations/tests/silver/client_x/test_claim.yaml

As you can see, we're following the SQLMesh documentation and even mixing different modalities of using variables.

Possible Causes

There are several possible causes for this issue:

Macro Variable Resolution: SQLMesh might not be resolving the macro variable correctly, leading to an incorrect model name.
Model Name Resolution: The model name might not be resolved correctly, resulting in an incorrect model being used.
Variable Scope: The variable scope might be incorrect, leading to the variable not being accessible in the test.

Solution

To resolve this issue, we need to ensure that the macro variable is resolved correctly and that the model name is resolved correctly. Here are the steps to follow:

Verify Macro Variable Resolution: Verify that the macro variable is resolved correctly by checking the config.py file and ensuring that the variable is defined correctly.
Verify Model Name Resolution: Verify that the model name is resolved correctly by checking the test file and ensuring that the model name is correct.
Check Variable Scope: Check the variable scope to ensure that the variable is accessible in the test.

Code Changes

To resolve this issue, we need to make the following code changes:

Update config.py File: Update the config.py file to ensure that the macro variable is defined correctly.
Update Test File: Update the test file to ensure that the model name is correct and that the variable scope is correct.

Here's an updated version of the test file:

test_claim_model:
    model: "{{ var('silver') }}.client_x.claim"
    inputs:
      "BRONZE.client_x.CLAIM":
        file: tests/silver/client_x/data/bronze_claim.csv
      "@{SILVER}.client_x.INSURANCE_PLAN":
        file: tests/silver/client_x/data/silver_insurance_plan.csv
    outputs:
      query:
        file: tests/silver/client_x/data/silver_claim.csv
    variables:
      silver: "SILVER"

In this updated version, we've added a variables section to the test file to define the variable scope.

Conclusion

In conclusion, unit tests do not respect macro variables in SQLMesh due to incorrect macro variable resolution, model name resolution, or variable scope. To resolve this issue, we need to verify macro variable resolution, model name resolution, and variable scope. By making the necessary code changes, we can ensure that unit tests respect macro variables and run successfully.

Best Practices

To avoid this issue in the future, follow these best practices:

Verify Macro Variable Resolution: Verify that the macro variable is resolved correctly by checking the config.py file and ensuring that the variable is defined correctly.
Verify Model Name Resolution: Verify that the model name is resolved correctly by checking the test file and ensuring that the model name is correct.
Check Variable Scope: Check the variable scope to ensure that the variable is accessible in the test.

Q: What is the issue with unit tests not respecting macro variables in SQLMesh?

A: The issue is that SQLMesh is not resolving the macro variable correctly, leading to an incorrect model name. This can cause unit tests to fail or produce unexpected results.

Q: What are the possible causes of this issue?

A: There are several possible causes of this issue, including:

Macro Variable Resolution: SQLMesh might not be resolving the macro variable correctly, leading to an incorrect model name.
Model Name Resolution: The model name might not be resolved correctly, resulting in an incorrect model being used.
Variable Scope: The variable scope might be incorrect, leading to the variable not being accessible in the test.

Q: How can I verify macro variable resolution?

A: To verify macro variable resolution, you can check the config.py file and ensure that the variable is defined correctly. You can also use the sqlmesh plan command to verify that the macro variable is resolved correctly.

Q: How can I verify model name resolution?

A: To verify model name resolution, you can check the test file and ensure that the model name is correct. You can also use the sqlmesh run command to verify that the model name is resolved correctly.

Q: How can I check variable scope?

A: To check variable scope, you can check the test file and ensure that the variable is accessible in the test. You can also use the sqlmesh test command to verify that the variable is accessible in the test.

Q: What are the best practices to avoid this issue in the future?

A: To avoid this issue in the future, follow these best practices:

Verify Macro Variable Resolution: Verify that the macro variable is resolved correctly by checking the config.py file and ensuring that the variable is defined correctly.
Verify Model Name Resolution: Verify that the model name is resolved correctly by checking the test file and ensuring that the model name is correct.
Check Variable Scope: Check the variable scope to ensure that the variable is accessible in the test.

Q: What are the code changes required to resolve this issue?

A: To resolve this issue, you need to make the following code changes:

Update config.py File: Update the config.py file to ensure that the macro variable is defined correctly.
Update Test File: Update the test file to ensure that the model name is correct and that the variable scope is correct.

Here's an updated version of the test file:

test_claim_model:
    model: "{{ var('silver') }}.client_x.claim"
    inputs:
      "BRONZE.client_x.CLAIM":
        file: tests/silver/client_x/data/bronze_claim.csv
      "@{SILVER}.client_x.INSURANCE_PLAN":
        file: tests/silver/client_x/data/silver_insurance_plan.csv
    outputs:
      query:
        file: tests/silver/client_x/data/silver_claim.csv
    variables:
      silver: "SILVER"

In this updated version, we've added a variables section to the test file to define the variable scope.

Q: Can you provide an example of how to use macro variables in SQLMesh?

A: Here's an example of how to use macro variables in SQLMesh:

test_claim_model:
    model: "{{ var('silver') }}.client_x.claim"
    inputs:
      "BRONZE.client_x.CLAIM":
        file: tests/silver/client_x/data/bronze_claim.csv
      "@{SILVER}.client_x.INSURANCE_PLAN":
        file: tests/silver/client_x/data/silver_insurance_plan.csv
    outputs:
      query:
        file: tests/silver/client_x/data/silver_claim.csv
    variables:
      silver: "SILVER"

In this example, we're using the var function to define a macro variable silver and then using it in the model name.

Q: Can you provide an example of how to use model names with macro variables in SQLMesh?

A: Here's an example of how to use model names with macro variables in SQLMesh:

test_claim_model:
    model: "{{ var('silver') }}.client_x.claim"
    inputs:
      "BRONZE.client_x.CLAIM":
        file: tests/silver/client_x/data/bronze_claim.csv
      "@{SILVER}.client_x.INSURANCE_PLAN":
        file: tests/silver/client_x/data/silver_insurance_plan.csv
    outputs:
      query:
        file: tests/silver/client_x/data/silver_claim.csv
    variables:
      silver: "SILVER"

In this example, we're using the var function to define a macro variable silver and then using it in the model name.

Q: Can you provide an example of how to use variable scope in SQLMesh?

A: Here's an example of how to use variable scope in SQLMesh:

test_claim_model:
    model: "{{ var('silver') }}.client_x.claim"
    inputs:
      "BRONZE.client_x.CLAIM":
        file: tests/silver/client_x/data/bronze_claim.csv
      "@{SILVER}.client_x.INSURANCE_PLAN":
        file: tests/silver/client_x/data/silver_insurance_plan.csv
    outputs:
      query:
        file: tests/silver/client_x/data/silver_claim.csv
    variables:
      silver: "SILVER"

In this example, we're using the variables section to define the variable scope.

Conclusion

In conclusion, unit tests do not respect macro variables in SQLMesh due to incorrect macro variable resolution, model name resolution, or variable scope. To resolve this issue, we need to verify macro variable resolution, model name resolution, and variable scope. By following the best practices and making the necessary code changes, we can ensure that unit tests respect macro variables and run successfully.