{"status":"ok","message-type":"work","message-version":"1.0.0","message":{"indexed":{"date-parts":[[2026,7,2]],"date-time":"2026-07-02T12:38:12Z","timestamp":1782995892105,"version":"3.54.5"},"reference-count":26,"publisher":"MDPI AG","issue":"4","license":[{"start":{"date-parts":[[2020,4,10]],"date-time":"2020-04-10T00:00:00Z","timestamp":1586476800000},"content-version":"vor","delay-in-days":0,"URL":"https:\/\/creativecommons.org\/licenses\/by\/4.0\/"}],"content-domain":{"domain":[],"crossmark-restriction":false},"short-container-title":["Information"],"abstract":"<jats:p>We present a novel strategy, based on the Extract, Transform and Load (ETL) process, to collect data from a blockchain, elaborate and make it available for further analysis. The study aims to satisfy the need for increasingly efficient data extraction strategies and effective representation methods for blockchain data. For this reason, we conceived a system to make scalable the process of blockchain data extraction and clustering, and to provide a SQL database which preserves the distinction between transaction and addresses. The proposed system satisfies the need to cluster addresses in entities, and the need to store the extracted data in a conventional database, making possible the data analysis by querying the database. In general, ETL processes allow the automation of the operation of data selection, data collection and data conditioning from a data warehouse, and produce output data in the best format for subsequent processing or for business. We focus on the Bitcoin blockchain transactions, which we organized in a relational database to distinguish between the input section and the output section of each transaction. We describe the implementation of address clustering algorithms specific for the Bitcoin blockchain and the process to collect and transform data and to load them in the database. To balance the input data rate with the elaboration time, we manage blockchain data according to the lambda architecture. To evaluate our process, we first analyzed the performances in terms of scalability, and then we checked its usability by analyzing loaded data. Finally, we present the results of a toy analysis, which provides some findings about blockchain data, focusing on a comparison between the statistics of the last year of transactions, and previous results of historical blockchain data found in the literature. The ETL process we realized to analyze blockchain data is proven to be able to perform a reliable and scalable data acquisition process, whose result makes stored data available for further analysis and business.<\/jats:p>","DOI":"10.3390\/info11040204","type":"journal-article","created":{"date-parts":[[2020,4,13]],"date-time":"2020-04-13T04:45:31Z","timestamp":1586753131000},"page":"204","update-policy":"https:\/\/doi.org\/10.3390\/mdpi_crossmark_policy","source":"Crossref","is-referenced-by-count":16,"title":["Applying the ETL Process to Blockchain Data. Prospect and Findings"],"prefix":"10.3390","volume":"11","author":[{"given":"Roberta","family":"Galici","sequence":"first","affiliation":[{"name":"Department of Mathematics and Computer Science, University of Cagliari, Via Ospedale 72, 09124 Cagliari, Italy"}],"role":[{"vocabulary":"crossref","role":"author"}]},{"given":"Laura","family":"Ordile","sequence":"additional","affiliation":[{"name":"Department of Mathematics and Computer Science, University of Cagliari, Via Ospedale 72, 09124 Cagliari, Italy"}],"role":[{"vocabulary":"crossref","role":"author"}]},{"ORCID":"https:\/\/orcid.org\/0000-0003-1540-8773","authenticated-orcid":false,"given":"Michele","family":"Marchesi","sequence":"additional","affiliation":[{"name":"Department of Mathematics and Computer Science, University of Cagliari, Via Ospedale 72, 09124 Cagliari, Italy"}],"role":[{"vocabulary":"crossref","role":"author"}]},{"ORCID":"https:\/\/orcid.org\/0000-0002-7530-0521","authenticated-orcid":false,"given":"Andrea","family":"Pinna","sequence":"additional","affiliation":[{"name":"Department of Electrical and Electronic Engineering (DIEE), University of Cagliari, Piazza D\u2019Armi, 09100 Cagliari, Italy"}],"role":[{"vocabulary":"crossref","role":"author"}]},{"ORCID":"https:\/\/orcid.org\/0000-0002-9090-7698","authenticated-orcid":false,"given":"Roberto","family":"Tonelli","sequence":"additional","affiliation":[{"name":"Department of Mathematics and Computer Science, University of Cagliari, Via Ospedale 72, 09124 Cagliari, Italy"}],"role":[{"vocabulary":"crossref","role":"author"}]}],"member":"1968","published-online":{"date-parts":[[2020,4,10]]},"reference":[{"key":"ref_1","unstructured":"Nakamoto, S. (2020, March 07). A Peer-to-Peer Electronic Cash System. Available online: https:\/\/bitcoin.org\/bitcoin.pdf."},{"key":"ref_2","unstructured":"Maxwell, G. (2020, March 07). CoinJoin: Bitcoin Privacy for the Real World. Available online: https:\/\/bitcointalk.org\/?topic=279249."},{"key":"ref_3","doi-asserted-by":"crossref","unstructured":"Ruffing, T., and Moreno-Sanchez, P. (2017, January 3\u20137). Valueshuffle: Mixing confidential transactions for comprehensive transaction privacy in bitcoin. Proceedings of the International Conference on Financial Cryptography and Data Security, Sliema, Malta.","DOI":"10.1007\/978-3-319-70278-0_8"},{"key":"ref_4","doi-asserted-by":"crossref","unstructured":"Bistarelli, S., Mercanti, I., and Santini, F. (2018, January 27\u201331). A suite of tools for the forensic analysis of bitcoin transactions: Preliminary report. Proceedings of the European Conference on Parallel Processing, Turin, Italy.","DOI":"10.1007\/978-3-030-10549-5_26"},{"key":"ref_5","doi-asserted-by":"crossref","unstructured":"Wu, Y., Luo, A., and Xu, D. (2019, January 1\u20133). Forensic Analysis of Bitcoin Transactions. Proceedings of the 2019 IEEE International Conference on Intelligence and Security Informatics (ISI), Shenzhen, China.","DOI":"10.1109\/ISI.2019.8823498"},{"key":"ref_6","doi-asserted-by":"crossref","unstructured":"Ron, D., and Shamir, A. (2013, January 1\u20135). Quantitative analysis of the full bitcoin transaction graph. Proceedings of the International Conference on Financial Cryptography and Data Security, Okinawa, Japan.","DOI":"10.1007\/978-3-642-39884-1_2"},{"key":"ref_7","doi-asserted-by":"crossref","unstructured":"Meiklejohn, S., Pomarole, M., Jordan, G., Levchenko, K., McCoy, D., Voelker, G.M., and Savage, S. (2013, January 23\u201325). A fistful of bitcoins: characterizing payments among men with no names. Proceedings of the 2013 Conference on Internet Measurement Conference, Barcelona, Spain.","DOI":"10.1145\/2504730.2504747"},{"key":"ref_8","doi-asserted-by":"crossref","unstructured":"Altshuler, Y., Elovici, Y., Cremers, A.B., Aharony, N., and Pentland, A. (2013). An Analysis of Anonymity in the Bitcoin System. Security and Privacy in Social Networks, Springer.","DOI":"10.1007\/978-1-4614-4139-7"},{"key":"ref_9","doi-asserted-by":"crossref","first-page":"237","DOI":"10.3390\/fi5020237","article-title":"Structure and anonymity of the bitcoin transaction graph","volume":"5","author":"Ober","year":"2013","journal-title":"Future Internet"},{"key":"ref_10","doi-asserted-by":"crossref","first-page":"109","DOI":"10.1089\/big.2015.0056","article-title":"Visualizing dynamic bitcoin transaction patterns","volume":"4","author":"McGinn","year":"2016","journal-title":"Big Data"},{"key":"ref_11","doi-asserted-by":"crossref","unstructured":"Harrigan, M., and Fretter, C. (2016, January 18\u201321). The unreasonable effectiveness of address clustering. Proceedings of the 2016 Intl IEEE Conferences on Ubiquitous Intelligence & Computing, Advanced and Trusted Computing, Scalable Computing and Communications, Cloud and Big Data Computing, Internet of People, and Smart World Congress (UIC\/ATC\/ScalCom\/CBDCom\/IoP\/SmartWorld), Toulouse, France.","DOI":"10.1109\/UIC-ATC-ScalCom-CBDCom-IoP-SmartWorld.2016.0071"},{"key":"ref_12","doi-asserted-by":"crossref","first-page":"180298","DOI":"10.1098\/rsos.180298","article-title":"Towards open data blockchain analytics: a Bitcoin perspective","volume":"5","author":"McGinn","year":"2018","journal-title":"R. Soc. Open Sci."},{"key":"ref_13","doi-asserted-by":"crossref","first-page":"1","DOI":"10.1007\/s11432-019-9900-9","article-title":"Identifying the vulnerabilities of bitcoin anonymous mechanism based on address clustering","volume":"63","author":"Zheng","year":"2020","journal-title":"Sci. China Inf. Sci."},{"key":"ref_14","doi-asserted-by":"crossref","unstructured":"Zheng, B., Zhu, L., Shen, M., Du, X., Yang, J., Gao, F., Li, Y., Zhang, C., Liu, S., and Yin, S. (2017, January 13\u201315). Malicious bitcoin transaction tracing using incidence relation clustering. Proceedings of the International Conference on Mobile Networks and Management, Melbourne, Australia.","DOI":"10.1007\/978-3-319-90775-8_25"},{"key":"ref_15","doi-asserted-by":"crossref","unstructured":"Ermilov, D., Panov, M., and Yanovich, Y. (2017, January 18\u201321). Automatic bitcoin address clustering. Proceedings of the 2017 16th IEEE International Conference on Machine Learning and Applications (ICMLA), Cancun, Mexico.","DOI":"10.1109\/ICMLA.2017.0-118"},{"key":"ref_16","doi-asserted-by":"crossref","unstructured":"Shin, M.G., Baek, U.J., Shim, K.S., Park, J.T., Yoon, S.H., and Kim, M.S. (2019, January 18\u201320). Block Analysis in Bitcoin System Using Clustering with Dimension Reduction. Proceedings of the 2019 20th Asia-Pacific Network Operations and Management Symposium (APNOMS), Matsue, Japan.","DOI":"10.23919\/APNOMS.2019.8892935"},{"key":"ref_17","doi-asserted-by":"crossref","unstructured":"Neudecker, T., and Hartenstein, H. (2017, January 3\u20137). Could network information facilitate address clustering in Bitcoin?. Proceedings of the International Conference on Financial Cryptography and Data Security, Sliema, Malta.","DOI":"10.1007\/978-3-319-70278-0_9"},{"key":"ref_18","doi-asserted-by":"crossref","first-page":"63","DOI":"10.1007\/s41060-017-0074-x","article-title":"Data-driven analysis of Bitcoin properties: exploiting the users graph","volume":"6","author":"Maesa","year":"2018","journal-title":"Int. J. Data Sci. Anal."},{"key":"ref_19","doi-asserted-by":"crossref","first-page":"1374","DOI":"10.1093\/comjnl\/bxy001","article-title":"A Petri Nets model for blockchain analysis","volume":"61","author":"Pinna","year":"2018","journal-title":"Comput. J."},{"key":"ref_20","doi-asserted-by":"crossref","unstructured":"Bartoletti, M., Lande, S., Pompianu, L., and Bracciali, A. (2017, January 11\u201315). A general framework for blockchain analytics. Proceedings of the 1st Workshop on Scalable and Resilient Infrastructures for Distributed Ledgers, Las Vegas, NV, USA.","DOI":"10.1145\/3152824.3152831"},{"key":"ref_21","first-page":"24","article-title":"Storing and Querying Blockchain using SQL Databases","volume":"17","author":"Yue","year":"2019","journal-title":"Inf. Syst. Educ. J."},{"key":"ref_22","doi-asserted-by":"crossref","unstructured":"Trujillo, J., and Luj\u00e1n-Mora, S. (2003, January 13\u201316). A UML based approach for modeling ETL processes in data warehouses. Proceedings of the International Conference on Conceptual Modeling, Chicago, IL, USA.","DOI":"10.1007\/978-3-540-39648-2_25"},{"key":"ref_23","doi-asserted-by":"crossref","unstructured":"Kiran, M., Murphy, P., Monga, I., Dugan, J., and Baveja, S.S. (November, January 29). Lambda architecture for cost-effective batch and speed big data processing. Proceedings of the 2015 IEEE International Conference on Big Data (Big Data), Santa Clara, CA, USA.","DOI":"10.1109\/BigData.2015.7364082"},{"key":"ref_24","unstructured":"Marz, N., and Warren, J. (2015). Big Data: Principles and Best Practices of Scalable Real-Time Data Systems, Manning Publications Co."},{"key":"ref_25","unstructured":"Hasani, Z., Kon-Popovska, M., and Velinov, G. (2014, January 7\u20139). Survey of technologies for real time big data streams analytic. Proceedings of the 11th International Conference on Informatics and Information Technologies, Las Vegas, NV, USA."},{"key":"ref_26","doi-asserted-by":"crossref","unstructured":"Androulaki, E., Karame, G.O., Roeschlin, M., Scherer, T., and Capkun, S. (2013, January 1\u20135). Evaluating user privacy in bitcoin. Proceedings of the International Conference on Financial Cryptography and Data Security, Okinawa, Japan.","DOI":"10.1007\/978-3-642-39884-1_4"}],"container-title":["Information"],"original-title":[],"language":"en","link":[{"URL":"https:\/\/www.mdpi.com\/2078-2489\/11\/4\/204\/pdf","content-type":"unspecified","content-version":"vor","intended-application":"similarity-checking"}],"deposited":{"date-parts":[[2025,10,11]],"date-time":"2025-10-11T09:17:30Z","timestamp":1760174250000},"score":1,"resource":{"primary":{"URL":"https:\/\/www.mdpi.com\/2078-2489\/11\/4\/204"}},"subtitle":[],"short-title":[],"issued":{"date-parts":[[2020,4,10]]},"references-count":26,"journal-issue":{"issue":"4","published-online":{"date-parts":[[2020,4]]}},"alternative-id":["info11040204"],"URL":"https:\/\/doi.org\/10.3390\/info11040204","relation":{},"ISSN":["2078-2489"],"issn-type":[{"value":"2078-2489","type":"electronic"}],"subject":[],"published":{"date-parts":[[2020,4,10]]}}}