Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 158 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

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

share|improve this question
14  
(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
    
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
1  
@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 Oct 11 '09 at 16:00
1  
@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
3  
Can this please link up to the SEDE FAQ!? It took a while to find this! – gideon Apr 7 '11 at 16:15
    
    
    
@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

Note: the foreign key fields are formatted as links to their parent table

Posts

  • Id
  • PostTypeId (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 is 1)

  • ParentID (only present if PostTypeId is 2)
  • CreationDate
  • DeletionDate (only non-null for the SEDE PostsWithDeleted table. Deleted posts are not present on Posts. Column not present on data dump.)
  • Score
  • 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="2009-03-05T22:28:34.823" - the date and time of the most recent edit to the post (nullable)
  • LastActivityDate="2009-03-11T12:51:01.480" - the date and time of the most recent activity on the post. For a question, this could be the post being edited, a new answer was posted, a bounty was started, etc.
  • Title (nullable)
  • Tags (nullable)
  • AnswerCount (nullable)
  • CommentCount
  • FavoriteCount
  • ClosedDate (present only if the post is closed)
  • CommunityOwnedDate (present only if post is community wikied)

Users

  • Id
  • Reputation
  • CreationDate
  • DisplayName
  • LastAccessDate
  • WebsiteUrl
  • Location
  • AboutMe
  • Views
  • UpVotes (how many upvotes the user has cast)
  • DownVotes
  • EmailHash (now always blank)
  • AccountId (StackExchange Network profile Id of the user)
  • Age

Comments

  • Id
  • PostId
  • Score (Only present if score > 0)
  • Text, e.g.: "@Stu Thompson: What a horrible idea, you clueless git!"
  • CreationDate, e.g.:"2008-09-06T08:07:10.730"
  • UserDisplayName
  • UserId (Optional. Absent if user has been deleted)

Badges

  • Id
  • UserId, e.g.: "420"
  • Name, e.g.: "Teacher"
  • 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 - The first title a question is asked with.
    2. Initial Body - The first raw body text a post is submitted with.
    3. Initial Tags - The first tags a question is asked with.
    4. Edit Title - A question's title has been changed.
    5. Edit Body - A post's body has been changed, the raw text is stored here as markdown.
    6. Edit Tags - A question's tags have been changed.
    7. Rollback Title - A question's title has reverted to a previous version.
    8. Rollback Body - A post's body has reverted to a previous version - the raw text is stored here.
    9. Rollback Tags - A question's tags have reverted to a previous version.
    10. Post Closed - A post was voted to be closed.
    11. Post Reopened - A post was voted to be reopened.
    12. Post Deleted - A post was voted to be removed.
    13. Post Undeleted - A post was voted to be restored.
    14. Post Locked - A post was locked by a moderator.
    15. Post Unlocked - A post was unlocked by a moderator.
    16. Community Owned - A post has become community owned.
    17. Post Migrated - A post was migrated. superseded now with id 35 and 36 (away/here)
    18. Question Merged - A question has had another, deleted question merged into itself.
    19. Question Protected - A question was protected by a moderator.
    20. Question Unprotected - A question was unprotected by a moderator.
    21. Post Disassociated - An admin removes the OwnerUserId from a post.
    22. Question Unmerged - A previously merged question has had its answers and votes restored.
    24. Suggested Edit Applied
    25. Post Tweeted
    31. Comment discussion moved to chat
    33. Post notice added
    34. Post notice removed
    35. Post migrated away replaces id 17
    36. Post migrated here replaces id 17
    37. Post merge source
    38. Post merge destination

  • 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: "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
  • Text: A raw version of the new value for a given revision
    • If PostHistoryTypeId = 10, 11, 12, 13, 14, 15, 19, 20 and 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
    3. Duplicate

PostsWithDeleted

Similar to Posts table but with deleted post as well. For deleted posts the extra column Deletiondate is not null. Listed here are the fields that contain info when DeletionDate is not null.

  • Id
  • PostTypeId (listed in the PostTypes table)
  • ParentId (only on when PostTypeId = 2 (answer))
  • CreationDate
  • DeletionDate

PostTags

ReviewRejectionReasons

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

  • Id
  • Name
  • Description
  • PostTypeId (for reasons that apply to specific post types only)

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

  • Id
  • PostId
  • CreationDate
  • ApprovalDate
  • RejectionDate
  • OwnerUserId
  • Comment
  • Text
  • Title
  • Tags
  • RevisionGUID

SuggestedEditVotes

Tags

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 (since 2013-06-25 close votes are ONLY stored in the PostHistory table)
    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 (only present if VoteTypeId is 5 or 8)
  • CreationDate (time data is purposefully removed to protect user privacy)
  • BountyAmount (only present if VoteTypeId is 8 or 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.

share|improve this answer
1  
I am working on the data dump and found that only 7 xml files: Posts, Users, Comments, Badges, PostHistory, PostLinks, Votes are now present with each Exchange. Has the structure changed, or am I missing something? – Divi Apr 4 '14 at 23:42
    
The PostHistory.xml file of sound.stackexchange.com seems to feature a different format for its JSON strings listing voters than the other sites. – O. R. Mapper May 21 '14 at 6:22
    
@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 at 22:56
    
So, there is no way to figure out which user has voted which user's post, right? – mostafiz Feb 20 at 14:33
    
So if I get it right the users displayName is stored in the task redundantly to prohibit joins when making a tasks query. But in the task overview we also show the users reputation. So you do make a join with the user table when making a query for tasks? Why would you store the creating users displayName redundantly then? – tobi Mar 2 at 12:27
    
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 at 16:35

You must log in to answer this question.

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