Advanced CI in dbt: compare changes on pull requests

1.What Advanced CI adds to a CI job

A standard CI job answers one question: does the changed code build, and do its tests pass? It runs dbt build --select state:modified+ in a temporary schema and posts a pass or fail status to the pull request. Code can pass every test and still change the data it produces, for example a join that quietly duplicates rows or a filter that drops a region. Advanced CI closes that gap. Its main feature, compare changes, shows you the data differences a code change causes, not only whether it ran.

Advanced CI is something you switch on. It is not part of every account. Three conditions must all hold before the option appears. The account is on a dbt Enterprise or Enterprise+ plan. An administrator has enabled Advanced CI features for the account. The warehouse is BigQuery, Databricks, Postgres, Redshift or Snowflake. Miss any one and the CI job settings page has no compare checkbox at all. If you are on the Starter plan, you still get CI jobs, concurrent CI checks and smart cancellation of stale builds, but not compare changes.

The results appear in two places. In dbt, the Compare tab on the job run details page lists the changes to primary keys, rows and columns. In the Git provider, dbt posts a summary of the same comparison as a comment on the pull request, so a reviewer sees the data impact next to the code diff.

Sources12

2.Turning on dbt compare in a CI job

Compare changes is a single checkbox, dbt compare, in the Execution settings of a CI job. It sits beside the Commands and Linting options. Turning it on tells dbt to compare the last applied state of production with the latest commit on the pull request. The rest of the setup decides whether that comparison is useful.

A primary key is how dbt matches a row in the CI build with the same row in production. Without one it cannot tell whether a row changed, was added or was removed, so it reports a "Primary key missing" error for that model. Add a uniqueness test or a primary key constraint to every model you expect reviewers to rely on in the Compare tab.

Compare changes also needs something to compare against, and deferral supplies it. Deferral points the CI job at another environment's latest successful run and uses the artifacts that run left behind, such as its manifest, as the reference state. dbt relies on that state twice: to find which models changed, so state:modified+ selects only those, and as the production baseline the CI build is diffed against. The Compare changes against an environment (Deferral) setting defaults to Production, and state comparison only works when a deferred environment is selected. The baseline is the latest successful run in that environment, and that run can come from any job that updates artifacts there. If many jobs with different settings write to the deferred environment, the comparison baseline moves in ways that are hard to predict. How deferral itself is configured is covered in [setting up a CI job with deferral](lesson:4.1).

Comparisons cost warehouse time, and two settings keep that cost in check. Run timeout cancels the job once it runs too long, and turning on dbt compare changes its default to 3600 seconds (one hour). The comparison command can also be customised. For example, a very large model that takes ages to diff can be left out with --exclude, and custom syntax can also change how deferral is used or exclude groups of models by tag.

Sources2314

3.Using event_time to compare matching time ranges

Many teams build only part of the data in CI, often controlled by an environment variable or the target name. That keeps CI fast and cheap, but it makes a naive data diff misleading. Suppose CI builds only the last 7 days. Compare changes then reads every older production row as missing and reports them as "deleted rows", even though the code deleted nothing. The reverse also happens. If the CI job ran more recently than production, its fresher rows show up as "new" data when they are simply newer.

Setting event_time on a model fixes both cases. With it set, compare changes limits the comparison to the timeframe that exists in both the CI and production environments. Only real differences are reported, and the comparison finishes faster because it scans less. event_time can be set on models, seeds, snapshots and sources. Point it at the column that records when the row's event actually happened, such as account_created_at, rather than an ingestion date.

models:
  - name: model_name
    config:
      event_time: my_time_field

models:
  - name: model_name
    config:
       ? : my_time_field

Sources15

4.Credentials, cached samples and the same-host rule

Compare changes runs SQL with the same credentials as the CI job, meaning the connection set in the CI job's environment. That choice has a consequence for governance. Every user on the account can see the comparison results and the cached sample rows. Whatever the CI credentials can read can therefore end up in front of anyone with access to the account. Restricting those credentials is the dbt administrator's job, and using synthetic data in CI is a valid choice when pre-production data is sensitive.

Dynamic data masking does not carry through. Masking in the warehouse depends on who runs the query, but the cached Advanced CI output is fixed when the CI credentials fetch it. A user who would normally see masked values may see unmasked ones in the Compare tab. To avoid this, limit access to unmasked data for the CI connection, or test with synthetic data.

To show example changes without querying the warehouse again, dbt caches a small sample of at most 100 records per modified model. The cache is encrypted, stored in Amazon S3 or Azure blob storage in the account's region, and kept for up to 30 days. If you open a CI run older than that, you see a message that the data has expired instead of comparison results. Review data changes while the pull request is still active.

Compare changes also has a hard requirement that is easy to miss: the CI model and the production model must live on the same database host or connection. The comparison is a query run in the CI environment that reads both, for example ci.dbt_cloud_123.foo against prod.analytics.foo. If the CI environment connects to abc123.rds.amazonaws.com and production to def456.rds.amazonaws.com, the CI connection cannot reach the production object, so the comparison does not work.

Sources1

5.Compare changes in development versus Advanced CI

The same idea also exists in development, and the exam can test which one fits a situation. Compare changes in development runs on demand in the dbt VS Code extension, which is powered by dbt v2. It previews the data effect of local edits to one model at a time, before any pull request exists. Advanced CI runs automatically for every modified model when a pull request opens or updates. It uses the CI job's credentials and posts its summary where reviewers will see it.

Use the editor version to catch a bad join while you are still writing the model, when fixing it costs nothing. Use Advanced CI when a reviewer needs a record of the data impact attached to the pull request, covering every model the change touches.

Sources61

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Compare changes is available on any plan that supports CI jobs.Why is that wrong?

   CI jobs are available on all plans, but compare changes needs an Enterprise or Enterprise+ account with Advanced CI features enabled by an administrator.

   Covered in What Advanced CI adds to a CI job

2. 2.Once dbt compare is ticked, every modified model gets a row-level diff automatically.Why is that wrong?

   Record-level comparison and primary key analysis need a primary key constraint or uniqueness test on the model. Without one, dbt reports "Primary key missing".

   Covered in Turning on dbt compare in a CI job

3. 3.Deleted rows in a compare report always mean the code change removed data.Why is that wrong?

   When CI builds only a subset of dates, compare changes reads the excluded rows as deleted unless event_time limits the comparison to the overlapping timeframe.

   Covered in Using event_time to compare matching time ranges

4. 4.Warehouse dynamic masking protects values shown in the Advanced CI Compare tab.Why is that wrong?

   The cached sample is fetched with the CI job's credentials and is not re-masked for each viewer, so unmasked values can reach users who would normally see masked ones.

   Covered in Credentials, cached samples and the same-host rule