What is the easiest way to write custom data integration?

1. Fetch source data via API calls – E of ELT
2. Store raw data via S3 / Data Lake – L of ELT
3. Transform data as you wish via dbt – T from ELT

While focusing mainly on Transformations, which the most complex and interesting part and all about delivering business value, it is still essential to perform Extract-Load in clear and understandable way.

Shell script is the easist way to perform EL in my opinion (where possible 😉).

Take a look at the example of Fetching exchange rates →

1. Useful shell options options – debugging and safe exit.

set -x expands variables and prints a little + sign before the line.

set -e instructs bash to immediately exit if any command has a non-zero exit status

2. Variables

Either assign directly in bash script or provide as Environment Varabiables (preferred).

TS=`date +"%Y-%m-%d-%H-%M-%S-%Z"`

3. Chain or pipe commands

Result of one command could be input to another command.

JSON response fetched from API call gets transferred to AWS S3 bucket directly without any intermediate storage:

curl -H "Authorization: Token $OXR_TOKEN" \
  "https://openexchangerates.org/api/historical/$BUSINESS_DT.json?base=$BASE_CURRENCY&symbols=$SYMBOLS" \
  | aws s3 cp - s3://$BUCKET/$BUCKET_PATH/$BUSINESS_DT-$BASE_CURRENCY-$TS.json

4. Echo log messages

5. Schedule and monitor with Airflow.

Use templates, variables, loops, dynamic DAGs.

Do it right way once and just monitor for any errors. As simple as that.

6. Additional pros:

- Shell (bash, zsh) is already installed on most VMs
- No module importing / lib / dependency crap
- Ability to parallelize heavy commands and do it in optimal way