dbt full-refresh flag

run_dbt.sh

#!/usr/bin/env bash

# When run, first tries to figure out if the full-refresh flag is set. 
# Then, runs dbt with or without the flag.

# Do not run me in dev mode!

full_refresh_needed="$(
psql $REDSHIFT_URI -t <<EOF
SELECT full_refresh
FROM public.full_refresh
ORDER BY created_at DESC
LIMIT 1
EOF
)"

# trick to get rid of whitespace!
full_refresh_needed=`echo $full_refresh_needed`

if [ "$full_refresh_needed" == "t" ]; then
	dbt run --target=${1-prod} --full-refresh
else 
	dbt run --target=${1-prod}
fi

rc=$?
if [ $rc == 0 ]; then
	psql $REDSHIFT_URI <<EOF
		INSERT INTO public.full_refresh (full_refresh) VALUES (FALSE);
EOF
fi

set_full_refresh.sh

#!/usr/bin/env bash

# When run, sets the "full refresh" flag, so the next time dbt runs, it'll do a full refresh.

# fail the script if the command fails!
set -e 

psql $REDSHIFT_URI <<EOF
INSERT INTO public.full_refresh (full_refresh) VALUES (TRUE);
EOF

setup.sql

-- This is used for the full-refresh logic -- see the README file.
-- Note that the dbt user should be a superuser, so none of the full-refresh code
-- requires this table to have any particular grants.

DROP TABLE IF EXISTS public.full_refresh;
CREATE TABLE public.full_refresh (
  	full_refresh boolean NOT NULL,
  	created_at datetime DEFAULT SYSDATE
);
INSERT INTO public.full_refresh (full_refresh) VALUES (TRUE);

Additional pieces:

• in our continuous deployment script, after pushing a Docker container, it runs ./set_full_refresh
• our Airflow instance runs ./run_dbt.sh prod twice daily
• our Airflow instance also runs ./set_full_refresh.sh once a week