In this guide 6 sections
Read the job log first, then work down a fixed order: expired source login, changed column or table, locked file, timeout, duplicate key and full disk. Most failures sit in the first two. Add an alert that fires on failure and on stale data, so you hear before users do.
Read the log before you touch anything
Open the scheduler history. Find the first red step. Not the last one. Later steps fail because an earlier load left a half-written staging table behind, and fixing that symptom wastes an hour you needed for the real fault.
Note the exact error text, the start time and the run duration. A job that normally takes twelve minutes and died at two points to a login or a network drop. A job that ran for three hours before dying points to a timeout or a lock.
Check whether yesterday’s run passed and whether anyone deployed, patched or changed a password since. Most refresh failures follow a change that nobody linked to the pipeline.
Check the source login and the schema first
Service accounts expire. A password rotation policy, a disabled employee account or a renewed API token will stop a nightly pull without any warning. Test the same credentials by hand in a SQL client or a REST tool.
Then look for schema drift. Someone renamed a column in the ERP, dropped a view or added a mandatory field. Now the extract query errors out. Worse, it may shift every column by one without a word.
Compare the current column list of the source table against the one in your mapping before you read a single line of transformation code.
- Expired password, token or certificate on the service account
- Firewall, VPN or IP allow-list change on the source server
- Renamed, retyped or dropped column in the source table
- New vendor release that replaced a view or stored procedure
- Changed file name pattern in an SFTP or shared-folder drop
Locked files, timeouts and duplicate keys come next
A CSV or Excel source that someone left open on a shared drive will refuse to be read. A database backup running at the same hour can hold locks long enough that your extract times out.
Duplicate keys show up at the load step as a primary key violation. They usually mean the source sent the same invoice twice, or a reprocessed file overlapped with the last one.
Do not delete rows to make the error go away. That hides the cause. Find which source record repeated and why, then decide whether the load should keep the latest version or reject the file.
- Source file still open or locked by a person or another job
- Extract query blocked by a backup, index rebuild or long report
- Command timeout set lower than the data now needs
- Duplicate invoice, order or customer number in the incoming batch
- Date format or decimal separator change in a flat file
Disks fill up quietly
Staging tables, temp files, transaction logs and old extracts grow every night. One day the volume reaches zero and the load dies with an error that mentions nothing about space.
Check free space. Look at the database server, the file server and the machine that runs the scheduler. Look at the transaction log size too, because a log that never gets truncated is a common culprit.
Set a retention rule. Keep seven days of raw extracts. Archive the rest, and delete staging tables at the end of each successful run.
Make the pipeline tell you before the user does
An email on failure is the minimum. It must go to a shared mailbox or a chat channel, not to one person who may be on leave.
Add a freshness check as well. A job can finish green and still load nothing, so test that the latest order date in the warehouse is today or yesterday. Add a row count check against the previous run.
Then write a runbook with the order above, the owner of each source and the person to call. The next failure should take minutes, not a morning.
- Failure alert to a shared channel, with the job name and error text
- Freshness test on the newest record date in each key table
- Row count drop or spike compared with the last seven runs
- Disk space warning before the volume is full
Failed refresh triage sheet
A single sheet with a table of six rows: login, schema, lock, timeout, duplicate key and disk. Across the table, columns record the check to run, the tool to run it in, the result, who owns the source and the time spent. A header block holds the job name, run time, first red step and error text. A footer lists alert recipients and the escalation phone number.
Open a blank worksheet to printQuestions owners ask
Should a failed refresh be rerun straight away?
Only after you read the error. A blind rerun on a half-loaded staging table can double rows or hide the original fault. Clear or roll back the staging step, fix the cause, then run once and watch it.
Why did the job succeed but the dashboard still shows old numbers?
The refresh may have loaded zero rows, written to a different schema, or the report may be reading a cached extract. Check the newest record date in the warehouse table and the dashboard’s own last-refresh timestamp.
How do we avoid refreshes colliding with backups?
Look at the schedule of every job on the source server and move the refresh out of any backup or index window. Leave a gap, because backups run longer as the database grows.
Who should own the alerts?
Name one owner per pipeline and a backup. Alerts should reach a shared inbox or channel, and the runbook should say who acts, who is told and when the business is warned.