Data Engineering Zoomcamp – Semana 4

Modelización de datos con dbt

Cada vez se pone más interesante el bootcamp para Data Engineers. La cuarta semana está dedicada al modelaje de datos y los Analytics Engineer como pieza intermedia entre los Data Engineers y los Data Scientists. Su labor es hacer de catalizador entre ambos roles participando en el modelado de datos para lo que hemos explorado dbt (Data build tool). El último capitulo está centrado en la visualización de datos con Google Data Studio, disponible dentro del ecosistema de GCP (Google Cloud Platform).

• Repo Bootcamp: https://github.com/DataTalksClub/data-engineering-zoomcamp/tree/main/week_4_analytics_engineering
• Repo personal con proyecto dbt: https://github.com/hegdehog/zoomcamp-2023-dbt
• Repo personal con ejercicios: https://github.com/hegdehog/data-engineering-zoomcamp-2023

Última actualización: 20/02/2023

Prerequisitos

Vamos a seguir utilizando los recursos que hemos desplegado en GCP (BigQuery y GCS Bucket). La novedad esta semana es la introducción de una nueva herramienta para el modelado de datos:

• dbt (Data Build Tool). Podemos utilizarla de dos formas, bien mediante el SaaS gratuito para usuarios individuales registrándonos en la web dbt Cloud o descargando el paquete para trabajar en local y con la consola CLI. Se puede descargar de varias formas, la más sencilla a mi parecer es con pip en nuestro entorno de Python. Es muy interesante el curso de introducción oficial (duración: 5 horas).
• Google Data Studio. Nuevo recurso que probamos en Google Cloud Platform, simplemente lo desplegamos. Podemos acceder desde aquí.

Mas allá de software y herramientas, vamos a necesitar cargar en nuestra base de datos de BigQuery los datasets de los ejercicios:

• Datasets de las carreras de taxis: he dejado en mi repositorio de github un script de Prefect para cargar todos los ficheros en GCS Bucket para luego trabajar con ellos en dbt y persistirlos en la base de datos BigQuery.
  • Yellow taxi data – Años 2019 y 2020
  • Green taxi data – Años 2019 y 2020
  • fhv data – Año 2019
• Dataset de zonas para lookup: puedes descargarlo desde el repositorio original aquí
• Schema, staging models y macros para generar los primeros modelos en dbt. Están publicados en la repo del bootcamp (macros y schema-models)

Analytics Engineer

Según el estudio What Are the Essential Roles for Data and Analytics? de Gartner publicado en el año 2022 los perfiles de D&A (Data and analytics) llevan varios años en continúo desarrollo y para este 2023 se espera que continúen surgiendo nuevos perfiles que respondan a necesidades más concretas:

Dentro de este extenso e imparable abanico de roles, los Analytics Engineers son un híbrido a medio camino entre los Data Engineers y los Data Scientists. Se encargan de diseñar, construir y mantener sistemas de análisis y visualización para facilitar la toma de decisiones basadas en datos. Similar a un ingeniero de datos, se encarga de crear canalizaciones y visualizaciones, pero principalmente para modelos de IA/ML y equipos de ciencia de datos.

En comparación, los data engineers se enfocan en la recopilación, integración y almacenamiento de datos, mientras que los data scientists se enfocan en la investigación y modelado de datos para el descubrimiento de patrones y tendencias. La tarea principal de un Analytics Engineer es proporcionar una plataforma de análisis de datos sólida y confiable para que los data scientists y otros perfiles puedan realizar análisis complejos.

Es importante que los Analytics Engineer estén familiarizados con una amplia gama de tecnologías, ya que la capacidad de trabajar con diferentes herramientas y plataformas es clave para la solución efectiva de problemas de análisis de datos. El stack tecnológico necesario puede variar dependiendo de los requisitos específicos del proyecto y la organización, pero algunas tecnologías comunes que se utilizan en este campo incluyen:

• Almacenamiento de datos: Data warehousing, sistemas de almacenamiento en la nube como Azure Blob Storage, Amazon S3 o Google Cloud Storage.
• Procesamiento de datos: Apache Hadoop, Apache Spark, Apache Flink.
• Lenguajes de programación: Python, SQL, Java.
  • Bibliotecas de análisis de datos: Pandas, Numpy, Matplotlib, etc.
• Visualización de datos: Tableau, Power BI, QlikView, Looker.
• Colaboración y gestión de proyectos: Azure DevOps, Github, JIRA, Confluence, Asana.
• Integración de datos: Apache NiFi, Apache Airflow, Prefect, Talend.

Modelización de Data Warehouse: Kimball, Inmon y Data Vault

Existen varias metodologías de Data Warehouse basadas en la arquitectura de la modelización, las más extendidas son las de Kimball, Inmon y más recientemente, Data Vault.

Data Build Tool (dbt)

