Data engineering
4 posts
Snowflake Cost and Performance: The Basics That Matter Most
In Snowflake you pay for storage and for compute, and compute is almost always the bigger bill. Compute is billed per second while a virtual warehouse is running, with a 60-second minimum each time it starts. Most savings come from keeping warehouses the right size and off when idle. Warehouses Auto-suspend and auto-resume. Set a short auto-suspend for most workloads: sql ALTER WAREHOUSE transformwh SET AUTOSUSPEND = 60 -- seconds AUTORESUME = TRUE; A warehouse left at a 10-minute suspend after every two-minute job spends most of its credits doing nothing. Size up for speed, not as a habit. Each size step roughly doubles both the compute and the credits per hour. A query that scales well can finish in half the time on the next size up for about the same cost. One that doesn't scale just costs double, so check before you upsize. Separate warehouses by workload. Keep ELT, BI dashboards and ad-hoc analysis on different warehouses, so one heavy job doesn't slow the CEO's dashboard and each cost is visible on its own. Queries 1. Select only the columns you need. Snowflake stores data by column, so SELECT on a wide table reads far more than it has to. 2. Filter on columns that prune well. Snowflake skips micro-partitions whose min/max range can't match your filter. Filtering on a date column that follows load order usually prunes well; wrapping it in a function (WHERE TOCHAR(createdat, 'YYYY') = '2026') can defeat pruning. 3. Read the Query Profile. Compare "Partitions scanned" with "Partitions total", and look for "Bytes spilled to local/remote storage". Spilling means the warehouse ran out of memory for that query. 4. Watch for exploding joins. A join on a non-unique key multiplies rows. If the output is much larger than both inputs, the join keys are probably wrong. Caching - Result cache. An identical query on unchanged data returns the stored result without using a warehouse, for up to 24 hours. - Warehouse cache. A running warehouse keeps recently read data on local disk. Suspending clears it, which is a fair trade for most batch jobs. Clustering keys Only for very large tables (multi-terabyte) where queries filter on the same columns and pruning is poor. Automatic clustering costs credits to maintain, so measure before and after. Keep an eye on spend sql SELECT warehousename, SUM(creditsused) AS credits FROM snowflake.accountusage.warehousemeteringhistory WHERE starttime = DATEADD('day', -30, CURRENTTIMESTAMP()) GROUP BY 1 ORDER BY 2 DESC; Pair it with a resource monitor on each warehouse, so a runaway query hits a credit limit instead of the monthly bill. Try it: three queries to run this week sql -- 1. Which warehouses burned the most credits in the last 7 days? select warehousename, sum(creditsused) as credits from snowflake.accountusage.warehousemeteringhistory where starttime = dateadd(day, -7, currenttimestamp()) group by 1 order by 2 desc; -- 2. The 10 slowest queries, and how much of each table they scanned select queryid, warehousename, totalelapsedtime / 1000 as seconds, partitionsscanned, partitionstotal from snowflake.accountusage.queryhistory where starttime = dateadd(day, -7, currenttimestamp()) order by totalelapsedtime desc limit 10; -- 3. A guardrail: suspend at the monthly budget create resource monitor monthlybudget with creditquota = 400 triggers on 90 percent do notify on 100 percent do suspend; alter warehouse etlwh set resourcemonitor = monthlybudget; When partitionsscanned is close to partitionstotal on a big table, the filter isn't pruning. That's your cue to check the where clause or think about a clustering key.
Designing Airflow DAGs That Don't Wake You Up
Airflow is a scheduler and orchestrator, not a processing engine. The DAGs that cause the fewest incidents keep the heavy lifting in the warehouse and use Airflow to decide what runs, when, and in what order. 1. Keep top-level code cheap The scheduler re-parses every DAG file every few seconds. Anything at module level runs on every parse: python Bad: hits the database on every parse tables = gettablesfromdb() Good: do it inside a task @task def listtables(): return gettablesfromdb() Slow parsing is the most common reason a scheduler "feels stuck". 2. One task, one job, safe to retry Each task should do one thing and be idempotent (same window in, same result out), so retries are harmless: python from datetime import datetime, timedelta from airflow.decorators import dag, task @dag( schedule="@daily", startdate=datetime(2026, 1, 1), catchup=False, maxactiveruns=1, defaultargs={"retries": 2, "retrydelay": timedelta(minutes=5)}, tags=["claims"], ) def claimsdaily(): @task def extract(dataintervalstart=None, dataintervalend=None): pull exactly this run's window ... @task def load(path: str): ... load(extract()) claimsdaily() 3. Use the run's window, never now() Every DAG run has a dataintervalstart and dataintervalend. Use them, and reruns and backfills just work. datetime.now() inside a task means a rerun on Tuesday processes Tuesday, not the Monday it was meant for. 4. Decide on catchup on purpose catchup=True with an old startdate schedules every missed interval the moment you unpause. Leave it False unless you actually want a backfill, and run backfills deliberately. 5. Pass references, not data XCom is for small values: a file path, a row count, a table name. Don't push DataFrames through it. Write to object storage or a staging table and pass the location. 6. Make waiting cheap Sensors that poke in mode="poke" hold a worker slot the whole time. Use mode="reschedule", or a deferrable sensor, so waiting costs nothing. 7. Let the warehouse do the work Pulling a million rows into a Python task to transform them is slower and more fragile than one SQL statement. Trigger the transform (a stored procedure, a dbt build, a MERGE) and let Snowflake run it. A pre-merge checklist - No database or API calls at module level. - catchup, retries, and maxactiveruns set explicitly. - Every task uses the data interval, not wall-clock time. - Only small values in XCom. - Alerts (onfailurecallback or email) wired to someone who will read them. Try it: lint your DAGs before they hit the scheduler Two commands catch most of the problems above before they reach production: bash Fails fast on import errors and slow top-level code time python dags/loadorders.py Runs one logical date end to end, no scheduler needed airflow dags test loadorders 2026-09-21 If the first command takes more than a second or two, something at the top of the file is calling a database or an API. Move it inside a task.
dbt Incremental Models on Snowflake, Without the Surprises
A table model rebuilds everything on every run. That's simple and correct, and it's the right default until the table gets big. An incremental model processes only new or changed rows and merges them into what's already there. It's faster and cheaper, and it's also where most dbt bugs live. The minimal correct version {% raw %} sql {{ config( materialized = 'incremental', uniquekey = 'claimid', incrementalstrategy = 'merge', onschemachange = 'appendnewcolumns' ) }} select claimid, policyid, claimamount, status, updatedat from {{ ref('stgclaims') }} {% if isincremental() %} where updatedat ( select dateadd('day', -3, max(updatedat)) from {{ this }} ) {% endif %} {% endraw %} What each piece does: - uniquekey tells dbt how to match incoming rows to existing ones. Without it, the merge becomes a plain append and reruns create duplicates. - incrementalstrategy = 'merge' is the default on Snowflake. It's written out here so nobody has to wonder. - isincremental() is false on the first run and on --full-refresh, so the filter only applies once the table exists. - The 3-day lookback catches late-arriving rows. Filtering on exactly max(updatedat) silently drops any record that lands after a later one has already been loaded. Things that bite 1. Duplicate keys in the source. If stgclaims has two rows for one claimid, the merge errors out. Deduplicate in staging with qualify rownumber() over (partition by claimid order by updatedat desc) = 1. 2. Logic changes don't backfill themselves. Change a calculation and only new rows get it. Run dbt run --select mymodel --full-refresh after changing business logic. 3. Schema changes. The default onschemachange is ignore, so new columns just don't appear. appendnewcolumns or syncallcolumns is usually what you want. 4. Hard deletes are invisible. Rows deleted upstream stay in your table forever. Use a soft-delete flag from the source, or a snapshot. Test it like you mean it yaml models: - name: fctclaims columns: - name: claimid tests: - unique - notnull A unique test on the uniquekey is the cheapest insurance you can buy for an incremental model. It fails the build the first time a duplicate slips through. When to go incremental Stay on table until a full rebuild is noticeably slow or expensive. Incremental models trade simplicity for speed, so make the trade only when you need to. Try it locally with DuckDB You don't need a Snowflake account to see the behaviour. dbt-duckdb runs the same Jinja and the same merge strategy on your laptop. bash pip install dbt-duckdb mkdir -p dbtdemo/models && cd dbtdemo curl -o models/fctevents.sql https://www.yashank.site/files/demos/fctevents.sql Add a two-line dbtproject.yml (name and profile) and a profiles.yml with type: duckdb and path: demo.duckdb. Create a rawevents table, run dbt run, then update 10 rows and insert 200 new ones upstream and run again: Open target/compiled/.../fctevents.sql after the second run and you'll see the where updatedat (select max(updatedat) - interval 3 day ...) filter that isincremental() switched on. When you move to Snowflake, only the profile changes.
Idempotent Pipelines: Making the 3 a.m. Rerun Safe
A pipeline is idempotent when running it once or five times for the same input gives the same result. It is the single most useful property a batch job can have, because every job eventually gets rerun: a retry after a timeout, a backfill after a bug fix, someone clicking "Clear" in Airflow at 3 a.m. If a rerun doubles your revenue numbers, the pipeline is broken even when every task is green. The three patterns that get you there 1. Process a window, not "whatever is new" Bad: WHERE loadedat (SELECT MAX(loadedat) FROM target). The answer changes every time you run it. Good: every run owns a fixed, explicit window, such as one day, passed in from the scheduler. In Airflow that is the run's dataintervalstart and dataintervalend. Rerunning the 7 October run always processes 7 October. 2. Overwrite the window, don't append to it Two safe ways to write: Delete-then-insert inside one transaction sql BEGIN; DELETE FROM analytics.dailypolicies WHERE businessdate = '2026-10-07'; INSERT INTO analytics.dailypolicies SELECT FROM staging.dailypolicies WHERE businessdate = '2026-10-07'; COMMIT; MERGE on a real business key sql MERGE INTO analytics.policies t USING staging.policies s ON t.policyid = s.policyid WHEN MATCHED THEN UPDATE SET t.status = s.status, t.premium = s.premium, t.updatedat = s.updatedat WHEN NOT MATCHED THEN INSERT (policyid, status, premium, updatedat) VALUES (s.policyid, s.status, s.premium, s.updatedat); INSERT INTO ... SELECT with no delete or key is the classic source of duplicates. Avoid it for anything that can be rerun. 3. Deduplicate the source before you merge MERGE fails (or behaves unpredictably) when the source has two rows for the same key. Keep only the latest: sql SELECT FROM raw.policies QUALIFY ROWNUMBER() OVER ( PARTITION BY policyid ORDER BY updatedat DESC ) = 1; A quick checklist - Every run is parameterised by a date window from the scheduler, never by CURRENTDATE inside the SQL. - Writes are overwrite-by-partition or MERGE on a key, never blind appends. - The source is deduplicated on that key first. - Side effects (emails, API calls, file drops) live in their own task, so retrying the load doesn't resend them. - You've actually tested it: run the same day twice and compare row counts. Make this the default and backfills stop being scary. Try it: break it, then fix it Here's the whole idea in 25 lines of Python and SQLite. The script loads the same 1,000 orders twice, once with a blind append and once with delete-then-insert inside a single transaction. bash curl -O https://www.yashank.site/files/demos/idempotentdemo.py python idempotentdemo.py append python idempotentdemo.py idempotent Swap SQLite for Snowflake and the pattern is the same: DELETE ... WHERE orderdate = :ds and INSERT inside one transaction, or a MERGE on the business key.