Dmytro Polishchuk

Model Access in dbt

As dbt projects grow, so do the challenges around collaboration, ownership, and reuse. What starts as a small analytics codebase can quickly evolve into a complex ecosystem of models shared across teams, domains, and even projects. Without clear boundaries, it becomes difficult to understand who owns what, which models are safe to reuse, and how to scale analytics without breaking downstream consumers.

This is where model access and groups come into play. Together, they form a powerful governance mechanism in dbt that helps teams structure responsibility, control visibility, and safely enable cross-project data sharing.

In this article, we’ll break down what groups are, how model access works, and how these features support scalable analytics and data mesh architectures.

What Are Groups in dbt?

A group in dbt is a named collection of nodes within a project. Groups provide a way to logically organize resources and define ownership, which becomes increasingly important as more people and teams contribute to the same dbt codebase.

Groups can include the following node types:

• Models
• Tests
• Seeds
• Snapshots
• Analyses
• Metrics

It’s important to note that sources and exposures are not included in groups.

There are a few key rules to understand:

• Each node can belong to only one group
• Every group must have a name
• Every group must have an owner

The owner definition requires at least a name and an email address. Typically, the owner represents a team rather than an individual, such as an Analytics or Finance team. This explicit ownership makes responsibilities visible and helps clarify who to contact when questions or issues arise.

Beyond organization, groups play a critical role in how dbt enforces model access rules. In fact, model access is defined and evaluated in the context of groups.

What Is Model Access in dbt?

Model access is a governance feature in dbt that controls how and where a model can be referenced. It works alongside other governance capabilities such as model contracts and model versions.

By assigning an access level to each model, teams can define which models are internal implementation details and which are intended for broader reuse.

dbt provides three access levels:

Private

Private models are the most restrictive. They can only be referenced by other models within the same group.

This is ideal for intermediate or helper models that support a specific team’s logic but are not meant to be consumed outside that context.

Protected

Protected is the default access level in dbt. Protected models can be referenced by any group within the same project, or by other projects if the project is installed as a package.

This level supports collaboration within a single dbt project while still preventing accidental exposure across project boundaries.

Public

Public models are designed to be consumed outside the project they are defined in. These models can be referenced from other dbt projects using cross-project references.

Public access is especially important for organizations adopting a data mesh approach, where teams expose trusted data products for others to consume.

One important limitation to keep in mind: models materialized as ephemeral cannot be public. Since ephemeral models are not materialized in the warehouse, they cannot be safely shared across projects.

Cross-project public access requires the Enterprise tier of dbt Cloud.

How Groups and Model Access Work Together

Groups and model access complement each other. While groups define ownership and responsibility, access modifiers define visibility and usage.

Consider the following example configuration:

groups:
  - name: analytics
    owner:
      name: Analytics team
      email: [email protected]

models:
  - name: orders_per_supplier_country_customer
    config:
      access: private
      group: analytics

  - name: orders_per_customer
    config:
      access: protected
      group: analytics

  - name: orders_per_country
    config:
      access: public
      group: analytics

In this setup, all models belong to the analytics group, but each has a different access level.

The private model can only be referenced within the analytics group. The protected model can be referenced anywhere inside the same project. The public model can be referenced by other projects entirely.

In the dbt DAG and lineage views, private and public models are visually marked, making it easier to understand boundaries at a glance.

This combination allows teams to clearly communicate intent:

• Which models are internal building blocks
• Which models are safe for internal reuse
• Which models are stable data products

Public Models and Cross-Project References

Public models enable collaboration across dbt projects. This is particularly valuable when different teams manage separate projects but still need to share data in a controlled way.

To make public models available to downstream projects, the upstream project must meet two conditions:

• An environment defined as PROD or STG
• At least one successful job run in that environment

These requirements ensure that dbt has generated the necessary metadata for downstream reference resolution.

In the downstream project, the upstream project is added as a dependency:

# dependencies.yml
projects:
  - name: hub_speak_dmytro_base

Once declared, public models from the upstream project can be referenced using a two-argument ref() function:

{{ ref('hub_speak_dmytro_base', 'dim_customer') }}

