Bronze Layer

Introduction to the Bronze Layer in Development

As you embark on exploring and utilizing the Bronze Layer within Ortege Lakehouse, it is crucial to understand the dynamic nature of the data in our development (dev) environment. The Bronze Layer, designed to house raw, unprocessed data directly ingested from diverse sources, serves as the foundational bedrock for all subsequent data analysis and processing within Ortege Lakehouse. At this stage, our focus is on the development environment, which is characterized by rapid iterations and frequent updates.

Data Fluidity and Expectations

In the development environment, data within the Bronze Layer is subject to change frequently. This fluidity is a natural and necessary part of the development process, allowing for the testing of new data sources, the refinement of ingestion processes, and the early detection of issues that could impact data quality or availability. Users interacting with the Bronze Layer in this environment should anticipate:

  • Frequent Updates: Data may be added, modified, or removed as part of ongoing development efforts to enhance data accuracy, completeness, and relevance.

  • Schema Evolution: The structure of datasets, including tables and views, may evolve as we refine our understanding of the data and its potential applications within Ortege Lakehouse.

  • Variable Data Quality: While we strive for high data quality across all layers, the Bronze Layer in the dev environment may include data that is in the early stages of quality assurance and validation.

Bitcoin

tbl_dev_br_blocks

col_name data_type comment
bits string The encoded form of the target threshold this block’s header hash must be less than or equal to.
chainwork string The total amount of work done on this block’s chain, in hexadecimal.
coinbase string The transaction containing the miner’s reward and any fees paid by transactions included in this block.
date string The date and time when this block was mined.
difficulty string The difficulty target for this block.
hash string The hash of this block’s header.
height bigint The height of this block in the blockchain.
merkle_root string The Merkle root of the transactions included in this block.
mint_reward double The total amount of new bitcoins generated by mining this block.
nonce string A random number used to try to find a valid block hash.
previous_block_hash string The hash of the previous block in the blockchain.
size bigint The size of this block in bytes.
stripped_size bigint The size of this block without witness data in bytes.
time timestamp The Unix timestamp when this block was mined.
total_fees double The total amount of fees paid by transactions included in this block.
total_reward double The total amount of bitcoins generated by mining this block, including both the mint reward and transaction fees.
transaction_count bigint The number of transactions included in this block.
weight bigint The weight of this block, used to calculate transaction fees.

tbl_dev_br_transactions

col_name data_type comment
block_date date The date when the block was added to the blockchain
block_hash string The unique identifier of the block
block_height bigint The height of the block in the blockchain
block_time timestamp The time when the block was added to the blockchain
coinbase string The address that received the block reward for mining the block
fee bigint The transaction fee paid by the sender
hex string The hexadecimal representation of the transaction
id string The unique identifier of the transaction
index bigint The index of the transaction in the block
input_count bigint The number of inputs in the transaction
input_value bigint The total value of all inputs in the transaction
inputs array An array of input objects containing information about each input in the transaction
is_coinbase boolean A boolean indicating whether the transaction is a coinbase transaction
lock_time bigint The lock time of the transaction
output_count bigint The number of outputs in the transaction
output_value bigint The total value of all outputs in the transaction
outputs array An array of output objects containing information about each output in the transaction
size bigint The size of the transaction in bytes
version bigint The version number of the transaction
virtual_size bigint The virtual size of the transaction

Stacks

tbl_dev_br_cycles

The tbl_dev_sl_cycles table provides a structured overview of cycles within the Stacks blockchain, starting from the genesis of Stacks 2.0. Each cycle represents a fixed number of blocks, facilitating the organization and analysis of blockchain data over time. This table is essential for understanding the temporal division of blockchain activity and aids in analyzing trends, rewards distributions, and other cycle-based metrics.

Column Description
cycle The sequential number of the cycle, starting from 0. Each cycle represents a group of 2100 blocks within the Stacks blockchain.
start_block The starting block number for the given cycle. This marks the first block in the cycle.
end_block The ending block number for the given cycle. This marks the last block in the cycle.

tbl_dev_br_blocks