dbt (Data Build Tool) es una librería Python open-source que agiliza la construcción de modelos de datos permitiendo a los desarrolladores definir, orquestar y ejecutar transformaciones en un almacén de datos moderno como BigQuery, Snowflake, Redshift, etc. Podríamos decir que se trata de una herramienta de gobierno focalizado en la «T» de un proceso ETL/ELT, nos permite centralizar y construir todas las transformaciones de datos en SQL organizándolas como módulos reutilizables (models). Por otro lado, al estar inspirado en las prácticas de la ingeniería software, podemos crear test de validación e implementar todo el ciclo de CI/CD en nuestras canalizaciones de datos. En paralelo al conocimiento que aporta el Bootcamp, es muy interesante el curso de introducción oficial (duración: 5 horas).

En mi periodo profesional anterior la lógica estaba repartida en procedimientos almacenados en el servidor de BBDD SQL Server, queries SQL en las ETL (Azure Data Factory, SSIS y Taled) e incluso en los modelos de carga de las herramientas de visualización (Qlikview y Power BI). No estaba documentada en ningún lado. Lo que aporta dbt en este paradigma es una capa de gobierno o control que facilita el mantenimiento y documentación de la lógica, el linaje, aumentar la resiliencia y colaboración gracias a un control de versiones y por último, facilitaría la integración continua / entrega o despliegue continuo CI/CD.

Algunas de las características principales de dbt.

1. Reutilización de código: Permite la definición de modelos de datos y la organización de transformaciones en paquetes.
2. Énfasis en los controles de calidad: fomenta el uso de pruebas automatizadas para garantizar la calidad de los datos y prevenir errores en las transformaciones.
3. Control de versiones y colaboración: está diseñado para trabajar con sistemas de control de versiones como Git, Bitbucket, etc., lo que facilita el seguimiento de los cambios y la colaboración en el desarrollo de pipelines de datos.
4. Escalabilidad: diseñado para trabajar con almacenes de datos modernos como BigQuery, Snowflake, Redshift, etc., permite escalar fácilmente el procesamiento de grandes volúmenes de datos.

¿Cómo empezar con dbt?

Hay dos formas de utilizar dbt de forma gratuita:

• dbt Core: versión de código abierto (open-source) que se instala localmente o en un servidor propio. La interacción es por consola CLI.
• dbt Cloud: plataforma alojada en la nube (SaaS) que ofrece funcionalidades adicionales a la versión Core (programación de ejecuciones, integraciones con servicios de BI, monitoreo y alertas). Es más fácil de utilizar al disponer de un GUI. Además de los planes de pago, ofrece una versión gratuita limitada para desarrolladores.

Instalar dbt Core con PIP

Tenemos varias opciones para instalar dbt Core en nuestro equipo o servidor local, la forma más sencilla es mediante pip en un entorno de python.

pip install dbt-core

Después instalaremos el adapter o conector del motor de base de datos que vayamos a utilizar. Tenemos a nuestra disposición un catálogo oficial y otro complementario de la comunidad, puedes consultar todos los conectores disponibles desde aquí. En nuestro caso vamos a hacer la instalación del adapter de BigQuery.

pip install dbt-bigquery

error: Microsoft Visual C++ 14.0 or greater is required. Get it with "Microsoft C++ Build Tools":
https://visualstudio.microsoft.com/visual-cpp-build-tools/
      [end of output]

  note: This error originates from a subprocess, and is likely not a problem with pip.
  ERROR: Failed building wheel for pandas
Failed to build pandas
ERROR: Could not build wheels for pandas, which is required to install pyproject.toml-based projects

Al intentar instalar el adapter de BigQuery me arrojó un error por una dependencia con la librería Pandas que necesita como mínimo la versión 14.0 del runtime de Microsoft Visual C++. Descargamos Build Tools desde aquí o el enlace facilitado en el mensaje de error. Lo ejecutamos y pulsamos en Modificar para añadir el paquete que nos falta:

Volvemos a instalar el adapter de BigQuery y debería terminar con éxito.

ERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.
prefect-gcp 0.2.3 requires google-api-python-client>=2.20.0, but you have google-api-python-client 1.7.11 which is incompatible.

pip install google-api-python-client --upgrade 

python -m venv PythonDbt
PythonDbt\Scripts\activate

Creamos un nuevo proyecto ejecutando el comando dbt init. Lo que hace este comando es clonar en la carpeta desde donde lo hayamos ejecutado el starter project de dbt que contiene todos los ficheros y directorios necesarios para arrancar nuestro proyecto

• dbt_project.yml: fichero de configuración del proyecto dbt (nombre, profile que identifica el motor de base de datos que vamos a utilizar, como PostgreSQL o BigQuery y variables globales). Si vas a usar dbt de forma local es necesario que asegurar que el profile indicado en este fichero de configuración coincida con el de la instalación (~/.dbt/profiles.yml).
• Readme.MD: fichero para literatura en la repo
• Directorios analysis, data, macros, models, snapshots y tests

dbt init

$ dbt init
21:47:00  Running with dbt=1.4.1
No adapters available. Go to https://docs.getdbt.com/docs/available-adapters

