https://linen.dev logo
Join Slack
Powered by
Digging into the Transform / DBT feature. With a c...
# announcements
p
Digging into the Transform / DBT feature. With a custom tap & target (tap-mysql, target-bigquery), I'm not seeing an obvious path to connect the models; the docs seem to suggest that the model files will be generated automatically, but I'm not seeing that. (
transform/*
is pretty much empty). Any pointers here? Looking at https://gitlab.com/meltano/dbt-tap-gitlab/-/tree/master/models/base, I see a bunch of .sql files -- is it possible to autogenerate these, or do they need to be built by hand? (I'm expecting they can be autogenerated as singer taps handle schema extraction, but it's not obvious to me how to do that)
d
@paul_tiplady You're trying to transform the schema of the data you extracted out of MySQL and loaded into BigQuery, right? Presumably you want to change the names or types of some columns, or generate entirely new ones? Do you have experience with dbt already?
Meltano does not auto-generate any dbt models (SQL files) because only you know how you'd like the data to be transformed 🙂
The simplest model would just be 
SELECT * FROM source_schema.source_table
, which creates a transformed table that looks exactly like the source table. Meltano could hypothetically autogenerate those based on the names of the entities, but you'd still need to fill in any column-level transformations manually. Autogenerating these DBT models listing all column names instead of 
*
, for you to easily remove or otherwise edit, would be cool, but suffers from the same issue I described in https://gitlab.com/meltano/meltano/-/issues/2221:
Extractors output 
SCHEMA
messages for the loader to use, but how exactly those are translated to database tables and columns depends on the specific target, and this behavior is not identical between the different variants of target-postgres and target-snowflake that exist (see #2134). To know the actual database schema generated by the loader, we'd need to somehow get the loader to tell us, which is not currently supported by the Singer spec, but could be added in theory.
What you're going to want to do is add your own model files to the DBT project at 
transform/my_meltano_model
, which will be executed automatically when you run 
meltano elt
with 
--transform=run
. The names of the model files match the created tables, and inside the model files you'd reference the "raw" extracted+loaded tables.
You'd also want to add a profile to 
transform/profile/profiles.yml
with the same name as your loader's 
namespace
, presumably 
bigquery
, so that DBT will know how to connect to your warehouse: https://docs.getdbt.com/reference/warehouse-profiles/bigquery-profile Inside that file, you can reference your 
target-bigquery
's config env vars, so that you don't have to repeat your secrets.
@paul_tiplady If any of that isn't quite clear, I'd be happy to jump on a quick call to provide some more context 🙂
p
Ok, I've got that wired up -- thanks for the pointers. The confusion was mainly around how to actually wire up the sources in the derived model files. I ended up with a 
sources.yml
file for my raw DB sources (to be used in DBT models) that looks like this:
Copy code
version: 2

sources:
  - name: monolith
    tables:
      - name: users
This seems to be something that could be autogenerated from the tap's input schema, to allow users to cleanly say `{{ source("monolith", "users") }} (maybe the source name should be 
tap-mysql
in this case to be clear about where it came from?) But that is probably just syntactic sugar to someone who is familiar with DBT.
Thanks for all the help here!
d
@paul_tiplady Ah, thanks a lot for pointing that out! I hadn't actually used the sources functionality before; in our tap-specific dbt packages (like 
dbt-tap-gitlab
), we just directly referenced the schema using 
{{ var('schema') }}
, with 
vars: schema: "{{ env_var('DBT_SOURCE_SCHEMA') }}"
in 
dbt_project.yml
. 
DBT_SOURCE_SCHEMA
here refers to the 
dbt
plugin's 
source_schema
setting, which has a default value of 
$MELTANO_LOAD_SCHEMA
, which itself refers to the loader's 
schema
setting -- see https://meltano.com/docs/command-line-interface.html#pipeline-environment-variables That works for packages, which specify the models but shouldn't hard-code source schemas, but using explicitly named sources is definitely much better for locally defined models. Would you like to create an issue to discuss auto-populating that file some more? 🙂
p
Ah, this is really helpful, thanks. I was struggling to figure out how those env vars were supposed to wire up. As I dig into this, I'm not seeing the 
MELTANO_LOAD_SCHEMA
set, which is preventing the 
schema
from getting set as expected (log: 
meltano               | DEBUG Variable MELTANO_LOAD_SCHEMA is missing from the environment.
) I'm suspect this means I've not configured something correctly on the bigquery loader, any thoughts on what to look for?
I just used 
meltano add loader
to create the target-bigquery loader, I haven't added extra config.
d
@paul_tiplady That's probably because your target doesn't actually have a 
schema
setting, which is where this assumption (that works for postgres and snowflake) falls apart 🙂 If you wanted to use the 
DBT_SOURCE_SCHEMA
env var from your models, you'd need to set manually set it to target-bigquery's 
schema
equivalent, by adding 
config: source_schema: $TARGET_BIGQUERY_DATASET
(or using the alias: 
$MELTANO_LOAD_DATASET
) to 
dbt
s definition in your 
meltano.yml
.
(Assuming target-bigquery has a dataset setting; I haven't actually checked)
In any case, since you're manually specifying your source, you should be good.
6 Views
Open in Slack
PreviousNext
Powered by