[web][feature] Use model context in POST /api/commands/fetchdf

[jonathan-ostrander]

SQLMesh Version: 0.119.0
Engine: BigQuery

Current Behavior

When using a config like the following:

Config(
    model_defaults=ModelDefaultsConfig(
        dialect="bigquery",
        cron="@daily",
    ),
    gateways={
        "production": BigQueryConnectionConfig(
            project="data-project",
            execution_project="execution-project",
        ),
    }
)

all table references without a project_id resolve to using data-project as the project_id for the query. The SQLMesh UI will suggest autocompletion for model names when writing custom SQL but this will only include the dataset and table IDs so when going to run that SQL through the UI the SQL will resolve to trying to query a dataset in the execution_project instead of the project.

Example

Using the config above the following happens

-- Raw Query in model def/UI
SELECT * FROM dataset.table;

-- Rendered Query for SQLMesh model
SELECT * FROM "data-project"."dataset"."table";

-- Rendered Query for SQLMesh web query
SELECT * FROM "execution-project"."dataset"."table";

Desired Behavior

The SQLMesh UI's queries should be rendered to match how the CLI renders model queries.

[treysp]

Hi @jonathan-ostrander - wanted to confirm that you hit this issue when writing custom SQL then clicking the Run Query button?

If so, where do you see the rendered query that uses the execution project?

[jonathan-ostrander]

Hey @treysp sorry for the late reply.

Yes it's an issue when writing custom SQL and hitting the run query button. I see the rendered query in the failed job in BigQuery.

[treysp]

@jonathan-ostrander The fetchdf command just passes raw SQL to the engine (no SQLMesh substitution or rendering), so this is expected behavior.

You're seeing execution-project in the BQ log because:

1. SQLMesh connects to the execution project because that's what's running the models
2. SQLMesh submits SELECT * FROM dataset.table; via its connection and Bigquery automatically adds the current default project to the schema.table name

[jonathan-ostrander]

Yes I understand that it's expected behavior, but wouldn't it make more sense to use the connection's data project so that the behavior matches writing SQL in models?

With the current behavior, you cannot copy SQL from a model file and run it using fetchdf.