Si todo ha ido bien, podremos configurar nuestro proyecto a través de la consola (CLI) para generar el fichero profiles.yml:

Comprobamos que se han generado todos los ficheros y directorios del proyecto dbt en la ruta en la que hemos ejecutado el comando:

Instalar dbt Core con una imagen de Docker

Os recomiendo esta lectura para profundizar más en este paso. Las imágenes disponibles para montar un contenedor de dbt con Docker son:

• dbt-core (no tiene soporte de base de datos)
• dbt-postgres
• dbt-redshift
• dbt-bigquery
• dbt-snowflake
• dbt-spark
• dbt-third-party 
• dbt-all (instala todas las imágenes en una sola)

docker build --tag my-dbt  --target dbt-bigquery .

Una vez creada la imagen arrancamos el contenedor:

docker run \
--network=host
--mount type=bind,source=path/to/project,target=/usr/app \
--mount type=bind,source=path/to/profiles.yml,target=/root/.dbt/profiles.yml \
my-dbt \
ls

Crear proyecto en DTB Cloud

Previo a crear nuestro primer proyecto en dbt Cloud debemos reunir los ingredientes necesarios: crear una service account, generar la JSON Key para otorgar acceso a dbt a nuestra instancia de BigQuery y crear una repo vacía en Github donde se almacenarán los ficheros y directorios del proyecto:

Crear service account + JSON Key para Big Query

Como en nuestro caso vamos a utilizar BigQuery, la autenticación se realiza por BigQuery OAuth. Debemos crear un service account desde GCP y descargar la JSON key para otorgar acceso a dbt.

1. Accedemos a la consola de Google Cloud Platform y nos dirigimos a IAM and admin > Service accounts para crear un nuevo service account con permisos BigQuery Admin y Storage Object Viewer.
2. Descargamos la JSON Key a nuestro equipo (más tarde la subiremos en dbt en el proceso de configuración del proyecto).

Crear repositorio en Github

Simplemente creamos un repo vacía en Github y pulsamos en Git Clone para copiar la clave SSH con la que vincularemos el proyecto de dbt. La conexión con Github se hace en dos partes, por ahora sólo nos interesa la key, más adelante veremos cómo configurar la deploy key generada desde dbt.

¡Ahora sí tenemos los ingredientes! Nos registramos en dbt cloud con la opción gratuita para un usuario desde este enlace. Una vez verificado el email podremos crear nuestro proyecto eligiendo en primer lugar el almacenamiento de datos.

En el siguiente paso cargamos la JSON Key que hemos generado con el service account de BigQuery y automáticamente se cargan todos los parámetros. Es recomendable crear el dataset en BigQuery de forma manual para evitar problemas de incompatibilidad entre regiones. En mi caso, he usado el nombre que ha generado por defecto y creado el dataset directamente:

Hacemos el test de conexión y si todo ha ido bien ¡al paso siguiente!

Ahora es el turno de configurar el repositorio Github que hemos creado previamente y realizar el segundo paso que teníamos pendiente. Seleccionamos Git Clone y pegamos la Key SSH que hemos copiado antes. Pulsamos el botón de Import.

Generará una clave de despliegue que debemos copiar en la configuración del repositorio de Github:

Volviendo a nuestro repositorio de Github, pulsamos en Settings y en la sección de Security pulsamos en Deploy Keys para añadirla. Es necesario marcar la opción Allow write access:

¡Listo!

Si damos al botón Next en la configuración del proyecto de dbt ya habremos terminado:

Accedemos a Develop y debemos inicializar nuestro proyecto en dbt cloud (similar al comando dbt init que ejecutaríamos en dbt core):

Tras esperar unos segundos, se crean todos los ficheros de yml, SQL y los directorios del proyecto dbt. Debemos pulsar sobre Commit and sync para hacer un push a nuestra repo de Github.

Recuerda que al estar vinculado a una repo en github, si quieres trabajar sobre el GUI de dbt cloud es necesario que crees primero una rama. Para ejecutar cualquier comando desde GUI, podemos usar la consola que tenemos en el pie:

dbt model

Un modelo en dbt es la unidad mínima con la que vamos a trabajar, se trata simplemente de una fichero SQL con una sentencia SELECT. En el siguiente ejemplo se va a crear una tabla en la BBDD de destino con los datos del SQL:

{{      config(materialized='table')    }}

SELECT *
FROM staging.source_table
WHERE record_state = 'ACTIVE'

Además de la query, un modelo dbt incluye al principio un bloque de código bajo la notación Jinja que reconoceremos por los doble brackets {{ }}. Dentro de este bloque se llama a la función config() de dbt que habitualmente se usa para especificar la estrategia de persistencia del modelo dbt en la base de datos de destino. Por defecto hay cuatro formas de materializar las consultas, aunque es posible crear las nuestras propias:

