Skip to content

Library · Data Practice OS

Data engineering on Snowflake

Reference guide · Published · Source reviewed

Snowflake engineering is ELT: land the raw data, then transform it inside the platform in version-controlled SQL. The skill is choosing the simplest mechanism for each hop.

Ingestion: choose by source

Source Default Alternative
Operational databases (SQL Server, Oracle, DB2, Postgres) Snowflake Openflow Change data capture connectors A partner CDC tool where it is already licensed
Files dropped in S3 (extracts, partner feeds) Snowpipe (auto-ingest on S3 events) COPY INTO on a schedule for low-frequency feeds
Streams (Kafka, Kinesis) Snowpipe Streaming or the Kafka connector Databricks, if the stream also needs heavy processing
SaaS (Salesforce, ServiceNow, SharePoint) Openflow connectors A partner ELT service
Data already in Iceberg on S3 Read it in place through a catalog integration No copy at all. See Apache Iceberg as the interchange format

Openflow is a managed version of Apache NiFi. Verify connector availability and whether it is deployed in your own VPC or Snowflake's (the "BYOC" versus Snowflake-hosted options) in the documentation.

Transformation: dbt first, Dynamic Tables for freshness

Use dbt for the modelled layers: silver conformance and gold data products. It is portable, testable, documented and reviewed in pull requests. See dbt project standards.

Use Dynamic Tables when the need is "keep this table no more than N minutes stale" and the logic is a plain query. Snowflake works out the incremental refresh. dbt can create Dynamic Tables (materialized='dynamic_table'), so you keep one codebase.

-- {{ config(materialized='dynamic_table', target_lag='10 minutes', snowflake_warehouse='wh_transform') }}
SELECT r.registration_id,
       r.plate,
       r.status,
       v.make, v.model, v.gvm_kg,
       r.expiry_date
FROM {{ ref('stg_registrations') }} r
JOIN {{ ref('stg_vehicles') }}      v USING (vehicle_id)

Streams and Tasks still have a place for procedural steps, such as calling an external function or handling SCD2 edge cases. Avoid them as the default pipeline engine: they are harder to test and review.

Use Snowpark Python when SQL is the wrong tool: parsing awkward formats, calling a Python library, or feature engineering. Keep it in a dbt Python model or a versioned stored procedure, not in a notebook someone runs by hand.

Layout

A pattern that scales without a sprawl of databases:

RAW_<source>         -- bronze: landed as-is, one database per source system, append-only
ANALYTICS            -- silver and gold schemas per domain: registrations, licensing, assets, safety
  registrations_silver / registrations_gold
SANDBOX_<team>       -- disposable, auto-dropped objects, no production consumers

Pair each environment (dev, test, prod) with its own account, or at least its own databases and roles. See CI/CD and environments. Create everything (warehouses, databases, roles, grants and policies) with Terraform, and deploy dbt from GitHub Actions.

Iceberg or native?

Use native tables for Snowflake-only data. They give the best performance and need the least management.

Use Snowflake-managed Iceberg tables in your own S3 bucket for gold data products that Databricks or AWS services also read. Snowflake writes them and is the catalog of record (ADR-004: One catalog of record per table).

Use externally managed Iceberg (read-only from Snowflake) for tables Databricks writes.

When not to do it on Snowflake

  • Sub-second streaming analytics, or complex stateful stream processing.
  • Spark-shaped jobs that are already written and working. Moving them to Snowpark is rarely worth it.
  • Large-scale image or video processing.

Pitfalls

  • One giant warehouse for everything. You lose isolation and cost attribution. See Cost control on Snowflake.
  • Business logic in procedures. It is invisible to lineage and hard to test. Keep it in dbt.
  • Overusing SELECT * in Dynamic Tables. An upstream schema change forces a full refresh.
  • Ignoring incremental refresh mode. Check that a Dynamic Table actually refreshes incrementally (refresh_mode). Some constructs force a full refresh.

Worked example

The road asset condition feed arrives nightly as CSV extracts from the asset management system. Snowpipe lands the files in RAW_ASSETS. dbt conforms them to the asset model and tests referential integrity against the road network. A Dynamic Table at a one-day lag builds the gold asset_condition_current product. Because maintenance planners in Databricks also need it, that table is a Snowflake-managed Iceberg table, published through the shared catalog.