Outcome
By the end of this lesson, you will be able to schedule SQL Warehouses off-hours, size them for actual usage in Databricks, and choose between the three scheduling patterns.
| Prerequisites | M2.7.L1 · M2.7.L2 |
| Time | 9 minutes |
1. Concept
A SQL Warehouse is what Databricks runs your queries on.
It pauses itself after a period of nobody using it, and you choose how long that is. Beyond that you can put it on a schedule, make it bigger or smaller, or stop it by hand.
SQL WAREHOUSE LEVERS: 1. Size (2X-Small to 4X-Large): affects concurrency + speed 2. Autopause (off after N min idle) 3. Schedule (start/stop times) 4. Resize (done in Databricks; ZopNight does not change size)ZopNight owns lever 3 only: it starts and stops the warehouse on a schedule. Size and autopause are set in Databricks. Combinations of these handle different workload patterns.
Warehouse sizes
SIZE CLUSTER COST FACTOR (DBU/h vs Small)─────────────────────────────────────────────────────────2X-Small 1 node 0.33xX-Small 2 nodes 0.5xSmall 4 nodes 1x (baseline)Medium 8 nodes 2xLarge 16 nodes 3.3xX-Large 32 nodes 6.7x2X-Large 64 nodes 12x3X-Large 128 nodes 22.7x4X-Large 256 nodes 44xEach size has different concurrency capacity. Cost follows DBUs per hour, which grow more slowly than node count at the larger sizes. Choose based on workload.
Three scheduling patterns
PATTERN A: SCHEDULE OFF-HOURS (like clusters) Warehouse stops nights/weekends entirely Cost: ~50-60% savings vs always-on Restart latency: 1-2 minutes Best for: non-prod or off-hours-light workloads
PATTERN B: AUTOPAUSE ONLY Warehouse stays available 24/7 Autoterm = 5 minutes (default) Cost: pays for actual query time + ~5 min warm Restart latency: zero on use (warehouse stays warm during business) Best for: 24/7 query availability needed
PATTERN C: TEAM-OWNED RESIZE + ZOPNIGHT STOP WINDOW Team resizes in Databricks: Medium by day, X-Small overnight (by hand or with the team's own automation) ZopNight stops the warehouse in the window nobody needs it Cost: variable based on traffic Best for: prod with diurnal variation, if the team owns the resizeFor non-prod: Pattern A. For prod with 24/7 query load: Pattern B. For prod with diurnal variation: Pattern C, with the resize handled on the Databricks side.
Pattern A details
WAREHOUSE: reporting-warehouse, Small (4 nodes)SCHEDULE: business-hours-reporting (8 AM - 8 PM Mon-Fri)
WEEKDAY: 8 AM: start warehouse (ZopNight: warehouses/start) Throughout day: serves queries 8 PM: stop warehouse (ZopNight: warehouses/stop)
WEEKEND: No scheduled start on weekends Cost during weekend: $0 unless a query wakes it
RESULT: Active hours/week: 60 (12 × 5) Inactive hours/week: 108 Theoretical savings: 64% of weekly cost Realistic: ~50% after accounting for some weekend queries (if any)
WEEKEND QUERY HANDLING: A query, dashboard refresh, alert or JDBC/ODBC connection starts the warehouse automatically It then bills until auto-stop shuts it down again Or: switch to Pattern B for 24/7 availabilityPattern A is the most cost-aggressive.
Pattern B details
WAREHOUSE: ad-hoc-warehouse, X-Small (2 nodes), serverlessSCHEDULE: none (24/7 available)AUTOTERM: 5 minutes
BEHAVIOR: Idle: warehouse auto-pauses after 5 minutes of no queries Query arrives: warehouse resumes (seconds on serverless; minutes on pro/classic) Cost: pay only for active query time Cold-start: minimal on serverless
GOOD FOR: Unpredictable / low-volume queries 24/7 availability requirement Cost-aware but availability-first
NOT GOOD FOR: Steady-state warehouses (always running anyway) Large warehouses (cold-start slower)Pattern B trades some cost for availability.
Pattern C details
ZopNight schedules SQL warehouses on and off only; it does not change their size. If a production warehouse needs to be smaller overnight, resize it in Databricks (or with your own automation) and use ZopNight for the stop/start window.
WAREHOUSE: prod-bi-warehouse, Medium (8 nodes during business hours)
SIZE (owned by the team, in Databricks): 6 PM weekdays: team's own job resizes to X-Small (2 nodes) 8 AM weekdays: team's own job resizes back to Medium
STOP WINDOW (owned by ZopNight schedule): Weekends: warehouse stopped (a weekend query starts it)
BEHAVIOR: Peak hours: Medium for full reporting capacity Weeknights: X-Small for occasional queries Weekends: off entirely Resize failures are the team's to monitor, not ZopNight's
GOOD FOR: Production BI workloads with predictable variation Teams that already run their own Databricks automationPattern C is the most operationally complex, because two systems own different levers on the same warehouse.
Cost implications comparison
PATTERN WEEKLY COST (relative to baseline)──────────────────────────────────────────────────────────A (off entirely) ~35-50% of baseline (savings: 50-65%)B (autoterm only) ~50-70% of baseline (savings: 30-50%)C (team resize + stop) ~50-70% of baseline (savings: 30-50%)
Baseline = warehouse running 24/7 at fixed sizePattern A wins on pure cost for non-prod. Pattern B/C optimize prod cost with availability; C needs resize automation the team runs itself.
Choosing the right pattern
SCENARIO RECOMMENDED PATTERN─────────────────────────────────────────────────────────Non-prod reporting warehouse A (off entirely)
Production BI; off-hours queries OK B (autoterm only)to be slower
Production BI; queries 24/7 normal B (autoterm)
Production BI with diurnal variation C (team resize + stop)
Dev / experimental warehouse A (off entirely)
Ad-hoc data exploration B (autoterm)
Critical regulatory reporting Keep large; minimal scheduling Don't compromise availabilityThe pattern depends on the workload’s actual access pattern.
Sizing for actual usage
COMMON SIZING MISTAKES:
OVER-SIZED: Workload runs Large but actual concurrency stays under Small capacity Cost: ~3.3× what's needed
UNDER-SIZED: Workload runs X-Small but queries queue up Cost: low but engineer wait time high
RIGHT-SIZED: Pick based on actual concurrent query load Use Databricks query metrics Resize quarterly as workload evolves
SIZING GUIDELINES: 2X-Small / X-Small: 1-5 concurrent users; light queries Small: 5-20 concurrent; standard reporting Medium: 20-50 concurrent; production BI Large+: 50+ concurrent; heavy data processingRight-sizing the warehouse is independent of scheduling but compounds the savings.
2. Demo
A team’s BI warehouse setup:
WAREHOUSE: reporting-warehouse Size: Medium (8 nodes) Usage: 12 BI users Queries: 200/day on weekdays; 0-5 on weekends
BEFORE (no scheduling, autoterm 60 minutes): Cost: ~$4,200/month Pattern: warehouse runs all the time except brief autoterm cooldowns during weekend evenings
WITH PATTERN A (schedule off-hours + autoterm): Schedule: business-hours 8 AM - 8 PM Mon-Fri Autoterm: 30 minutes (within business hours)
Cost: ~$1,400/month (66% reduction) Trade-off: a weekend query starts the warehouse on demand, and it bills until auto-stop (30 minutes) shuts it again Team workflow change: none; the first weekend query waits for the warehouse to start
Decision factors: Few weekend queries (5/week) Team OK with a slower first query on weekends Cost savings significant ($2,800/mo)
DECISION: acceptable for this team
WITH PATTERN B (autoterm only, smaller default size): Size: X-Small (2 nodes) baseline (instead of Medium), serverless Autoterm: 5 minutes
Cost: ~$1,100/month Trade-off: weekend queries still work but with X-Small performance Complex reports slower
Decision factors: Similar cost to A Weekend availability preserved Some query latency increase
CHOSEN: Pattern A Reasoning: cost clarity; clear off-hours boundary Team adapts to "scheduled" weekends Predictable cost
RESULTS (3 months in): Actual cost: $1,420/mo (close to projection) Weekend wake-ups: averaged 3 per month Engineer satisfaction: high (predictable) No workload disruption during weekdaysThe team chose A for the cost clarity, even though B was close.
3. Hands-on (5 min)
Choose a pattern for your warehouse:
□ STEP 1: Identify warehouse usage Warehouse: __________ Size: __________ Current cost: $_____/mo
Weekday queries: _____/day Weekend queries: _____/day Off-hours queries: _____/day
□ STEP 2: Pick a pattern □ A: Schedule off-hours □ B: Autoterm only □ C: Resize in Databricks + ZopNight stop window
□ STEP 3: Estimate savings Pattern A: ~50-65% = $_____/mo savings Pattern B: ~30-50% = $_____/mo savings Pattern C: ~30-50% = $_____/mo savings (resize owner: __________)
□ STEP 4: Configure Schedule: __________ Times: __________
□ STEP 5: Monitor first month Actual savings: $_____ Disruptions: _____ Decision to adjust: __________A 10-minute exercise per warehouse. Pattern A often the right choice for non-prod.
4. Knowledge check
Q1
Pattern A (schedule off-hours) for a SQL Warehouse provides:
A. Highest availability
B. No savings
C. Highest savings (~50-65%) for non-prod or off-hours-light workloads, at the cost of a cold start for the first query after a stop
D. The lowest possible cold-start latency for the users querying it during the whole working day, every single day
Show answer
Correct: C. Pattern A is the cost-aggressive choice; the trade-off is a cold start for the first off-hours query, and each such query restarts the warehouse until auto-stop.
Q2
In Pattern C, a prod warehouse runs Medium by day and X-Small overnight. What does ZopNight do?
A. Resizes the warehouse to X-Small each night
B. Sets the autopause timer on the warehouse
C. Queues queries while the warehouse resizes
D. Starts and stops it; you change its size
Show answer
Correct: D. The SQL warehouse provider only starts and stops the warehouse. The size change is done in Databricks, by hand or with the team’s own automation, and ZopNight covers the stop/start window.
Q3
A weekend query to a Pattern A warehouse:
A. Is rejected until the next scheduled start
B. Fails: warehouse is stopped
C. Cached
D. It auto-starts on demand
Show answer
Correct: D. A query, dashboard refresh, alert or JDBC/ODBC connection starts a stopped warehouse automatically, and it then bills until auto-stop shuts it down. Pattern A ends idle spend at the window boundary; it does not block queries.
5. Apply
ZopNight schedules SQL warehouses on and off; size and autopause stay in Databricks. Pattern A is a ZopNight schedule, Pattern B is a Databricks setting, and Pattern C combines a schedule with a resize your team runs.
For your team: choose pattern by access pattern; tune over quarters.