• table: los datos del modelo se persisten en una tabla sobre el warehouse
• view: ídem al anterior, pero en lugar de una tabla se trata de una vista
• incremental: estos modelos permiten a dbt insertar y/o actualizar registros en una table si han cambiado desde la última vez que se ejecutó.
• ephemeral: no generan un objeto directamente en la base de datos, crea un CTE (Common Table Expression), que viene a ser una subquery temporal para utilizar en otras consultas (como el WITH de SQL Server).

Para construir el modelo que persiste la query en una tabla de nuestra base de datos, debemos ejecutar el comando dbt build. Si no añadimos ningún parámetro, compilará y construirá todos los models. Para especificar que sólo queremos construir uno en concreto añadimos el parámetro --select; veámoslo con dos ejemplos:

Ejecutamos y construimos todos los modelos:

dbt run

Para construir sólo el model myModel.sql corremos este comando:

dbt run --select myModel.sql

Cuando ejecutamos el modelo, dbt lo compila en la siguiente query:

CREATE TABLE my_schema.my_model AS (
    SELECT *
    FROM staging.source_table
    WHERE record_state = 'ACTIVE'
)

La cláusula FROM en un modelo dbt

Además de indicar directamente esquema y nombre de la tabla, en un modelo dbt hay disponibles dos formas de configurar el origen de los datos en la cláusula FROM: sources y seeds.

{{ config(materialized='table') }}

SELECT *
FROM {{ source('staging','green_tripdata') }}

version: 1

sources:
    - name: staging
      database: dbt_mtorregrosa
      schema: trips_data_all

      tables:
          - name: green_tripdata
          - name: yellow_tripdata

Por tanto, en nuestra carpeta de /models/ de dbt tendremos un fichero .sql para cada modelo y un schema.yml con la configuración de los sources.

Seeds

Similar a las External Tables de BigQuery o Azure Synapse, podemos referenciar cualquier fichero CSV almacenado en el repositorio dentro del directorio /seeds, Al estar almacenado en una repo podemos aprovechar su control de versiones. Se recomiendo usar seeds para datos que no cambian con frecuencia (tablas de dimensiones paramétricas, como provincias).

En lugar de usar la macro source() como hemos visto antes para una tabla de BBDD, para los seeds se usa la macro ref() que recibe como parámetro el nombre del fichero y dbt automáticamente detecta las dependencias y su ubicación. La macro ref() también se puede usar para referenciar tablas o vistas de BBDD del mismo modo, sólo pasando por parámetro el nombre de la tabla. Para crear un seed simplemente subimos un fichero CSV al directorio /seeds de nuestra repo y ejecutamos el comando dbt seed taxi_zone_lookup.csv. Si ejecutasemos dbt seed se cargarían en la base de datos todos los CSVs del directorio. Este comando genera una tabla en nuestra bbdd, en BigQuery:

dbt seed taxi_zone_lookup.csv

Si accedemos a BigQuery observamos que se ha creado una tabla con nombre taxi_zone_lookup que podremos consultar con ref() en cualquier dbt model.

Ahora podemos crear un dbt model que referencie al seed recién creado:

{{ config(materialized='table') }}

SELECT *
FROM {{ ref('taxi_zone_lookup') }}

Si quisiéramos cambiar los tipos de datos del seed que por defecto a inferido del CSV, debemos modificar el fichero de configuración del proyecto dbt_project.yml y añadir el bloque de seeds:

seeds:
    taxi_riders_ny:
       taxi_zone_lookup:
          +column_types:
              locationid: numeric

Si queremos recrear el seed debemos utilizar el comando dbt seed micsv.csv --full-refresh.

Macros

Una macro en dbt es similar a una función en cualquier otro lenguaje redactadas en notación jinja. Se generan en ficheros independientes .sql en el directorio /macros del proyecto dbt. Como ya hemos comentado, por defecto dbt dispone de varias macros como source(), ref() y config(), pero podemos crear una nueva que atienda a nuestras necesidades. Algunas características de las macros:

• Permite añadir lógica con bucles FOR y sentencias IF
• Pueden utilizar variables de entorno definidas a nivel de proyecto en dbt
• Permite reutilizar código entre distintos models
• Utiliza en una consulta el resultado de otra subconsulta

Podemos utilizar tres tipos de bloques jinja distintos dentro de una macro:

