What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 130 Stack Exchange communities.

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
11  
(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 Kim 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 at 17:05

2 Answers 2

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
  • Score
  • ViewCount
  • Body
  • OwnerUserId (present only if user has not been deleted; always -1 for tag wiki entries (i.e., the community user owns them))
  • OwnerDisplayName
  • LastEditorUserId
  • LastEditorDisplayName="Rich B"
  • LastEditDate="2009-03-05T22:28:34.823" - the date and time of the most recent edit to the post
  • 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
  • Tags
  • AnswerCount
  • 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
  • DownVotes
  • EmailHash (now always blank)
  • AccountId
  • 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"

CloseAsOffTopicReasonTypes

  • Id
  • IsUniversal
  • MarkdownMini — the text of the close reason (in markdown syntax)
  • CreationDate
  • CreationModeratorId
  • ApprovalDate
  • ApprovalModeratorId
  • DeactivationDate
  • DeactivationModeratorId

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

  • Id
  • PostId
  • IsAnonymous
  • VoteTypeId
  • 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, or 15 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

PostTags

  • PostId
  • TagId

ReviewRejectionReasons

Canned rejection reasons for suggested edits. http://data.stackexchange.com/stackoverflow/query/199800/show-all-review-rejection-reasons

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

ReviewTaskResults

  • Id
  • ReviewTaskId
  • ReviewTaskResultTypeId (listed in ReviewTaskResultTypeId)

    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

  • CreationDate

  • DeletionDate
  • ReviewTaskStateId (listed in ReviewTaskStates, see http://data.stackexchange.com/stackoverflow/query/199801/show-all-review-task-states)

    1. Active
    2. Completed
    3. Invalidated
  • PostId

  • SuggestedEditId (for suggested edits, which have their own numbering for historical reasons)
  • CompletedByReviewTaskId

SuggestedEdits

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

SuggestedEditVotes

  • Id
  • SuggestedEditId
  • UserId
  • VoteTypeId
  • CreationDate
  • TargetUserId
  • TargetRepChange

Tags

  • Id
  • TagName
  • Count
  • ExcerptPostId
  • WikiPostId

TagSynonyms

  • Id
  • SourceTagName
  • TargetTagName
  • CreationDate
  • OwnerUserId
  • AutoRenameCount
  • LastAutoRename
  • Score
  • ApprovedByUserId
  • ApprovalDate

Votes

  • Id
  • PostId
  • VoteTypeId (listed in the VoteTypes table)

    1 - AcceptedByOriginator
    2 - UpMod
    3 - DownMod
    4 - Offensive
    5 - Favorite (if VoteTypeId = 5, UserId will be populated)
    6 - Close
    7 - Reopen
    8 - BountyStart (if VoteTypeId = 8, UserId will be populated)
    9 - BountyClose
    10 - Deletion
    11 - Undeletion
    12 - Spam
    15 - ModeratorReview  
    16 - ApproveEditSuggestion
    
  • UserId (only present if VoteTypeId is 5 or 8)
  • CreationDate
  • BountyAmount (only present if VoteTypeId is 8 or 9)

Not listed here: xxxTypes tables which list (Id, Name) pairs for Posts.PostTypeId, Votes.VoteTypeId, etc. See http://data.stackexchange.com/stackoverflow/query/36599/show-all-types for an up-to-date list of all types.

share|improve this answer
    
@Jeff Atwood, Does the data dump include anything about the close reason? I am interested it seeing a list of all questions that have been migrated. –  Zoredache Jul 28 '10 at 22:30
    
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 at 23:42
    
what is up- and downvote attribute in users? i though that it was the total upvotes and downvotes the user has taken but i checked that from questions and answers of users and concluded that it is not so. –  Asqan Apr 10 at 22:44
    
Actually, the LinkTypeId field seems to contain 3 for duplicates, not 2. –  O. R. Mapper May 15 at 12:42
    
The JSON-encoded string with the voters seems to be present for a PostHistoryTypeId of 19 and 20, as well. –  O. R. Mapper May 19 at 11:23
    
PostHistoryTypeId values of 24, 25, 33 and 36 exist as well and are undocumented here. –  O. R. Mapper May 19 at 12:43
    
Actually, all values from 33 to 38 appear in the files. –  O. R. Mapper May 20 at 6:33
    
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 at 6:22

Here is a summary the columns and types that are anonymized or seemingly empty:

Columns

  • Votes.UserId unless VoteType is 5 or 8

  • User.EmailHash

Types

  • All VoteTypes except 5, 8, 9

  • PostHistoryTypes 17, 18, 25, 31

share|improve this answer

You must log in to answer this question.

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