col_name data_type comment
canonical boolean Canonical Blocks are the building blocks that contribute to the continuous, unbroken chain of blocks in the blockchain, each linking to its predecessor, forming the indelible ledger that blockchain is celebrated for. You expect this to be true.
number bigint stacks block number
hash string A unique identifier for each block, generated by applying a cryptographic hash function to the block’s data
index_block_hash string The hash of the index block.
parent_block_hash string The hash of the parent block.
timestamp bigint Timestamp of relevant block. If you use to_timestamp(timestamp) SQL code, you will get the actual date-time.
burn_block_hash string Corresponding Bitcoin block number’s hash. The block that is written in Bitcoin block’s hash.
burn_block_height bigint Corresponding Bitcoin block number. The block that is written in a Bitcoin block.
miner_txid string Corresponding transaction burned on Bitcoin block for the stacks block. Every stacks block is a transaction on Bitcoin. And this is that tx’s id. If you remove the 0x from the beginning, you will get the Bitcoin tx id.
transaction_count bigint Count of each transaction in the relevant block.
execution_cost_read_count bigint Captures the number of independent reads performed on the underlying data store.
execution_cost_read_length bigint The number of bytes read from the underlying data store.
execution_cost_runtime bigint Captures the number of cycles that a single processor would require to process the Clarity block. This is a unitless metric, meant to provide a basis for comparison between different Clarity code blocks.
execution_cost_write_count bigint Captures the number of independent writes performed on the underlying data store (see SIP-004).
execution_cost_write_length bigint The number of bytes written to the underlying data store.

tbl_dev_br_transactions

col_name data_type comment
hash string Unique identifier for the transaction
nonce bigint Random number used in mining process
fee_rate string Fee rate for the transaction
sender_address string Address of the sender
sponsored boolean Boolean indicating if the transaction is sponsored
post_condition_mode string Mode for post-conditions
post_conditions array Array of post-conditions
anchor_mode string Mode for anchoring
is_unanchored boolean Boolean indicating if the transaction is unanchored
parent_block_hash string Hash of the parent block
block_hash string Hash of the block containing the transaction
block_number bigint Block number containing the transaction
block_timestamp bigint Timestamp of the block containing the transaction
parent_burn_block_time bigint Timestamp of the parent burn block
canonical boolean Boolean indicating if the block is canonical
tx_index bigint Index of the transaction within the block
tx_status string Status of the transaction
tx_result struct Result of the transaction
microblock_hash string Hash of the microblock containing the transaction
microblock_sequence bigint Sequence number of the microblock containing the transaction
microblock_canonical boolean Boolean indicating if the microblock is canonical (Field marked as ‘null’ suggests missing information or not applicable.)
event_count bigint Count of events (Field marked as ‘null’ suggests missing information or not applicable.)
events array Array of events (Field marked as ‘null’ suggests missing information or not applicable.)
execution_cost_read_count bigint Number of independent reads performed on the underlying data store (Field marked as ‘null’ suggests missing information or not applicable.)
execution_cost_read_length bigint Number of bytes read from the underlying data store (Field marked as ‘null’ suggests missing information or not applicable.)
execution_cost_runtime bigint Number of cycles for processing (Field marked as ‘null’ suggests missing information or not applicable.)
execution_cost_write_count bigint Number of independent writes performed on the underlying data store (Field marked as ‘null’ suggests missing information or not applicable.)
execution_cost_write_length bigint Number of bytes written to the underlying data store (Field marked as ‘null’ suggests missing information or not applicable.)
tx_type string Type of the transaction (Field marked as ‘null’ suggests missing information or not applicable.)
token_transfer struct Details of token transfer if applicable (Field marked as ‘null’ suggests missing information or not applicable.)
coinbase_payload struct Data for coinbase transactions (Field marked as ‘null’ suggests missing information or not applicable.)
smart_contract struct Details of the smart contract if applicable (Field marked as ‘null’ suggests missing information or not applicable.)
contract_call struct Details of the contract call if applicable (Field marked as ‘null’ suggests missing information or not applicable.)
poison_microblock map<string,string> Information on poisoned microblock if applicable (Field marked as ‘null’ suggests missing information or not applicable.)

tbl_dev_br_contracts

col_name data_type comment
tx_hash string Transaction hash of the contract. Try searching it in explorer.hiro.so
canonical boolean Canonical Blocks are the building blocks that contribute to the continuous, unbroken chain of blocks in the blockchain, each linking to its predecessor, forming the indelible ledger that blockchain is celebrated for. You expect this to be true.
address string Address of the contract.
block_number bigint In which block this contract recorded in.
clarity_version bigint Clarity version of the contract.
source_code string The source code of the contract.
abi string The ABI (Application Binary Interface) of the contract.
is_stx20 boolean Indicates whether the contract is a Stacks 2.0 token (STX20).
is_nft boolean Indicates whether the contract is a non-fungible token (NFT).