dbt in WSL: Reproducible SQL Models, Compilation, and Data Tests

dbt is a development workflow for turning SQL models into tested, documented data transformations. WSL is a good control environment when the project uses Linux tools, a Linux Python runtime, and warehouse adapters that are not installed on the Windows side. A local compile or test can validate project structure and model logic, but it does not prove that a production warehouse has the same dialect, permissions, data volume, query optimizer, or concurrency behavior.

dbt’s product and package names have changed over time. Current documentation describes a newer dbt v2 installation path while older open-source projects may still use dbt v1 packages and adapter-specific packages. Follow the installation instructions and release notes for the project’s chosen major version. Do not blindly replace dbt-core with a different package or combine v1 adapters with a v2 CLI without checking compatibility. Record dbt, adapter, Python, and warehouse versions in the project’s development setup.

Keep the project environment native to WSL

Store the repository, virtual environment or CLI configuration, generated target/ directory, profiles, and local fixtures under the Linux filesystem. Microsoft recommends this placement for Linux command-line workloads. It avoids mixing Windows line endings, path conventions, and file watcher behavior into a Linux build. Keep profiles.yml outside source control if it contains connection details; .gitignore should cover generated artifacts and local secrets without hiding source models or tests.

Use the official dbt install guide for the selected release. The current local installation guide documents the dbt package for dbt v2, while dbt v1 installation remains distinct. After installation, inspect dbt --version and the adapter list for the exact environment. Test the connection using the documented debug command only with a development account and a non-production target.

Define targets intentionally. A developer profile should point at a disposable schema or sandbox database, use minimal permissions, and clearly identify the environment. Avoid storing credentials in dbt_project.yml, model SQL, shell history, or a checked-in profile. Use environment variables or the supported secret mechanism for the selected adapter, and ensure shell startup scripts do not silently override the target between terminals.

Understand project parsing, compilation, and execution

A dbt project contains models, macros, tests, and configuration. dbt parse validates project structure and YAML, while dbt compile generates executable SQL and may require a data-platform connection and run introspective queries. Compile does not materialize the model’s SQL into its target relation, so the compiled query is a critical review artifact, not proof that the model will execute successfully against representative warehouse data or that production permissions are correct.

Use a narrow loop: parse the project, compile one selected model, inspect its compiled SQL, then run a small test against an isolated target. The command set and flags may differ by major version, so consult the current command reference and the project’s lockfile. For repeatable reviews, record the exact target and selected model, and avoid using broad selectors against a database that contains shared or valuable state.

A model’s materialization changes its effect. A view, table, incremental model, and ephemeral model have different warehouse behavior. Do not assume a local compile tells you how an incremental model will merge or replace data. Test incremental logic on a scratch schema with boundary timestamps, late-arriving rows, duplicate source keys, and rerun behavior. Document how the model handles an empty input and how a full refresh would affect the target.

Add data tests around meaningful contracts

Tests should express the properties downstream consumers rely on, not merely produce a green command. Common generic tests include uniqueness, non-null values, accepted values, and relationships between models. Current dbt documentation uses the data_tests key and explains that tests remains a supported alias in some versions. Keep YAML syntax aligned with the project version and validate it through dbt parsing rather than copying a snippet from an older article.

For example, a model can assert that a key is present and unique:

models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique

Do not interpret a test pass as proof that all business rules are covered. Add singular SQL tests for domain-specific invariants and tests for relationships or freshness where they matter. Run against an isolated target with enough representative data to expose duplicates and boundary errors, but keep test fixtures small enough for a workstation. A database can accept a test query while containing no rows, making assertions vacuously pass unless empty-source behavior is also checked.

Review WSL paths and generated artifacts

dbt writes generated files such as compiled SQL, logs, and manifests under configured target locations. Keep those artifacts on the Linux filesystem for native tools, and decide which ones are disposable versus useful evidence. Do not edit generated compiled SQL as if it were the model source. A developer can print the resolved project root and target path before running a command to avoid two independent project copies under /home and /mnt/c.

If a Windows IDE is used to edit the WSL repository, open the remote WSL path rather than a second Windows copy. Verify Git status from Linux and inspect file modes and line endings. A model that compiles in a Windows dbt process but is executed by a Linux CI runner can use different adapters, environment variables, and path resolution. Reproduce the CI command within WSL before deciding a failure is database-specific.

Keep source freshness and model correctness as separate assertions. A source can be reachable while its data is stale, incomplete, or missing a partition. Define the expected freshness window with the project owners, then validate it using the selected adapter’s supported workflow. Do not write a local timestamp comparison that assumes every warehouse stores timestamps in the same timezone or precision. Include time-zone boundaries and daylight-saving transitions in tests if those dates drive partitioning.

Pay attention to model selection and dependency graph effects. Selecting one model with --select can include parents or children depending on the selector syntax and modifiers. Review the selected nodes before running against a database, especially when an operation can rebuild tables. For a code review, record the compiled SQL, selected-node list, and test results so reviewers can see the database-facing operation.

If the project also generates dbt documentation, treat generated catalog artifacts as derived output. Keep descriptions and column contracts in source YAML or documentation blocks, then regenerate the site in a clean target directory. Compare documented lineage and schema with the compiled project graph. A docs site that builds can still be incomplete when models lack descriptions or sources are not represented; review content quality separately from command success.

Troubleshooting and change control

If dbt cannot find a profile, inspect DBT_PROFILES_DIR, the current home directory, and the exact target name. If parsing fails, inspect YAML indentation, Jinja syntax, package versions, and macros. If compile succeeds but execution fails, compare compiled SQL, adapter, role, schema, and source grants. If only incremental runs fail, test key selection and the affected time window against a disposable relation rather than repeatedly full-refreshing a shared schema.

For package upgrades, update the dbt CLI and adapters as a planned set. Re-resolve dependencies in a clean environment, run parse and compile, then execute the project’s narrow data tests. Do not upgrade a global Python environment shared by separate dbt repositories. Save the generated lock or constraints artifact used by the chosen toolchain and review changes before applying them in CI.

Acceptance criteria

Accept a WSL dbt environment when the project version and adapter are pinned, the correct Linux CLI and profile target are confirmed, parse and compile succeed, compiled SQL is reviewed for the selected model, and meaningful data tests pass against a disposable target. Confirm that secrets and generated state are not committed and that the same command works from a clean WSL shell.

WSL is a practical dbt control node for local model work. It does not replace warehouse integration tests, production permission review, query cost controls, or a tested deployment workflow.

Related:

Sources: