blocks|key|1662439|text|ETL+=提取，变换和Load。使用转换位对数据库进行暂存。就我个人而言，我总是包括一个阶段DB和ETL步骤。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1662440|暂存数据库有助于将源数据与数据仓库事实和维度目的地等效的结构。它还将仓库和仓库ETL过程与源数据解耦。|1662441|如果您的数据仓库目标表很大程度上映射了您的生产DB表，其中只包含一些额外的维度字段，那么您可以忽略暂存数据库。这将为您节省一点开发时间。我不建议你这么做：|1662442|将最终将数据仓库解决方案直接绑定到源数据库。|ordered-list-item|1662443|很可能最终会有一个非常复杂的ETL步骤|1662444|由于您的源数据库在ETL过程中发生更改，可能会产生争用条件/孤儿记录。|1662445|数据仓库的人可能会对你发出“hrumph”式的声音。|1662446|1662447|1662448|最有可能的是，您将执行某种数据操作(将日期转换为DATE_DIM键，聚合值)，在这种情况下，暂存DB将帮助您将转换逻辑和计算与数据仓库操作(维度数据)分离开来。|1662449|您可能也遇到过这样的模式：|1662450|[PROD+DB]+-(ETL)->++[RAW+DB]+-(ETL)->+[STAGING+DB]+-(ETL)->+[DW+DB]++-(ETL)->+[DM+DB]|code-block|syntax|javascript|1662451|如果性能方面的考虑很重要，那么您可能想看看。在您的示例中，RAW_DB可能是生产数据库的1:1副本，而创建它的ETL步骤可能只是从最近的夜间备份中重新创建DB。(传统上，RAW_DB用于从各种外部源获取数据，每个字段都是纯文本，这些字段然后转换为预期的数据类型，并根据遇到的情况处理异常。当您有一个源，并且它是一个很好的强类型规范化数据库时，这并不是什么问题)|1662452|在这个RAW_DB中，下一个ETL进程将截断和填充暂存，以便暂存DB包含进入仓库的所有新的/更新的记录。|1662453|所有这些步骤的另一个额外好处是，它确实有助于调试奇怪的数据，因为对于任何给定的运行，您都可以看到每个差异数据库中的记录值，并确定哪个ETL进程引入了悲伤。|1662454|entityMap^0|8|2|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1B|8|@$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1E|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|1F|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|1G|8|@]|D|@]|E|$]]|$1|M|3|N|5|L|7|1H|8|@]|D|@]|E|$]]|$1|O|3|P|5|L|7|1I|8|@]|D|@]|E|$]]|$1|Q|3|R|5|L|7|1J|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1K|8|@]|D|@]|E|$]]|$1|T|3|-4|5|6|7|1L|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|1M|8|@]|D|@]|E|$]]|$1|W|3|X|5|6|7|1N|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|10|7|1O|8|@]|D|@]|E|$11|12]]|$1|13|3|14|5|6|7|1P|8|@]|D|@]|E|$]]|$1|15|3|16|5|6|7|1Q|8|@]|D|@]|E|$]]|$1|17|3|18|5|6|7|1R|8|@]|D|@]|E|$]]|$1|19|3|-4|5|6|7|1S|8|@]|D|@]|E|$]]]|1A|$]]

ETL = Extract, Transform and Load. Staging database's help with the Transform bit. Personally I always include a staging DB and ETL step.

A Staging database assists in getting your source data into structures equivalent with your data warehouse FACT and DIMENSION destinations. It also decouples your warehouse and warehouse ETL process from your source data.

If your data warehouse destination tables pretty much map your production DB tables with only some additional dimension fields then you could get away with ignoring the Staging Database. This will save you a little development time. I don't recommend this as you: 

<ol>
<li>Will end up tying your data warehouse solution directly to your
source database </li>
<li>Will most likely end up with a very complicated ETL step </li>
<li>May end up with race conditions/orphan records due to
changes in your source database during the ETL process</li>
<li>Data Warehousey people may make 'hrumph' type sounds at you</li>
</ol>

Most likely though you will be performing some sort of data manipulation (converting dates to DATE_DIM keys, aggregating values) in which case a staging DB will help you separate your transformation logic and calculations from your data warehouse operations (dimensioning data).

You may have also come across this sort of pattern:

<pre><code>[PROD DB] -(ETL)-&gt; [RAW DB] -(ETL)-&gt; [STAGING DB] -(ETL)-&gt; [DW DB] -(ETL)-&gt; [DM DB]
</code></pre>

which if performance considerations are important you may want to look at. In your case the RAW_DB could be an exact 1:1 copy of your production database and the ETL step that creates it might just be a recreate the DB from the most recent nightly backup. (Traditionally RAW_DB was used for getting data from various external sources with each field as pure text, these fields where then converted to their expected data type with exceptions handled as encountered. This is not so much of a problem when you have one source and its a nice strongly typed normalized database)

From this RAW_DB the next ETL process would truncate and populate staging such that the STAGING DB contains all the new/updated records that are going into the warehouse.

Another added benefit of all these steps is that it really assists in debugging weird data as for any given run you can see record values inside each of the difference databases and identify which ETL process is introducing the sadness.

blocks|key|3281249|text|以下要点摘自DWBI+Organization's的文章|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3281250|如果有下列任何方案之一，则可能需要暂放区域：|3281251|Delta加载：您的数据是从源增量读取的，您需要一个中间存储，其中可以临时存储增量数据集，以便进行转换。|ordered-list-item|style|BOLD|3281252|转换需要：在使用仓库中的数据之前，需要执行数据清理、验证等操作|3281253|De-coupling：您的处理过程需要很多时间，并且您不希望在整个处理过程中保持与源系统的连接(据推测，源系统一直被实际业务用户使用)，因此，您更愿意一次读取源系统中的数据，从源系统断开连接，然后在“自己的一侧”继续处理数据。|3281254|调试目的：您不必总是回到源代码，并且可以单独解决从暂存区域产生的问题(如果有的话)。|3281255|故障恢复：源系统可能是暂时的，数据的状态可能正在改变。如果您遇到任何上游故障，您可能无法重新提取您的数据，因为源已经改变了。有一个本地副本会有帮助|3281256|3281257|3281258|性能和减少的处理可能不仅仅是考虑因素。添加分期有时会增加latency+(即从业务事件发生到报告之间的时间延迟)。但我希望以上各点能帮助你作出更好的判断。|CODE|3281259|entityMap|0|LINK|mutability|MUTABLE|url|https://dwbi.org/etl/etl/52-why-do-we-need-staging-area-during-etl-load^0|6|J|0|0|0|0|7|0|0|4|0|0|B|0|0|4|0|0|4|0|0|0|S|7|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@$A|16|B|17|1|18]]|C|$]]|$1|D|3|E|5|6|7|19|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|1A|8|@$A|1B|B|1C|I|J]]|9|@]|C|$]]|$1|K|3|L|5|H|7|1D|8|@$A|1E|B|1F|I|J]]|9|@]|C|$]]|$1|M|3|N|5|H|7|1G|8|@$A|1H|B|1I|I|J]]|9|@]|C|$]]|$1|O|3|P|5|H|7|1J|8|@$A|1K|B|1L|I|J]]|9|@]|C|$]]|$1|Q|3|R|5|H|7|1M|8|@$A|1N|B|1O|I|J]]|9|@]|C|$]]|$1|S|3|-4|5|6|7|1P|8|@]|9|@]|C|$]]|$1|T|3|-4|5|6|7|1Q|8|@]|9|@]|C|$]]|$1|U|3|V|5|6|7|1R|8|@$A|1S|B|1T|I|W]]|9|@]|C|$]]|$1|X|3|-4|5|6|7|1U|8|@]|9|@]|C|$]]]|Y|$Z|$5|10|11|12|C|$13|14]]]]

Below points are taken from, <a href="https://dwbi.org/etl/etl/52-why-do-we-need-staging-area-during-etl-load">DWBI Organization's</a> article

Staging area may be required if you have any of the following scenarios:

<ol>
<li>Delta Loading: Your data is read incrementally from the source and you need an intermediate storage where incremental set of your data can be stored temporarily for transformation purpose</li>
<li>Transformation need: You need to perform data cleansing, validation etc. before consuming the data in the warehouse</li>
<li>De-coupling: Your processing takes lot of time and you do not want to remain connected to your source system (presumably the source system is being constantly used by the actual business users) during the entire time of your processing and, hence, prefer to just read the data from source system in one-go, disconnect from the source and then continue processing the data at your "own side"</li>
<li>Debugging purpose: You need not go back to your source all the time and you can troubleshoot issues (if any) from staging area alone</li>
<li>Failure Recovery: Source system may be transitory and the state of the data may be changing. If you encounter any upstream failure, you may not be in a position to re-extract your data as source has changed by that time. Having a local copy helps</li>
</ol>

Performance and reduced processing may not be only considerations. Adding a staging may sometimes increase <code>latency</code> (i.e. time delay between occurrence of a business incidence and it's reporting). But I hope above points will help you to make a better judgement.

blocks|key|3281270|text|使用中间暂存数据库有一些潜在的优点，这些优点可能适用于您的情况，也可能不适用于您。没有完美的、一刀切的解决方案。一些潜在的优势包括：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3281271|如果合适，您可以获取生产数据库的快照(可能已经有每日备份或热站点快照)，然后从还原的备份或快照中执行ETL操作。这可以节省生产数据库的负载。|unordered-list-item|3281272|您可能需要对ETL进行复杂的处理，这需要许多中间表，除了ETL进程之外，这些表没有任何用处。您可能不希望将数据仓库与这些中间表混在一起。|3281273|您的原始数据可能不是一次性可用的，在开始构建数据仓库的ETL过程之前，您需要在某个地方进行积累。|3281274|您的数据仓库可能具有ETL无法满足的生产窗口要求，因此您需要分阶段进行“输出”(即数据仓库的新记录)，而不是或添加到生产数据库中。|3281275|生产系统可能处于高度安全的环境中，无论出于何种原因，都可能决定不允许ETL过程完全访问原始生产数据。控制生产数据库的组可能只希望将必要的数据提取到暂存数据库，以便ETL进程只能看到它需要什么。我已经看到生产系统和ETL过程是由不同的第三方供应商管理的。|3281276|可能是您的ETL过程创建了大型中间表。有时候，如果您从ETL暂存区域的空模型数据库开始，然后每天“丢弃”空间，而不是尝试以更外科的方式恢复空间，就像生产或报告数据库那样，空间管理会更容易一些。|3281277|3281278|3281279|也有一些可能的缺点，这对你来说可能重要，也可能无关紧要。其中最主要的是必须有另一个数据库服务器。如果您使用同一台服务器来承载生产和/或数据仓库数据库，那么许多优点可能是毫无意义的。|3281280|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|W|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|X|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Y|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|Z|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|10|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|11|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

There are a few potential advantages of using an intermediary staging database, which may or may not apply to your situation. There is no perfect, one-size fits all solution. Some of the potential advantages include:

<ul>
<li>If it is appropriate, you can take a snapshot of your production database (you may have a daily backup or hot-site snapshot already) and then do your ETL from the restored backup or snapshot. This could save load on your production database.</li>
<li>You may need complicated processing for your ETL which requires many intermediate tables which have no use except for the ETL process. You may not want to clutter your data warehouse with these intermediary tables.</li>
<li>Your raw data may not be available all at once and you need somewhere to accumulate it before starting your ETL process to build your data warehouse.</li>
<li>Your data warehouse may have production window requirements which can't be met by your ETL and so you need to stage your "output" (i.e. new records for the data warehouse) rather than or in addition to your production database.</li>
<li>The production system may be in a highly secured environment and for whatever reason a decision may have been made to not allow the ETL process full access to the raw production data. The group that controls the production database may want to extract only the necessary data to a staging database so that the ETL process can only see what it needs. I've seen this where the production system and ETL process are managed by different third-party vendors.</li>
<li>It may be that your ETL process creates large intermediate tables. Sometimes space management is easier if you start with an empty model database for your ETL staging area and then "throw it away" each day rather than trying to recover the space in a more surgical way, as you might do with a production or reporting database.</li>
</ul>

There are possible disadvantages too, which may or may not matter to you. Chief among these is having to have another database server. A lot of the advantages could be meaningless if you are using the same server to host the production and/or data warehouse databases.

blocks|key|3281292|text|如果我们能在飞行中处理这件事的话，那真的不是必要的。但我们可以吗？以下是您无法避免暂存区域的几个原因:+1.源系统只能在特定时间段内进行提取，这通常比总体数据加载时间要短。在失去与源系统的连接之前，提取并保存这些信息是一个好主意。2.您希望根据需要将两个或多个不同系统连接在一起的某些条件来提取数据。例如，您只想提取那些在其他系统中也存在的客户。您将无法执行将两个物理上不同的数据库中的两个表连接起来的SQL查询。3.不同的源系统对数据提取有不同的分配时间。4.数据仓库的数据加载频率与源系统的刷新频率不匹配。5.从同一组源系统中提取的数据将用于多个地方(数据仓库加载、ODS加载、第三方应用程序等)。6.+ETL过程涉及复杂的数据转换，需要额外的空间来暂存数据7。有特定的数据协调/调试要求，需要在加载数据验证之前、期间或之后使用暂存区域。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3281293|显然，在数据加载期间，分期区域提供了很大的灵活性。难道我们不应该一直都有一个单独的集结地吗？有舞台区域会有什么影响吗？是的有几个。1.暂存区域增加延迟--即源系统更改在数据仓库中生效所需的时间。在许多实时/接近实时的应用中，分期区域内的数据占据了额外的空间2。对我来说，在所有实际意义上，拥有一个分期区域的好处大于它的问题。因此，总的来说，我建议在数据仓库项目中指定一个特定的分阶段区域。|3281294|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Really staging area is not a necessity if we can handle it on the fly. But can we? Here are a few reasons why you can’t avoid a staging area:
1. Source systems are only available for extraction during a specific
time slot which is generally lesser than your overall data loading
time. It’s a good idea to extract and keep things at your end before
you lose the connection to the source systems.
2. You want to extract data based on some conditions which require you to join two or more different systems together. E.g. you want to only extract those customers who also exist in some other system. You will not be able to perform a SQL query joining two tables from two physically different databases.
3. Various source systems have different allotted timing for data extraction.
4. Data warehouse’s data loading frequency does not match with the refresh frequencies of the source systems
5. Extracted data from the same set of source systems are going to be used in multiple places (data warehouse loading, ODS loading, third-party applications etc.)
6. ETL process involves complex data transformations that require extra space to temporarily stage the data
7. There is specific data reconciliation / debugging requirement which warrants the use of staging area for pre, during or post load data validations

Clearly staging area gives lot flexibility during data loading. Shouldn’t we have a separate staging area always then? Is there any impact of having a stage area? Yes there are a few.
1. Staging area increases latency – that is the time required for a change in the source system to take effect in the data warehouse. In lot of real time / near real time applications, staging area is rather avoided Data in the staging area occupies extra space
2. To me, in all practical senses, the benefit of having a staging area outweighs its problems. hence, in general I will suggest designating a specific staging area in data warehousing
 projects.

I am in process of designing a Data Warehouse Architecture.
While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly suggested following two approaches - 

<blockquote>
 <ol>
 <li>Production DB ----> Data Warehouse (Star Schema) ----> OLAP Cube</li>
 <li>Production DB ----> Staging Database ----> Data Warehouse (Star Schema) ----> OLAP Cube</li>
 </ol>
</blockquote>

I am still not sure which one is the better approach in terms of Performance and reducing processing load on Production database.

Which approach you find better while designing Data Warehouse ?

Benefits of using Staging Database while designing Data Warehouse

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在设计一个数据仓库架构。在探索从生产中提取数据并放入数据仓库的各种选项时，我遇到了许多文章，其中主要建议采用以下两种方法：生产数据库生产数据库我仍然不确定哪一种方法在性能和减少生产数据库的处理负载方面是更好的方法。在设计数据仓库时，您发现哪种方法更好？

问在设计数据仓库时使用暂存数据库的好处
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在设计数据仓库时使用暂存数据库的好处EN