Equipping a connected-device data platform to scale with dbt.

The client develops connected water-monitoring technology that helps customers understand consumption, identify abnormal behavior, and manage resources more effectively.

As the company worked toward supporting 100,000 connected devices, its engineering team recognized that decisions made today would shape the reliability, cost, and maintainability of its data systems at that scale.

The engagement began while investigating an immediate production pipeline issue. That investigation exposed a broader architectural risk: business-critical Snowflake transformations were running, but much of their logic existed primarily as database procedures, functions, and scheduled tasks.

The immediate issue could be fixed. The more important opportunity was to strengthen the system around it before the company's device footprint—and the operational cost of a failure—grew substantially larger.

The client had already built a capable IoT pipeline for moving device telemetry through AWS and into Snowflake. Snowflake handled ingestion, transformation, historical state, partner-facing data, and the publication of calculated corrections back to the application.

The active flow-correction workflow included:

• Hourly ingestion from an S3-backed Snowflake stage
• A multi-step zero-correction algorithm implemented inside a stored procedure
• Permanent intermediate tables recreated during each run
• Updates to meter state and correction history
• A Snowflake Python function that submitted correction results to the client API
• A task graph coordinating ingestion, transformation, history, and publishing

Although operational, the architecture lacked several controls normally expected in a production software system. Changes were difficult to review before execution. Dependencies had to be reconstructed from stored-procedure code. Developers lacked isolated environments. Data-quality expectations were not consistently executable, and missed hourly ingestion had no straightforward recovery mechanism.

A static DDL export also proved insufficient as an authoritative representation of the warehouse: it omitted objects and columns present in the live Snowflake environment.

Rather than replacing working ingestion infrastructure or introducing a new external platform, the solution focused on modernizing the transformation layer around Snowflake.

dbt Core became the framework through which warehouse transformations would be defined, reviewed, tested, documented, and deployed. Snowflake remained the system of record and execution engine. This was a transformation migration—not a data migration. Historical data remained in place, and existing ingestion continued throughout implementation.

The project combined several forms of discovery:

• Live Snowflake catalog inspection using SHOW and GET_DDL
• dbt-codegen generation against live relations and columns
• Historical Snowflake task execution analysis
• Dependency tracing through procedures, functions, and tasks
• The client's exported DDL as a supplemental snapshot

The discovery process identified three active production domains—flow correction, full-datapoint ingestion, and partner data sharing—and uncovered operational behavior that table definitions alone did not reveal, including a Python function used to publish calculated corrections to the client API.

Publishing was brought under version control while remaining separately governed so ordinary local and CI builds could never make outbound production calls.

The existing zero-correction stored procedure was decomposed into an explicit dbt model graph. Each stage became a named, testable model with documented upstream and downstream dependencies. Staging and mart models represented the full-datapoint source and both internal and partner-facing outputs.

The generated lineage graph gives engineers a visual map of the system and makes the effect of a proposed change visible before deployment.

Migration was designed around evidence rather than assumption. The reconciliation workflow creates two isolated Snowflake environments from the same point-in-time data using zero-copy cloning. It runs the existing procedure against one environment and the dbt model chain against the other.

Every intermediate output is compared in both directions across nine stages of the zero-correction transformation. This validates more than a final table: it makes any difference throughout the algorithm locatable and explainable.

A cross-platform setup process installs the locked development environment, authenticates engineers through Snowflake's browser-based flow, and verifies the dbt connection.

Each engineer receives a personal Snowflake schema. Pull requests follow the same isolation principle: GitHub Actions creates a temporary schema, runs the build and test suite, and removes temporary objects when the pull request closes.

CI/CD authenticates through GitHub OIDC. No Snowflake password, private key, personal access token, or refresh token is stored in GitHub. Snowflake Workspaces connects to the same Git-governed project for native execution and inspection.

The rollout includes an operator-triggered backfill workflow for missed flow-correction ingestion. It determines which completed UTC hours are missing, restores the relevant S3 stage partitions in order, rebuilds the affected dbt path, and preserves approval and validation evidence in GitHub.

Snowflake's loaded-file tracking keeps normal retries idempotent. Monitoring definitions cover pipeline execution, source freshness, and critical data-quality conditions, with actionable alerts linked to the appropriate runbook.

One protective test addresses devices reporting meaningful flow rates while recording effectively zero volume. The upstream calculation fix addresses the source behavior; the dbt test provides downstream protection against recurrence.

The implementation establishes the components of a version-controlled transformation system: explicit models, executable tests, documented sources and lineage, isolated development, secure CI/CD, reconciliation, recovery workflows, monitoring definitions, and team-facing operational documentation.

Production adoption is being handled as a separate engineering milestone. Final validation, operator testing, onboarding, and cutover will determine the observed operational outcomes; they are not being presented here as completed business results.

The intended result is not simply a dbt repository. It is a shared engineering system that lets the client's team understand, review, test, operate, and extend how production data changes—from the first source record through the final operational output.