This pattern allows downstream teams to consume only the models explicitly marked as public, while all internal logic remains private or protected in the upstream project.

Why Model Access Matters for Data Mesh

Model access is a foundational capability for implementing data mesh with dbt.

By treating public models as stable interfaces, teams can publish data products with clear contracts and ownership. Downstream consumers rely on these models without needing to understand or depend on upstream implementation details.

At the same time, teams retain full control over what they expose. Internal experimentation and refactoring can happen safely behind private and protected boundaries.

This approach enables autonomy without chaos — a key principle of data mesh.

Conclusion

As dbt projects scale, governance becomes essential. Groups and model access provide simple but powerful tools to define ownership, enforce boundaries, and enable safe reuse of analytics models.

By thoughtfully combining groups with private, protected, and public access levels, teams can scale collaboration, support cross-project data sharing, and build reliable data products without sacrificing flexibility.

In upcoming sessions, we’ll demonstrate these concepts hands-on in dbt Cloud, showing how producer and consumer projects interact and how these relationships appear in the Catalog and lineage views.

If you’re working toward a scalable analytics or data mesh architecture, mastering model access in dbt is a crucial step.

Watch the Video

How to Validate Data Freshness in dbt Cloud

Ensuring that your data is fresh, reliable, and aligned with your SLAs is one of the most important responsibilities of any analytics engineering or BI team. In modern data stacks, dbt Source Freshness plays a key role in validating that upstream systems are loading data as expected. When used properly, it helps teams identify delays, pipeline failures, or missing updates before they impact models and downstream reporting.

This article walks through a full demo of how to configure, run, and monitor Source Freshness checks in dbt Cloud. It builds on the fundamentals introduced in the first video of our series, where we explained what source freshness is, why it matters, and how dbt evaluates freshness. If you haven’t watched that introduction yet, we recommend doing so first.

In this second part, we go hands-on: reviewing source configurations, running freshness checks using both fields and custom SQL queries, applying optional filters, triggering warnings and failures, and inspecting the results in the dbt Cloud UI and Catalog. By the end, you’ll have a clear understanding of how to integrate freshness checks into your workflows and jobs to maintain a highly trustworthy data foundation.

Understanding the Source Freshness Configuration

The demo starts inside a dbt Cloud project with a YAML file containing our source definitions. For this walkthrough, we are working with a source called dbt_talk_demo_sources, which includes two tables:

• customer_source
• employee_source

Inside the configuration block, we define the core freshness thresholds:

• warning_after: 30 minutes
• error_after: 60 minutes

These settings tell dbt when to flag a source as slightly stale (warning) or critically outdated (error). They are typically aligned with SLAs and expectations for how often upstream data should be updated.

Using loaded_at_field

For the customer_source table, we use a basic configuration: the table includes an updated_at timestamp column, which dbt uses directly to calculate the freshness. However, the timestamps in this demo are recorded in CET (Europe/Berlin), which means dbt converts them to UTC before evaluating freshness. This highlights a common real-world consideration: time zones must always be handled consistently in freshness checks.

Using loaded_at_query

For employee_source, we use a different approach. This table does not store a timestamp column. Instead, the load timestamps are stored in a metadata table. To handle this, we configure a loaded_at_query—a SQL query that retrieves the latest load time externally. This method is often used when:

• Timestamps come from an ETL metadata or logging table
• Data loads use high-watermark patterns
• You want more control over how freshness timestamps are calculated

In the demo, the query simply selects the MAX(updated_at) value from the metadata table. While simple, it demonstrates how flexible dbt is when working with custom data loading patterns.

Using Optional Filters

dbt also supports an optional filter configuration, which lets you skip certain rows when evaluating freshness. For example, if a table contains soft-deleted records or historical rows that should not count toward freshness checks, you can filter them out. In our demo, the filter excludes rows where deleted = TRUE, ensuring only active records contribute to the freshness calculation.

This becomes particularly useful when old records appear fresher than the latest valid ones, which could skew your results or hide actual issues.

Running Freshness Checks in dbt Cloud

With the configuration in place, we run our first freshness check via the CLI:

dbt source freshness

