285

Stack Exchange releases "data dumps" of all its publicly available content roughly every three months via archive.org, and also makes that information queryable over the Internet at the Stack Exchange Data Explorer (SEDE). Over time, as new features and other bits of data are introduced to Stack Exchange sites, the "anatomy" of the data dump is likely to change. What is the current structure of the public data dump?

Return to FAQ index

  • 19
    (Moved from being an answer.) I think I'd rather the data dump came with a README.txt which gave the full format, and a CHANGES.txt which showed what had changed over time. Yes, we could also have a question with an answer per entry in CHANGES.txt, but it's best to make the data dump file fairly self-contained, IMO. We can crowd source here, and then Jeff can put it into the zip file... – Jon Skeet Jul 6 '09 at 10:17
  • Like I said before (but was lost do to answer->comment move), with the description/explanation/etc. here we can do it ourselves, "crowd source" it. It were a trivial effort I think the SO team would have done it already. Where was this information before? In the comments of the first data dump post on blog.stackoverflow.com. – Stu Thompson Jul 6 '09 at 10:56
  • 1
    Surely there should be an XSD for the dump? – John Topley Aug 4 '09 at 15:15
  • No XSD. Remember that the dump is a derivative of an existing Schema (SQL DB) and not it's native format. And the dump is basically used only by a few people. And, the format has been changing from dump to dump. Why bother? Much work, little gain. – Stu Thompson Sep 2 '09 at 8:26
  • 3
    @Stu: But the reason not many people are using the data dump could be because there is no XSD schema or in another form that specifies actual data types of each column domain. I was a bit hesitant to get started because there was no schema. But real hardcore folks would probably can figure it out and might actually prefer it this way. – Sung M. Kim Oct 11 '09 at 16:00
  • 2
    @Sung Meister: That's what separates the men from the boys. If one can't work out what the data types are in the dump with the below description, then maybe one is not worthy and should stick to the happy world of copy-paste JavaScript. Sarcasm aside, you are asking much and giving little. If you (and others) think a schema is that valuable, whip it up yourself and post here on MSO. And maintain it. Otherwise, such requests are all take and no give. – Stu Thompson Oct 15 '09 at 18:45
  • 2
    But the reason not many people are using the data dump could be because there is no XSD schema or in another form that specifies actual data types of each column domain. [citation needed] The reason not that many people are using the dump is because not many people have a real use for it. – perbert Nov 2 '09 at 4:56
  • @JonSkeet My vote would be to have README and CHANGES in markdown; both to force it to conform to a basic standard of readability, and allow it to be easily ported by the end user into a more consumable format. Although, since I wouldn't be the one writing it, feel free to ignore that suggestion. – Parthian Shot Jul 31 '14 at 17:05
245

About this list:
- foreign key fields are formatted as links to their parent table
- italic table names are found in both the Data Dump on Archive.org as well as in the SEDE


Posts / PostsWithDeleted

