Scheduling CSV Reports From SQL With cron Reliably

The report that lands in someone's inbox every morning is a small thing until it stops arriving, and it stops arriving for boring reasons: cron could not find psql, the job ran at midnight UTC and not local midnight, two runs wrote to the same file, or the query returned nothing and the script cheerfully published an empty report. None of those is hard to prevent once you have met it. This article builds a daily CSV report from SQL as a shell script plus a crontab entry, and spends its words on the parts that go wrong in practice. If the report is a periodic export of a whole table, exporting large tables to CSV covers the size side, and automating a nightly CSV export with cron covers a similar job from the export direction.

The report script

Keep the logic in a script and the schedule in cron. A script can be run by hand to test, and a crontab line cannot. This one writes a per-day summary of orders from a SQLite database, but the structure is identical for psql -c "\copy (...) TO ..." or mysql --batch:

#!/usr/bin/env bash
set -euo pipefail

DB=/srv/shop/shop.db
OUT_DIR=/srv/reports
STAMP=$(date +%F)
OUT="$OUT_DIR/daily-orders-$STAMP.csv"

mkdir -p "$OUT_DIR"
sqlite3 -header -csv "$DB" \
  "SELECT placed_on, count(*) AS orders, round(sum(total), 2) AS revenue
   FROM orders GROUP BY placed_on ORDER BY placed_on" > "$OUT.part"

lines=$(wc -l < "$OUT.part")
if [ "$lines" -lt 2 ]; then
  echo "report is empty, keeping the previous one" >&2
  rm -f "$OUT.part"
  exit 1
fi
mv "$OUT.part" "$OUT"
echo "wrote $OUT ($((lines - 1)) rows)"

Running it by hand against the sample database:

wrote ./reports/daily-orders-2026-10-26.csv (2 rows)

placed_on,orders,revenue
2026-10-01,3,227.9
2026-10-02,1,200.0

(The paths in the test run were relative; in the version above they are absolute, which is the form cron needs.) Several lines in the script carry weight.

**set -euo pipefail.** -e stops at the first failing command, -u makes an unset variable an error and -o pipefail makes a pipeline fail if any stage fails. Without them, a failed sqlite3 or psql leaves an empty file and the script goes on to publish it.

**The .part file and mv.** The query writes to daily-orders-DATE.csv.part, and the script renames it to the final name only at the end. A rename on the same file system is atomic, so a person or a downstream job that reads daily-orders-*.csv never sees a half-written file, and a script that dies in the middle leaves a .part file that nothing picks up.

The sanity check. A query that succeeds and returns no rows writes just the header, which is one line. The script treats fewer than two lines as a failure, exits non-zero and removes the temporary file, so yesterday's report is still the latest. Whether an empty report is an error depends on the report: "orders today" can legitimately be empty on a holiday, while "all orders" cannot. Put the right threshold in the check, and consider comparing to yesterday's row count and failing on a drop of more than half.

The date in the name. date +%F gives 2026-10-26. A rerun on the same day replaces the day's file instead of adding a second, which is the idempotence you want. Mind the time zone: cron uses the system's local time unless told otherwise, and a server in UTC produces a file for "today" that may still be yesterday for your users. Set TZ in the script or the crontab if it matters.

The crontab entry

[email protected]
PATH=/usr/local/bin:/usr/bin:/bin
15 6 * * * flock -n /var/lock/daily-orders.lock /srv/shop/report.sh >> /var/log/daily-orders.log 2>&1

The five fields are minute, hour, day of month, month and day of week, so 15 6 * * * is 06:15 every day. Edit with crontab -e, and check the syntax against the manual page (crontab(5)). The other lines matter as much.

**PATH.** Cron starts jobs with a minimal environment, often just /usr/bin:/bin. A psql in /usr/local/bin or /usr/pgsql-17/bin is not found, and the failure message goes to a mailbox nobody reads. Set PATH at the top, or use absolute paths for every command in the script. This is the most common reason a job that works in a terminal fails in cron.

**MAILTO.** Cron mails any output of a job to the owner. With >> log 2>&1 redirecting everything to a file, nothing is mailed and failures are silent, so decide which you want. A good pattern is to redirect normal output to a log and let errors reach a mailbox, or to alert from the script itself on a non-zero exit.

**flock -n.** If the 06:15 run is still going at 06:15 tomorrow, a second copy starts. flock -n takes a lock file and, with -n, exits at once instead of waiting if another process holds it (flock(1)). The overlap cannot happen, and the second run's exit status shows in the log. On macOS, where flock is not part of the base system, use mkdir as a lock or install it.

Absolute paths. Cron runs with the user's home directory as the working directory, so a relative ./reports lands in the home directory and not where you expect. Use full paths for the database, the output directory and the script.

Secrets. Do not put a password in the crontab, where it is visible to anyone who can read it. Use a .pgpass file (mode 0600), a ~/.my.cnf with credentials or an environment file loaded by the script with restricted permissions.

Test the way cron runs

A job that passes in your shell can still fail in cron, so test it under cron's conditions. Run it with a clean environment: env -i /bin/bash -c '/srv/shop/report.sh' starts with no variables at all, which exposes every dependency on your shell's PATH or your home directory. Better, add a temporary crontab line that runs the job two minutes from now, wait, and read the log. Do this once for every new job. It takes five minutes and finds the problems that would otherwise appear on the first Monday.

Then check the output file as a consumer would: open it, check the header, the row count and a known value. Keep a few days of reports so you can compare today's with yesterday's, and have the job delete files older than your retention period (archiving processed CSV files automatically has the pattern).

When the report must arrive

Cron does not know whether the job succeeded in the sense you care about. If the machine was off at 06:15, nothing runs and nothing complains. A separate check closes that gap: another job at 07:00 that tests whether today's file exists and is recent, and alerts if not. A dead-man's-switch service (the job pings a URL on success and an outside service alerts if no ping arrives) does the same from outside the machine. The check is the part that tells you the job stopped, and the part people skip.

For delivery, a file on a server is not a report until someone has it. Send it by whichever channel fits, and keep sensitive data off plain email (sending a CSV by email safely, and better options).

When cron is not enough

Cron is the right tool for a few independent jobs on one machine. It is the wrong one when jobs depend on each other ("build the report only after the load finishes"), when you need retries with backoff, when jobs span several servers or when you need a history of runs and a place to see failures. At that point look at systemd timers (which add Persistent=true to run a missed job after the machine comes back), a workflow tool (loading CSV files in Airflow with a plain Python task) or your cloud's scheduler.

Do not move to a heavier tool because cron seems unfashionable. A script, a lock, an atomic write, a sanity check and a freshness alert cover most daily reports for years, and they are things you can read in one sitting.