Before running the check, we insert new rows into the source tables so that the latest data delay is around 20 minutes. Since this is below both the warning and error thresholds, the run reports everything as green.

This confirms that both types of configurations—loaded_at_field and loaded_at_query—are working as expected.

Triggering a Warning

Next, we enable the earlier-mentioned filter configuration on customer_source. After filtering out the deleted rows, the next valid record has a delay of about 50 minutes. When we run:

dbt source freshness -s source:dbt_talk_demo_sources.customer_source

dbt reports a warning state, because the threshold of 30 minutes is exceeded. This demonstrates how filtering can impact the evaluation in meaningful ways.

Triggering a Failure

To understand how a failed freshness check behaves, we insert data with delays exceeding the 60-minute error threshold. Running the same command again produces an error state. The dbt output also shows the exact SQL query it executed to determine freshness—useful when troubleshooting unexpected results.

Including Freshness in dbt Cloud Jobs

dbt Cloud provides two ways to incorporate freshness checks into scheduled jobs:

Option 1: “Run Source Freshness” Checkbox

With this option enabled, dbt automatically runs dbt source freshness as the first step of the job. However, failures do not stop the rest of the job from executing. This mode is ideal when you want visibility but don’t want freshness violations to block model builds.

Option 2: Adding Freshness as a Job Step

Alternatively, you can include freshness checks as an explicit job step. In this case, if freshness fails, subsequent steps are skipped and the job fails. This is the preferred option when:

• Data reliability is critical
• Your models depend on up-to-date sources
• You want strong enforcement of data SLAs

The demo shows examples of both approaches, so you can choose which one best fits your project needs.

Monitoring Freshness in the dbt Cloud Catalog

dbt Cloud makes it easy to monitor freshness results long after the run completes. In the Catalog, you can drill down into each source and see the most recent freshness status, including warnings and errors. This gives data teams better visibility into upstream issues without needing to dive into logs.

For example, in our demo environment, the Catalog displays a warning icon for dbt_talk_demo_sources. Opening the source reveals the individual freshness statuses for each table. This is especially helpful in larger projects where tracking freshness manually would be impractical.

Key Takeaways

This demo highlights the full power and flexibility of dbt Source Freshness in real-world analytics environments. Here are the main lessons:

• Freshness thresholds provide an essential guardrail for data reliability.
• loaded_at_field is simple when the timestamp is in the table.
• loaded_at_query enables more advanced scenarios using external metadata.
• Filters help refine which rows count toward freshness.
• dbt distinguishes between OK, warning, and error states in a clear, actionable way.
• Freshness checks can run as part of your dbt Cloud jobs with configurable strictness levels.
• The dbt Cloud Catalog provides ongoing visibility into the freshness of all sources.

By combining these tools, you can ensure your source data stays timely, trustworthy, and perfectly aligned with your organization’s SLAs. This ultimately improves downstream analytics quality, enhances user confidence, and reduces the risk of building insights on outdated data.

Watch the Video

dbt Source Freshness

Data teams rely on timely, accurate, and complete data to support dashboards, KPIs, reporting, and data-driven decision making. But even the most advanced data models and transformation logic cannot fix one critical issue: stale or outdated upstream data. This is where dbt Source Freshness becomes one of the most valuable quality checks in your analytics engineering toolkit.

In this article, we take a close look at what dbt Source Freshness is, why it matters, how it works under the hood, and how you can configure and run freshness checks both locally and in dbt Cloud. If your organization depends on reliable data pipelines—or if you’ve ever discovered too late that a report was built on old data—this guide will help you avoid those costly surprises.

What Is dbt Source Freshness?

Source freshness in dbt is a built-in mechanism that measures how up-to-date data is in your defined source tables. While data transformations can apply logic, aggregations, and business rules, they inherently depend on data arriving on time. If source data is delayed, incomplete, or entirely outdated, every model downstream will reflect that delay.

dbt Source Freshness provides a simple, reliable indicator of whether the data you are working with is fresh enough to support your operational and analytical processes. It helps you answer one crucial question:

“Is the data I’m transforming actually the latest data available?”

When enabling freshness checks, dbt evaluates the most recent timestamp from a specified column in your source table and determines whether that timestamp violates your defined freshness thresholds. These thresholds act as data SLAs for your pipeline.

