Specify schema for duplicate table dependencies.

[DasOfChrist]

Hello, apologies for any error, this is my first question.

I have a table table_overview with a dependency to table_tickets. Now table_tickets is present in two different schema so we essentially have service_one.table_tickets and service_two.table_tickets.

Hence I get the error:

Ambiguous Action name: {"name":"table_tickets","includeDependentAssertions":false}. Did you mean one of: service_one.table_tickets, service_two.table_tickets.

I tried adding the dependency with the following combinations but none of them worked:

1. "table_tickets"
2. "service_one.table_tickets"
3. "sources.services_one.table_tickets" (because that is the folder structure to refer to the table)

Thanks in advance.

[NorieRam · Accepted solution]

Hi @DasOfChrist,

Welcome to Google Cloud Community!

You're encountering this error because Dataform needs a clear and unambiguous way to identify which table_tickets you're referring to. Since you have two tables with the same name in different schemas, you need to specify the full path to the table.

To fix this, use the ref function within your SQL query to explicitly reference the tables you need. This ensures Dataform correctly identifies the dependencies. Here’s a sample:

config { 
  type: "table", 
  schema: "adp", 
  name: "overview", 
  description: "Query loading data...", 
  tags: ["daily", "looker"], 
  dependencies: ["transactions"], 
  columns: { locations: "...", }, 
}

-- Rest of the SQL

SELECT ...
FROM ${ref("service_one", "table_tickets")}
UNION ALL
SELECT ...
FROM ${ref("service_two", "table_tickets")}

You can also consider some important considerations:

• Folder Structure: The folder structure in your Dataform project doesn't directly influence how dependencies are resolved. Dataform relies on the schema and table names within the SQLX files.
• Data Consistency: If you're using both service_one.table_tickets and service_two.table_tickets in your overview table, ensure that the data structure and relevant columns are compatible for the UNION operation.

I hope the above information is helpful.

[mars124]

What does your sqlx look like?

[DasOfChrist]

config { type: "table", schema: "adp", name: "overview", description: "Query loading data...", tags: ["daily", "looker"], dependencies: ["transactions", "table_tickets"], columns: { locations: "...", }, } -- Rest of the SQL

[DasOfChrist]

config { 
  type: "table", 
  schema: "adp", 
  name: "overview", 
  description: "Query loading data...", 
  tags: ["daily", "looker"], 
  dependencies: ["transactions", "table_tickets"], 
  columns: { locations: "...", }, 
}

-- Rest of the SQL

[NorieRam · Accepted solution]

Hi @DasOfChrist,

Welcome to Google Cloud Community!

You're encountering this error because Dataform needs a clear and unambiguous way to identify which table_tickets you're referring to. Since you have two tables with the same name in different schemas, you need to specify the full path to the table.

To fix this, use the ref function within your SQL query to explicitly reference the tables you need. This ensures Dataform correctly identifies the dependencies. Here’s a sample:

config { 
  type: "table", 
  schema: "adp", 
  name: "overview", 
  description: "Query loading data...", 
  tags: ["daily", "looker"], 
  dependencies: ["transactions"], 
  columns: { locations: "...", }, 
}

-- Rest of the SQL

SELECT ...
FROM ${ref("service_one", "table_tickets")}
UNION ALL
SELECT ...
FROM ${ref("service_two", "table_tickets")}

You can also consider some important considerations:

• Folder Structure: The folder structure in your Dataform project doesn't directly influence how dependencies are resolved. Dataform relies on the schema and table names within the SQLX files.
• Data Consistency: If you're using both service_one.table_tickets and service_two.table_tickets in your overview table, ensure that the data structure and relevant columns are compatible for the UNION operation.

I hope the above information is helpful.

[DasOfChrist]

Thanks @NorieRam for a solution. The ref function would automatically add it as a dependency to the table creation and solves my dependency issue.

But I think being able to state it as a dependency in the config section helps other developers when they need to work on my table. This will be purely a readability benefit but could it be considered as a feature for some future release? Sorry if I'm misunderstanding something here.

[NorieRam · Accepted solution]

@DasOfChrist You're right, explicitly listing dependencies in the config block would enhance readability and make the table easier to maintain. It offers a high-level overview of relationships, which is especially useful for onboarding new developers or debugging.

Although the ref() function ensures dependencies are correctly resolved, explicitly documenting them in the config section would make dependencies more transparent. This could prevent confusion and align with best practices for collaborative development.

Since this would be more of a usability enhancement than a bug fix, I recommend submitting a feature request through the Dataform issue tracker. While I can’t guarantee a timeline for implementation, this is a great idea for improving developer experience. You can stay updated on progress via the issue tracker or Dataform release notes.

[DasOfChrist]

Thanks for the suggestion and encouragement @NorieRam I created a feature request.