Leveraging Looker's Native Derived Tables (NDTs) for ETL Processes

Extract, Transform, Load (ETL) processes are at the heart of any data-driven organization, enabling the conversion of raw data into meaningful information. Looker introduces an efficient and scalable way to manage ETL processes using Native Derived Tables (NDTs). Let's delve into what NDTs are and how you can leverage them to streamline your ETL processes.

Understanding Native Derived Tables

Native Derived Tables (NDTs) in Looker are similar to regular tables, but they are computed on the fly by executing a SQL query defined within LookML. Unlike Persistent Derived Tables (PDTs), NDTs are not saved to a physical table and do not persist across queries.

Defining an NDT is simple. For example, let's create an NDT that extracts a subset of data from a larger orders table:

view: recent_orders {
  derived_table: {
    sql: SELECT order_id, order_date, customer_id, total_price
          FROM orders
          WHERE order_date >= CURRENT_DATE - INTERVAL '7 DAY' ;;
  }
  ...
}

This recent_orders NDT fetches the orders from the last seven days whenever it's queried.

Leveraging NDTs in ETL Processes

NDTs can be very useful in ETL processes, mainly for the transformation part. Here are some ways you can leverage NDTs:

1. Data Transformation:

NDTs can transform raw data into a more analysis-friendly format. You can perform various operations like aggregation, filtration, column renaming, datatype conversion, etc., within the NDT's SQL.

view: orders_summary {
  derived_table: {
    sql: SELECT customer_id,
                 COUNT(order_id) as num_orders,
                 SUM(total_price) as total_spent
          FROM orders
          GROUP BY customer_id ;;
  }
  ...
}

This orders_summary NDT transforms the raw orders data into a summary view with the number of orders and total spent for each customer.

2. Data Consolidation:

If your data is spread across multiple tables, you can consolidate it using NDTs. You can join multiple tables within an NDT's SQL, creating a unified view for analysis.

view: orders_customers {
  derived_table: {
    sql: SELECT o.order_id, o.order_date, o.total_price,
                 c.customer_id, c.first_name, c.last_name
          FROM orders o
          JOIN customers c ON o.customer_id = c.customer_id ;;
  }
  ...
}

This orders_customers NDT consolidates data from the orders and customers tables.

3. Data Pre-aggregation:

For large datasets, running aggregations on the fly can be slow. You can use NDTs to pre-aggregate your data, reducing query times.

view: daily_sales {
  derived_table: {
    sql: SELECT DATE(order_date) as date,
                 SUM(total_price) as total_sales
          FROM orders
          GROUP BY DATE(order_date) ;;
  }
  ...
}

This daily_sales NDT pre-aggregates the sales data on a daily basis.

Optimizing NDT Usage

While NDTs are powerful, they are best for smaller transformations or datasets due to their non-persistent nature. For large transformations or repeated computations, consider using PDTs instead to save computational resources.

In conclusion, Native Derived Tables offer a powerful way to perform on-the-fly transformations, making them a crucial tool in your ETL toolkit. By effectively using NDTs, you can streamline your ETL processes and empower your team to perform more complex analyses with ease.