Why Source Freshness Matters

The importance of monitoring source data freshness cannot be overstated. When upstream data is stale, the consequences cascade throughout your entire analytics ecosystem. Dashboards may show outdated KPIs. Operational teams may make decisions based on incomplete numbers. Forecasts and reports may misrepresent the true state of the business.

One scenario that many data teams have encountered illustrates the problem perfectly: a business report runs on what everyone assumes is the latest data. After a few weeks, the team discovers that the upstream system had stopped updating its tables entirely. What appeared to be fresh data was actually months old. As a result, the report generated incorrect metrics for an extended period.

With source freshness monitoring in place, delays like these can be caught immediately. dbt highlights them clearly, allowing teams to:

• Detect upstream system failures.
• Identify delays in ingestion or replication pipelines.
• Enforce data delivery SLAs with source system owners.
• Stop inaccurate transformations from running on stale data.

Freshness checks turn what could be a hidden issue into a transparent, actionable signal.

How dbt Source Freshness Works

Source freshness configuration lives directly inside the YAML file where your source is defined. This design decision is intentional—freshness belongs to the source, not to downstream models. Each source or table can have its own customized freshness rules.

A typical source block with freshness configuration looks like this:

sources:
  - name: my_source
    tables:
      - name: orders
        freshness:
          warn_after: {hours: 24}
          error_after: {hours: 48}
        loaded_at_field: updated_at

Let’s break down the key components.

loaded_at_field

This is the timestamp column dbt uses to determine when the most recent record arrived. dbt queries this field, finds the newest timestamp, and calculates its age relative to the current time.

Important: dbt always evaluates freshness in UTC time. If your source system stores local timestamps (e.g., CET, EST), the value in loaded_at_field must be converted to UTC.

Thresholds: warn_after and error_after

Freshness thresholds define what “fresh enough” means. dbt compares the age of the newest record with these time limits and returns one of three statuses:

• pass – the data is within the acceptable freshness window.
• warn – the data is late but not critically late.
• error – the data is beyond the maximum acceptable age.

These thresholds effectively act as SLAs, helping teams formalize expectations about data arrival. For example:

• Warn after 24 hours.
• Error after 48 hours.

If the source table hasn’t received new records in over 48 hours, dbt marks the freshness check as an error, signaling that the table is unreliable until updated.

What Happens During a Freshness Check?

When you run a freshness check, dbt performs a straightforward but effective procedure:

1. dbt queries the loaded_at_field and finds the most recent timestamp.
2. It calculates the time difference between that timestamp and the current UTC time.
3. It compares the age of the data to your defined thresholds.
4. It returns a pass, warn, or error result.

This process is intentionally lightweight and fast. It avoids unnecessary complexity while giving teams a dependable, high-value signal about upstream data timeliness.

How to Run Freshness Checks in dbt

Running a freshness check in dbt is simple. The main command is:

dbt source freshness

This command evaluates freshness for all sources that have freshness configurations defined. You can also target a specific source or table:

dbt source freshness --select source:my_source
dbt source freshness --select source:my_source.orders

When executed, dbt displays the freshness status for each table along with metadata such as:

• The latest timestamp found.
• The calculated age of the data.
• The threshold values used.

Running Freshness Checks in dbt Cloud

dbt Cloud makes managing freshness checks even easier. You can create a dedicated job that runs only freshness checks, or you can add freshness as a step in a larger job. This enables automatic monitoring without requiring manual execution.

Once the job completes, results appear directly in the dbt Cloud UI. For each table, you can see:

• The age of the most recent record.
• Whether the table passed, warned, or errored.
• When the freshness check was last executed.

You can also inspect the detailed logs to understand exactly how dbt evaluated each source.

Why Freshness Checks Should Be a Standard Practice

In modern analytics engineering, data reliability is just as important as transformation logic. Freshness checks are a lightweight yet powerful way to ensure that your source systems are delivering data on time.

Without freshness checks, data issues may go unnoticed until they have already impacted dashboards, stakeholder decisions, or downstream processes. With freshness monitoring enabled, you gain visibility into problems early, allowing your team to respond quickly and prevent incorrect reporting.

