Hi all, I’m a new user to Meltano and have been having some issues with the

target-snowflake

extractor and was wondering if anyone could help. I have been trying to change the schema that the extractor writes to in the snowflake database. In the configuration fields in both the CLI and the UI only the following fields are available (https://meltano.com/plugins/loaders/snowflake.html#installing-from-the-meltano-cli): Account Username Password Role Database Warehouse

Hi @hector_zumaeta and welcome to Meltano! You touch a lot of really important parts of how Meltano works behind the scenes in your questions 🙂 Let me start with how Meltano treats the data brought in with an extractor and our default conventions: The extractor fetches the data and sends them to the loader. The loader then, using its configuration, loads the data to a RAW schema in the DB or Data Warehouse it is working with. By default, that schema is the name of the extractor: (SOURCE) --> [Extractor] --> [Loader] --> (RAW schema) In your case, you were using

tap-csv

to load data from CSV files, so the final schema is named by default

TAP_CSV

(underscore so that it is a valid SQL identifier and all caps when we are working with Snowflake cause it likes caps and makes our life simpler afterwards) You can change that destination by using the

MELTANO_LOAD_SCHEMA

environment variable. (the one you used is for the final analytics schema which I'll explain in a while) So if you want to only use the cli and set everything by using ENV variables, you can create a

.env

file in your Meltano Project's root with the following:

my_meltano_project/.env

# CSV settings
export TAP_CSV_FILES_DEFINITION="extract/files_def.json"

#Snowflake settings
export SF_ACCOUNT=""
export SF_USER=""
export SF_PASSWORD=""
export SF_ROLE=""       # in UPPERCASE
export SF_DATABASE=""   # in UPPERCASE
export SF_WAREHOUSE=""  # in UPPERCASE

# Global settings for the Target DB/Data Warehouse
export MELTANO_LOAD_SCHEMA="MY_RAW_CSV_DATA"

The

MELTANO_ANALYZE_SCHEMA

you mentioned is for the next step of the process, the Transformations. This step runs for extractors with pre-bundled transformations (like Gitlab, Salesforce, Google Analytics) or if you have created custom Transformations. What Meltano does on the Transform step is to run the Transformations over the RAW schema and then store the results to the

analytics

schema of the Data Warehouse: (RAW schema) --> Transforms --> (Analytics Schema) The idea is that we want to keep the RAW data untouched (like a small data lake) and always store the final transformed results in an Analytics schema. By setting the

MELTANO_ANALYZE_SCHEMA

environment variable, you can change the Analytics schema from the default

analytics

one to whatever you may want to use in your Data Warehouse. .env

...
# Global settings for the Target DB/Data Warehouse
export MELTANO_LOAD_SCHEMA="MY_RAW_CSV_DATA"
export MELTANO_ANALYZE_SCHEMA="MY_ANALYTICS_SCHEMA"

In the case of CSV files, Meltano can not know what you are going to bring in, so we do not include any pre-bundled Transformations and that step has nothing to do even if you ask meltano to write it. You can add your own Custom Transformations as explained in our Tutorial: https://meltano.com/tutorials/csv-with-postgres.html To wrap up this intro with a full description of Meltano's architecture, the Analyze feature inside Meltano UI uses Models to connect to that final Analytics schema and allow the ad-hoc generation of queries and reports. Not for Advanced Use Cases: We also support the

MELTANO_LOAD_SCHEMA_SUFFIX

and

MELTANO_ANALYZE_SCHEMA_SUFFIX

env vars in case you want to dynamically set pipelines that create

XXX_test

or

XXX_production

, etc schemas for specific runs (or whatever other suffix you may want, like when you are doing some type of versioning)

@yannis_roussos this is one of the best technical responses I’ve received, thank you so much I’ll follow this and let you know how it goes!

Thank you @hector_zumaeta! I hope that it will help you get started 🙂