Who am I

• Augusto Herrmann – https://herrmann.tech, backgrounds:

• civil servant since 2004.

Where to find this presentation

https://herrmann.tech/slide-decks/2023/05/integrating-data-sources-and-publishing-open-data-with-fastetl-airflow-and-frictionless

licensed under a Creative Commons Attribution 4.0 International License.

The team who made it

Data engineering team
Secretariat for Management and Innovation
Ministry of Management and Innovation in Public Services, Brazil

What is it

• a free and open source plugin for Apache Airflow
• has features that are useful for making ETL pipelines 🛠
• speeds up the data pipeline development process

Why we made it

• to develop data pipelines faster
• promote code reuse
• code we wrote had been using extensively since 2019 could be useful to others who use Airflow
• we free and open source software
• share knowledge with other teams, expand our network of developers
• get feedback & contributions

About Airflow

Apache Airflow is

• free and open source software with a robust and active community
• task scheduler and orchestrator: directed acyclic graphs (DAGs)
• visibility into ETL processes and notifications: what broke, when and why

We use Airflow

and fastETL to

• periodically synchronize data sources in the data lake
  • sources are databases, spreadsheets (Excel, Google, Sharepoint) and others
  • data is used for analysis, auditing and to create dashboards
• publish open data on the open data portal
• notify people about publications in the official gazette (Ro-DOU project)
• send notifications by email and Slack
• manage sprint stories and tasks using the Trello API

Features

• Full or incremental replication of tables in SQL Server, and Postgres databases (and MySQL sources)
• Load data from GSheets and from spreadsheets on Samba/Windows networks
• Extracting CSV from SQL
• Clean data using custom data patching tasks (e.g. for messy geographical coordinates, mapping canonical values for columns, etc.)

Features

• Querying the Brazilian National Official Gazette's (DOU's) API
• Using a Open Street Routing Machine service to calculate route distances
• Using CKAN or dados.gov.br's API to update dataset metadata
• Using Frictionless Tabular Data Packages to write data dictionaries in OpenDocument Text format

Example: replicating a database table

DbToDbOperator

• Copies can be:
  • full data
  • incremental
• Destination databases can be
  • Postgres
  • SQL Server
• Source databases can be:
  • Postgres
  • SQL Server
  • MySQL

Example: extracting a database table

Example: using Ro-DOU for gazette notifications

More info: https://github.com/gestaogovbr/Ro-dou/ (for now only in Portuguese)

Ro-DOU

is a sister project to FastETL

• List of keywords can be read:
  • from the yaml file
  • from a database
  • from an Airflow variable
• Send search results, organized by keyword, to:
  • email
  • Slack
  • Discord

Example: copying data from GSheet to database

TaxiGov: the data sources

• 29 heterogeneous databases with various table schemas
• 5 different suppliers
• many fields of unstructured text (motive, status, organization) – messy!

TaxiGov: the data pipelines

1. collect from the 29 data sources (starts daily at 7:45 am)
2. clean the data with fastETL patchwork and load it into a unified schema
3. select data with no privacy restrictions and publish it as open data (finished before 8:30 am )

TaxiGov: cleaning data with patchwork

Example: Geographical coordinates in sources have sometimes

• wrong decimal separator
• wrong signal
• values multiplied by 1,000 or 10,000
• etc.

fastETL patchwork

using GeoPointDataCleaner

using GSheetMappingCleaner

• for infrequent new values
• create a spreadsheet
  • column for observed values (automatically updated)
  • column for canonical values

table metadata

read column descriptions from the database with fastETL's TableComments

and record a Tabular Data Package

table metadata

from Tabular Data Package

to data dictionary document with fastETL's DocumentTemplateToDataDictionaryOperator

TaxiGov: open data publication

Using open data with Frictionless

Visualizing open data

Available at: https://gestaogovbr.github.io/taxigovviz/

Where to find fastETL

https://github.com/gestaogovbr/FastETL

Installing

• install the msodbcsql17 and unixodbc-dev libraries to your Airflow environment, then
  • add the apache-airflow-providers-fastetl Python package .
  • or

Credits

• fastETL logo by Moisés Lima
• photo by Alex Lvrs on Unsplash
• photo by Vinicius "amnx" Amano on Unsplash
• presentation done in Markdown using Marp by Yuki Hattori

Contact

• data engineering team @ the Secretariat for Management and Innovation: seges.cginf@economia.gov.br
• me: https://herrmann.tech

Questions & feedback

are welcome and appreciated