As data ecosystems grow more complex—with multiple ingestion pipelines, third-party APIs, and event-based systems—freshness checks provide a simple, standardized way to maintain trust in your data.

Watch the Video

Publishing Power BI Reports with the dbt Semantic Layer

Welcome back to our two-part guide on connecting Power BI to the dbt Semantic Layer. In Part 1, we demonstrated how to connect Power BI Desktop to dbt Cloud and build a simple dashboard using semantic metrics. That was just the beginning.

In this article, we continue the journey by publishing that report to Power BI Service and configuring the On-premises Data Gateway to ensure the connection to the dbt Semantic Layer remains alive. This is a crucial step for moving from a personal development workflow to a shared, production-ready BI environment.

Why Power BI Service Needs a Gateway

When you publish a Power BI Desktop report connected to the dbt Semantic Layer, the connection details don’t automatically carry over to Power BI Service. Instead, you’ll likely see an error message such as “The model cannot be loaded”. This happens because:

• Power BI Service cannot directly use the desktop connection credentials.
• A secure, always-online connection is required to keep queries alive.

The solution is to use the On-premises Data Gateway. This component securely bridges Power BI Service with your data sources—whether on-premises or cloud-based—so your dashboards can refresh seamlessly.

Step 1: Publish the Report to Power BI Service

We’ll start where we left off in Part 1, with a working report in Power BI Desktop named DEMO – dbt Semantic Layer.

1. Open the report in Power BI Desktop.
2. Click the Publish button.
3. Choose a workspace (for this demo, we’ll use “My workspace”).
4. Open Power BI Service and locate the published report.

At this stage, you’ll notice that the report cannot load data. This is expected and is exactly why we need to configure the gateway.

Step 2: Install the On-Premises Data Gateway

To enable the connection, download and install the On-premises Data Gateway in Standard mode. The dbt Semantic Layer connector is not supported in Personal mode at this time.

Download link: Get the Power BI Gateway (Microsoft).

During installation:

• Choose Standard mode.
• Follow the guided steps and complete the setup.
• Launch the gateway after installation.
• Sign in with your Power BI Service account to register the gateway.

At this point, your gateway should show as online and ready to use.

Step 3: Configure Custom Connector Support

The dbt Semantic Layer connection is enabled through a custom Power BI connector. This requires a manual file copy:

1. Locate the connector file on your machine. By default, it is stored in:
   C:\Users\<YourUsername>\Documents\Power BI Desktop\Custom Connectors\dbtSemanticLayer.pqx
2. Copy this file into the gateway’s custom connector directory:
   C:\Windows\ServiceProfiles\PBIEgwService\Documents\Power BI Desktop\Custom Connectors
3. Restart the gateway if needed. The connector should now be detected under the Connectors section of the gateway.

This step is critical—without copying the connector, the gateway will not recognize the dbt Semantic Layer.

Step 4: Configure the Gateway in Power BI Service

With the gateway online and the custom connector installed, it’s time to configure the connection inside Power BI Service:

1. In Power BI Service, navigate to Settings → Manage Connections and Gateways.
2. Select your gateway from the list of available gateways.
3. Go to the gateway Settings and allow the use of Custom Connectors. Without this option, the connection will fail.
4. Next, go to Settings → Power BI Settings → Semantic Models.
5. Locate your published report (DEMO – dbt Semantic Layer).
6. Under Gateway and Cloud Connections, add your connector to the gateway and provide the required host, environment ID, and service token values.

Once these steps are complete, your Power BI Service report should start loading live data from the dbt Semantic Layer.

Step 5: Validate the Connection

Open your report in Power BI Service. This time, instead of the “model cannot be loaded” error, you should see the data loading and the dashboard visuals appearing. This confirms that the gateway is successfully maintaining the connection to the dbt Semantic Layer.

Summary of the Workflow

Let’s recap what we’ve achieved across both parts of this series:

1. Part 1: Connected Power BI Desktop to the dbt Semantic Layer and built a working dashboard using semantic metrics.
2. Part 2: Published the report to Power BI Service, configured the On-premises Data Gateway in Standard mode, enabled custom connectors, and restored connectivity to the dbt Semantic Layer.