You find in Posts all non-deleted posts. PostsWithDeleted includes deleted posts while sharing the same columns with Posts but with only a few fields populated which are marked with a 1 below.

  • Id1
  • PostTypeId1 (listed in the PostTypes table)
    1 = Question
    2 = Answer
    3 = Orphaned tag wiki
    4 = Tag wiki excerpt
    5 = Tag wiki
    6 = Moderator nomination
    7 = "Wiki placeholder" (seems to only be the election description)
    8 = Privilege wiki

  • AcceptedAnswerId (only present if PostTypeId = 1)

  • ParentId1 (only present if PostTypeId = 2)
  • CreationDate1
  • DeletionDate1 (only non-null for the SEDE PostsWithDeleted table. Deleted posts are not present on Posts. Column not present on data dump.)
  • Score1
  • ViewCount (nullable)
  • Body (as rendered HTML, not Markdown)
  • OwnerUserId (only present if user has not been deleted; always -1 for tag wiki entries, i.e. the community user owns them)
  • OwnerDisplayName (nullable)
  • LastEditorUserId (nullable)
  • LastEditorDisplayName (nullable)
  • LastEditDate (e.g. 2009-03-05T22:28:34.823) - the date and time of the most recent edit to the post (nullable)
  • LastActivityDate (e.g. 2009-03-11T12:51:01.480) - datetime of the post's most recent activity
  • Title (nullable)
  • Tags1 (nullable)
  • AnswerCount (nullable)
  • CommentCount
  • FavoriteCount
  • ClosedDate1 (present only if the post is closed)
  • CommunityOwnedDate (present only if post is community wiki'd)

Users


Comments

  • Id
  • PostId
  • Score
  • Text (Comment body)
  • CreationDate
  • UserDisplayName
  • UserId (Optional. Absent if user has been deleted)

Badges

  • Id
  • UserId
  • Name (Name of the badge)
  • Date (e.g. 2008-09-15T08:55:03.923)
  • Class
    1 = Gold
    2 = Silver
    3 = Bronze

  • TagBased = True if badge is for a tag, otherwise it is a named badge


CloseAsOffTopicReasonTypes


PendingFlags

Despite the name, this table in fact contains close-related flags and votes.

  • Id
  • FlagTypeId (listed in the FlagTypes table)
    13 = canned flag for closure
    14 = vote to close
    15 = vote to reopen

  • PostId

  • CreationDate
  • CloseReasonTypeId (listed in the CloseReasonTypes table)
  • CloseAsOffTopicReasonTypeId, if CloseReasonTypeId = 102 (off-topic) (listed in the CloseAsOffTopicReasonTypes table)
  • DuplicateOfQuestionId, if CloseReasonTypeId is 1 or 101 (old duplicate or current duplicate)
  • BelongsOnBaseHostAddress, for votes to close and migrate

PostFeedback

Collects up and down votes from anonymous visitor and/or unregistered users. See here

  • Id
  • PostId
  • IsAnonymous
  • VoteTypeId (listed in the VoteTypes table)
    2 = UpMod
    3 = DownMod

  • CreationDate


PostHistory

  • Id
  • PostHistoryTypeId (listed in the PostHistoryTypes table)
    1 = Initial Title - initial title (questions only)
    2 = Initial Body - initial post raw body text
    3 = Initial Tags - initial list of tags (questions only)
    4 = Edit Title - modified title (questions only)
    5 = Edit Body - modified post body (raw markdown)
    6 = Edit Tags - modified list of tags (questions only)
    7 = Rollback Title - reverted title (questions only)
    8 = Rollback Body - reverted body (raw markdown)
    9 = Rollback Tags - reverted list of tags (questions only)
    10 = Post Closed - post voted to be closed
    11 = Post Reopened - post voted to be reopened
    12 = Post Deleted - post voted to be removed
    13 = Post Undeleted - post voted to be restored
    14 = Post Locked - post locked by moderator
    15 = Post Unlocked - post unlocked by moderator
    16 = Community Owned - post now community owned
    17 = Post Migrated - post migrated - now replaced by 35/36 (away/here)
    18 = Question Merged - question merged with deleted question
    19 = Question Protected - question was protected by a moderator.
    20 = Question Unprotected - question was unprotected by a moderator.
    21 = Post Disassociated - OwnerUserId removed from post by admin
    22 = Question Unmerged - answers/votes restored to previously merged question
    24 = Suggested Edit Applied
    25 = Post Tweeted
    31 = Comment discussion moved to chat
    33 = Post notice added - comment contains foreign key to PostNotices
    34 = Post notice removed - comment contains foreign key to PostNotices
    35 = Post migrated away - replaces id 17
    36 = Post migrated here - replaces id 17
    37 = Post merge source
    38 = Post merge destination
    50 = Bumped by Community User
    52 = Question became hot network question
    53 = Question removed from hot network questions by a moderator

    Additionally, in older dumps (all guesses, all seem no longer present in the wild):
    23 = Unknown dev related event
    26 = Vote nullification by dev (ERM?)
    27 = Post unmigrated/hidden moderator migration?
    28 = Unknown suggestion event
    29 = Unknown moderator event (possibly de-wikification?)
    30 = Unknown event (too rare to guess)

  • PostId

  • RevisionGUID: At times more than one type of history record can be recorded by a single action. All of these will be grouped using the same RevisionGUID
  • CreationDate (e.g. 2009-03-05T22:28:34.823)
  • UserId
  • UserDisplayName: populated if a user has been removed and no longer referenced by user Id
  • Comment: This field will contain the comment made by the user who edited a post.

    • If PostHistoryTypeId = 10, this field contains the CloseReasonId of the close reason (listed in CloseReasonTypes):
      Old close reasons:
      1 = Exact Duplicate
      2 = Off-topic
      3 = Subjective and argumentative
      4 = Not a real question
      7 = Too localized
      10 = General reference
      20 = Noise or pointless (Meta sites only)
      Current close reasons:
      101 = Duplicate
      102 = Off-topic
      103 = Unclear what you're asking
      104 = Too broad
      105 = Primarily opinion-based

    • If PostHistoryTypeId in (33,34) this field contains the PostNoticeId of the PostNotice

  • Text: A raw version of the new value for a given revision
    - If PostHistoryTypeId in (10,11,12,13,14,15,19,20,35) this column will contain a JSON encoded string with all users who have voted for the PostHistoryTypeId
    - If it is a duplicate close vote, the JSON string will contain an array of original questions as OriginalQuestionIds
    - If PostHistoryTypeId = 17 this column will contain migration details of either from <url> or to <url>


PostLinks

  • Id primary key
  • CreationDate when the link was created
  • PostId id of source post
  • RelatedPostId id of target/related post
  • LinkTypeId type of link
    1 = Linked (PostId contains a link to RelatedPostId)
    3 = Duplicate (PostId is a duplicate of RelatedPostId)

PostNotices

  • Id
  • PostId
  • PostNoticeTypeId
    1 = Citation needed
    2 = Current event
    3 = Insufficient explanation
    10 = Current answers are outdated
    11 = Draw attention
    12 = Improve details
    13 = Authoritative reference needed 14 = Canonical answer required
    15 = Reward existing answer
    20 = Content dispute
    21 = Offtopic comments
    22 = Historical significance
    23 = Wiki Answer
  • CreationDate
  • DeletionDate
  • ExpiryDate
  • Body (when present contains the custom text shown with the notice)
  • OwnerUserId
  • DeletionUserId

PostNoticeTypes

  • Id
  • ClassId
    1 = Historical lock
    2 = Bounty
    4 = Moderator notice
  • Name
  • Body (contains the default notice text)
  • IsHidden
  • Predefined
  • PostNoticeDurationId
    -1 = No duration specified
    1 = 7 days (bounty)

PostTags


ReviewRejectionReasons

Canned rejection reasons for suggested edits. See Show all review rejection reasons

  • Id
  • Name
  • Description
  • PostTypeId (for reasons that apply to Wiki (5) or Excerpt (6) post types only, otherwise null)

ReviewTaskResults

  • Id
  • ReviewTaskId
  • ReviewTaskResultTypeId (listed in ReviewTaskResultTypes)
    1 = Not Sure
    2 = Approve (suggested edits)
    3 = Reject (suggested edits)
    4 = Delete (low quality)
    5 = Edit (first posts, late answers, low quality)
    6 = Close (close, low quality)
    7 = Looks OK (low quality)
    8 = Do Not Close (close)
    9 = Recommend Deletion (low quality answer)
    10 = Recommend Close (low quality question)
    11 = I'm Done (first posts)
    12 = Reopen (reopen)
    13 = Leave Closed (reopen)
    14 = Edit and Reopen (reopen)
    15 = Excellent (community evaluation)
    16 = Satisfactory (community evaluation)
    17 = Needs Improvement (community evaluation)
    18 = No Action Needed (first posts, late answers)

  • CreationDate

  • RejectionReasonId (for suggested edits; listed in ReviewRejectionReasons)
  • Comment

ReviewTasks

  • Id
  • ReviewTaskTypeId (listed in ReviewTaskTypes)
    1 = Suggested Edit
    2 = Close Votes
    3 = Low Quality Posts
    4 = First Post
    5 = Late Answer
    6 = Reopen Vote
    7 = Community Evaluation
    8 = Link Validation
    9 = Flagged Posts
    10 = Triage
    11 = Helper

  • CreationDate

  • DeletionDate
  • ReviewTaskStateId (listed in ReviewTaskStates)
    1 = Active
    2 = Completed
    3 = Invalidated

  • PostId

  • SuggestedEditId (for suggested edits, which have their own numbering for historical reasons)
  • CompletedByReviewTaskId id associated to the ReviewTaskResult that stores the outcome of a completed review.

SuggestedEdits

If both approval and rejection date are null then this edit is still in review (and its corresponding entry in ReviewTasks will have an active state as well).

  • Id
  • PostId
  • CreationDate
  • ApprovalDate - NULL if not approved (yet).
  • RejectionDate - NULL if not rejected (yet).
  • OwnerUserId
  • Comment
  • Text
  • Title
  • Tags
  • RevisionGUID

SuggestedEditVotes


Tags

  • Id
  • TagName
  • Count
  • ExcerptPostId (nullable) Id of Post that holds the excerpt text of the tag
  • WikiPostId (nullable) Id of Post that holds the wiki text of the tag

TagSynonyms


Votes

  • Id
  • PostId
  • VoteTypeId (listed in the VoteTypes table)
    1 = AcceptedByOriginator
    2 = UpMod (AKA upvote)
    3 = DownMod (AKA downvote)
    4 = Offensive
    5 = Favorite (UserId will also be populated)
    6 = Close (effective 2013-06-25: Close votes are only stored in table: PostHistory)
    7 = Reopen
    8 = BountyStart (UserId and BountyAmount will also be populated)
    9 = BountyClose (BountyAmount will also be populated)
    10 = Deletion
    11 = Undeletion
    12 = Spam
    15 = ModeratorReview
    16 = ApproveEditSuggestion
  • UserId (present only if VoteTypeId in (5,8); -1 if user is deleted)
  • CreationDate Date only (2018-07-31 00:00:00 time data is purposefully removed to protect user privacy)
  • BountyAmount (present only if VoteTypeId in (8,9))

xxxTypes

Not listed here:
- xxxTypes tables which list (Id, Name) pairs for Posts.PostTypeId, Votes.VoteTypeId, etc. See Show all types for an up-to-date list of all types.


All Tables/Columns/Type

Find the exact T-SQL datatype and length/precision of each specific column in this query:

List all Fields in all Tables on SEDE


TIMESTAMPS

All timestamps are UTC, default format: yyyy-MM-dd hh:mm:ss (stored with milliseconds).

Example of conversion current time to PST (including DST) using At Time Zone:

SELECT GetDate() At Time Zone 'UTC' At Time Zone 'Pacific Standard Time'

To list time zones: SELECT * FROM sys.time_zone_info

  • 1
    @Stu Thompson where can the DB schema, for the tables you listed above, be found ? – eran otzer Jul 31 '15 at 8:47
  • It would be useful to have the expanded sizes of the various files as well, for provisioning purposes. – MasterScrat Feb 10 '16 at 22:56
  • I'm sorry if this was asked in another question somewhere, but I'm curious why the schema dump doesn't just maintain the column descriptions as MS_Description details? I opened in Red Gate SQL Doc to review a few tables and didn't see them utilized. Not complaining, just wondering why this wouldn't be leveraged instead of a post.... and if there is a link discussing this further please let me know so I can go read it :-) – SheldonH May 12 '16 at 16:35
  • BountyStart and BountyClose seem to be no longer available. I just notices when I wanted to create a query listing active bounty questions ordered by bounty start date. Why are they gone? How do I find out instead? Update: I just saw that there seems to be a way via table VoteTypes. You still should update this answer. – kriegaex Mar 4 '17 at 10:34
  • @ashleedawg This post was Community Wiki, so your edit was applied immediately. – Sonic the Wizard Werehog Apr 4 '18 at 0:34
  • 1
    @ashleedawg Please see Suggested edit by Anonymous for the full story. – Sonic the Wizard Werehog Apr 4 '18 at 0:50
  • Re the latest edits, see this post. I appreciate all the work that went into this, but this edit introduced some big accessibility problems and relies on tools not available to other would-be maintainers, so I think we need to adjust something here. – Monica Cellio Jun 27 '18 at 19:13
  • @MonicaCellio Their edits have since been rolled back. Does it look better now? – Sonic the Wizard Werehog Jun 28 '18 at 22:13
  • @SonictheInclusiveHedgehog much better! The remaining small fonts are regretable but the main problem has been fixed and some of the small fonts have been there a while, probably because trying to normalize this huge post is hard. :-) – Monica Cellio Jun 28 '18 at 22:25
  • Apologies @E.P. during the edit, exclusion of PostHistory was an oversight. In the future, remember you're can instantly rollback to undo one or more edits with which you're dissatisfied. This applies to any post, but especially wiki's which belong to us all (and mistakes happen) – ashleedawg Jun 29 '18 at 5:12
  • (Sry apparently I'm not getting pings from here.) If the goal is strictly a list of tables/field types/etc, sql system tables may be helpful such as select * from information_schema.columns. Here is a zip file with a custom XLSM template reference images. Interesting that we tend to focus on negatives, while in 2+ months there were 4 commenters re: the issue, 33 upvotes, 4 fav's, and no downvotes. I think usage is dropping in favor of Google BigQuery (same data). – ashleedawg Jun 29 '18 at 5:31
  • @ashleedawg Maybe it is me but I can't download that zip, the whole site seems unreachable from my location (Western Europe). I tried a different browser and a different network but no can do so far. – rene Jun 29 '18 at 21:35
  • @rene odd. Try this one. – ashleedawg Jun 30 '18 at 11:23
  • @rene I stumbled across the SEDE query I used to list/verify the table fields and data types and differentiate the static/reference tables... It's nothing spectacular but figured I'd pass it along anyway. – ashleedawg Jul 14 '18 at 14:03
  • In Tags, what is the meaning of "ExcerptPostId" and "WikiPostId"? The data dump has tags.xml, where each row would have something like: <row Id="1" TagName="comedy" Count="54" ExcerptPostId="280" WikiPostId="279" />, I'm building a mock database for testing graphql, but not sure how to incorporate these two fields... – jupiar Mar 16 at 8:38
4

The Schema Visualized SEDE ERD The Entity Relationship Diagram (ERD) above was constructed using the data found in the information_schema.columns table. (Thank you to ashleedawg for paring the table down to its bare essentials). Python 3 was used to convert all the table, column and key information into a SQL script. (All tables, columns, and foreign key constraints as specified in the accepted answer were included.) The ERD was reverse engineered using dbschema.

Please find the script and other related files in my GitHub repository.

The interactive HTML version of the ERD is posted up here: https://sedeschema.github.io/

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .