Declare data source with alias

[DZlAmir]

Hello,I am trying to declare a data source in Dataform, but I am having issue to add aliases to those declares in the sources.js file

since I have two tables with the same name but in different databases, in the SQLX still confusion between the two since they have the same name

Is there a way to add aliases to the declaration

[kolban · Accepted solution]

When you declare a data source, the identity of the data source is given by the combination of database (usually a GCP project), a schema (usually a BigQuery Dataset) and a name (usually a BigQuery table or view).  This "triple" is the unambiguous identity of the data source.  To reference a data source, you can use the ref() function (see: here).  When you refer to a data source, you need only specify the minimum parameters such that the identity is unambiguous.  For example, if there is only one data source with the name "XYZ" then you need only specify "XYZ" to locate the data source.  However, if the name is not unique ... i.e. it exists in multiple distinct schemas, then you need to additionally supply the "schema" identity in the reference to disambiguate.

Thus, instead of coding: ${ref("XYZ")} you may have to code ${ref("<Schema>", "XYZ")}

You asked about creating an alias.  There is no mechanism, to do that in a declaration ... however ... lets think through what you are actually looking for.  I am imagining that you have two declarations for two distinct data sources ... both with the same name (eg. XYZ) but distinct schemas.  This would mean that you can no longer simply codef ${ref("XYZ")} as that would be ambiguous.  What you "could" do is create some more JavaScript that contains a mapping from a name to a fully qualified entry ... something like:

datasources = {
  "XYZ": {
    "name": "XYZ", "schema": "schema1"
  },
  "ABC": {
    "name": "XYZ", "schema": "schema2"
  }
}

function myRef(alias) {
  return ref(datasources[alias].schema, datasources[alias].name);
}

I haven't tried the above but in theory it should work.  You might have to tweak for error handling etc.

[kolban · Accepted solution]

When you declare a data source, the identity of the data source is given by the combination of database (usually a GCP project), a schema (usually a BigQuery Dataset) and a name (usually a BigQuery table or view).  This "triple" is the unambiguous identity of the data source.  To reference a data source, you can use the ref() function (see: here).  When you refer to a data source, you need only specify the minimum parameters such that the identity is unambiguous.  For example, if there is only one data source with the name "XYZ" then you need only specify "XYZ" to locate the data source.  However, if the name is not unique ... i.e. it exists in multiple distinct schemas, then you need to additionally supply the "schema" identity in the reference to disambiguate.

Thus, instead of coding: ${ref("XYZ")} you may have to code ${ref("<Schema>", "XYZ")}

You asked about creating an alias.  There is no mechanism, to do that in a declaration ... however ... lets think through what you are actually looking for.  I am imagining that you have two declarations for two distinct data sources ... both with the same name (eg. XYZ) but distinct schemas.  This would mean that you can no longer simply codef ${ref("XYZ")} as that would be ambiguous.  What you "could" do is create some more JavaScript that contains a mapping from a name to a fully qualified entry ... something like:

datasources = {
  "XYZ": {
    "name": "XYZ", "schema": "schema1"
  },
  "ABC": {
    "name": "XYZ", "schema": "schema2"
  }
}

function myRef(alias) {
  return ref(datasources[alias].schema, datasources[alias].name);
}

I haven't tried the above but in theory it should work.  You might have to tweak for error handling etc.

[DZlAmir · Accepted solution]

Thank you a lot with your answer I found a way to reference without the confusion as you can see below

FROM ${ref('schema_name','source_name')}

[JOHN_MUKHWANA]

FROM ${ref('schema_name','source_name')