The end result? A Power BI dashboard that queries dbt metrics live, end-to-end, even in the Service environment.

Best Practices

While the demo used a local laptop, in production environments the On-premises Data Gateway should be installed on a reliable, always-on machine, such as:

• An on-premises server.
• A cloud-hosted virtual machine (e.g., Azure VM, Amazon EC2).

This ensures the connection remains stable, and report refreshes happen as scheduled without interruption.

Conclusion

Connecting Power BI Service to the dbt Semantic Layer is a powerful way to extend governed, consistent metrics to your entire organization. By configuring the On-premises Data Gateway and enabling custom connectors, you ensure that your published reports continue to deliver real-time insights based on dbt’s semantic definitions.

If this guide was helpful, make sure to check out Part 1 if you haven’t already, and stay tuned for more deep dives into the dbt platform and BI integrations.

Additional Resources

• Part 1: Connecting Power BI Desktop to the dbt Semantic Layer
• Microsoft Power BI Gateway Download
• Official dbt Documentation

Watch the Video

Connecting Power BI to the dbt Semantic Layer

As organizations increasingly rely on data-driven decision making, the ability to connect business intelligence tools directly to semantic layers becomes essential. One of the most common requests we’ve heard from our community is: “How can I connect Power BI to the dbt Semantic Layer to expose metrics?”

In this guide, we’ll walk through the entire process of setting up and connecting Power BI with the dbt Semantic Layer. By the end, you’ll be able to query dbt metrics directly in Power BI and build interactive dashboards that stay in sync with your semantic models.

Why Connect Power BI with the dbt Semantic Layer?

The dbt Semantic Layer allows teams to define business metrics and dimensions in one central place. Instead of duplicating logic across BI tools, analysts and business users can rely on consistent definitions for KPIs such as revenue, churn, or order count. When Power BI is connected to this layer, dashboards automatically reflect the same trusted metrics already defined in dbt.

This integration helps:

• Maintain consistency in metric definitions across the organization.
• Reduce manual work for analysts when creating Power BI reports.
• Ensure real-time access to governed data models.

Pre-Requisites

Before starting, make sure you have the following:

• A working dbt Cloud project with a configured Semantic Layer.
• Permission to create or access a Service Token in dbt Cloud.
• Installed version of Power BI Desktop.
• Internet access to download the dbt Semantic Layer Power BI connector.

Step 1: Review the dbt Semantic Layer Setup

If you’re not familiar with how the dbt Semantic Layer is configured, check out Hernan Revale’s detailed session on setting up metrics and dimensions in dbt Cloud:
Watch the dbt Semantic Layer session here.

Step 2: Collect Required Credentials in dbt Cloud

Navigate to your dbt Cloud Dashboard and head to:

• Settings → Semantic Layer or Settings → Edit Semantic Layer

Here, confirm or configure the following:

1. Credentials for the deployment environment where your semantic models run.
2. A Service Token linked to the Semantic Layer. If you don’t have permission, ask your dbt admin to create one.
3. Your Environment ID and Host, which will be used in Power BI during connection setup.

Important: Store your Service Token securely. You’ll need it to authenticate Power BI.

Step 3: Install the dbt Semantic Layer Power BI Connector

Download the Power BI connector for dbt Semantic Layer from the official documentation:
Download the connector here.

Run the installer and follow the on-screen steps. After installation, verify it by checking the list of available drivers in the ODBC Data Sources. The dbt Semantic Layer connector should now appear in the list.

Step 4: Connect Power BI to the dbt Semantic Layer

Now that everything is set up, it’s time to establish the connection:

1. Open Power BI Desktop and start a blank report.
2. Search for dbt Semantic Layer in the available connectors.
3. Accept the beta notice (as the connector is still under development).
4. Provide the required details:
   • Host
   • Environment ID
   • Service Token
5. Choose DirectQuery (Import is not yet supported).
6. Click Load to access your metrics.

Step 5: Build a Simple Dashboard

Once the semantic model is loaded, you’ll see your dbt metrics in the Power BI fields pane. You can now build visualizations just like you would with any other dataset. For example:

• Create a stacked column chart with “Orders Total” on the Y-axis and “Customer Region” on the X-axis.
• Add slicers for Region, Market, or Segment to filter the data dynamically.
• Include a card visualization to highlight key metrics such as total revenue.

At this point, your Power BI dashboard is fully connected to the dbt Semantic Layer. Metrics are updated live and reflect the definitions you’ve configured in dbt Cloud.

Step 6: What’s Next?

In this tutorial, we focused on connecting Power BI Desktop to the dbt Semantic Layer. In the next part of this series, we’ll publish the report to Power BI Service and explain how to retain dbt connectivity in a collaborative environment.

Stay tuned for the next video and article, and don’t forget to subscribe to our channel for updates.

Conclusion

Connecting Power BI to the dbt Semantic Layer is a powerful way to bring consistent, governed metrics directly into your BI environment. With a few configuration steps, you can ensure that every report and dashboard your team creates in Power BI leverages the same trusted metric definitions managed in dbt.

This setup not only accelerates dashboard creation but also strengthens data governance across your organization. As the connector continues to evolve, we can expect even smoother integrations and more functionality in the near future.

Additional Resources

• Official dbt Documentation
• dbt Semantic Layer Setup Session (YouTube)
• Upcoming video: Publishing Power BI Reports with dbt Semantic Layer Access

Watch the Video

DBT Snapshots Question

This topic was inspired by a viewer’s question: “How can you build a Data Vault data warehouse downstream from dbt snapshots?”

What is a Snapshot in dbt?

A snapshot in dbt is a tool that captures and preserves changes in data for tables that may be updated over time. This enables historical analysis and ensures that changes in data are properly recorded.

Key Features of dbt Snapshots

• Definition: Snapshots capture historical data by preserving changes over time.
• Purpose: They allow analysts to look back at previous versions of data, supporting historical analysis, auditing, and compliance.
• Mechanism: Snapshots are built on Slowly Changing Dimensions (SCD) Type 2.
• Value: Essential for auditing, regulatory compliance, and data analysis.

To create snapshots, use the command:

dbt snapshot

They are also included in the broader dbt build command. Notably, snapshots cannot be rebuilt in a direct way to prevent accidental loss of historical data.

Metadata Fields

dbt adds four key metadata fields to snapshot tables:

• dbt_scd_id: Unique identifier for change tracking.
• dbt_updated_at: Timestamp of the latest update.
• dbt_valid_from: Start timestamp of the record.
• dbt_valid_to: End timestamp (when applicable).

Starting with dbt version 1.9, these field names are customizable.

Snapshot Strategies in dbt

• Timestamp Strategy: Tracks changes using an updated_at column. Recommended due to its performance.
• Check Strategy: Compares column values for changes. Useful when there is no reliable timestamp column but can be less efficient.

Handling Hard Deletes

By default, dbt does not track hard deletes. However, enabling invalidate_hard_deletes: true ensures that deleted records are marked with an updated dbt_valid_to timestamp. This is crucial for effectivity satellites in Data Vault modeling.

Demo: Data Vault Integration with dbt Snapshots

Source Data: Snowflake Sample Data

For the demo, we use Snowflake’s TPCH sample dataset:

• Database: snowflake_sample_data
• Schema: tpch_sf1
• Selected Tables: customer, orders, part, lineitem

Each table has a corresponding dbt snapshot:

• snap_customer
• snap_orders
• snap_part
• snap_lineitem

The snapshot configurations:

• Strategy: Check (due to lack of reliable timestamp columns)
• Hard Deletes: Enabled (invalidate_hard_deletes: true)

Target: Raw Data Vault Model

The Data Vault model consists of:

• Hubs: customer_h, order_h, part_h
• Links: orders_l (customer-order relationship), orders_parts_l (order line items)
• Satellites: customer_s, order_s, part_s, orders_parts_s
• Effectivity Satellites: Track historical changes with dbt_valid_from and dbt_valid_to

During the demo, we focus on the customer entity, tracking how changes in the customer table are reflected in its corresponding Data Vault satellites.

Conclusion

Using dbt snapshots as a persistent staging area (PSA) for a Data Vault-powered EDW enables accurate historical tracking. The combination of snapshot strategies and effectivity satellites ensures that data lineage and changes are well-documented and auditable. By leveraging Snowflake’s sample datasets, we can efficiently test and refine Data Vault implementations.

Watch the Video

What is a Microbatch?

Microbatch is an innovative incremental strategy designed for large time-series datasets. Introduced in dbt Core version 1.9 (currently in beta), it complements existing incremental strategies by offering a structured and efficient way to process data in batches.

Key features of Microbatch include:

• Utilizes a time column to define batch ranges.
• Supports reprocessing failed batches.
• Auto-detects parallel batch y
• Eliminates complex conditional logic for backfilling.

However, it’s not suitable for datasets lacking a reliable time column or requiring fine-grained control over processing logic.

How Microbatches Work

Microbatching works by splitting model processing into multiple queries (batches) based on:

• event_time: The time column defining batch ranges.
• batch_size: The time period for each batch (hour, day [default], month, year).

Each batch functions as an independent, atomic unit, meaning:

• Batches can be processed, retried, or replaced individually.
• Parallel execution enables separate, idempotent batch processing.

Batch replacement strategies vary by database adapter:

• Postgres: Uses merge.
• BigQuery, Spark: Uses insert_overwrite.
• Databricks: Uses replace_where.
• Redshift, Snowflake: Uses delete + insert.

Microbatch Model Configurations

When setting up a Microbatch model, the following configurations are required:

• event_time: Specifies the time column in UTC.
• batch_size: Defines batch granularity (hour, day, month, year).
• begin: Sets the start point for initial or full-refresh builds.

Optional configurations include:

• lookback: Processes prior batches for late-arriving records.
• concurrent_batches: Controls parallel execution (auto-detected by default).

Running Batches in Parallel

Parallel execution is automatically detected based on batch conditions and adapter support. However, users can override this behavior using the concurrent_batches setting.

Parallel execution is possible when:

• The batch is neither the first nor last in the sequence.
• The database adapter supports parallel execution.
• The model logic does not depend on execution order.

How to Backload Data

Backloading allows reprocessing historical data within a specific time range using the following command:

dbt run --event-time-start "2025-02-01" --event-time-end "2025-02-03"

This ensures that only batches within the defined range are processed independently.

Microbatch vs. Other Incremental Strategies

Microbatch differs from traditional incremental strategies by:

• Using independent queries for time-based batches.
• Eliminating the need for is_incremental() and complex SQL logic.
• Automatically selecting the most efficient operation (insert, update, replace) for each platform.

Conclusion

Microbatch is a powerful new approach to incremental data processing in dbt Core. By breaking down large datasets into manageable, parallelizable chunks, it simplifies data modeling while improving efficiency and scalability. However, it is essential to consider whether Microbatch suits your data pipeline’s requirements before implementing it.

Watch the Video

Watch the Video

Snapshots in dbt: A Quick Overview

dbt snapshots allow you to “look back” at historical data by capturing changes in your database tables. This is achieved by implementing type-2 Slowly Changing Dimensions (SCDs), which track how a row has changed over time. For example, you can keep track of an order’s status as it moves from ‘pending’ to ‘shipped’ to ‘delivered’.

How Snapshots Work

When you run dbt snapshot, it creates a new table with the same columns as your source table, plus additional metadata columns like dbt_valid_from and dbt_valid_to. On subsequent runs, dbt updates the dbt_valid_to column for changed records and adds new records with dbt_valid_to set to null.

dbt offers different strategies for detecting changes, including comparing timestamps, specific column values, or a unique key. You can also configure dbt to track deletions.

Snapshots Best Practices

• Store snapshots in a separate schema to keep your historical data organized.
• Snapshot raw data using the source function and select all columns.
• Avoid joins in your snapshot queries; instead, create separate snapshots and join them downstream.
• If you need to perform transformations, do so in ephemeral models before snapshotting.
• Schedule snapshots to run frequently to capture changes regularly.

By following these best practices, you can ensure that your dbt snapshots are accurate, efficient, and easy to maintain.