• Expresiones {{ }}: cuando queremos devolver una cadena. Se pueden utilizar expresiones para referenciar variables o llamar a otras macros.
• Sentencias {% %}: se utilizan para el control del flujo, por ejemplo, para loops FOR o sentencias IF.
• Comentarios {# #}: el texto del comentario no se compila, nos permite indicar notas.

Por ejemplo, en el siguiente código vamos a crear la macro get_payment_type_description que recibe como parámetro un valor en payment_type y resuelve el CASE WHEN devolviendo el valor correspondiente:

{# This macro returns the description of the payment_type #}

{% macro get_payment_type_description(payment_type) %}

    case {{ payment_type }}
        when 1 then 'Credit card'
        when 2 then 'Cash'
        when 3 then 'No charge'
        when 4 then 'Dispute'
        when 5 then 'Unknown'
        when 6 then 'Voided trip'
    end

{% endmacro %}

Usamos la macro en nuestro modelo dbt:

{{ config(materialized='table') }}

SELECT 
    get_payment_type_description(payment_type)
FROM{{ source('staging','green_tripdata') }}
WHERE vendorid is not null

Cuando ejecutamos el modelo, dbt compila el código de forma que la tabla generada ha traducido la macro a un CASE WHEN en SQL:

SELECT 
    case payment_type
        when 1 then 'Credit card'
        when 2 then 'Cash'
        when 3 then 'No charge'
        when 4 then 'Dispute'
        when 5 then 'Unknown'
        when 6 then 'Voided trip'
    end as payment_type_description
FROM {{ source('staging','green_tripdata') }}
WHERE vendorid is not null

Packages

Nos permite reutilizar macros entre distintos proyectos, similar a las librerías o módulos en otros lenguajes de programación. Para utilizar un package en nuestro proyecto debemos crear un fichero de configuración packages.yml en el directorio raíz de nuestro proyecto dbt.

packages:
  - package: dbt-labs/dbt_utils
    version: 0.8.0

Después debemos instalarlos ejecutando el comando dbt deps que se encarga de descargar todas las dependencias y ficheros del paquete dentro de nuestro proyecto. Una vez haya terminado se creará el directorio dbt_packages/dbt_utils en nuestro proyecto.

Podemos utilizar las macros del package recién instalado en cualquier model de nuestro proyecto. Por ejemplo, el package dbt-utils incluye la macro surrogate_key para crear una clave subrogada (id secuencial interno a la tabla).

{{ config(materialized='table') }}

SELECT
    {{ dbt_utils.surrogate_key(['vendorid', 'lpep_pickup_datetime']) }} as tripid,
    vendorid
FROM {{ source('staging','green_tripdata') }}
WHERE vendorid is not null

Dbt compila este modelo creando una clave subrogada con una función hash:

SELECT
    to_hex(md5(cast(coalesce(cast(vendorid as string), '') || '-' 
        || coalesce(cast(lpep_pickup_datetime as string), '') as string))) as tripid,
    vendorid
FROM {{ source('staging','green_tripdata') }}
WHERE vendorid is not null

Variables

Similar a las variables de cualquier lenguaje de programación. Con la misma finalidad, nos permite guardar un valor y reutilizarlo en cualquier parte del proyecto. Se pueden crear de dos formas:

• En el fichero de configuración del proyecto dbt (dbt_project.yml) ubicado en el directorio raíz añadiendo:

vars:
    payment_type_values: [1, 2, 3, 4, 5, 6]

• Por línea de comandos cuando construimos los modelos:

dbt build --var 'is_test_run: false'

Para utilizar una variable debemos usar la macro var() dentro de un modelo:

{{ config(materialized='table') }}

SELECT *
FROM {{ source('staging','green_tripdata') }}
{% if var('is_test_run', default=true) %}

    limit 100

{% endif %}

Caso de uso: crear dbt models para proyecto tripdata

Vamos a cargar en nuestro proyecto dbt los datasets del bootcamp y crear todos los artefactos (models, macros y schema). Repito los prerequesitos de este post:

1. Cargamos en nuestro GCS Bucket todos los datasets comprimidos en formato csv.gz:

• Datasets de las carreras de taxis: he dejado en mi repositorio de github un script de Prefect para cargar todos los ficheros en GCS Bucket para luego trabajar con ellos en dbt y persistirlos en la base de datos BigQuery.
  • Yellow taxi data – Años 2019 y 2020
  • Green taxi data – Años 2019 y 2020
  • fhv data – Año 2019
• Dataset de zonas para lookup: puedes descargarlo desde el repositorio original aquí

3. Una vez hemos cargado en nuestro GCS Bucket todos los ficheros csv.gz de tripdata, vamos a crear las external tables para consumirlos como tablas de BigQuery:

CREATE OR REPLACE EXTERNAL TABLE trips_data_all.fhv_tripdata
OPTIONS (
  format = 'CSV',
  uris = ['gs://tripdata_n4gash/data/fhv/*.csv.gz']
);

CREATE OR REPLACE EXTERNAL TABLE trips_data_all.green_tripdata
OPTIONS (
  format = 'CSV',
  uris = ['gs://tripdata_n4gash/data/green/*.csv.gz']
);

CREATE OR REPLACE EXTERNAL TABLE trips_data_all.yellow_tripdata
OPTIONS (
  format = 'CSV',
  uris = ['gs://tripdata_n4gash/data/yellow/*.csv.gz']
);

CREATE OR REPLACE EXTERNAL TABLE trips_data_all.zones_tripdata
OPTIONS (
  format = 'CSV',
  uris = ['gs://tripdata_n4gash/data/taxi+_zone_lookup.csv']
);

3. Creamos en dbt el schema, staging models y macros. Están publicados en la repo del bootcamp (macros y schema-models), simplemente copiamos el código en nuestra repo de github o directamente en dbt clout y compilamos:

dbt build

Compilation Error in test accepted_values_stg_green_tripdata_Payment_type__False___var_payment_type_values_ (models/staging/schema.yml)
  'NoneType' object is not iterable
  
  > in macro test_accepted_values (tests/generic/builtin.sql)
  > called by test accepted_values_stg_green_tripdata_Payment_type__False___var_payment_type_values_ (models/staging/schema.yml)

vars:
  payment_type_values: [1, 2, 3, 4, 5, 6]

BigQuery adapter: 404 Not found: Dataset was not found in location EU

No hemos configurado correctamente la location del conector a BigQuery en dbt. Para revisar la configuración en dbt nos dirigimos a Account settings > Project Analytics > BigQuery > bajamos hasta abajo del todo en Location y escribimos exactamente la location que vemos en GCP (EU):

Si todo ha ido bien, vemos que al ejecutar el comando dbt build va a compilar y generar todos los modelos:

Y si comprobamos en BigQuery, podemos ver que se han creado dos vistas (green y yellow):

4. Creamos un seed de la tabla lookup de zonas. Podemos subir el CSV directamente al directorio /seeds en dbt o crear un fichero y copiar el contenido. Después ejecutamos el comando dbt seed para crear la external table en BigQuery y observamos cómo se ha creado en nuestro dataset la tabla taxi_zones_lookup.

5. Creamos un dbt model del seed de zonas en la carpeta models/core con nombre dim_zones.sql:

{{ config(materialized='table') }}

select 
    locationid, 
    borough, 
    zone, 
    replace(service_zone,'Boro','Green') as service_zone
from {{ ref('taxi_zone_lookup') }}

6. Vamos a unir las tablas de hechos (stg_green_tripdata y stg_yellow_tripdata) y la el seed lookup (dim_zones) en una nueva tabla usando el script fact_trips.sql que encontramos en la repo del bootcamp. Creamos un nuevo dbt model en la carpeta models/core con nombre fact_table.sql con el contenido. Si lo copiáis de la repo, recordad que debéis revisar los nombres de las tablas en vuestro propio proyecto. Lanzamos el comando dbt build. Si todo ha ido bien vemos que cada uno de los pasos se ha ejecutado correctamente:

7. Comprobamos el linaje del dbt model fact_trips y observamos cómo se identifican todas las dependencias desde la capa de origen, a staging y por último la tabla final.

8. Por último, vemos cómo se ha creado la tabla fact_trips en BigQuery:

Testing en dbt

Una de las herramientas de las que disponemos los desarrolladores para certificar que nuestro código es correcto, hace lo que se espera y no afecta a lo que hubiera consolidado antes, es pasar varios tests automáticos. Pueden realizar distintas operaciones, por ejemplo, si nuestro proyecto está compuesto por varios métodos podríamos crear un test por cada una de las posibles salidas de cada método. Esto nos permite comprobar si todo sigue funcionando con normalidad y la nueva funcionalidad aporta el valor esperado. Si alguno de los test automáticos no pasa satisfactoriamente, el código no se despliega en el siguiente entorno.

En el mundo de datos el testing es más ortopédico en el sentido de que las validaciones que podemos construir son suposiciones en base a los datos: conteos, sumatorios, cálculo de KPIs, etc. Es decir, se trataría de sentencias SELECT cuyo resultado vamos a comparar con un valor esperado. En función del proyecto, podemos encontrarnos con frameworks de data quality o simplemente un puñado de queries que se ejecutan antes de desplegar los cambios en produccion.

En dbt podemos crear tests automáticos de forma sencilla usando sentencias SELECT. Cuando lanzamos un test en dbt, éste compila el SQL y nos devuelve el número de registros que no lo han cumplido. Podemos usar tres tipos de tests en dbt:

• Singular test: es la forma más sencilla de crear un test en dbt. Simplemente es una query donde definimos exactamente lo que deseamos comprobar para un caso de uso concreto. Esto implica que no se puede reutilizar, sólo nos sirve para un modelo. Se crea como un fichero .sql y se almacena en la carpeta de tests /tests o en la que hayamos definido en dbt-project.yml. Podemos asignarle el nombre que deseemos, ya que se trata de un test único para un sólo model.
• Generic test: como su nombre indica, se trata de tests genéricos que podemos reutilizar para cualquier model. Se trata de queries parametrizadas que aceptan parámetros de forma que podamos usarla para cualquier caso de uso.
• Out-of-the-box test: de forma predeterminada dbt dispone de 4 tests genéricos:
  • unique: la columna tiene todos los valores únicos
  • Not null: comprueba si tiene valor nulo (sin informar)
  • accepted_values: similar a los check constrings en una BBDD, se trata de una lista de valores que puede contener la columna. Se construye como un array de strings: values: ['placed', 'shipped', 'completed', 'returned']
  • relationships: comprueba la IR del modelo. Por ejemplo, cada valor de la columna debe existir como PK en otra tabla.

Los tests se definen en un fichero yml en la carpeta models, por ejemplo, schema.yml, donde definimos las reglas de validación para cada columna. Por ejemplo, usando los tests out-of-the-box. Para utilizar un test que hayamos creado nosotros, debemos usar el nombre del .sql del mismo. Por cada test podemos indicar la severidad: error o warn:

columns:
          - name: tripid
            description: Primary key for this table, generated with a concatenation of vendorid+pickup_datetime
            tests:
                - unique:
                    severity: warn
                - not_null:
                    severity: warn

Podemos ejecutar todos los tests del proyecto con el comando dbt_test. Al igual que para construirlos, si indicamos en el parámetro --select el nombre del model, sólo se ejecutarán los correspondientes.

dbt test --select stg_green_tripdata

Documentación automática

Podemos configurar nuestro proyecto dbt para que genere de forma automática la documentación en formato web y publicada en dbt cloud. Incluye información del proyecto:

• Models: script de los ficheros .sql y el compilado. Incluye sus dependencias con otros objetos en dbt (models, seeds…)
• Sources: tablas de base de datos origen
• DAG (Directed acyclic graph) de las macros ref() y source() son diagramas directos acíclicos dirigidos, una forma de representar el flujo de datos en una ETL donde la información viaja desde el punto A hasta el punto B.
• Descripciones ubicadas en los ficheros .yml
• Tests
• Información sobre los datos de las BBDD origen (nombre de columnas y tipo de datos, estadísticas de tablas, etc.)

Despliegue de un model dbt

Con dbt podemos configurar CI/CD (integración continua y despliegue/entrega continua) para desplegar nuestros models en producción. Separar los entornos de desarrollo y despliegue nos permite construir modelos y probarlos sin afectar a producción. En función del proyecto podemos encontrar que el entorno de despliegue o producción está ubicado en otra máquina o simplemente en otro esquema y usuario. El flujo habitual de desarrollo-despliegue sería:

1. Creamos una nueva rama en la repo
2. Abrimos una pull request para unir nuestra rama a la principal
3. Unimos (merge) nuestra rama con la principal
4. Ejecutamos los nuevos models en el entorno de producción
5. Programamos la ejecución de los models usando jobs.

Jobs en dbt

Dbt incluye un programador donde configurar jobs para que ejecuten los modelos en producción. Los jobs se pueden programar o ejecutar manualmente y cada uno puede tener uno o varios comandos (construir varios modelos, lanzar tests, etc).

Previo a crear el job, lo primero que tendríamos que hacer es configurar un entorno de producción para así tener diferenciados el de desarrollo del de despliegue. Para crearlo nos dirigimos a Deploy > Environments y pulsamos sobre el botón Create Environment. En Deployment credentials debemos indicar el dataset de BigQuery de destino. Podemos crearlo previamente desde GCP o escribir un nombre y dbt automáticamente lo creará:

Ahora sí podríamos crear un job para que publique los cambios de desarrollo en producción. Para ello, vamos a pulsar en Deploy > Jobs y en el botón Create Job. Vamos a especificar un nombre:

Marcamos que genere la documentación y añadimos los comandos: dbt seed (crea el seed del CSV de zonas), dbt run (ejecuta y compila los models) y dbt build (para construirlos).

Por último, configuramos la programación en el apartado de triggers:

Una vez ejecutado el job, comprobamos que todos los pasos han terminado correctamente y se ha generado la documentación:

Y que en BigQuery se han creado las tablas y vistas que teníamos en el dataset de staging:

Integración continua (CI)

La integración continua (CI) es una práctica de desarrollo de software que se utiliza para garantizar que el código se integra y se prueba automáticamente de manera regular y frecuente.

En CI, los desarrolladores envían su código a un repositorio compartido varias veces al día, lo que desencadena una serie de procesos automatizados que incluyen compilación, prueba y análisis estático de código. Estos procesos se ejecutan automáticamente en un entorno de prueba aislado, que se crea y destruye para cada ciclo de integración, lo que garantiza que el código se pruebe en un entorno limpio y repetible.

El objetivo de la integración continua es detectar y solucionar problemas en el código de manera temprana, lo que ayuda a reducir el tiempo y el costo de corrección de errores más adelante en el ciclo de desarrollo. Además, al integrar y probar regularmente el código, se mejora la calidad del mismo y se facilita el proceso de entrega de software.

Podemos emplear la integración continua (CI) en un proyecto dbt en las pull request (cuando solicitamos unir nuestra rama a la principal) usando webhooks de Github, Azure DevOps o Gitlab. Cuando una PR está aprobada se envía un webhook a dbt cloud que encola una nueva ejecución del job correspondiente. La ejecución del job se realiza sobre un esquema temporal que se crea y autodestruye. La PR no realizará el merge hasta que finalice la ejecución del job. Vamos a hacer una prueba (puedes consultar aquí toda la documentación):

1. Creamos un nuevo un job cuyo trigger sea Continious Integration (CI) y activamos la opción de Run on Pull Requests?

Si no nos aparece el check Run on Pull Requests? debemos reconfigurar la conexión a Github y usar la conexión nativa desde dbt. Es necesario seguir los siguientes pasos:

1. Conecta tu cuenta de dbt con Github y otorga permisos de lectura/escritura sobre el repositorio que vayas a utilizar. Desde Profile Settings > Linked Accounts seleccionamos Github y en el botón Configure integration with Github. Más info en esta nota de dbt.
2. Desconecta en el proyecto la configuración actual de Github por SSH desde Account Settings > Projects (analytics) > Github connection pulsamos en editar y abajo a la izquierda aparece el botón Disconnect.
3. SI volvemos a la pantalla de configuración del proyecto y pulsamos de nuevo en Repository Details podemos seleccionar de nuevo el proveedor de repositorios. Esta vez en lugar de clonar, vamos a conectarnos directamente a Github y seleccionar un repositorio:

2. Actualizamos nuestro proyecto con la última foto de la repo si es necesario (pull) y creamos una nueva rama (create new branch):

3. Hacemos cualquier cambio en alguno de los models. Hacemos commit del cambio y finalmente una PR:

4. Desde Github aprobamos la PR.

Si nos dirigimos a dbt podemos ver como se ha encolado una nueva ejecución del job:

Si revisamos los pasos del job podemos ver que se ha disparado desde una PR y que se crea un esquema temporal en nuestro dataset de BigQuery con nombre dbt_cloud_pr_219037_6. Este esquema se autodestruye cuando finaliza el job.

Google Data Studio

Google Data Studio es una herramienta gratuita basada en Looker Studio de visualización de datos y creación de informes que permite a los usuarios conectarse a múltiples fuentes de datos, como Google Analytics, Google Ads, Google Sheets, bases de datos y más, para crear informes personalizados y visualizaciones interactivas.

La plataforma ofrece una interfaz gráfica fácil de usar que permite a los usuarios diseñar y personalizar informes con diferentes tipos de gráficos, tablas, indicadores clave de rendimiento (KPIs) y otras visualizaciones, y compartirlos con otros usuarios de forma segura y fácilmente.

Básicamente tenemos dos tipos de elementos: informes y fuentes de datos. Los primeros son los cuadros de mando con las visualizaciones y los segundos son los conectores con las tablas de los sistemas fuente. El primer paso para generar un dashboard es configurar las fuentes de datos.

Fuente de datos (Data Source)

Una fuente de datos es una conexión a una tabla de nuestro sistema origen (BigQuery en el caso del ejemplo). Por defecto infiere la estructura de datos de la base de datos, pero podemos modificarla o crear nuevos elementos como campos y parámetros:

• Campos: podemos crear nuevos KPI o campos derivados a partir de otros de la tabla utilizando el catálogo de funciones disponible en la documentación de Google Data Studio.
• Parámetros: los parámetros permiten interactuar de forma dinámica al usuario con los datos del informe. Mediante entrada de datos, podemos por ejemplo realizar estimaciones de algún cálculo en base al valor que haya introducido el usuario.

Seguimos los siguientes pasos para configurar una fuente de datos:

1. Pulsamos en Crear y seleccionamos fuente de datos. En el nuevo panel buscamos por el conector de BigQuery:

2. Autorizamos el acceso de Google Data Studio a nuestro BigQuery:

3. Seleccionamos la tabla que queremos usar como origen de datos:

4. El último paso para la fuente de datos es revisar y confirmar la estructura de datos que ha inferido del sistema origen. En este punto también podemos realizar tareas de transformación y crear nuevos campos y parámetros. Por ejemplo, aunque podemos hacerlo al vuelo mientras diseñamos un informe, desde este punto podríamos crear el campo month_pickup que segmente los datos por mes con la fórmula month(pickup_datetime):

O en tiempo de diseño, desde el blade de datos:

Informes en Google Data Studio (reports)

Crear un informe en Google Data Studio es muy sencillo, siguiendo la tendencia actual de herramientas como Power BI de Microsoft, Qlik Sense o MicroStrategy Visual Insights. Tenemos a nuestra disposición un lienzo en blanco sobre el que vamos a construir visualizaciones en base al set de datos que hemos configurado: seleccionamos el control, el gráfico sobre el que configuramos sus dimensiones y métricas y ¡listo! Veamos qué son estos dos tipos de elementos:

• Controles: objetos que nos permiten interactuar con los datos de las visualizaciones, por ejemplo, filtros de selección, cajas para introducir texto, listas desplegables con todos los valores de una dimensión, etc.
• Gráficos: o visualizaciones, son todo tipo de gráficos estadísticos que podemos utilizar para analizar y presentar la información: gráficos de tartas, barras, línea, burbujas, etc. En función del gráfico seleccionado debemos elegir una o varias dimensiones y una o varias métricas.

Tras unos minutos de esfuerzo podemos disfrutar de nuestro primer report en Google DAta Studio: