Analytics Engineering: Building Data like Software

Introduction

Data modeling is the process of creating a conceptual model of a data landscape to represent things, events, concepts, and the relationships between them. Modelling can happen at any stage:

• In your SQL query. This is quick but siloed (not version-controlled) and can be computationally costly.
• In your BI tool: This establishes a shared understanding and works well bring together model relationships. Computationally more efficient than a vanilla SQL query, but will get unwieldy with more complex transformations (if required).
• In your Analytical Warehouse: Computationally efficient and handles complexity well. Easy to debug and closer to what engineers operate on, but comes with a moderate learning curve. This is the focus of this article.

Which approach you choose depends on the stage of your company, but it is undeniable that you will need to model your data as your business scales. To understand more, see this article.

Benefits of Analytics Engineering

Once you’ve decided that it looks worthwhile to invest in modelling data in your data warehouse, it’s worthwhile seeing the analytics engineering paradigm to the data modelling problem.

At its core, analytics engineering is about modelling data thoughtfully to create trustworthy, accessible datasets in the data warehouse. Just like software engineers modularize code into reusable libraries and APIs, analytics engineers componentize business logic into composable data models. The benefits of this approach are immense:

As a Founder or Product Manager:

• Creates a data schema aligned to the product and business model. Common data content in language further facilitates collaboration.
• As the company evolves, the data architecture flexes to capture new metrics and dimensions. This facilitates making data-informed strategic decisions with confidence.

As a Data Engineer:

• Get to focus on data infrastructure management, metadata management and data tooling rather than directly tackle content management.
• Separation of environments prevents downtime which is a key problem with self-serve data motions.
• Modular data models and reusable templates reduce duplicate work across teams.

As a Data Analyst:

• With ultimate control over definitions, there is less dependence on data engineers to deploy and manage their transformations
• Version control allows for the ability to roll back changes as needed. It also prevents context drift.
• Makes your queries more performant by distilling to business-relevant entities.

It’s also worthwhile highlighting some drawbacks:

• The overhead of DBT's test-driven approach can sometimes be overkill for early-stage startups.
• Similarly, large enterprises with complex legacy data environments may find DBT hard to adopt holistically. As with any technology, the right solution depends on the context.

DBT for Analytics Engineering

DBT treats data transformations as code, allowing standard software development practices like modularity, testing, CI/CD, and documentation.

When to Use DBT

• To build key data assets in a dedicated DWH DB, i.e. for Transformations exclusively, only if you subscribe to the Extract-Load-Transform paradigm (in that order)
• If using SQL to do these transformations
• For Batch processing transformations

When to not use DBT

• If you need to use it for Real-time
• If running it on your production DB
• If you need to test data consistency in your production DB
• If you need to Transform before loading

More on this topic can be found here

Interested in how to apply Analytics Engineering to your data warehouse? We’d love to talk! Book a chat with us here.