Overview

Note: This guide has been written and tested with Airflow Airflow 1.10.10. New tables may be introduced in newer versions.

We at Astronomer often get asked about the structure of the underlying Airflow metadata database. The Airflow metadata database stores configurations, such as variables and connections, user information, roles, and policies. It is also the Airflow Scheduler's source of truth for all metadata regarding DAGs, schedule intervals, statistics from each run, and tasks.

Airflow uses SQLAlchemy and Object Relational Mapping (ORM) in Python to connect and interact with the underlying metadata database from the application layer. Thus, any database supported by SQLAlchemy can theoretically be configured to host Airflow's metadata. On Astronomer, each Airflow deployment is equipped with PostgreSQL database for this purpose. The following guide details all of the tables available in the database repository including dependencies and the complete ERD.

Even though we don't recommend modifying the values directly on the database, as doing so might affect dependencies, understanding the underlying structure can be very useful when it comes to building your own reports or querying directly against the database. You can find some useful queries that go directly against these tables in our Useful SQL queries for Apache Airflow guide.

ERD Diagram

The following diagram displays all the tables from the Airflow database.

Airflow DB


Tables

The Airflow metadata database has a total of 30 tables tables are stored on the public schema by default. The following describe the table structure and reference for the Airflow metadata tables.

public.ab_permission

Table Structure:


F-KeyNameTypeDescription
idintegerPRIMARY KEY
namecharacter varying(100)UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:


public.ab_permission_view

Table Structure:


F-KeyNameTypeDescription
idintegerPRIMARY KEY
public.ab_permission.idpermission_idintegerUNIQUE#1
public.ab_view_menu.idview_menu_idintegerUNIQUE#1

Tables referencing this one via Foreign Key Constraints:


public.ab_permission_view_role

Table Structure:


F-KeyNameTypeDescription
idintegerPRIMARY KEY
public.ab_permission_view.idpermission_view_idintegerUNIQUE#1
public.ab_role.idrole_idintegerUNIQUE#1

public.ab_register_user

Table Structure:


F-KeyNameTypeDescription
idintegerPRIMARY KEY
first_namecharacter varying(64)NOT NULL
last_namecharacter varying(64)NOT NULL
usernamecharacter varying(64)UNIQUE NOT NULL
passwordcharacter varying(256)
emailcharacter varying(64)NOT NULL
registration_datetimestamp without time zone
registration_hashcharacter varying(256)

public.ab_role

Table Structure:


F-KeyNameTypeDescription
idintegerPRIMARY KEY
namecharacter varying(64)UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:


public.ab_user

Table Structure:


F-KeyNameTypeDescription
idintegerPRIMARY KEY
first_namecharacter varying(64)NOT NULL
last_namecharacter varying(64)NOT NULL
usernamecharacter varying(64)UNIQUE NOT NULL
passwordcharacter varying(256)
activeboolean
emailcharacter varying(64)UNIQUE NOT NULL
last_logintimestamp without time zone
login_countinteger
fail_login_countinteger
created_ontimestamp without time zone
changed_ontimestamp without time zone
public.ab_user.idcreated_by_fkinteger
public.ab_user.idchanged_by_fkinteger

Tables referencing this one via Foreign Key Constraints:


public.ab_user_role

Table Structure:


F-KeyNameTypeDescription
idintegerPRIMARY KEY
public.ab_user.iduser_idintegerUNIQUE#1
public.ab_role.idrole_idintegerUNIQUE#1

public.ab_view_menu

Table Structure:


F-KeyNameTypeDescription
idintegerPRIMARY KEY
namecharacter varying(100)UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:


public.alembic_version

Table Structure:


F-KeyNameTypeDescription
version_numcharacter varying(32)PRIMARY KEY

public.chart

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
labelcharacter varying(200)
conn_idcharacter varying(250)NOT NULL
public.users.iduser_idinteger
chart_typecharacter varying(100)
sql_layoutcharacter varying(50)
sqltext
y_log_scaleboolean
show_datatableboolean
show_sqlboolean
heightinteger
default_paramscharacter varying(5000)
x_is_dateboolean
iteration_nointeger
last_modifiedtimestamp with time zone

public.connection

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
conn_idcharacter varying(250)
conn_typecharacter varying(500)
hostcharacter varying(500)
schemacharacter varying(500)
logincharacter varying(500)
passwordcharacter varying(500)
portinteger
extracharacter varying(5000)
is_encryptedboolean
is_extra_encryptedboolean

public.dag

Table Structure:


F-KeyNameTypeDescription
dag_idcharacter varying(250)PRIMARY KEY
is_pausedboolean
is_subdagboolean
is_activeboolean
last_scheduler_runtimestamp with time zone
last_pickledtimestamp with time zone
last_expiredtimestamp with time zone
scheduler_lockboolean
pickle_idinteger
fileloccharacter varying(2000)
ownerscharacter varying(2000)
descriptiontext
default_viewcharacter varying(25)
schedule_intervaltext
root_dag_idcharacter varying(250)

Indexes:

  • idx_root_dag_id root_dag_id

public.dag_pickle

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
picklebytea
created_dttmtimestamp with time zone
pickle_hashbigint

public.dag_run

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
dag_idcharacter varying(250)UNIQUE#2 UNIQUE#1
execution_datetimestamp with time zoneUNIQUE#2
statecharacter varying(50)
run_idcharacter varying(250)UNIQUE#1
external_triggerboolean
confbytea
end_datetimestamp with time zone
start_datetimestamp with time zone

