Data platform audit checklist: how to review dbt, Airflow and Snowflake

A data platform audit often begins with a specific complaint.

A pipeline fails too frequently. A dbt project has become difficult to change. Snowflake costs are rising. Dashboard data arrives late and the team cannot identify the source of the delay.

Reviewing the affected code is useful, but it covers only one part of the platform. Reliability also depends on orchestration, alerting, access controls, warehouse configuration, deployment practices and clear ownership.

This audit framework covers five areas:

1. Orchestration and reliability
2. Secrets and access
3. Warehouse cost and workload management
4. dbt project controls
5. Documentation and ownership

The output should be a prioritised remediation backlog. Each finding needs an impact, owner, estimate and target date.

1. Orchestration and reliability

Start with recent execution history.

Review at least 30 days of DAG runs and record:

• repeated task and DAG failures
• missed or delayed schedules
• retry frequency
• unusually long execution times
• manual reruns
• incomplete backfills
• freshness failures
• recurring incidents affecting the same pipeline

A successful final task does not always mean the pipeline operated correctly. Repeated retries may hide instability. A completed dbt run can also build valid models from stale source data.

Check how failures reach the team

Each critical DAG should have a defined alert path. The notification should identify:

• the DAG
• the failed task
• the run identifier
• the execution time
• the current retry state
• a direct link to the relevant logs

Airflow supports DAG-level and task-level callbacks, including failure and retry callbacks. Current Airflow releases also provide Notifiers and Deadline Alerts for runs that exceed a configured time threshold. Callback failures need monitoring as well, because their errors may appear outside the task logs.

Assign operational ownership

Every business-critical pipeline needs a named owner.

Ownership should cover:

• routine failures during business hours
• urgent incidents
• source-system outages
• failed backfills
• communication with data consumers
• escalation when the owner is unavailable

The owner does not need to resolve every incident personally. They remain responsible for coordinating the response and confirming that service has been restored.

Review retry and backfill behaviour

Retries should match the failure mode.

A temporary network error may justify a retry. Invalid credentials or malformed source data usually require intervention. Repeating the same request several times can also create duplicate data when the operation is not idempotent.

Backfills need documented behaviour for:

• existing successful runs
• failed runs
• concurrent processing
• run ordering
• partial completion
• downstream publication

Airflow 3 provides explicit backfill reprocessing modes and independent concurrency controls. These options should be chosen deliberately rather than left to an improvised manual process.

Run freshness checks before publication

Source freshness checks should run early enough to prevent stale data from reaching downstream reports.

The audit should identify:

• which sources are business-critical
• how freshness is calculated
• the warning and error thresholds
• the team receiving alerts
• whether downstream models continue after a failed freshness check

A pipeline can finish successfully while processing data that stopped arriving several days earlier.

2. Secrets and access

A repository and deployment review should include a search for:

• database passwords
• private keys
• API tokens
• webhook URLs
• committed .env files
• credentials embedded in CI configuration
• shared service-account passwords

Any exposed credential should be rotated. Removing it from the latest commit does not remove it from Git history.

Review Airflow credential storage

Airflow Connections may be stored in the metadata database, environment variables or an external secrets backend.

Sensitive values stored in the Airflow metastore are encrypted with Fernet. An external secrets backend, such as AWS Secrets Manager or HashiCorp Vault, keeps the secret outside the Airflow database. These approaches have different operational and access implications.

The audit should verify:

• where each connection is stored
• which components can read it
• how Fernet keys are managed and rotated
• whether duplicate values exist in several secret stores
• whether credentials differ between development and production

Airflow Variables should contain runtime configuration rather than general-purpose credentials.

Review Snowflake authentication

Long-lived shared passwords create avoidable operational risk.

For cloud workloads, Snowflake workload identity federation can use AWS IAM, Microsoft Entra ID or Google Cloud service identities without storing a long-lived Snowflake credential. Key-pair authentication and OAuth remain appropriate alternatives depending on the runtime and identity architecture.

The review should also cover:

• inactive service accounts
• excessive role grants
• shared human accounts
• access retained after role changes
• credential rotation
• ownership of authentication policies

3. Warehouse cost and workload management

Create an inventory of analytical workloads before changing warehouse configuration.

Typical categories include:

• ingestion
• scheduled dbt transformations
• ad hoc analyst queries
• BI dashboard refreshes
• data science workloads
• exports and customer-facing data products

Record which warehouse each workload uses, who owns it and how its cost is attributed.

Separate workloads when there is a reason

Multiple workloads may share a warehouse successfully when their schedules, access requirements and performance profiles are compatible.

Separation becomes useful when the team observes:

• persistent query queueing
• one workload delaying another
• incompatible access requirements
• unclear cost attribution
• different availability requirements
• unpredictable ad hoc queries affecting scheduled transformations

Creating more warehouses also creates more resume cycles and more configuration to manage. The decision should follow observed workload behaviour.

Review Snowflake cost controls

Resource monitors can track warehouse credit consumption, send notifications and suspend assigned standard warehouses after configured thresholds. They cover user-managed warehouses and do not control spending from all serverless features or AI services. Snowflake budgets are needed for broader cost monitoring. Notifications also require explicit configuration.

For each material warehouse, review:

• credit limits and alert thresholds
• notification recipients
• response procedures
• auto-suspend configuration
• warehouse size
• scheduled operating periods
• ownership

Auto-suspend should match the workload cadence. A low value works well for intermittent batch workloads. Frequent suspend and resume cycles may be inefficient when queries arrive every few minutes because each warehouse start carries a minimum billing period.

Inspect queueing and spilling

Snowflake QUERY_HISTORY exposes several useful operational metrics:

• queued_overload_time
• queued_provisioning_time
• bytes_spilled_to_local_storage
• bytes_spilled_to_remote_storage
• execution and compilation time

queued_overload_time measures time spent waiting because the warehouse was overloaded. queued_provisioning_time measures time spent waiting for compute resources to start, resume or resize. Remote spilling may indicate that the query requires more memory than the warehouse can provide efficiently.

These metrics should be interpreted alongside:

• query type
• warehouse size
• execution schedule
• concurrency
• data scanned
• user or service account
• workload priority

Plan full refreshes and backfills

Large refresh operations should have an estimate for:

• expected runtime
• warehouse size
• credit consumption
• source-system impact
• downstream availability
• rollback
• validation after completion

The plan should also state whether normal scheduled workloads can run at the same time.

4. dbt project controls

The dbt review should cover project structure, model configuration, testing, environments and CI.

Naming and layering

Naming conventions should reflect the project architecture.

A typical structure may include:

• stg_ for staging models
• int_ for intermediate transformations
• fct_ for fact models
• dim_ for dimensions

The exact prefixes matter less than consistent usage and documented expectations. Reviewers should be able to understand the model layer and intended responsibility without opening every SQL file.

Sources and freshness

Raw tables should be declared as dbt sources and referenced with source() from staging models.

Source definitions provide:

• source-level lineage
• documentation
• freshness configuration
• source-specific tests
• clearer dependency graphs

Current dbt source freshness can use loaded_at_field, loaded_at_query or adapter-supported metadata, depending on the source and adapter. Freshness thresholds still need to be configured and the freshness command or managed job needs to run.

Prioritise sources that directly affect:

• financial reporting
• customer-facing data
• operational decisions
• regulatory processes
• contractual SLAs

Model contracts

Contracts are useful on stable models whose schemas are consumed by dashboards, exports, applications or other teams.

A contract should define the expected columns and data types. Enforcing it causes incompatible schema changes to fail during the build rather than appearing later in a downstream consumer.

Contracts need an ownership process. When a schema change is intentional, the team should update the contract and coordinate the release with affected consumers.

Incremental models

Each incremental model should have an explicit policy for schema changes.

Review:

• on_schema_change
• incremental strategy
• unique_key
• late-arriving data
• lookback window
• full-refresh behaviour
• idempotency
• backfill procedure

unique_key is appropriate when the model updates existing records. Append-only event models may intentionally omit it. The choice should match the grain and update behaviour of the source data.

on_schema_change should also be deliberate. Available behaviours differ in how they ignore, reject, append or synchronise columns. None of them automatically backfill historical rows with new column values.

Test the declared grain

Every critical model should document its grain.

Tests should confirm that grain using:

• not_null
• unique
• relationship tests
• accepted values
• unique_combination_of_columns
• custom reconciliation tests where required

Testing every column creates noise. Prioritise the columns and assumptions that define the model's meaning.

CI and environment isolation

dbt Slim CI uses a previous manifest with state selection and deferral to build and test modified resources and the relevant downstream graph. It requires production artifacts and a correctly configured --state path.

A representative command is:

dbt build \
  --select state:modified+ \
  --defer \
  --state path/to/prod/artifacts

The audit should verify:

• the production manifest is current
• CI writes to an isolated schema
• failures block merging
• changed seeds, snapshots and macros are handled
• CI results are reviewed
• developers cannot overwrite production objects

Development, CI and production should use separate schemas and appropriately separated credentials.

Repository maintenance

Remove:

• stale models
• unused configuration
• dead SQL
• obsolete packages
• outdated comments
• abandoned feature flags
• unused sources
• documentation that describes old behaviour

Repository hygiene is usually lower priority than reliability or security findings. It still affects how safely engineers can understand and modify a project.

5. Documentation and ownership

Documentation should support operation and change management.

For every critical mart or data product, record:

• business purpose
• grain
• owner
• source systems
• refresh schedule
• SLA or expected availability
• downstream consumers
• known limitations

dbt exposures can connect dashboards, applications and other downstream consumers to the project graph. This makes impact analysis more useful when upstream models change.

Record architectural decisions

An architectural decision record should include:

• the selected approach
• the context at the time
• alternatives considered
• expected trade-offs
• owner
• review date

Review dates matter because warehouse sizes, team structures and workload patterns change.

Document deployment and recovery

A new engineer should be able to follow the written process for:

• deploying a change
• validating the deployment
• rolling it back
• rerunning failed data intervals
• handling a source outage
• notifying affected users
• closing an incident

Test the documentation during the audit. A runbook that has never been followed may contain missing permissions, outdated commands or dependencies known only to its author.

Turn findings into a remediation backlog

An audit report should help the team decide what to do next.

Each finding should contain:

• evidence
• affected systems
• operational or business impact
• recommended change
• effort estimate
• owner
• target date
• acceptance criteria

A practical prioritisation model separates findings into:

Immediate risk

Examples:

• exposed credentials
• stale critical data with no alert
• production access retained by former team members
• pipelines that cannot be safely replayed

Near-term reliability and cost

Examples:

• recurring failures
• persistent query queueing
• missing resource alerts
• unsafe CI configuration
• untested model grain

Longer-term maintainability

Examples:

• inconsistent naming
• incomplete documentation
• repository cleanup
• architectural decisions without review dates

The final deliverable should identify the highest-value work that fits the team's available engineering capacity.

Download the checklist

Use the three-page Data Platform Audit Checklist during an internal review to record findings across Airflow, Snowflake and dbt.

Northgrain also conducts independent platform audits covering reliability, warehouse costs, dbt project controls, security and operational ownership. Request a Data Platform Audit.