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
9  
(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
    
    
add comment

2 Answers

Posts

  • Id
  • PostTypeId
    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"

PostFeedback

  • Id
  • PostId
  • IsAnonymous
  • VoteTypeId
  • CreationDate

PostHistory

  • Id
  • PostHistoryTypeId
    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.
    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.
  • 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:
    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)
    New 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>

PostHistoryTypes

  • Id
  • Name

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
    2. Duplicate

PostLinkTypes

  • Id
  • Name

PostTags

  • PostId
  • TagId

PostTypes

  • Id
  • Name

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

    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)

VoteTypes

  • Id
  • Name
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
add comment

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
add comment

You must log in to answer this question.

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