Indexes:

  • dag_id_state dag_id, state

public.import_error

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
timestamptimestamp with time zone
filenamecharacter varying(1024)
stacktracetext

public.job

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
dag_idcharacter varying(250)
statecharacter varying(20)
job_typecharacter varying(30)
start_datetimestamp with time zone
end_datetimestamp with time zone
latest_heartbeattimestamp with time zone
executor_classcharacter varying(500)
hostnamecharacter varying(500)
unixnamecharacter varying(1000)

Indexes:

  • idx_job_state_heartbeat state, latest_heartbeat
  • job_type_heart job_type, latest_heartbeat

public.known_event

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
labelcharacter varying(200)
start_datetimestamp without time zone
end_datetimestamp without time zone
public.users.iduser_idinteger
public.known_event_type.idknown_event_type_idinteger
descriptiontext

public.known_event_type

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
know_event_typecharacter varying(200)

Tables referencing this one via Foreign Key Constraints:


public.kube_resource_version

Table Structure:


F-KeyNameTypeDescription
one_row_idbooleanPRIMARY KEY DEFAULT true
resource_versioncharacter varying(255)

Constraints:

NameConstraint
kube_resource_version_one_row_idCHECK (one_row_id)

public.kube_worker_uuid

Table Structure:


F-KeyNameTypeDescription
one_row_idbooleanPRIMARY KEY DEFAULT true
worker_uuidcharacter varying(255)

Constraints:

NameConstraint
kube_worker_one_row_idCHECK (one_row_id)

public.log

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
dttmtimestamp with time zone
dag_idcharacter varying(250)
task_idcharacter varying(250)
eventcharacter varying(30)
execution_datetimestamp with time zone
ownercharacter varying(500)
extratext

Indexes:

  • idx_log_dag dag_id

public.serialized_dag

Table Structure:


F-KeyNameTypeDescription
dag_idcharacter varying(250)PRIMARY KEY
fileloccharacter varying(2000)NOT NULL
fileloc_hashintegerNOT NULL
datajsonNOT NULL
last_updatedtimestamp with time zoneNOT NULL

Indexes:

  • idx_filelo_hash fileloc_hash

public.sla_miss

Table Structure:


F-KeyNameTypeDescription
task_idcharacter varying(250)PRIMARY KEY
dag_idcharacter varying(250)PRIMARY KEY
execution_datetimestamp with time zonePRIMARY KEY
email_sentboolean
timestamptimestamp with time zone
descriptiontext
notification_sentboolean

Indexes:

  • sm_dag dag_id

public.slot_pool

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
poolcharacter varying(50)UNIQUE
slotsinteger
descriptiontext

public.task_fail

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
task_idcharacter varying(250)NOT NULL
dag_idcharacter varying(250)NOT NULL
execution_datetimestamp with time zoneNOT NULL
start_datetimestamp with time zone
end_datetimestamp with time zone
durationinteger

Indexes:

  • idx_task_fail_dag_task_date dag_id, task_id, execution_date

public.task_instance

Table Structure:


F-KeyNameTypeDescription
task_idcharacter varying(250)PRIMARY KEY
dag_idcharacter varying(250)PRIMARY KEY
execution_datetimestamp with time zonePRIMARY KEY
start_datetimestamp with time zone
end_datetimestamp with time zone
durationdouble precision
statecharacter varying(20)
try_numberinteger
hostnamecharacter varying(1000)
unixnamecharacter varying(1000)
job_idinteger
poolcharacter varying(50)NOT NULL
queuecharacter varying(256)
priority_weightinteger
operatorcharacter varying(1000)
queued_dttmtimestamp with time zone
pidinteger
max_triesintegerDEFAULT '-1'::integer
executor_configbytea

Tables referencing this one via Foreign Key Constraints:

Indexes:

  • ti_dag_date dag_id, execution_date
  • ti_dag_state dag_id, state
  • ti_job_id job_id
  • ti_pool pool, state, priority_weight
  • ti_state state
  • ti_state_lkp dag_id, task_id, execution_date, state

Table public.task_reschedule

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
public.task_instance.task_id#1task_idcharacter varying(250)NOT NULL
public.task_instance.dag_id#1dag_idcharacter varying(250)NOT NULL
public.task_instance.execution_date#1execution_datetimestamp with time zoneNOT NULL
try_numberintegerNOT NULL
start_datetimestamp with time zoneNOT NULL
end_datetimestamp with time zoneNOT NULL
durationintegerNOT NULL
reschedule_datetimestamp with time zoneNOT NULL

Indexes:

  • idx_task_reschedule_dag_task_date dag_id, task_id, execution_date

public.users

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
usernamecharacter varying(250)UNIQUE
emailcharacter varying(500)
passwordcharacter varying(255)
superuserboolean

Tables referencing this one via Foreign Key Constraints:


public.variable

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
keycharacter varying(250)UNIQUE
valtext
is_encryptedboolean

public.xcom

Table Structure:


F-KeyNameTypeDescription
idserialPRIMARY KEY
keycharacter varying(512)
valuebytea
timestamptimestamp with time zoneNOT NULL
execution_datetimestamp with time zoneNOT NULL
task_idcharacter varying(250)NOT NULL
dag_idcharacter varying(250)NOT NULL

Indexes:

  • idx_xcom_dag_task_date dag_id, task_id, execution_date

Never miss an update from us.

Do Airflow the easy way.

Run production-grade Airflow out-of-the-box with Astronomer.