# Data Integration (EL)
Meltano lets you easily extract and load data from and to databases, SaaS APIs, and file formats using Singer taps and targets, which take the role of your project's extractors and loaders.
Meltano manages your tap and target configuration for you, makes it easy to select which entities and attributes to extract, and keeps track of the incremental replication state, so that subsequent pipeline runs with the same job ID will always pick up right where the previous run left off.
You can run EL(T) pipelines using meltano elt
.
# Plugin configuration
As described in the Configuration guide, meltano elt
will determine the configuration of the extractor, loader, and (optionally) transformer by looking in the environment, your project's .env
file, the system database, and finally your meltano.yml
project file, falling back to a default value if nothing was found.
You can use meltano config <plugin> list
to list all available settings with their names, environment variables, and current values. meltano config <plugin>
will print the current configuration in JSON format.
# Pipeline-specific configuration
If you'd like to specify (or override) the values of certain settings at runtime, on a per-pipeline basis, you can set them in the meltano elt
execution environment using environment variables.
This lets you use the same extractors and loaders (Singer taps and targets) in multiple pipelines, configured differently each time, as an alternative to creating multiple configurations using plugin inheritance.
On a shell, you can explicitly export
environment variables, that will be passed along to every following command invocation, or you can specify them in-line with a specific invocation, ahead of the command:
export TAP_FOO_BAR=bar
export TAP_FOO_BAZ=baz
meltano elt ...
TAP_FOO_BAR=bar TAP_FOO_BAZ=baz meltano elt ...
To verify that these environment variables will be picked up by Meltano as you intended, you can test them with meltano config <plugin>
before running meltano elt
.
If you're using meltano schedule
to schedule your pipelines, you can specify environment variables for each pipeline in your meltano.yml
project file, where each entry in the schedules
array can have an env
dictionary:
schedules:
- name: foo-to-bar
extractor: tap-foo
loader: target-bar
transform: skip
interval: '@hourly'
env:
TAP_FOO_BAR: bar
TAP_FOO_BAZ: baz
Different runners and execution/orchestration platforms will have their own way of specifying environment variables along with a command invocation.
Airflow's BashOperator
, for example, supports an env
parameter:
BashOperator(
# ...
bash_command="meltano elt ...",
env={
"TAP_FOO_BAR": "bar",
"TAP_FOO_BAZ": "baz",
},
)
# Pipeline environment variables
To allow loaders and transformers to adapt their configuration and behavior based on the extractor and loader they are run with,
meltano elt
dynamically sets a number of pipeline-specific environment variables before compiling their configuration and invoking their executables.
# Extractor variables
In addition to variables available to all plugins, the following variables describing the extractor are available to loaders and transformers:
MELTANO_EXTRACTOR_NAME
: the extractor'sname
, e.g.tap-gitlab
MELTANO_EXTRACTOR_NAMESPACE
: the extractor'snamespace
, e.g.tap_gitlab
MELTANO_EXTRACT_<SETTING_NAME>
: one environment variable for each of the extractor's settings and extras, e.g.MELTANO_EXTRACT_PRIVATE_TOKEN
for theprivate_token
setting, andMELTANO_EXTRACT__LOAD_SCHEMA
for theload_schema
extra<SETTING_ENV>
: all of the extractor's regular configuration environment variables, as listed bymeltano config <plugin> list
, e.g.TAP_GITLAB_API_URL
for theapi_url
setting
# Loader variables
Additionally, the following variables describing the loader are available to transformers:
MELTANO_LOADER_NAME
: the loader'sname
, e.g.target-postgres
MELTANO_LOADER_NAMESPACE
: the loader'snamespace
, e.g.postgres
MELTANO_LOAD_<SETTING_NAME>
: one environment variable for each of the loader's settings and extras, e.g.MELTANO_LOAD_SCHEMA
for theschema
setting, andMELTANO_LOAD__DIALECT
for thedialect
extra<SETTING_ENV>
: all of the loader's regular configuration environment variables, as listed bymeltano config <plugin> list
, e.g.PG_ADDRESS
for thehost
setting
# Transform variables
Additionally, the following variables describing the transform are available to transformers:
MELTANO_TRANSFORM_NAME
: the loader'sname
, e.g.tap-gitlab
MELTANO_TRANSFORM_NAMESPACE
: the loader'snamespace
, e.g.tap_gitlab
MELTANO_TRANSFORM_<SETTING_NAME>
: one environment variable for each of the transform's settings and extras, e.g.MELTANO_TRANSFORM__PACKAGE_NAME
for thepackage_name
extra
# How to use
Inside your loader or transformer's config
object in your meltano.yml
project file, you can reference these (and other) environment variables as $VAR
(as a single word) or ${VAR}
(inside a word). Inside your plugin, you can reference them through os.environ
as usual (assuming you're using Python).
This feature is used to dynamically configure the target-postgres
and target-snowflake
loaders and dbt
transformer as appropriate, independent of the specific extractor and loader used:
- Default value for the
target-postgres
andtarget-snowflake
schema
settings:$MELTANO_EXTRACT__LOAD_SCHEMA
, e.g.tap_gitlab
fortap-gitlab
- Default value for
dbt
'starget
setting:$MELTANO_LOAD__DIALECT
, e.g.postgres
fortarget-postgres
andsnowflake
fortarget-snowflake
, which correspond to the target names intransform/profile/profiles.yml
- Default value for
dbt
'ssource_schema
setting:$MELTANO_LOAD__TARGET_SCHEMA
, the value of theschema
setting fortarget-postgres
andtarget-snowflake
- Default value for
dbt
'smodels
setting:$MELTANO_TRANSFORM__PACKAGE_NAME
$MELTANO_EXTRACTOR_NAMESPACE my_meltano_model
, e.g.tap_gitlab tap_gitlab my_meltano_model
for thetap-gitlab
transform andtap-gitlab
extractor
# Extractor catalog generation
Many extractors (Singer taps) expect to be provided a catalog
when they are run in sync mode using meltano elt
or meltano invoke
.
This catalog is a JSON file describing the schemas of the available entities (streams, tables) and attributes (properties, columns),
along with metadata to indicate (among other things) which entities and attributes should (or should not) be extracted.
A catalog can be generated by running the extractor in discovery mode and making the desired modifications to the schemas and metadata for the discovered entities and attributes. Because these catalog files can be very large and can get outdated as data sources evolve, this process can be tedious and error-prone.
To save you a headache, Meltano can handle catalog generation for you, by letting you describe your desired modifications using
entity selection, metadata, and schema rules that can be configured like any other setting,
and are applied to the discovered catalog on the fly when the extractor is run using meltano elt
or meltano invoke
.
If you'd like to manually inspect the generated catalog for debugging purposes, you can dump it to STDOUT or a file using the --dump=catalog
option on meltano invoke
or meltano elt
.
Note that if you've already manually discovered a catalog and modified it to your liking, it can be provided explicitly using meltano elt
's --catalog
option or the catalog
extractor extra.
# Selecting entities and attributes for extraction
Extractors are often capable of extracting many more entities and attributes than your use case may require. To save on bandwidth and storage, it's usually a good idea to instruct your extractor to only select those entities and attributes you actually plan on using.
Meltano makes it easy to select specific entities and attributes for inclusion or exclusion using meltano select
and the select
extractor extra,
which let you specify inclusion and exclusion rules that can contain Unix shell-style wildcards to match multiple entities and/or attributes at once.
Note that exclusion takes precedence over inclusion: if an entity or attribute is matched by an exclusion pattern, there is no way to get it back using an inclusion pattern unless the exclusion pattern is manually removed from your meltano.yml
project file first.
If no rules are defined using meltano select
, Meltano will fall back on catch-all rule *.*
so that all entities and attributes are selected.
# Setting metadata
Additional Singer stream and property metadata
(like replication-method
and replication-key
) can be specified
using the metadata
extractor extra,
which can be treated like a _metadata
setting with
nested properties _metadata.<entity>.<key>
and _metadata.<entity>.<attribute>.<key>
.
# Overriding schemas
Similarly, a schema
extractor extra is available that lets you easily override
Singer stream schema descriptions.
Here too, Unix shell-style wildcards can be used to match multiple entities and/or attributes at once.
# Replication methods
Extractors can replicate data from a source using one of the following methods:
Extractors for SaaS APIs typically hard-code the appropriate replication method for each supported entity.
Most database extractors, on the other hand, support two or more of these methods and require you to choose an appropriate option for each table through the replication-method
stream metadata key.
To support incremental replication, where a data integration pipeline run picks up where the previous run left off, Meltano keeps track of incremental replication state.
# Log-based Incremental Replication
replication-method
stream metadata value:LOG_BASED
The extractor uses the database's binary log files to identify what records were inserted, updated, and deleted from the table since the last run (if any), and extracts only these records.
This option is not supported by all databases and database extractors.
To learn more about how Log-based Incremental Replication works and its limitations, refer to the Stitch Docs, which by and large also apply to Singer taps used with Meltano.
# Key-based Incremental Replication
replication-method
stream metadata value:INCREMENTAL
The extractor uses the value of a specific column on the table (the Replication Key, e.g. an updated_at
timestamp or incrementing id
integer) to identify what records were inserted or updated (but not deleted) since the last run (if any), and extracts only those records.
To learn more about how Key-based Incremental Replication works and its limitations, refer to the Stitch Docs, which by and large also apply to Singer taps used with Meltano.
# Replication Key
Replication Keys are columns that database extractors use to identify new and updated data for replication.
When you set a table to use Key-based Incremental Replication, you’ll also need to define a Replication Key for that table by setting the replication-key
stream metadata key.
To learn more about replication keys, refer to the Stitch Docs, which by and large also apply to Singer taps used with Meltano.
# Full Table Replication
replication-method
stream metadata value:FULL_TABLE
The extractor extracts all available records in the table on every run.
To learn more about how Full-Table Replication works and its limitations, refer to the Stitch Docs, which by and large also apply to Singer taps used with Meltano.
# Incremental replication state
Most extractors (Singer taps) generate state when they are run, that can be passed along with a subsequent invocation to have the extractor pick up where it left off the previous time.
Loaders (Singer targets) take in data and state messages from extractors and are responsible for forwarding the extractor state to Meltano once the associated data has been successfully persisted in the destination.
Meltano stores this pipeline state in its system database, identified by the meltano elt
run's Job ID.
When meltano elt
is run a subsequent time, it will look for the most recent completed (successful or failed) pipeline run with the same job ID that generated some state. If found, this state is then passed along to the extractor.
Note that if you already have a state file you'd like to use, it can be provided explicitly using meltano elt
's --state
option or the state
extractor extra.
If you'd like to manually inspect a pipeline's state for debugging purposes, or so that you can store it somewhere other than the system database and explicitly pass it along to the next invocation, you can dump it to STDOUT or a file using meltano elt
's --dump=state
option.
Not seeing state picked up after a failed run?
Some loaders only emit state once their work is completely done, even if some data may have been persisted already, and if earlier state messages from the extractor could have been forwarded to Meltano. When a pipeline with such a loader fails or is otherwise interrupted, no state will have been emitted yet, and a subsequent ELT run will not be able to pick up where this run actually left off.