dbt + Hex

dbt (data build tool) is a command line tool that enables data analysts and engineers to transform data in their warehouses more effectively

dbt is the T in ELT. It doesn’t extract or load data, but it’s extremely good at transforming data that’s already loaded into your warehouse. This “transform after load” architecture is becoming known as ELT (extract, load, transform).

​

​

How I use dbt as a Data Director

Cleaning messy data

• I often have to perform the same cleaning operations when working with a source:
• Cleaning cell phones
• Converting timezones
• Working with JSON columns
• Cleaning bad data from organizers
• dbt let’s me clean the data ONCE and then keep it forever in a staged model
• I also get to do things like:
• Rename columns to my naming preferences
• Reorder columns in models so they are easier to work with
• Do some light joining to bring to make my base models more meaningful

Bringing in the source

Staging the model

Renaming columns

Staging the model

Cleaning up messy ref code data

Automating Hard Tasks

• I often have to write the same SQL over and over again
• dbt support Jinja, which is a Python templating language
• I can write macros with Jinja to automate my SQL

​

Simple macro that converts a timestamp to ET

Snippet of a large macro that pivots EveryAction Online Form Custom Questions into a human-readable format

Building robust infrastructure

• After I stage and clean my data, I can model it
• I use dbt to build “intermediate” models that combine my staged models
• On top of that, I can add documentation + testing to make my infrastructure robust

Testing can be as easy as adding YAML to your source file

Or you can write custom tests

Hex is a modern platform for data science and analytics, bringing together collaborative notebooks, beautiful data apps, and enterprise-grade security.

​

​

Write SQL + Python + R in the same notebook

How I use Hex

• Anything I would use a Jupyter Notebook for, I use Hex
• Sharing reports with organizers
• Investigating data
• Troubleshooting a SQL problem
• Visualizing data quickly

Write SQL

Write Python from that SQL

Thank you

​

– bb